Advanced SQL Interview Questions with Answers

What this topic covers

30 subtopics

Subquery4
CTE3
Dates3
Nth highest3
Recursive CTE3
UNION3
Duplicates2
Triggers2
CASE1
COALESCE1
Conditional aggregation1
Correlated subquery1

Show all 30 subtopics

Derived table1
EXCEPT1
EXISTS1
Gaps1
Greatest per group1
GROUP_CONCAT1
HAVING1
INTERSECT1
Multi-table UPDATE1
NULL1
NULL in subqueries1
NULLIF1
Pivot1
Self join1
String functions1
Top-N per group1
UPDATE with CASE1
Views1

Beginner 15
Intermediate 15
Advanced 15

About this topic

The questions interviewers love: Nth-highest salary, deleting duplicates, finding gaps, top-N per group, organisation charts with recursive CTEs, and turning rows into columns. Every answer here was produced by actually running the query, so watch the details – ties, NULLs inside subqueries and the difference between UNION and UNION ALL decide most of them.

Free preview

Sample questions

Try each one first, then open the answer for the explanation and why the other options are wrong.

Question 1 UNION Beginner Practice Query executed

What does this return?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

SELECT
  (SELECT COUNT(*) FROM (SELECT dept FROM staff UNION SELECT dept FROM staff) a) AS with_union,
  (SELECT COUNT(*) FROM (SELECT dept FROM staff UNION ALL SELECT dept FROM staff) b) AS with_union_all;

with_union	with_union_all
16	16

with_union	with_union_all
3	3

with_union	with_union_all
8	16

with_union	with_union_all
3	16

Show answer and explanation

Correct answer D

Explanation

UNION removes duplicate rows from the combined result, leaving the three departments. UNION ALL keeps everything: 8 + 8 = 16 rows. UNION ALL is also faster because it skips the de-duplication step.

Why the other options are wrong

AUNION removes duplicates.
BUNION ALL keeps duplicates.
CUNION de-duplicates across both inputs and within each.

Question 2 Subquery Beginner Practice Query executed

Who earns more than the company average?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

SELECT name FROM staff
WHERE salary > (SELECT AVG(salary) FROM staff)
ORDER BY id;

name
Asha
Ravi
Meera

name
Asha
Ravi
Meera
Karan
Arjun

name
Asha

name
Asha
Ravi
Meera
Arjun

Show answer and explanation

Correct answer A

Explanation

The scalar subquery computes the average once: 522000 / 8 = 65250. Arjun's 65000 is just below it.

Why the other options are wrong

BKaran and Arjun are below the average.
CRavi and Meera also exceed the average.
DArjun earns 65000, below 65250.

Question 3 Nth highest Intermediate Practice Query executed

Who has the third-highest salary?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

WITH r AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM staff
)
SELECT name FROM r WHERE rk = 3;

name
Ravi
Meera

name
Arjun

name
Meera

name
Karan

Show answer and explanation

Correct answer B

Explanation

DENSE_RANK gives tied salaries the same rank with no gaps: 90000 → 1, 70000 → 2 (Ravi and Meera), 65000 → 3. With ROW_NUMBER the answer would depend on how ties are broken.

Why the other options are wrong

AThey share rank 2.
CThat is what ROW_NUMBER might give, ignoring the tie.
DKaran's 62000 has rank 4.

Question 4 NULL in subqueries Advanced Practice Query executed

The intent is "people who manage nobody". What does the query return?

staff

id	name	dept	salary	manager_id	joined	email
1	Asha	Eng	90000	NULL	2019-04-01	asha@example.com
2	Ravi	Eng	70000	1	2020-07-15	ravi@example.com
3	Meera	Eng	70000	1	2021-01-10	meera@example.com
4	John	Sales	60000	1	2020-02-01	john@example.com
5	Priya	Sales	55000	4	2022-09-01	priya@example.com
6	Karan	Sales	62000	4	2023-03-15	NULL
7	Neha	HR	50000	1	2021-06-20	neha@example.com
8	Arjun	Eng	65000	2	2024-01-05	ravi@example.com

sales

id	staff_id	quarter	amount
1	4	Q1	100
2	4	Q2	150
3	5	Q1	80
4	5	Q1	40
5	6	Q2	90
6	5	Q2	60

SELECT COUNT(*) AS n FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff);

n
0

n
3

n
5

n
8

Show answer and explanation

Correct answer A

Explanation

Asha's manager_id is NULL, so the subquery's list contains a NULL. id NOT IN (…, NULL) can never be TRUE, so no row qualifies. NOT EXISTS (SELECT 1 FROM staff r WHERE r.manager_id = s.id) returns the intended 5.

Why the other options are wrong

BThree people are managers, but that is not what is counted.
CThat is the intended answer, which NOT EXISTS would give.
DNOT IN filters every row here.

41 more questions in the timed setsSame format as the samples, with a timer, a question palette and a full review at the end.

Start practising

Advanced SQL Queries: questions with answers

What this topic covers

About this topic

Sample questions

All 11 topics