Top 40+ SQL Queries Interview Questions with Detailed Answers- Basic to Advanced

SQL, or Structured Query Language, is widely used for managing and manipulating relational databases. Whether you’re a data analyst, database administrator, or software developer, a solid understanding of SQL is crucial. This guide compiles the top SQL queries interview questions and provides detailed answers to help you prepare effectively.

Our guide covers essential SQL concepts such as different types of JOINs, finding duplicate records, writing complex queries, understanding primary keys, normalization, indexing, and more. With these questions and answers, you’ll be well-prepared to handle even the most challenging SQL interview scenarios.

SQL Queries Interview Questions with Detailed Answers

1. What is SQL?
2. Explain the different types of JOINs in SQL.
3. How do you find duplicate records in a table?
4. Write a query to retrieve the nth highest salary from a table.
5. What is an aggregate function? Provide examples.
6. Explain what a primary key is.
7. How can you retrieve unique values from a column?
8. Write a query to get employees who earn more than their managers.
9. What is normalization? Why is it important?
10. Explain what an index is.
11. Write a query to find employees who do not belong to any department.
12. How do you update records in SQL?
13. What are constraints in SQL? Name some types.
14. Write a query to fetch all employees who joined in the year ‘2020’.
15. How do you delete duplicate rows in SQL?
16. Explain what subqueries are and provide an example.
17. What is the difference between UNION and UNION ALL?
18. Write a query to find total sales amount for each product in each region.
19. How do you retrieve only odd rows from a table?
20. Explain what transaction control commands are with examples.
21. What is the difference between WHERE and HAVING clauses in SQL?
22. Write a query to get the second highest salary of each department.
23. Explain the COALESCE function in SQL and give an example.
24. Write a SQL query to transpose rows into columns.
25. What are TRUNCATE, DELETE, and DROP commands, and how do they differ?
26. How do you perform a Full Outer Join in SQL?
27. Write a query to retrieve the cumulative salary of employees ordered by their joining date.
28. Explain the concept of a Self Join with an example.
29. Write a query to find the median salary in a table.
30. What is a Composite Key? Provide an example.
31. Write a query to find common records between two tables.
32. Explain the concept of EXISTS and provide an example.
33. Write a query to retrieve employees who joined on the last day of each month.
34. What is the GROUP BY clause used for in SQL? Give an example.
35. Write a query to delete rows with a specific condition using DELETE JOIN.
36. Explain CASE in SQL and give an example.
37. Write a query to list all departments with more than 5 employees.
38. What are SQL wildcards and give examples of how to use them?
39. Write a query to retrieve the maximum salary of each department, along with the department name.
40. How do you write recursive queries in SQL? Provide an example.

1. What is SQL?

Answer:

SQL (Structured Query Language) is a standard programming language specifically designed for managing and manipulating relational databases. It allows users to perform various operations such as querying data, updating records, inserting new data, and deleting existing data. SQL is widely used across different database systems like MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

2. Explain the different types of JOINs in SQL.

Answer: SQL JOINs are used to combine records from two or more tables based on related columns. The main types of JOINs include:

• INNER JOIN: Returns records that have matching values in both tables.

  SELECT A.*, B.*
  FROM TableA A
  INNER JOIN TableB B ON A.common_field = B.common_field;

• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. If there is no match, NULL values are returned for columns from the right table.

  SELECT A.*, B.*
  FROM TableA A
  LEFT JOIN TableB B ON A.common_field = B.common_field;

• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. If there is no match, NULL values are returned for columns from the left table.

  SELECT A.*, B.*
  FROM TableA A
  RIGHT JOIN TableB B ON A.common_field = B.common_field;

• FULL OUTER JOIN: Returns all records when there is a match in either left or right table records. If there is no match, NULL values are returned for non-matching columns.

  SELECT A.*, B.*
  FROM TableA A
  FULL OUTER JOIN TableB B ON A.common_field = B.common_field;

3. How do you find duplicate records in a table?

Answer:

