SQL exercises with solutions: JOINs

Updated on 22 September 2026

JOIN links two tables through a shared column; LEFT JOIN also keeps the rows with no match. These 15 exercises range from level 2 to level 8; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: Give each table a short alias (c, o) and always write the ON condition.

Read the “JOIN” card in the cheat sheet

Exercise 1 · level 2

Show the name of each customer with the amount of each of their orders.

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: JOIN.

Query structure:

SELECT …, …
FROM …
INNER JOIN … ON … = …

Show the solution

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Expected result (6 rows):

name	amount
Alice	80
Alice	120
Bruno	200
Chloe	30
Chloe	50
Chloe	75

Exercise 2 · level 2

Show the id of each flight with the city of the departure airport.

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

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

Show the hint

Topics to use: JOIN.

Query structure:

SELECT …, …
FROM … …
JOIN … … ON … = …

Show the solution

SELECT f.id, a.city
FROM flights f
JOIN airports a ON a.code = f.origin;

Expected result (12 rows):

id	city
1	Paris
2	Paris
3	Lyon
4	Madrid
5	Berlin
6	Nice
7	Paris
8	Lisbon
9	Rome
10	Paris
11	Madrid
12	Lyon

Exercise 3 · level 3

Show each city with the total number of orders placed by the customers of that city.

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: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, COUNT(*)
FROM …
INNER JOIN … ON … = …
GROUP BY …

Show the solution

SELECT customers.city, COUNT(*)
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.city;

Expected result (2 rows):

city	COUNT(*)
Lyon	1
Paris	5

city

COUNT(*)

Lyon

Paris

Exercise 4 · level 3

Show the name of each director and their number of movies, including 0 for those who have none.

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: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, COUNT(…)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

Show the solution

SELECT d.name, COUNT(m.id)
FROM directors d
LEFT JOIN movies m ON m.director_id = d.id
GROUP BY d.id;

Expected result (6 rows):

name	COUNT(m.id)
Nora Ellis	2
Paulo Reis	2
Kenji Mori	2
Anna Berg	1
Luc Martin	2
Sara Diaz	0

Exercise 5 · level 3

Show the name of each member and their number of loans, including 0 for those who borrowed nothing.

Table `members` (6 rows)
id	name	city	joined
1	Lena	Lyon	2022-01-15
2	Marc	Paris	2021-06-03
3	Nadia	Lyon	2023-03-20
4	Oscar	Lille	2020-11-11
5	Paula	Paris	2024-02-01
6	Quentin	Nantes	2023-09-09

Table `loans` (12 rows)
id	book_id	member_id	loan_date	return_date
1	1	1	2025-01-05	2025-01-19
2	3	2	2025-01-10	2025-02-02
3	5	1	2025-02-01	2025-02-10
4	7	3	2025-02-03	NULL
5	3	4	2025-02-15	2025-03-01
6	2	2	2025-03-02	2025-03-30
7	8	5	2025-03-05	NULL
8	1	3	2025-03-10	2025-03-18
9	6	1	2025-03-20	2025-04-15
10	3	5	2025-04-01	NULL
11	9	4	2025-04-05	2025-04-12
12	10	2	2025-04-08	2025-04-20

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, COUNT(…)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

Show the solution

SELECT m.name, COUNT(l.id)
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id;

Expected result (6 rows):

name	COUNT(l.id)
Lena	3
Marc	3
Nadia	2
Oscar	2
Paula	2
Quentin	0

Exercise 6 · level 3

Show the date of each match with the name of the home team and the name of the away team.

Table `matches` (10 rows)
id	played_on	home_id	away_id	home_goals	away_goals
1	2025-08-02	1	2	2	1
2	2025-08-03	3	4	0	0
3	2025-08-09	5	1	1	3
4	2025-08-10	2	3	2	2
5	2025-08-16	4	5	3	1
6	2025-08-17	1	3	1	0
7	2025-08-23	2	4	0	1
8	2025-08-24	3	5	2	3
9	2025-08-30	4	1	1	1
10	2025-08-31	5	2	0	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: JOIN.

Query structure:

SELECT …, …, …
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …

Show the solution

SELECT m.played_on, h.name, a.name
FROM matches m
JOIN teams h ON h.id = m.home_id
JOIN teams a ON a.id = m.away_id;

Expected result (10 rows):

played_on	name	name
2025-08-02	Red Foxes	Blue Owls
2025-08-03	Green Bulls	Gold Hawks
2025-08-09	Grey Wolves	Red Foxes
2025-08-10	Blue Owls	Green Bulls
2025-08-16	Gold Hawks	Grey Wolves
2025-08-17	Red Foxes	Green Bulls
2025-08-23	Blue Owls	Gold Hawks
2025-08-24	Green Bulls	Grey Wolves
2025-08-30	Gold Hawks	Red Foxes
2025-08-31	Grey Wolves	Blue Owls

