In this blog, we’ll cover some of the most commonly asked advanced SQL interview questions, along with explanations and examples. Whether you are preparing for a data engineering or analytics role, these will help sharpen your SQL skills.

1️⃣ How do you find the second highest salary in SQL without using TOP, LIMIT, or RANK?

You can use a subquery:

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

This works by finding the maximum salary less than the overall maximum.

2️⃣ What’s the difference between WHERE and HAVING clauses?

WHERE filters rows before aggregation.
HAVING filters groups after aggregation.

Example:

SELECT department, AVG(salary)
FROM employees
WHERE salary > 3000
GROUP BY department
HAVING AVG(salary) > 5000;

3️⃣ How do you detect and delete duplicate rows in SQL?

Detect duplicates:

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

Delete duplicates (keeping one copy):

DELETE FROM employees
WHERE rowid NOT IN (
  SELECT MIN(rowid)
  FROM employees
  GROUP BY name
);

4️⃣ What’s the difference between correlated and uncorrelated subqueries?

Uncorrelated subquery runs independently.
Correlated subquery depends on outer query row.

Example (correlated):

SELECT e1.*
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e1.department_id = e2.department_id
);

5️⃣ How do you calculate running totals or moving averages using window functions?

SELECT employee_id, salary,
       SUM(salary) OVER (ORDER BY employee_id) AS RunningTotal,
       AVG(salary) OVER (ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM employees;

6️⃣ How do you handle NULL values in aggregate functions like COUNT, SUM, or AVG?

COUNT(column) ignores NULLs.
SUM and AVG also ignore NULLs.
Use COALESCE(column, 0) to replace NULLs.

SELECT COUNT(salary), SUM(COALESCE(salary,0)), AVG(COALESCE(salary,0))
FROM employees;

7️⃣ What’s the difference between UNION and UNION ALL?

UNION removes duplicates.
UNION ALL keeps duplicates (faster).

SELECT name FROM employees
UNION
SELECT name FROM managers;

8️⃣ How do you pivot rows into columns in SQL (and vice versa)?

Pivot:

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

Unpivot:

SELECT department, gender, cnt
FROM (
  SELECT department,
         COUNT(CASE WHEN gender='M' THEN 1 END) AS Male,
         COUNT(CASE WHEN gender='F' THEN 1 END) AS Female
  FROM employees
  GROUP BY department
) t
UNPIVOT (cnt FOR gender IN (Male, Female)) u;

9️⃣ How do you perform recursive queries for hierarchical data (parent-child relationships)?

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

🔟 How do you find gaps and islands in a sequence of dates using SQL?

SELECT id, date_column,
       date_column - ROW_NUMBER() OVER (ORDER BY date_column) AS grp
FROM events;

Then group by grp to find islands.

1️⃣1️⃣ What’s the difference between DELETE, TRUNCATE, and DROP commands?

DELETE: Removes rows (can use WHERE).
TRUNCATE: Removes all rows, faster, cannot use WHERE.
DROP: Removes entire table.

1️⃣2️⃣ How do you implement Slowly Changing Dimensions (SCD) Type 2 logic in SQL?

MERGE INTO dim_customer d
USING staging_customer s
ON d.customer_id = s.customer_id
WHEN MATCHED AND d.current_flag = 1 AND d.attribute <> s.attribute
  THEN UPDATE SET d.current_flag = 0, d.end_date = CURRENT_DATE
WHEN NOT MATCHED THEN
  INSERT (customer_id, attribute, start_date, end_date, current_flag)
  VALUES (s.customer_id, s.attribute, CURRENT_DATE, NULL, 1);

1️⃣3️⃣ How do you write a query to return employees with salaries greater than their manager’s?

SELECT e.name AS Employee, m.name AS Manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

1️⃣4️⃣ What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

INNER JOIN: Only matching rows.
LEFT JOIN: All left rows + matches.
RIGHT JOIN: All right rows + matches.
FULL OUTER JOIN: All rows from both sides.

1️⃣5️⃣ How do you calculate the median in SQL without a built-in MEDIAN function?

SELECT AVG(salary) AS Median
FROM (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER() AS cnt
  FROM employees
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2));

1️⃣6️⃣ How do you find the Nth highest value in a column without using window functions?

SELECT DISTINCT salary
FROM employees e1
WHERE (N-1) = (
  SELECT COUNT(DISTINCT salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

1️⃣7️⃣ What are CROSS APPLY and OUTER APPLY in SQL Server, and when do you use them?

CROSS APPLY: Acts like INNER JOIN with a table-valued function.
OUTER APPLY: Acts like LEFT JOIN with a table-valued function.