To find duplicate records in a table based on specific columns, you can use the GROUP BY clause along with the HAVING clause to filter groups with more than one occurrence.

SELECT column1, column2, COUNT(*)
FROM your_table
GROUP BY column1, column2
HAVING COUNT(*) > 1;

4. Write a query to retrieve the nth highest salary from a table.

Answer: To find the nth highest salary without using LIMIT or TOP, you can use a subquery:

SELECT DISTINCT Salary 
FROM Employee 
WHERE Salary NOT IN (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT n-1);

This query selects distinct salaries and excludes those that are higher than the nth salary.

5. What is an aggregate function? Provide examples.

Answer:

Aggregate functions perform calculations on multiple rows of a single column of a table and return a single value. Common aggregate functions include:

• SUM: Calculates the total sum of a numeric column.

  SELECT SUM(Salary) AS TotalSalary FROM Employee;

• AVG: Calculates the average value of a numeric column.

  SELECT AVG(Salary) AS AverageSalary FROM Employee;

• COUNT: Counts the number of rows that match a specified condition.

  SELECT COUNT(*) AS EmployeeCount FROM Employee WHERE Department = 'HR';

6. Explain what a primary key is.

Answer:

A primary key is a unique identifier for each record in a database table. It ensures that no two rows have the same value in that column(s) and cannot contain NULL values. Primary keys are essential for maintaining data integrity and establishing relationships between tables.

7. How can you retrieve unique values from a column?

Answer: To retrieve unique values from a column in SQL, you can use the DISTINCT keyword:

SELECT DISTINCT column_name FROM your_table;

8. Write a query to get employees who earn more than their managers.

Answer: This requires a self-join where you compare employee salaries with their respective manager’s salary:

SELECT E1.EmployeeName 
FROM Employees E1 
JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID 
WHERE E1.Salary > E2.Salary;

9. What is normalization? Why is it important?

Answer:

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them. Normalization helps eliminate duplicate data and ensures that data dependencies make sense.

10. Explain what an index is.

Answer:

An index is a database object that improves the speed of data retrieval operations on a database table at the cost of additional space and slower writes (inserts/updates). An index creates an entry for each value in the indexed column(s) and helps speed up queries by allowing quick lookups.

11. Write a query to find employees who do not belong to any department.

Answer:

SELECT * 
FROM Employees 
WHERE DepartmentID IS NULL;

This query retrieves all employees whose department ID is not assigned.

12. How do you update records in SQL?

Answer: To update existing records in a table, use the UPDATE statement along with the SET clause:

UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE Department = 'Sales';

This increases salaries by 10% for employees in the Sales department.

13. What are constraints in SQL? Name some types.

Answer: Constraints are rules applied to columns in tables to enforce data integrity. Common types of constraints include:

• NOT NULL: Ensures that a column cannot have NULL values.
• UNIQUE: Ensures all values in a column are different.
• FOREIGN KEY: Ensures referential integrity between two tables.
• CHECK: Ensures that all values in a column satisfy certain conditions.

14. Write a query to fetch all employees who joined in the year ‘2020’.

Answer:

SELECT * 
FROM Employees 
WHERE YEAR(JoiningDate) = '2020';

This retrieves all employee records where the joining date falls within the year 2020.

15. How do you delete duplicate rows in SQL?

Answer: To delete duplicate rows while keeping one instance, you can use Common Table Expressions (CTE) or subqueries:

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY (SELECT NULL)) AS row_num 
    FROM your_table
)
DELETE FROM CTE WHERE row_num > 1;

16. Explain what subqueries are and provide an example.

Answer: A subquery is a query nested inside another query. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements to perform operations based on results from another query.

Example:

SELECT EmployeeName 
FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

This retrieves names of employees whose salary exceeds the average salary of all employees.

17. What is the difference between UNION and UNION ALL?

Answer:

• UNION: Combines results from two or more SELECT statements while removing duplicates.

SELECT ColumnName FROM TableA 
UNION 
SELECT ColumnName FROM TableB;

• UNION ALL: Combines results without removing duplicates.