played_on

name

2025-08-02

Red Foxes

Blue Owls

2025-08-03

Green Bulls

Gold Hawks

2025-08-09

Grey Wolves

Red Foxes

2025-08-10

Blue Owls

Green Bulls

2025-08-16

Gold Hawks

Grey Wolves

2025-08-17

Red Foxes

Green Bulls

2025-08-23

Blue Owls

Gold Hawks

2025-08-24

Green Bulls

Grey Wolves

2025-08-30

Gold Hawks

Red Foxes

2025-08-31

Grey Wolves

Blue Owls

Exercise 7 · level 3

Show the name of each artist and their number of songs, including 0 for those who have none.

Table `artists` (6 rows)
id	name	country	debut_year
1	Luna Vale	USA	2012
2	The Static	UK	2005
3	Kofi Mensah	Ghana	2016
4	Mira Sol	Spain	2010
5	Neon Harbor	Canada	2018
6	Iris Nova	France	2020

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: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, COUNT(…)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

Show the solution

SELECT a.name, COUNT(s.id)
FROM artists a
LEFT JOIN songs s ON s.artist_id = a.id
GROUP BY a.id;

Expected result (6 rows):

name	COUNT(s.id)
Luna Vale	2
The Static	2
Kofi Mensah	2
Mira Sol	2
Neon Harbor	2
Iris Nova	0

Exercise 8 · level 3

Show the name of each guest and their number of bookings, including 0 for those who never booked.

Table `guests` (6 rows)
id	name	country
1	Ana Silva	Portugal
2	Ben Ford	USA
3	Chen Li	China
4	Dana Weiss	Germany
5	Emma Roy	France
6	Farid Nasser	Morocco

Table `bookings` (11 rows)
id	room_id	guest_id	check_in	check_out
1	2	1	2025-07-01	2025-07-04
2	5	2	2025-07-02	2025-07-05
3	8	3	2025-07-03	2025-07-06
4	3	4	2025-07-05	2025-07-12
5	6	5	2025-07-06	2025-07-08
6	1	2	2025-07-08	2025-07-10
7	9	3	2025-07-10	2025-07-11
8	7	5	2025-07-11	2025-07-15
9	4	1	2025-07-14	2025-07-16
10	6	4	2025-07-15	2025-07-19
11	6	5	2025-07-16	2025-07-18

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, COUNT(…)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

Show the solution

SELECT g.name, COUNT(b.id)
FROM guests g
LEFT JOIN bookings b ON b.guest_id = g.id
GROUP BY g.id;

Expected result (6 rows):

name	COUNT(b.id)
Ana Silva	2
Ben Ford	2
Chen Li	2
Dana Weiss	2
Emma Roy	3
Farid Nasser	0

Exercise 9 · level 3

For each order, show its id and its total amount (quantity × price), from the largest amount to the smallest (on ties, by id).

Table `order_items` (14 rows)
order_id	product_id	qty
1	1	1
1	2	2
2	4	1
3	3	5
3	2	1
4	5	1
5	6	2
5	1	1
6	2	4
6	3	3
7	4	1
7	6	1
8	5	2
8	2	1

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: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, SUM(… * …)
FROM … …
JOIN … … ON … = …
GROUP BY …
ORDER BY SUM(… * …) DESC, …

Show the solution

SELECT oi.order_id, SUM(oi.qty * p.price)
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY oi.order_id
ORDER BY SUM(oi.qty * p.price) DESC, oi.order_id;

Expected result (8 rows, in this order):

order_id	SUM(oi.qty * p.price)
7	171
2	149
8	102
5	79
6	66
1	59
4	45
3	42

Exercise 10 · level 3

For each project with tasks, show its name and the total hours of its tasks, from the largest total to the smallest (on ties, by name).

Table `projects` (4 rows)
id	name	client	budget	deadline
1	Atlas	Acme	20000	2025-06-30
2	Beacon	Bolt	12000	2025-05-15
3	Comet	Acme	8000	2025-04-30
4	Delta	Cyan	15000	2025-07-31

Table `tasks` (10 rows)
id	project_id	dev_id	title	hours	status	done_on
1	1	1	Landing page	12	done	2025-03-10
2	1	3	Data model	20	done	2025-03-20
3	1	2	Login	8	doing	NULL
4	2	4	ETL job	16	done	2025-04-02
5	2	5	CI pipeline	6	done	2025-03-15
6	3	1	Dashboard	14	todo	NULL
7	3	3	Report	10	done	2025-04-25
8	4	2	API	18	doing	NULL
9	4	5	Monitoring	9	todo	NULL
10	4	4	Forecast	22	done	2025-05-05

