SQL Interview Questions with Detailed Answers
Below are some of the generally asked SQL interview questions with their solutions. These SQL interview questions are constructed to evaluate your grasp of basic SQL fundamentals, which include simple queries, complex database operations, and so on. Going through these SQL interview questions is highly essential as it will allow you to tilt the interview in your favor and will also prove your technical expertise. With the help of the this SQL interview questions, you will get a chance to enhance your thinking and obtain real examination of your knowledge of SQL, which you can get the full of headway in job interviews.
There is an extensive list of SQL interview questions that cover many important topics such as joins, subqueries, and normalization. The command of these SQL interview questions will enhance your proficiency in tackling different SQL issues that might arise during an interview. Going through the usual SQL interview questions and their answers is an indispensable part of your journey in the IT department. Remember to practice your SQL interview questions so you are ready for your next interview.
1. What is SQL and what are its types?
Answer:
- SQL, which is short for Structured Query Language, is a standard programming language that allows for the manipulation and management of relational databases and was made for this purpose.
- SQL is what you need to ensure that all the required database activities, like querying, updating, and managing data, are done successfully.
- It is endowed with the aforesaidable capabilities and the others like retrieval of, insertion, upgrading of, and deletion of data.
SQL falls into the following categories:
1.DML (Data Manipulation Language):
These are SQL commands are which used for retraction, removal, insertion, and upgradation of data in a database. An example is that the commands below are with the database:
- SELECT: Retrieves data from the database.
- INSERT: Adds new data into a table.
- UPDATE: Modifies existing data.
- DELETE: Removes data from a table.
2.DDL (Data Definition Language):
These were used to define and modify database structures such as tables, schemas, and indexes. For example, the following commands were included:
- CREATE: Creates new tables, views, and indexes.
- ALTER: Modifies existing database objects.
- DROP: Deletes database objects.
- TRUNCATE: The statement of Removing all records from a table that is still there of the table itself.
3.DCL (Data Control Language):
These are commands that carry out access control to the database. Examples include:
- GRANT: Grants privileges to a user.
- REVOKE: Removes privileges from a user.
4.TCL (Transaction Control Language):
These are commands that perform transaction management within the database.Examples include:
- COMMIT: Saves all changes made in the transaction.
- ROLLBACK: A feature that reverses any changes made in a transaction.
- SAVEPOINT: A point in the transaction where you can back to if the transaction is rolled back.
2. What are the different types of JOINs in SQL?
Answer:
- A JOIN operation in SQL is used to combine the rows from one or several tables if the records in these tables are related. There are various kinds of JOINs in SQL:
1.INNER JOIN:
- This join returns the rows when there is a match in both tables.
- If there is no match, the row is excluded from the output.
SELECT * FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
2.LEFT JOIN (or LEFT OUTER JOIN):
- This join retrieves all the records from the left table and the related records from the right table which are connected with the columns from the left table.
- If there is no match, NULL is used for the right table columns.
SELECT * FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
3.RIGHT JOIN (or RIGHT OUTER JOIN):
- This join gets all the rows that exist in the right table and the rows from the left table that are associated with columns from the right table.
- If there is no match, NULL is used for the left table columns.
SELECT * FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
4.FULL JOIN (or FULL OUTER JOIN):
- This join returns all rows from the tables when there is a match in one of the tables.
- It returns NULL for non-matching rows in both tables.
SELECT * FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;
5.CROSS JOIN:
- This join produces the Cartesian product of two tables.
- It combines every row of the first table with every row of the second table.
SELECT * FROM employees
CROSS JOIN departments;
6.SELF JOIN:
- This is a case where a table is joined with itself.
- It is often used in this situation when we want to compare the rows in the same table.
SELECT e1.name, e2.name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_id;
3. Explain the concept of Normalization and its types.
Answer:
- Normalization refers to the setting of data in a database so as to remove redundancies and ensure data integrity.
- Basically, separation makes tables larger by making smaller related tables and using the connections between them.
- Irregularities associated with insertion, updating, and deletion are the cases that normalization tries to avoid.
There are various types of normalization, namely:
1.First Normal Form (1NF):
- A table is in 1NF if it contains neither a repeating group nor an array.
- The table should have only atomic (indivisible) values, and no other data should be present.
- A single identifier (primary key) must be the only record (row) in each of them.
- Example: A table with multiple phone numbers in one column violates 1NF.
2.Second Normal Form (2NF):
- A table is in 2NF if it is in 1NF and all non-key columns are fully dependent on the primary key.
- Here the term “part of the primary key” is translated into “part of — the other nonkey columns (in a composite key).
- This means no partial dependency exists (where a non-key column depends on part of the primary key).
- Example: In a table with a composite primary key (e.g., student_id, course_id), if the student’s name is stored, it violates 2NF because the student’s name is dependent only on student_id.
3.Third Normal Form (3NF):
- A table is in 3NF when it is in 2NF and there is no transitive dependency.
- Which means no columns should depend on other non-key columns.(non-key columns should not depend on other non-key columns)
- Example: If a table holds both the student’s address and the student’s city, with the city dependent on the address, it is against 3NF standards.
4.Boyce-Codd Normal Form (BCNF):
- A stricter version of 3NF, a table is in BCNF if every determinant is a candidate key.
- This means that no partial dependency can hold, i.e. such an attribute factor in that is neither a candidate key nor a prime attribute of some candidate.
- For example, A table with functional dependencies like (Course → Instructor), but the Course is not a primary key, violates BCNF.
5.Fifth Normal Form (5NF):
- A table is in 5NF if it is in 4NF and cannot be decomposed any further without losing information.
- This is typically used in very complex databases.
6.Fourth Normal Form (4NF):
- A table is in 4NF if it is in Boyce-Codd Normal Form and has no multi-valued dependencies.
- A multi-valued dependence is when one attribute in a table determines multiple values of another attribute.
4. What is the difference between UNION and UNION ALL?
Answer:
- Both UNION and UNION ALL are used to merge the result sets of two or more SELECT queries.
- Nevertheless, the following are the differences.
1.UNION:
- Folds the sets of the result and removes the repeating rows.
- It conducts the distinct operation on the obtained set.
SELECT name FROM employees
UNION
SELECT name FROM contractors;
2.UNION ALL:
- Merges the result sets and includes all the rows even if there are duplicates.
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;
Key Difference:
- UNION removes duplicates and union retains all duplicates.
5. What is a subquery in SQL? Explain the types of subqueries.
Answer:
- A subquery is a query within another query.
- It helps you to obtain the work you want in a single query.
- Sub-query that can occur SELECT, INSERT, UPDATE, DELETE, or WHERE clauses
The three main sub-query forms can be named as the following:
1.Single-row Subquery:
- A subquery is like this. It returns one row with a single column value.
- A common usage for this is the symphony of syntactic operators like =, < , >, and so on.
SELECT name FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = ‘Sales’);
2.Multi-row Subquery:
- A subquery now gives more than one result but just from a particular column.
- It works along with operators like IN, ANY, or ALL.
SELECT name FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = ‘Sales’);
3.Correlated Subquery:
- A subquery is a group of SQL SELECT statements that refer to one or more columns of the outer query.
- It is checked for every valid row from the outer query.
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
6. What is an index in SQL? Explain the types of indexes.
Answer:
- An index is a database object that is used for the purpose of speeding up the processes of selecting data from a table still at the cost of the extra consumed space and maintenance overhead.
- Indexes are often made on columns that are searched frequently, joined, or used in a sort.
The two major categories of indexes are as follows:
1.Unique Index:
- Makes sure that the values in the indexed column(s) are unique.
- It is automatically created when a primary key or unique constraint is defined on a table.
CREATE UNIQUE INDEX idx_employee_id ON employees(employee_id);
2.Non-Unique Index:
- Allows duplicate values in the indexed column(s).
- It is commonly used to improve query performance on non-unique columns.
CREATE INDEX idx_department_id ON employees(department_id);
3.Composite Index:
- An index created on multiple columns.
- It improves performance on queries that filter on more than one column.
CREATE INDEX idx_name_dept ON employees(name, department_id);
4.Full-text Index:
- Used for indexing large text fields for searching within text-based data, typically for searching keywords in documents or articles.
7. What is a primary key and a foreign key?
Answer:
1.Primary Key:
- A primary key is a column or a set of columns that uniquely identify each row in a table.
- The values in the primary key column(s) must be unique and cannot contain NULL values.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100)
);
2.Foreign Key:
- A foreign key is a column or set of columns in one table that refers to the primary key in another table.
- It creates a relationship between two tables.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
8. Explain the concept of transactions in SQL.
Answer:
A transaction is a logical unit of work that contains one or more SQL operations. Transactions ensure data integrity by following the ACID (Atomicity, Consistency, Isolation, Durability) properties:
- Atomicity: Makes sure any operation within a transaction is either finished or none. If it fails, all the operations will be literally rolled back.
- Consistency: Ensures that a transaction is responsible for the whole database state change from a legitimate or valid state to an equally valid state.
- Isolation: Makes the process of a transaction separated from other processes. Since other processes “don’t see” the intermediate states of the transactions, there is no way for the other transactions to make updates to the transaction.
- Durability: If a transaction is committed, it will be durable so that any changes made will stay even if there are some system failures.
BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 1;
COMMIT; — They become permanent for all the times the changes are made
- If there is an error:
ROLLBACK; — Again make undone that we have previously done