SELECT ColumnName FROM TableA 
UNION ALL 
SELECT ColumnName FROM TableB;

18. Write a query to find total sales amount for each product in each region.

Answer:

SELECT ProductID, RegionID, SUM(SaleAmount) AS TotalSales 
FROM SalesData 
GROUP BY ProductID, RegionID;

This aggregates sales amounts based on product and region.

19. How do you retrieve only odd rows from a table?

Answer:

SELECT * 
FROM your_table 
WHERE MOD(RowNumColumn,2) = 1; -- Assuming RowNumColumn contains row numbers.

This retrieves rows where row numbers are odd.

20. Explain what transaction control commands are with examples.

Answer: Transaction control commands manage transactions within databases to ensure data integrity. Common commands include:

• COMMIT: Saves all changes made during the current transaction.

COMMIT;

• ROLLBACK: Undoes changes made during the current transaction if an error occurs.

ROLLBACK;

These commands ensure that either all operations within a transaction are completed successfully or none at all, maintaining consistency within databases.

21. What is the difference between WHERE and HAVING clauses in SQL?

Answer:

The WHERE clause is used to filter records before any groupings are made, while the HAVING clause is used to filter records after groupings. WHERE works on individual rows of data and does not work with aggregate functions. On the other hand, HAVING works with grouped data and can be used with aggregate functions.

Example:

SELECT department, SUM(salary)
FROM employees
WHERE salary > 5000
GROUP BY department
HAVING SUM(salary) > 100000;

In this example, WHERE filters rows with salaries greater than 5000 before aggregation, and HAVING filters groups where the total salary in the department exceeds 100,000.

22. Write a query to get the second highest salary of each department.

Answer:

To get the second highest salary for each department, use the DENSE_RANK() window function, which assigns ranks to each salary per department.

SELECT department, salary
FROM (
    SELECT department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
    FROM employees
) AS ranked_salaries
WHERE rank = 2;

This query partitions the data by department, ranks salaries in descending order, and then retrieves the second-highest salary by filtering on rank = 2.

23. Explain the COALESCE function in SQL and give an example.

Answer:

The COALESCE function returns the first non-null value from a list of expressions. It is useful for handling NULL values in SQL queries.

Example:

SELECT employee_id, COALESCE(manager_id, 'No Manager') AS manager
FROM employees;

This query returns “No Manager” if the manager_id is NULL for an employee.

24. Write a SQL query to transpose rows into columns.

Answer: To transpose rows into columns, use the CASE statement along with an aggregation function like MAX().

SELECT department,
    MAX(CASE WHEN job_title = 'Manager' THEN employee_name END) AS Manager,
    MAX(CASE WHEN job_title = 'Engineer' THEN employee_name END) AS Engineer,
    MAX(CASE WHEN job_title = 'HR' THEN employee_name END) AS HR
FROM employees
GROUP BY department;

This query transposes the job_title rows into columns for each department.

25. What are TRUNCATE, DELETE, and DROP commands, and how do they differ?

Answer:

• DELETE: Removes rows based on a condition. Can be rolled back. It is slower and can use a WHERE clause.
• TRUNCATE: Removes all rows from a table quickly and cannot be rolled back (in most RDBMS). Cannot use a WHERE clause.
• DROP: Deletes the entire table structure along with its data, and it cannot be rolled back.

26. How do you perform a Full Outer Join in SQL?

Answer: A Full Outer Join returns all records where there is a match in either the left or right table. It combines the result of Left and Right Joins.

SELECT a.id, a.name, b.order_id
FROM customers a
FULL OUTER JOIN orders b ON a.id = b.customer_id;

This query returns all customers and their orders, including customers without orders and orders without customers.

27. Write a query to retrieve the cumulative salary of employees ordered by their joining date.

Answer: To calculate cumulative salary, use the SUM() window function with ORDER BY.

SELECT employee_id, salary, joining_date,
    SUM(salary) OVER (ORDER BY joining_date) AS cumulative_salary
