SQL exercises with solutions: subqueries

Updated on 22 September 2026

A subquery is a query inside a query: a value, a list (IN) or a table. These 15 exercises range from level 2 to level 8; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: Write the subquery on its own first to check its result, then plug it in.

Read the “Subqueries” card in the cheat sheet

Exercise 1 · level 2

Show the name of the employees whose salary is above the average salary of all employees.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …
FROM …
WHERE … > (SELECT AVG(…) FROM …)

Show the solution

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Expected result (4 rows):

name
Bob
Emma
Farid
Hugo

name

Bob

Emma

Farid

Hugo

Exercise 2 · level 3

Show the name of the customers who have not placed any order.

Table `customers` (4 rows)
id	name	city
1	Alice	Paris
2	Bruno	Lyon
3	Chloe	Paris
4	Dylan	Nantes

Table `orders` (6 rows)
id	customer_id	amount
1	1	120
2	1	80
3	2	200
4	3	50
5	3	75
6	3	30

Show the hint

Topics to use: WHERE (filters), Subqueries.

Query structure:

SELECT …
FROM …
WHERE … NOT IN (SELECT … FROM …)

Show the solution

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Expected result (1 row):

name
Dylan

name

Dylan

Exercise 3 · level 3

Show the name of the employees who earn more than the average salary of their own department.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …
FROM … …
WHERE … > (SELECT AVG(…) FROM … WHERE … = …)

Show the solution

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

Expected result (4 rows):

name
Emma
Farid
Gaelle
Hugo

name

Emma

Farid

Gaelle

Hugo

Exercise 4 · level 3

Show the code and city of the airports where no flight arrives.

Table `airports` (7 rows)
code	city	country
CDG	Paris	France
LYS	Lyon	France
MAD	Madrid	Spain
LIS	Lisbon	Portugal
FCO	Rome	Italy
BER	Berlin	Germany
NCE	Nice	France

Table `flights` (12 rows)
id	airline	origin	dest	departs	duration_min	price	seats_sold	capacity
1	SkyJet	CDG	MAD	2025-06-01 08:10	125	89	150	180
2	AirNova	CDG	LIS	2025-06-01 11:40	155	120	160	170
3	BlueWing	LYS	FCO	2025-06-02 07:30	95	75	110	150
4	SkyJet	MAD	CDG	2025-06-02 18:20	120	95	170	180
5	AirNova	BER	CDG	2025-06-03 09:05	110	105	140	160
6	BlueWing	NCE	BER	2025-06-03 13:50	130	140	90	150
7	SkyJet	CDG	FCO	2025-06-04 06:45	135	99	175	180
8	AirNova	LIS	MAD	2025-06-04 16:15	75	65	60	120
9	BlueWing	FCO	LYS	2025-06-05 20:30	100	82	130	150
10	SkyJet	CDG	BER	2025-06-05 10:00	105	110	120	180
11	AirNova	MAD	LIS	2025-06-06 12:25	80	70	95	120
12	BlueWing	LYS	MAD	2025-06-06 15:40	115	79	100	150

Show the hint

Topics to use: WHERE (filters), Subqueries.

Query structure:

SELECT …, …
FROM …
WHERE … NOT IN (SELECT … FROM …)

Show the solution

SELECT code, city
FROM airports
WHERE code NOT IN (SELECT dest FROM flights);

Expected result (1 row):

code	city
NCE	Nice

code

city

NCE

Nice

Exercise 5 · level 5

Show the name and department of the highest-paid employee of each department (correlated subquery).

Table `staff` (10 rows)
id	name	department	salary	manager_id	hired
1	Alice	Finance	52000	NULL	2015-03-01
2	Bob	IT	41000	1	2018-06-15
3	Claire	Finance	38000	1	2019-01-10
4	David	HR	29000	1	2020-09-01
5	Emma	IT	50000	2	2017-11-20
6	Farid	IT	47000	2	2021-02-14
7	Gaelle	HR	33000	4	2022-05-30
8	Hugo	Finance	44000	3	2016-08-08
9	Iris	IT	47000	5	2023-01-09
10	Jules	Finance	44000	3	2024-03-18

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …, …
FROM …
WHERE … = (SELECT MAX(…) FROM … … WHERE … = …)

Show the solution

SELECT name, department
FROM staff
WHERE salary = (SELECT MAX(salary) FROM staff s WHERE s.department = staff.department);

Expected result (3 rows):

name	department
Alice	Finance
Emma	IT
Gaelle	HR

name

department