Show the hint

Topics to use: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, SUM(…)
FROM … …
JOIN … … ON … = …
GROUP BY …
ORDER BY SUM(…) DESC, …

Show the solution

SELECT p.name, SUM(t.hours)
FROM projects p
JOIN tasks t ON t.project_id = p.id
GROUP BY p.id
ORDER BY SUM(t.hours) DESC, p.name;

Expected result (4 rows, in this order):

name	SUM(t.hours)
Delta	49
Atlas	40
Comet	24
Beacon	22

Exercise 11 · level 3

For each owner with transactions, show their name and their total balance (all their accounts), from the largest to the smallest (on ties, by name).

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: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, SUM(…)
FROM … …
JOIN … … ON … = …
GROUP BY …
ORDER BY SUM(…) DESC, …

Show the solution

SELECT a.owner, SUM(t.amount)
FROM accounts a
JOIN transactions t ON t.account_id = a.id
GROUP BY a.owner
ORDER BY SUM(t.amount) DESC, a.owner;

Expected result (4 rows, in this order):

owner	SUM(t.amount)
Alice	5165
Chloe	1455
Bruno	830
David	30

Exercise 12 · level 5

Show each employee with their manager's name (ignore those who have no manager).

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: JOIN.

Query structure:

SELECT …, …
FROM … …
JOIN … … ON … = …

Show the solution

SELECT e.name, m.name
FROM staff e
JOIN staff m ON e.manager_id = m.id;

Expected result (9 rows):

name	name
Bob	Alice
Claire	Alice
David	Alice
Emma	Bob
Farid	Bob
Gaelle	David
Hugo	Claire
Iris	Emma
Jules	Claire

name

Bob

Alice

Claire

Alice

David

Alice

Emma

Bob

Farid

Bob

Gaelle

David

Hugo

Claire

Iris

Emma

Jules

Claire

Exercise 13 · level 5

Show the name of the employees who were hired before their own manager.

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), JOIN.

Query structure:

SELECT …
FROM … …
JOIN … … ON … = …
WHERE … < …

Show the solution

SELECT e.name
FROM staff e
JOIN staff m ON e.manager_id = m.id
WHERE e.hired < m.hired;

Expected result (2 rows):

name
Emma
Hugo

name

Emma

Hugo

Exercise 14 · level 5

Show the pairs of players from the same team (columns: name 1, name 2) where player 1 scored more goals than player 2.

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: JOIN.

Query structure:

SELECT …, …
FROM … …
JOIN … … ON … = … AND … > …

Show the solution

SELECT a.name, b.name
FROM players a
JOIN players b ON a.team_id = b.team_id AND a.goals > b.goals;

Expected result (4 rows):

name	name
Alex Moreau	Bilal Sow
Carl Weber	Diego Ruiz
Femi Adeyemi	Goran Petrov
Hugo Lamy	Ivan Kral

name

Alex Moreau

Bilal Sow

Carl Weber

Diego Ruiz

Femi Adeyemi

Goran Petrov

Hugo Lamy

Ivan Kral

Exercise 15 · level 8

Show the pairs of products bought together in the same order (the name of the product with the smaller id first), with the number of orders in which they appear together.

Table `order_items` (14 rows)
order_id	product_id	qty
1	1	1
1	2	2
2	4	1
3	3	5
3	2	1
4	5	1
5	6	2
5	1	1
6	2	4
6	3	3
7	4	1
7	6	1
8	5	2
8	2	1

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: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN.

Query structure:

SELECT …, …, COUNT(*)
FROM … …
JOIN … … ON … = … AND … < …
JOIN … … ON … = …
JOIN … … ON … = …
GROUP BY …, …

Show the solution

SELECT p1.name, p2.name, COUNT(*)
FROM order_items a
JOIN order_items b ON b.order_id = a.order_id AND a.product_id < b.product_id
JOIN products p1 ON p1.id = a.product_id
JOIN products p2 ON p2.id = b.product_id
GROUP BY a.product_id, b.product_id;

Expected result (5 rows):

name	name	COUNT(*)
Desk Lamp	Coffee Mug	1
Desk Lamp	Cushion	1
Coffee Mug	Notebook	2
Coffee Mug	Kettle	1
Office Chair	Cushion	1

name

COUNT(*)

Desk Lamp

Coffee Mug

Desk Lamp

Cushion

Coffee Mug

Notebook

Coffee Mug

Kettle

Office Chair

Cushion

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 60 “JOIN” questions