Advanced SQL Interview Questions

Advanced SQL Interview Questions & Answers

1️⃣ Second Highest Salary (No TOP/LIMIT/RANK)

You can use a subquery to find the second highest salary:

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

This finds the maximum salary less than the overall maximum.

2️⃣ WHERE vs HAVING

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

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

3️⃣ Detect & Delete Duplicates

Detect duplicates:

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

Delete duplicates (keep one copy):

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

4️⃣ Correlated vs Uncorrelated Subqueries

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

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

5️⃣ Running Totals & Moving Averages

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️⃣ Handling NULLs in Aggregates

COUNT, SUM, and AVG ignore NULLs. Use COALESCE to replace NULLs.

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

7️⃣ UNION vs UNION ALL

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

SELECT name FROM employees
UNION
SELECT name FROM managers;

8️⃣ Pivot & Unpivot

Pivot rows into columns 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️⃣ Recursive Queries for Hierarchies

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;

🔟 Gaps & Islands in Dates

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️⃣ DELETE vs TRUNCATE vs DROP

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

1️⃣2️⃣ SCD Type 2 Logic

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️⃣ Employees With Higher Salary Than Manager

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️⃣ Types of JOINs

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️⃣ Calculate Median Without MEDIAN()

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️⃣ Nth Highest Value (No Window)

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

1️⃣7️⃣ CROSS APPLY vs OUTER APPLY

CROSS APPLY: Like INNER JOIN with table-valued function.
OUTER APPLY: Like LEFT JOIN with table-valued function.

SELECT e.name, d.*
FROM employees e
CROSS APPLY fnGetDetails(e.id) d;

1️⃣8️⃣ Identify & Remove Orphan Records

SELECT c.*
FROM child c
LEFT JOIN parent p ON c.parent_id = p.id
WHERE p.id IS NULL;

DELETE FROM child
WHERE parent_id NOT IN (SELECT id FROM parent);

1️⃣9️⃣ Clustered vs Non-Clustered Indexes

Clustered index: Sorts and stores rows physically in table.
Non-clustered index: Separate structure with pointers to data.

2️⃣0️⃣ Query Optimization Tips

• Create proper indexes (on join/filter columns).
• Use covering indexes to reduce lookups.
• Check execution plan for bottlenecks.
• Avoid SELECT *.
• Partition large tables.