Alice

Finance

Emma

Gaelle

Exercise 6 · level 5

Show the title of the movies whose rating is above the average rating of the movies of their own genre.

Table `movies` (10 rows)
id	title	genre	year	duration	rating	director_id
1	Night Train	Thriller	2015	118	7.8	1
2	Blue Harbor	Drama	2018	102	7.1	2
3	Paper Moon City	Comedy	2012	95	6.4	5
4	Silent Peak	Drama	2020	131	8.2	3
5	Last Signal	Sci-Fi	2019	142	7.5	1
6	Summer Keys	Comedy	2016	88	5.9	4
7	Iron Garden	Sci-Fi	2021	125	8	3
8	Dust and Gold	Western	2014	110	6.8	2
9	The Quiet Hour	Drama	2022	97	7.4	5
10	Deep Current	Thriller	2017	105	6.9	NULL

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …
FROM … …
WHERE … > (SELECT AVG(…) FROM … … WHERE … = …)

Show the solution

SELECT title
FROM movies m
WHERE rating > (SELECT AVG(rating) FROM movies x WHERE x.genre = m.genre);

Expected result (4 rows):

title
Night Train
Paper Moon City
Silent Peak
Iron Garden

title

Night Train

Paper Moon City

Silent Peak

Iron Garden

Exercise 7 · level 5

Show the name and number of goals of the players who scored more than the average of the players of their team.

Table `players` (9 rows)
id	name	team_id	position	goals
1	Alex Moreau	1	FW	9
2	Bilal Sow	1	MF	4
3	Carl Weber	2	FW	7
4	Diego Ruiz	2	DF	1
5	Eli Novak	3	FW	6
6	Femi Adeyemi	4	FW	11
7	Goran Petrov	4	GK	0
8	Hugo Lamy	5	MF	5
9	Ivan Kral	5	DF	2

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …, …
FROM … …
WHERE … > (SELECT AVG(…) FROM … … WHERE … = …)

Show the solution

SELECT name, goals
FROM players p
WHERE goals > (SELECT AVG(goals) FROM players x WHERE x.team_id = p.team_id);

Expected result (4 rows):

name	goals
Alex Moreau	9
Carl Weber	7
Femi Adeyemi	11
Hugo Lamy	5

Exercise 8 · level 5

Show the title of the songs that are longer than the average duration of their artist's songs.

Table `songs` (10 rows)
id	title	artist_id	genre	duration_s	released
1	Glass Heart	1	Pop	214	2019-04-12
2	Low Tide	1	Pop	198	2021-06-01
3	Rust	2	Rock	256	2008-09-30
4	Wires	2	Rock	301	2015-02-14
5	Sunday Market	3	Afrobeat	233	2020-11-20
6	Palm Wine	3	Afrobeat	245	2022-03-03
7	Alma	4	Latin	189	2013-07-07
8	Brisa	4	Pop	205	2018-05-25
9	Night Drive	5	Electro	276	2021-10-10
10	Pulse	5	Electro	230	2023-01-15

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …
FROM … …
WHERE … > (SELECT AVG(…) FROM … … WHERE … = …)

Show the solution

SELECT title
FROM songs s
WHERE duration_s > (SELECT AVG(duration_s) FROM songs x WHERE x.artist_id = s.artist_id);

Expected result (5 rows):

title
Glass Heart
Wires
Palm Wine
Brisa
Night Drive

title

Glass Heart

Wires

Palm Wine

Brisa

Night Drive

Exercise 9 · level 5

Show the city, day and maximum temperature of the readings hotter than the average maximum of their city.

Table `readings` (15 rows)
id	city	day	temp_max	temp_min	rain_mm
1	Paris	2025-07-01	24	15	0
2	Paris	2025-07-02	27	17	0
3	Paris	2025-07-03	22	16	4.5
4	Paris	2025-07-04	19	14	12
5	Paris	2025-07-05	23	13	0
6	Lyon	2025-07-01	26	16	0
7	Lyon	2025-07-02	29	18	0
8	Lyon	2025-07-03	31	19	0
9	Lyon	2025-07-04	24	17	8.5
10	Lyon	2025-07-05	22	15	3
11	Marseille	2025-07-01	30	21	0
12	Marseille	2025-07-02	32	22	0
13	Marseille	2025-07-03	33	23	0
14	Marseille	2025-07-04	29	21	0
15	Marseille	2025-07-05	28	20	0

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …, …, …
FROM … …
WHERE … > (SELECT AVG(…) FROM … … WHERE … = …)