FROM employees
ORDER BY joining_date;

This query calculates the cumulative salary by adding each salary sequentially according to the joining date.

28. Explain the concept of a Self Join with an example.

Answer: A Self Join joins a table with itself to compare rows within the same table.

Example:

SELECT a.employee_id, a.name, b.name AS manager_name
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.employee_id;

This query retrieves each employee along with their manager’s name.

29. Write a query to find the median salary in a table.

Answer: Finding the median requires using a window function to find the middle salary(s).

SELECT AVG(salary) AS median_salary
FROM (
    SELECT salary, 
           ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
           COUNT(*) OVER () AS total_rows
    FROM employees
) AS sorted_salaries
WHERE row_num IN ((total_rows + 1) / 2, (total_rows + 2) / 2);

This query calculates the median by averaging the middle salary(s).

30. What is a Composite Key? Provide an example.

Answer: A Composite Key consists of two or more columns used together as a primary key. This is used when a single column is insufficient to ensure uniqueness.

Example:

CREATE TABLE course_enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

31. Write a query to find common records between two tables.

Answer: To find common records, use an INNER JOIN on matching columns.

SELECT a.*
FROM table1 a
INNER JOIN table2 b ON a.id = b.id;

This query returns rows where id exists in both tables.

32. Explain the concept of EXISTS and provide an example.

Answer: EXISTS is used to test for the existence of any row in a subquery.

SELECT employee_id, name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id);

This query retrieves employees only if they belong to an existing department.

33. Write a query to retrieve employees who joined on the last day of each month.

Answer: To retrieve employees who joined on the last day of each month, use date functions.

SELECT *
FROM employees
WHERE joining_date = LAST_DAY(joining_date);

This query checks if joining_date is the last day of its respective month.

34. What is the GROUP BY clause used for in SQL? Give an example.

Answer: The GROUP BY clause groups rows with the same values in specified columns and allows aggregate functions to operate on each group.

Example:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

This query counts employees in each department.

35. Write a query to delete rows with a specific condition using DELETE JOIN.

Answer: To delete rows from one table based on a condition in another table, use DELETE JOIN.

DELETE e
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

This deletes employees who belong to the “Sales” department.

36. Explain CASE in SQL and give an example.

Answer: The CASE statement is used for conditional logic in SQL.

Example:

SELECT name,
    CASE
        WHEN salary > 10000 THEN 'High'
        WHEN salary > 5000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_grade
FROM employees;

This query assigns a salary grade based on each employee’s salary.

37. Write a query to list all departments with more than 5 employees.

Answer: To list departments with more than 5 employees, use GROUP BY and HAVING.

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

38. What are SQL wildcards and give examples of how to use them?

Answer: Wildcards in SQL are used with LIKE for pattern matching:

• % matches zero or more characters.
• _ matches a single character.

Example:

SELECT name
FROM employees
WHERE name LIKE 'J%';

This finds employees whose names start with “J”.

39. Write a query to retrieve the maximum salary of each department, along with the department name.

Answer: To get the maximum salary for each department, join departments and employees tables and use GROUP BY.

SELECT d.department_name, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

40. How do you write recursive queries in SQL? Provide an example.

Answer: Recursive queries are written using a WITH clause, often called a Common Table Expression (CTE).

Example:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

This query finds hierarchical relationships between employees and their managers.

Learn More: Carrer Guidance

PySpark Interview Questions and Answers- Basic to Advanced

Kubernetes Interview Questions and Answers- Basic to Advanced

Embedded C Interview Questions with Detailed Answers- Basic to Advanced

Zoho Technical Support Engineer Interview Questions and Answers

Cucumber Interview Questions with Detailed Answers

Accounts Payable Interview Questions with Detailed Answers

Entity Framework Interview Questions with detailed answers- Basic to Advanced

Vue Js interview questions and answers- Basic to Advance

CN Interview Questions and Answers for Freshers

Desktop Support Engineer Interview Questions and Detailed Answer- Basic to Advanced

Apex Interview Questions and Answers- Basic to Advance