SQL exercises with solutions: EXISTS and NOT EXISTS

Updated on 22 September 2026

EXISTS is true when the subquery returns at least one row; NOT EXISTS, when it returns none. These 15 exercises range from level 5 to level 8; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: NOT EXISTS is safer than NOT IN when the subquery may contain NULLs.

Read the “EXISTS” card in the cheat sheet

Exercise 1 · level 5

Show the name of the employees who have at least one direct report. Use EXISTS.

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

Query structure:

SELECT …
FROM … …
WHERE EXISTS (SELECT … FROM … … WHERE … = …)

Show the solution

SELECT name
FROM staff m
WHERE EXISTS (SELECT 1 FROM staff e WHERE e.manager_id = m.id);

Expected result (5 rows):

name
Alice
Bob
Claire
David
Emma

name

Alice

Bob

Claire

David

Emma

Exercise 2 · level 5

Show the name of the employees who have no direct reports. Use NOT EXISTS.

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

Query structure:

SELECT …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = …)

Show the solution

SELECT name
FROM staff m
WHERE NOT EXISTS (SELECT 1 FROM staff e WHERE e.manager_id = m.id);

Expected result (5 rows):

name
Farid
Gaelle
Hugo
Iris
Jules

name

Farid

Gaelle

Hugo

Iris

Jules

Exercise 3 · level 5

Show the name of the customers who placed at least one order above 100. Use EXISTS.

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

Query structure:

SELECT …
FROM … …
WHERE EXISTS (SELECT … FROM … … WHERE … = … AND … > …)

Show the solution

SELECT name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 100);

Expected result (2 rows):

name
Alice
Bruno

name

Alice

Bruno

Exercise 4 · level 5

Show the name of the actors who have not appeared in any movie. Use NOT EXISTS.

Table `actors` (7 rows)
id	name	birth_year
1	Ava Stone	1985
2	Ben Cole	1978
3	Chloe Park	1990
4	Dario Vega	1982
5	Emi Sato	1995
6	Felix Grant	1970
7	Gus Hale	1988

Table `casting` (14 rows)
movie_id	actor_id	role
1	1	lead
1	2	support
2	3	lead
2	6	support
3	4	lead
4	5	lead
4	1	support
5	2	lead
5	5	support
7	5	lead
7	6	support
8	6	lead
9	3	lead
9	4	support

Show the hint

Topics to use: WHERE (filters), EXISTS.

Query structure:

SELECT …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = …)

Show the solution

SELECT name
FROM actors a
WHERE NOT EXISTS (SELECT 1 FROM casting c WHERE c.actor_id = a.id);

Expected result (1 row):

name
Gus Hale

name

Gus Hale

Exercise 5 · level 5

Show the title of the books that have never been borrowed. Use NOT EXISTS.

Table `books` (10 rows)
id	title	author_id	genre	pages	year
1	Cold River	1	Novel	320	2011
2	Salt Roads	2	Travel	210	2016
3	The Glass Hive	3	Sci-Fi	412	2019
4	Winter Ledger	1	Crime	288	2014
5	Desert Letters	2	Novel	356	2020
6	Small Engines	4	Sci-Fi	198	2022
7	Harbor Lights	5	Novel	445	2008
8	Night Garden	3	Crime	301	2017
9	Paper Birds	5	Poetry	96	2012
10	Open Maps	4	Travel	240	2021

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: WHERE (filters), EXISTS.

Query structure:

SELECT …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = …)

Show the solution

SELECT title
FROM books b
WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.book_id = b.id);

Expected result (1 row):

title
Winter Ledger

title

Winter Ledger

Exercise 6 · level 5

Show the name of the teams that played at least one home match that ended in a draw. Use EXISTS.

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

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

Show the hint

Topics to use: WHERE (filters), EXISTS.

Query structure:

SELECT …
FROM … …
WHERE EXISTS (SELECT … FROM … … WHERE … = … AND … = …)

Show the solution

SELECT name
FROM teams t
WHERE EXISTS (SELECT 1 FROM matches m WHERE m.home_id = t.id AND m.home_goals = m.away_goals);

Expected result (3 rows):

name
Blue Owls
Green Bulls
Gold Hawks

name

Blue Owls

Green Bulls

Gold Hawks

Exercise 7 · level 5

Show the code and city of the airports A from which at least one flight goes to an airport B while another flight connects B back to A. Use EXISTS.

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

Query structure:

SELECT …, …
FROM … …
WHERE EXISTS (SELECT … FROM … … WHERE … = … AND EXISTS (SELECT … FROM … … WHERE … = … AND … = …))

Show the solution

SELECT code, city
FROM airports a
WHERE EXISTS (SELECT 1 FROM flights f WHERE f.origin = a.code AND EXISTS (SELECT 1 FROM flights g WHERE g.origin = f.dest AND g.dest = f.origin));

Expected result (6 rows):

code	city
CDG	Paris
LYS	Lyon
MAD	Madrid
LIS	Lisbon
FCO	Rome
BER	Berlin

code

city

CDG

Paris

LYS

Lyon

MAD

Madrid

LIS

Lisbon

FCO

Rome

BER

Berlin

Exercise 8 · level 5

Using NOT EXISTS, show the id, type and price of the rooms that have never been booked.

Table `rooms` (10 rows)
id	hotel_id	type	price
1	1	single	70
2	1	double	95
3	2	double	130
4	2	suite	210
5	3	single	110
6	3	double	150
7	4	double	65
8	5	double	260
9	5	suite	480
10	5	single	190

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: WHERE (filters), EXISTS.

Query structure:

SELECT …, …, …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = …)

Show the solution

SELECT r.id, r.type, r.price
FROM rooms r
WHERE NOT EXISTS (SELECT 1 FROM bookings b WHERE b.room_id = r.id);

Expected result (1 row):

id	type	price
10	single	190

type

price

single

190

Exercise 9 · level 5

Using EXISTS, show the name and city of the customers who have at least one cancelled order ('cancelled').

Table `customers` (6 rows)
id	name	city	signup
1	Alba	Paris	2024-01-10
2	Boris	Lyon	2024-02-15
3	Carla	Paris	2024-03-01
4	Denis	Nantes	2024-05-20
5	Eva	Lyon	2024-06-30
6	Fabio	Lille	2024-08-08

Table `orders` (8 rows)
id	customer_id	order_date	status
1	1	2025-01-05	shipped
2	2	2025-01-12	shipped
3	1	2025-02-03	paid
4	3	2025-02-10	cancelled
5	4	2025-02-20	shipped
6	5	2025-03-02	shipped
7	2	2025-03-15	paid
8	3	2025-03-28	shipped

Show the hint

Topics to use: WHERE (filters), EXISTS.

Query structure:

SELECT …, …
FROM … …
WHERE EXISTS (SELECT … FROM … … WHERE … = … AND … = …)

Show the solution

SELECT c.name, c.city
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'cancelled');

Expected result (1 row):

name	city
Carla	Paris

name

city

Carla

Paris

Exercise 10 · level 5

Using NOT EXISTS, show the name and team of the developers who have not finished any task.

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

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: WHERE (filters), EXISTS.

Query structure:

SELECT …, …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = … AND … = …)

Show the solution

SELECT d.name, d.team
FROM devs d
WHERE NOT EXISTS (SELECT 1 FROM tasks t WHERE t.dev_id = d.id AND t.status = 'done');

Expected result (2 rows):

name	team
Bo	Web
Finn	Ops

name

team

Web

Finn

Ops

Exercise 11 · level 5

Using NOT EXISTS, show the id, owner and kind of the accounts that have never paid rent (label 'rent').

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

Query structure:

SELECT …, …, …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = … AND … = …)

Show the solution

SELECT a.id, a.owner, a.kind
FROM accounts a
WHERE NOT EXISTS (SELECT 1 FROM transactions t WHERE t.account_id = a.id AND t.label = 'rent');

Expected result (3 rows):

id	owner	kind
2	Alice	savings
5	David	savings
6	Emma	current

owner

kind

Alice

savings

David

savings

Emma

current

Exercise 12 · level 8

Show the name of the authors all of whose books have been borrowed at least once (among the authors who have at least one book).

Table `authors` (6 rows)
id	name	country	birth_year
1	Maya Lind	Sweden	1968
2	Omar Haddad	Morocco	1975
3	Julia Brandt	Germany	1981
4	Tom Reyes	Mexico	1990
5	Ines Dupuis	France	1959
6	Ravi Menon	India	1984