Show the solution

SELECT city, day, temp_max
FROM readings r
WHERE temp_max > (SELECT AVG(temp_max) FROM readings x WHERE x.city = r.city);

Expected result (6 rows):

city	day	temp_max
Paris	2025-07-01	24
Paris	2025-07-02	27
Lyon	2025-07-02	29
Lyon	2025-07-03	31
Marseille	2025-07-02	32
Marseille	2025-07-03	33

Exercise 10 · level 5

Show the name, category and price of the products that cost more than the average product of their category.

Table `products` (7 rows)
id	name	category	price
1	Desk Lamp	Home	35
2	Coffee Mug	Kitchen	12
3	Notebook	Office	6
4	Office Chair	Office	149
5	Kettle	Kitchen	45
6	Cushion	Home	22
7	Stapler	Office	9

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …, …, …
FROM … …
WHERE … > (SELECT AVG(…) FROM … … WHERE … = …)

Show the solution

SELECT name, category, price
FROM products p
WHERE price > (SELECT AVG(x.price) FROM products x WHERE x.category = p.category);

Expected result (3 rows):

name	category	price
Desk Lamp	Home	35
Office Chair	Office	149
Kettle	Kitchen	45

name

Exercise 11 · level 5

Show the name, team and rate of the developers whose rate is above their team's average.

Table `devs` (6 rows)
id	name	team	rate
1	Ana	Web	55
2	Bo	Web	48
3	Cleo	Data	62
4	Dan	Data	58
5	Eve	Ops	50
6	Finn	Ops	45

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …, …, …
FROM … …
WHERE … > (SELECT AVG(…) FROM … … WHERE … = …)

Show the solution

SELECT name, team, rate
FROM devs d
WHERE rate > (SELECT AVG(x.rate) FROM devs x WHERE x.team = d.team);

Expected result (3 rows):

name	team	rate
Ana	Web	55
Cleo	Data	62
Eve	Ops	50

name

team

rate

Ana

Web

Cleo

Data

Eve

Ops

Exercise 12 · level 5

Show the id, account and amount of the transactions whose amount is above the average transaction of their account.

Table `transactions` (14 rows)
id	account_id	made_on	amount	label
1	1	2025-01-02	2500	salary
2	1	2025-01-05	-60	groceries
3	1	2025-01-12	-800	rent
4	2	2025-01-15	500	transfer
5	3	2025-01-03	1900	salary
6	3	2025-01-20	-120	groceries
7	3	2025-02-01	-950	rent
8	4	2025-01-25	2100	salary
9	4	2025-02-03	-45	restaurant
10	1	2025-02-02	2600	salary
11	1	2025-02-06	-75	groceries
12	5	2025-02-10	30	interest
13	2	2025-02-15	500	transfer
14	4	2025-02-18	-600	rent

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries.

Query structure:

SELECT …, …, …
FROM … …
WHERE … > (SELECT AVG(…) FROM … … WHERE … = …)

Show the solution

SELECT id, account_id, amount
FROM transactions t
WHERE amount > (SELECT AVG(x.amount) FROM transactions x WHERE x.account_id = t.account_id);

Expected result (4 rows):

id	account_id	amount
1	1	2500
5	3	1900
8	4	2100
10	1	2600

Exercise 13 · level 7

For each account with expenses, show the account id, the owner, and the date, amount and label of its biggest expense (on ties, all the tied expenses).

Table `accounts` (6 rows)
id	owner	city	opened	kind
1	Alice	Paris	2021-03-01	current
2	Alice	Paris	2022-06-15	savings
3	Bruno	Lyon	2020-09-10	current
4	Chloe	Lyon	2023-01-20	current
5	David	Nice	2019-11-05	savings
6	Emma	Nice	2024-04-01	current

Table `transactions` (14 rows)
id	account_id	made_on	amount	label
1	1	2025-01-02	2500	salary
2	1	2025-01-05	-60	groceries
3	1	2025-01-12	-800	rent
4	2	2025-01-15	500	transfer
5	3	2025-01-03	1900	salary
6	3	2025-01-20	-120	groceries
7	3	2025-02-01	-950	rent
8	4	2025-01-25	2100	salary
9	4	2025-02-03	-45	restaurant
10	1	2025-02-02	2600	salary
11	1	2025-02-06	-75	groceries
12	5	2025-02-10	30	interest
13	2	2025-02-15	500	transfer
14	4	2025-02-18	-600	rent

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, JOIN, Subqueries.

Query structure:

