Advanced SQL Interview Questions and Answers

1. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

ROW_NUMBER() assigns a unique sequential number to each row.

RANK() gives the same rank to ties but leaves gaps.

DENSE_RANK() gives the same rank to ties without leaving gaps.

SELECT name, salary,

RANK() OVER (ORDER BY salary DESC) AS rank,

DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num

FROM employees;

2. How do you find the second highest salary from an Employee table?

SELECT MAX(salary) AS SecondHighest

FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);

Or using LIMIT

— — — — — — — — — — — — — — — — — — — — — — — — -

SELECT DISTINCT salary

FROM employees

ORDER BY salary DESC

LIMIT 1 OFFSET 1;

3. Explain Common Table Expressions (CTEs) and their use.

CTEs are temporary result sets used to simplify complex joins and subqueries.

WITH DeptTotal AS (

SELECT department_id, SUM(salary) AS total_salary

FROM employees

GROUP BY department_id

)

SELECT e.name, e.salary, d.total_salary

FROM employees e

JOIN DeptTotal d ON e.department_id = d.department_id;

4. How do you detect and remove duplicate records from a table?

To find duplicates:

SELECT name, COUNT(*)

FROM employees

GROUP BY name

HAVING COUNT(*) > 1;

To delete duplicates (keeping the lowest ID):

DELETE FROM employees

WHERE id NOT IN (

SELECT MIN(id)

FROM employees

GROUP BY name, department_id, salary

);

5. What is a window function? Give an example.

A window function performs a calculation across a set of table rows related to the current row.

Example: Running Total

SELECT name, salary,

SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS running_total

FROM employees;

6. Write a query to pivot data in SQL.

Using CASE WHEN:

SELECT department_id,

SUM(CASE WHEN gender = ‘M’ THEN 1 ELSE 0 END) AS male_count,

SUM(CASE WHEN gender = ‘F’ THEN 1 ELSE 0 END) AS female_count

FROM employees

GROUP BY department_id;

7. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

INNER JOIN: Returns matching rows.

LEFT JOIN: Returns all from the left table, and matched rows from the right.

RIGHT JOIN: All from the right table, and matched from the left.

FULL JOIN: All rows when there’s a match in one of the tables.

8. What is the use of EXISTS vs IN vs JOIN?

IN works on a list of values.

EXISTS returns true if subquery returns rows.

JOIN merges rows from multiple tables.

EXISTS is faster than IN in correlated subqueries with large data sets.

SELECT name

FROM employees e

WHERE EXISTS (

SELECT 1

FROM departments d

WHERE d.manager_id = e.id

);

9. What is a recursive CTE?

Used to query hierarchical data like org charts.

WITH RECURSIVE EmployeeHierarchy AS (

SELECT id, name, manager_id

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id

FROM employees e

INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id

)

SELECT * FROM EmployeeHierarchy;

10. How would you optimize a slow SQL query?

Use EXPLAIN to analyze.

Add indexes on filtered/joined columns.

Avoid **SELECT ***; select only required columns.

Use CTEs or temp tables for complex subqueries.

Minimize use of functions in WHERE clause.

11. What is the difference between CROSS JOIN and INNER JOIN?

• CROSS JOIN: Returns the Cartesian product of two tables. No condition is used.
• INNER JOIN: Returns only matching rows based on a join condition.

— CROSS JOIN

SELECT * FROM employees CROSS JOIN departments;

— INNER JOIN

SELECT * FROM employees INNER JOIN departments

ON employees.department_id = departments.id;

12. How do you calculate a rolling average using SQL?

SELECT name, salary,

AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg

FROM employees;

This gives a 3-row moving average (current row + 2 previous rows).

13. Explain MERGE (aka UPSERT) statement.

Used to insert, update, or delete records based on conditions.

MERGE INTO target_table AS target

USING source_table AS source

ON target.id = source.id

WHEN MATCHED THEN

UPDATE SET target.name = source.name

WHEN NOT MATCHED THEN

INSERT (id, name) VALUES (source.id, source.name);

14. How do you find gaps in a sequence?

Suppose you have employee IDs and want to find missing ones:

SELECT (t1.id + 1) AS start_gap

FROM employees t1

LEFT JOIN employees t2 ON t1.id + 1 = t2.id

WHERE t2.id IS NULL;

15. How do you rank items within groups in SQL (e.g., top 3 per department)?

SELECT *

FROM (

SELECT name, department_id, salary,

RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank

FROM employees

) ranked

WHERE dept_rank <= 3;

16. How do you handle NULLs in aggregations?

Use COALESCE or default values:

SELECT department_id, SUM(COALESCE(salary, 0)) AS total_salary

FROM employees

GROUP BY department_id;

17. Difference between DELETE, TRUNCATE, and DROP?

18. How do you perform full outer join in MySQL (which doesn’t support it directly)?

SELECT *

FROM table1

LEFT JOIN table2 ON table1.id = table2.id

UNION

SELECT *

FROM table1

RIGHT JOIN table2 ON table1.id = table2.id;

19. What is the use of LAG() and LEAD()?

• LAG(): Get value from a previous row.
• LEAD(): Get value from a following row.

SELECT name, salary,

LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,

LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary

FROM employees;

20. What is a correlated subquery?

A subquery that depends on the outer query for its value.

SELECT name, salary

FROM employees e

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = e.department_id

);

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

• WHERE filters rows before grouping.
• HAVING filters rows after grouping (used with aggregates).

— Example

SELECT department_id, COUNT(*) AS emp_count

FROM employees

WHERE status = ‘Active’

GROUP BY department_id

HAVING COUNT(*) > 5;

22. What are indexes? What are their types and trade-offs?

• Indexes speed up query lookups by creating a data structure (usually B-tree).
• Types:
• Single-column index
• Composite index
• Unique index
• Full-text index
• Bitmap index (for low-cardinality columns)

Trade-offs:

• Faster reads.
• Slower inserts/updates/deletes.
• Consumes storage.

23. How do you detect slow queries in a SQL database?

• Use EXPLAIN or EXPLAIN ANALYZE.
• Use Query Execution Plan to see:
• Full Table Scans
• Missing Indexes
• High Cost Steps

EXPLAIN SELECT * FROM employees WHERE salary > 100000;

24. What’s the difference between normalization and denormalization?

25. What is the difference between UNION, UNION ALL, INTERSECT, and EXCEPT?

26. How do you implement pagination in SQL?

— For PostgreSQL/MySQL

SELECT * FROM employees

ORDER BY name

LIMIT 10 OFFSET 20;

— For SQL Server

SELECT *

FROM (

SELECT *, ROW_NUMBER() OVER (ORDER BY name) AS rn

FROM employees

) AS sub

WHERE rn BETWEEN 21 AND 30;

27. How can you pivot and unpivot data in SQL Server?

— PIVOT example

SELECT *

FROM (

SELECT department, gender

FROM employees

) AS source

PIVOT (

COUNT(gender)

FOR gender IN ([M], [F])

) AS pivoted;

28. How do you remove duplicate rows but keep the most recent based on a timestamp?

DELETE FROM employees

WHERE id NOT IN (

SELECT MAX(id)

FROM employees

GROUP BY email

);

29. How do you perform case-insensitive searches in SQL?

SELECT * FROM employees

WHERE LOWER(name) = ‘john doe’;

Or using ILIKE in PostgreSQL:

SELECT * FROM employees

WHERE name ILIKE ‘john%’;

30. What are materialized views and how are they different from regular views?

Feature