Here are Top 30 Advanced SQL Interview Questions and Answers, perfect for experienced developers, analysts, and DBAs:

---

✅ Top 30 Advanced SQL Interview Questions and Answers

---

1. What is the difference between WHERE and HAVING clause?

Answer:

• WHERE is used to filter rows before aggregation.
• HAVING is used to filter after aggregation.

SELECT department, COUNT(*) 
FROM employees 
WHERE status = 'active'
GROUP BY department 
HAVING COUNT(*) > 5;

---

2. What is a CTE (Common Table Expression)?

Answer:
A temporary result set defined with WITH clause that can be referenced within a query.

WITH dept_cte AS (
  SELECT department_id, COUNT(*) as total 
  FROM employees 
  GROUP BY department_id
)
SELECT * FROM dept_cte WHERE total > 10;

---

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

Function	Gaps in Ranks	Use Case
ROW_NUMBER()	Yes	Unique number per row
RANK()	Yes	Same rank, then skip next ranks
DENSE_RANK()	No	Same rank, next is next rank

---

4. What are window functions in SQL?

Answer:
Functions that perform calculations across a set of rows related to the current row using OVER().

SELECT name, salary, 
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

---

5. Explain the concept of indexing.

Answer:
An index speeds up data retrieval by creating a lookup structure on one or more columns. It improves read performance but can slow down writes.

---

Top 30 Advanced SQL Interview Questions and Answers

6. What is a composite index?

Answer:
An index on two or more columns.

CREATE INDEX idx_name_dept ON employees(name, department);

Useful when queries use left-most columns.

---

7. Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

Join Type	Description
INNER JOIN	Rows with matching keys in both tables
LEFT JOIN	All rows from left + matched from right
RIGHT JOIN	All rows from right + matched from left
FULL OUTER JOIN	All rows from both; NULLs if no match

---

8. How to get the second highest salary in a table?

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

---

9. What is a correlated subquery?

Answer:
A subquery that references columns from the outer query.

SELECT name 
FROM employees e 
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department = e.department
);

---

10. What is a self-join?

Answer:
A join of a table to itself.

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

---

Top 30 Advanced SQL Interview Questions and Answers

11. What are ACID properties in SQL databases?

• Atomicity: All or none.
• Consistency: Valid state.
• Isolation: Concurrent transactions don’t interfere.
• Durability: Changes persist after commit.

---

12. How do you handle duplicate records in SQL?

SELECT DISTINCT name FROM employees;
-- Or using ROW_NUMBER() to remove duplicates

---

13. What is normalization and its types?

• Organizing data to reduce redundancy.
• Types:
  • 1NF – Atomic values
  • 2NF – No partial dependency
  • 3NF – No transitive dependency
  • BCNF – Advanced version of 3NF

---

14. What is denormalization?

Answer:
Introducing redundancy for read performance improvement (opposite of normalization).

---

15. What is a NULL in SQL? How is it handled?

Answer:
NULL represents missing or unknown value. Use IS NULL, IS NOT NULL, or COALESCE() to handle.

---

Top 30 Advanced SQL Interview Questions and Answers

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

SELECT name, COUNT(*) 
FROM employees 
GROUP BY name 
HAVING COUNT(*) > 1;

---

17. What is the difference between TRUNCATE, DELETE, and DROP?

Command	Deletes Data	Logs	Rollback	Removes Structure
DELETE	Yes	Yes	Yes	No
TRUNCATE	Yes (All)	Minimal	No	No
DROP	Yes	No	No	Yes

---

18. What is a pivot in SQL?

Answer:
Transforming rows into columns.

SELECT department, 
       SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS MaleCount,
       SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleCount
FROM employees
GROUP BY department;

---

19. How can you optimize a slow SQL query?

• Use indexes.
• Avoid SELECT *.
• Use EXISTS instead of IN.
• Limit subqueries.
• Check execution plan.

---

20. What is the difference between EXISTS and IN?

Answer:

• EXISTS stops at the first match (faster for large subqueries).
• IN evaluates all values.

---

Top 30 Advanced SQL Interview Questions and Answers

21. How do you delete duplicate records but keep one?

DELETE FROM employees 
WHERE id NOT IN (
  SELECT MIN(id) 
  FROM employees 
  GROUP BY name, department
);

---

22. How to use recursive CTEs?

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 5
)
SELECT * FROM nums;

---

23. How do you find the nth highest salary using DENSE_RANK()?

SELECT * FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk 
  FROM employees
) t WHERE rnk = 3;

---

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

Clause	Removes Duplicates	Performance
UNION	Yes	Slower
UNION ALL	No	Faster

---

25. How do you perform case-insensitive search in SQL?

SELECT * FROM users WHERE LOWER(name) = LOWER('Alice');

---

Top 30 Advanced SQL Interview Questions and Answers

26. How do you update data from another table?

UPDATE employees e
SET salary = s.salary
FROM salaries s
WHERE e.id = s.emp_id;

---

27. What is the difference between scalar, inline, and multi-statement functions?

• Scalar: Returns a single value.
• Inline Table-Valued: Returns a table (like a view).
• Multi-Statement: Returns table with multiple statements.

---

28. How to use MERGE statement in SQL?

Answer:
For UPSERT operations (Insert or Update).

MERGE INTO target t
USING source s
ON t.id = s.id
WHEN MATCHED THEN 
  UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN 
  INSERT (id, name) VALUES (s.id, s.name);

---

29. What is an execution plan?

Answer:
A report showing how SQL Server executes a query. Helps in performance tuning.

Use:

EXPLAIN ANALYZE SELECT * FROM employees;

---

30. What is a stored procedure?

Answer:
A precompiled set of SQL statements saved in the database.

CREATE PROCEDURE GetEmployeeDetails (@id INT)
AS
BEGIN
  SELECT * FROM employees WHERE employee_id = @id;
END;

---

Top 30 Advanced SQL Interview Questions and Answers