SELECT …, …, …, …, …
FROM … …
JOIN … … ON … = …
WHERE … < … AND … = (SELECT MIN(…) FROM … … WHERE … = …)

Show the solution

SELECT a.id, a.owner, t.made_on, t.amount, t.label
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE t.amount < 0 AND t.amount = (SELECT MIN(x.amount) FROM transactions x WHERE x.account_id = t.account_id);

Expected result (3 rows):

id	owner	made_on	amount	label
1	Alice	2025-01-12	-800	rent
3	Bruno	2025-02-01	-950	rent
4	Chloe	2025-02-18	-600	rent

Exercise 14 · level 8

For each director who has at least one movie, show their name, the title of their longest movie and that movie's duration.

Table `movies` (10 rows)
id	title	genre	year	duration	rating	director_id
1	Night Train	Thriller	2015	118	7.8	1
2	Blue Harbor	Drama	2018	102	7.1	2
3	Paper Moon City	Comedy	2012	95	6.4	5
4	Silent Peak	Drama	2020	131	8.2	3
5	Last Signal	Sci-Fi	2019	142	7.5	1
6	Summer Keys	Comedy	2016	88	5.9	4
7	Iron Garden	Sci-Fi	2021	125	8	3
8	Dust and Gold	Western	2014	110	6.8	2
9	The Quiet Hour	Drama	2022	97	7.4	5
10	Deep Current	Thriller	2017	105	6.9	NULL

Table `directors` (6 rows)
id	name	country
1	Nora Ellis	UK
2	Paulo Reis	Brazil
3	Kenji Mori	Japan
4	Anna Berg	Sweden
5	Luc Martin	France
6	Sara Diaz	Spain

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, JOIN, Subqueries.

Query structure:

SELECT …, …, …
FROM … …
JOIN … … ON … = …
WHERE … = (SELECT MAX(…) FROM … … WHERE … = …)

Show the solution

SELECT d.name, m.title, m.duration
FROM directors d
JOIN movies m ON m.director_id = d.id
WHERE m.duration = (SELECT MAX(duration) FROM movies x WHERE x.director_id = d.id);

Expected result (5 rows):

name	title	duration
Nora Ellis	Last Signal	142
Paulo Reis	Dust and Gold	110
Kenji Mori	Silent Peak	131
Anna Berg	Summer Keys	88
Luc Martin	The Quiet Hour	97

name

title

duration

Nora Ellis

Last Signal

142

Paulo Reis

Dust and Gold

110

Kenji Mori

Silent Peak

131

Anna Berg

Summer Keys

Luc Martin

The Quiet Hour

Exercise 15 · level 8

Show the player's name, their team's name and their number of goals, for the players who scored more goals than every Blue Owls player, from the most goals to the fewest.

Table `players` (9 rows)
id	name	team_id	position	goals
1	Alex Moreau	1	FW	9
2	Bilal Sow	1	MF	4
3	Carl Weber	2	FW	7
4	Diego Ruiz	2	DF	1
5	Eli Novak	3	FW	6
6	Femi Adeyemi	4	FW	11
7	Goran Petrov	4	GK	0
8	Hugo Lamy	5	MF	5
9	Ivan Kral	5	DF	2

Table `teams` (5 rows)
id	name	city	founded
1	Red Foxes	Lyon	1950
2	Blue Owls	Paris	1962
3	Green Bulls	Lille	1971
4	Gold Hawks	Nantes	1988
5	Grey Wolves	Paris	1990

Show the hint

Topics to use: WHERE (filters), ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, JOIN, Subqueries.

Query structure:

SELECT …, …, …
FROM … …
JOIN … … ON … = …
WHERE … > (SELECT MAX(…) FROM … … JOIN … … ON … = … WHERE … = …)
ORDER BY … DESC

Show the solution

SELECT p.name, t.name, p.goals
FROM players p
JOIN teams t ON t.id = p.team_id
WHERE p.goals > (SELECT MAX(x.goals) FROM players x JOIN teams y ON y.id = x.team_id WHERE y.name = 'Blue Owls')
ORDER BY p.goals DESC;

Expected result (2 rows, in this order):

name	name	goals
Femi Adeyemi	Gold Hawks	11
Alex Moreau	Red Foxes	9

name

goals

Femi Adeyemi

Gold Hawks

Alex Moreau

Red Foxes

Practice with automatic checking

In SpeedQL, you write your query and it is checked straight away, on these tables and then on a hidden dataset.

Practice on the 34 “Subqueries” questions