SQL Interview Questions Flashcards
What is SQL?
SQL stands for Structured Query Language. It is a database computer language, designed to retrieve and manage data, create and modify DB schema, etc.
What is a Database?
Systematically organized or structured repository of indexed information (usually as a group of linked data files) that allows easy retrieval, updating, analysis, and output of data. Stored usually in a computer, this data could be in the form of graphics, reports, scripts, tables, text, etc., representing almost every kind of information. Most computer applications (including antivirus software, spreadsheets, word-processors) are databases at their core.
What is a Query?
A query is your request to the database to retrieve information. What is a subquery? A subquery is a query within another query. The outer query is called the main query, and the inner query is called the subquery. The subquery is always executed first, and the results of the subquery is passed on to the main query.
What is the DBMS?
DBMS (Database Management System) is software that controls the organization, storage, retrieval, security and integrity of data in a database.
What is a Primary Key?
The Primary Key is a unique identifier of every record in the database table. A Primary Key is a column in a database where each row has a unique value. Each table has only one primary key. No NULL values are allowed.
A Unique Key is a column or group of columns that together hold unique values. A table can have more than one unique key.
For example, in a list of American citizens, the column with social security numbers (SSN) would be a Primary Key whereas the first and last name columns combined with phone number would be a Unique Key.
What is a Foreign Key (SQL)?
A Foreign Key is a column (or combination of columns) that is used to establish a relationship, linking the table rows to another table’s Primary Key (a.k.a. Referential Integrity). A Foreign Key enforces the relationship between two tables via the Primary Key of the parent table and the Foreign Key of the development child table.
A Foreign Key is usually not unique (one-to-many relation) and always points to a Primary Key.
What is a SQL Join?
A SQL Join is an instruction to combine data from two sets of data (i.e. two tables). Before we dive into the details of a SQL Join, let’s briefly discuss why someone would want to perform a SQL Join.
SQL Join Example
Let’s say we want to find all orders placed by a particular customer. We can do this by joining the customers and orders tables together using the relationship established by the customer_id key.
What are the types of Join and explain each?
There are various types of Join that can be used to retrieve data and it depends on the relationship between the tables.
Inner Join:
Inner Join return rows when there is at least one match of rows between the tables.
Right Join:
Right Join return rows that are common between the tables and all rows of the right-hand side table. Simply, it returns all the rows from the right-hand side table even though there are no matches in the left-hand side table.
Left Join:
Left Join return rows that are common between the tables and all rows of the left-hand side table. Simply, it returns all the rows from the left-hand side table even though there are no matches in the right-hand side table.
Full Join:
Full Join return rows when there are matching rows in any one of the tables. This means that it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
What are Tables and Fields?
A Table is a set of data that is organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A Table has a specified number of Columns called Fields but can have any number of Rows that are called a record.
Example:
Table: Employee.
Field: Emp ID, Emp Name, Date of Birth.
Data: 201456, David, 11/15/1960.
What are the DELETE and TRUNCATE Commands?
A DELETE command is used to remove rows from the table, and a WHERE clause can be used for a conditional set of parameters. Commit and Rollback can be performed after a delete statement.
The TRUNCATE command removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.
What is a Constraint?
A constraint can be used to specify the limit on the data type of table. A constraint can be specified while creating or altering the table statement. Sample of constraints are:
NOT NULL
CHECK
DEFAULT
UNIQUE
PRIMARY KEY
FOREIGN KEY
What is a CLAUSE?
A SQL clause is defined to limit the result set by providing the condition to the query. This usually filters some rows from the whole set of records. Example – Query that has WHERE condition Query that has HAVING condition.