Table `books` (10 rows)
id	title	author_id	genre	pages	year
1	Cold River	1	Novel	320	2011
2	Salt Roads	2	Travel	210	2016
3	The Glass Hive	3	Sci-Fi	412	2019
4	Winter Ledger	1	Crime	288	2014
5	Desert Letters	2	Novel	356	2020
6	Small Engines	4	Sci-Fi	198	2022
7	Harbor Lights	5	Novel	445	2008
8	Night Garden	3	Crime	301	2017
9	Paper Birds	5	Poetry	96	2012
10	Open Maps	4	Travel	240	2021

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: WHERE (filters), EXISTS.

Query structure:

SELECT …
FROM … …
WHERE EXISTS (SELECT … FROM … … WHERE … = …) AND NOT EXISTS (SELECT … FROM … … WHERE … = … AND NOT EXISTS (SELECT … FROM … … WHERE … = …))

Show the solution

SELECT name
FROM authors a
WHERE EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id) AND NOT EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id AND NOT EXISTS (SELECT 1 FROM loans l WHERE l.book_id = b.id));

Expected result (4 rows):

name
Omar Haddad
Julia Brandt
Tom Reyes
Ines Dupuis

name

Omar Haddad

Julia Brandt

Tom Reyes

Ines Dupuis

Exercise 13 · level 8

Show the name of the hotels that have rooms and whose rooms have all been booked at least once.

Table `hotels` (5 rows)
id	name	city	stars
1	Seaside Inn	Nice	3
2	Alpine Lodge	Annecy	4
3	City Loft	Paris	4
4	Old Mill	Bordeaux	2
5	Grand Palace	Paris	5

Table `rooms` (10 rows)
id	hotel_id	type	price
1	1	single	70
2	1	double	95
3	2	double	130
4	2	suite	210
5	3	single	110
6	3	double	150
7	4	double	65
8	5	double	260
9	5	suite	480
10	5	single	190

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: WHERE (filters), EXISTS.

Query structure:

SELECT …
FROM … …
WHERE EXISTS (SELECT … FROM … … WHERE … = …) AND NOT EXISTS (SELECT … FROM … … WHERE … = … AND NOT EXISTS (SELECT … FROM … … WHERE … = …))

Show the solution

SELECT h.name
FROM hotels h
WHERE EXISTS (SELECT 1 FROM rooms r WHERE r.hotel_id = h.id) AND NOT EXISTS (SELECT 1 FROM rooms r WHERE r.hotel_id = h.id AND NOT EXISTS (SELECT 1 FROM bookings b WHERE b.room_id = r.id));

Expected result (4 rows):

name
Seaside Inn
Alpine Lodge
City Loft
Old Mill

name

Seaside Inn

Alpine Lodge

City Loft

Old Mill

Exercise 14 · level 8

Show the name of the customers who ordered every product of the Kitchen category (each at least once, cancelled orders included).

Table `customers` (6 rows)
id	name	city	signup
1	Alba	Paris	2024-01-10
2	Boris	Lyon	2024-02-15
3	Carla	Paris	2024-03-01
4	Denis	Nantes	2024-05-20
5	Eva	Lyon	2024-06-30
6	Fabio	Lille	2024-08-08

Table `orders` (8 rows)
id	customer_id	order_date	status
1	1	2025-01-05	shipped
2	2	2025-01-12	shipped
3	1	2025-02-03	paid
4	3	2025-02-10	cancelled
5	4	2025-02-20	shipped
6	5	2025-03-02	shipped
7	2	2025-03-15	paid
8	3	2025-03-28	shipped

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: WHERE (filters), JOIN, EXISTS.

Query structure:

SELECT …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = … AND NOT EXISTS (SELECT … FROM … … JOIN … … ON … = … WHERE … = … AND … = …))

Show the solution

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category = 'Kitchen' AND NOT EXISTS (SELECT 1 FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.customer_id = c.id AND oi.product_id = p.id));

Expected result (1 row):

name
Carla

name

Carla

Exercise 15 · level 8

Show the name of the developers who have at least one task on every project of the client Acme.

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

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: WHERE (filters), EXISTS.

Query structure:

SELECT …
FROM … …
WHERE NOT EXISTS (SELECT … FROM … … WHERE … = … AND NOT EXISTS (SELECT … FROM … … WHERE … = … AND … = …))

Show the solution

SELECT d.name
FROM devs d
WHERE NOT EXISTS (SELECT 1 FROM projects p WHERE p.client = 'Acme' AND NOT EXISTS (SELECT 1 FROM tasks t WHERE t.project_id = p.id AND t.dev_id = d.id));

Expected result (2 rows):

name
Ana
Cleo

name

Ana

Cleo

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 22 “EXISTS” questions