SQL exercises with solutions: UNION, INTERSECT, EXCEPT

Updated on 22 September 2026

UNION merges two results (no duplicates), INTERSECT keeps what they share, EXCEPT removes the second from the first. These 11 exercises range from level 4 to level 4; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: Both queries must return the same number of columns.

Read the “UNION, INTERSECT, EXCEPT” card in the cheat sheet

Exercise 1 · level 4

Show the list, without duplicates, of all the members of both clubs (tables chess and music). Use UNION.

Table `chess` (4 rows)
member
Alice
Bob
Claire
David

Table `music` (4 rows)
member
Claire
David
Emma
Farid

Show the hint

Topics to use: UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM …
UNION SELECT …
FROM …

Show the solution

SELECT member
FROM chess
UNION SELECT member
FROM music;

Expected result (6 rows):

member

Alice

Bob

Claire

David

Emma

Farid

Exercise 2 · level 4

Show the members who belong to both clubs at the same time. Use INTERSECT.

Table `chess` (4 rows)
member
Alice
Bob
Claire
David

Table `music` (4 rows)
member
Claire
David
Emma
Farid

Show the hint

Topics to use: UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM …
INTERSECT SELECT …
FROM …

Show the solution

SELECT member
FROM chess
INTERSECT SELECT member
FROM music;

Expected result (2 rows):

member

Claire

David

Exercise 3 · level 4

Show the members of the chess club who are not in the music club. Use EXCEPT.

Table `chess` (4 rows)
member
Alice
Bob
Claire
David

Table `music` (4 rows)
member
Claire
David
Emma
Farid

Show the hint

Topics to use: UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM …
EXCEPT SELECT …
FROM …

Show the solution

SELECT member
FROM chess
EXCEPT SELECT member
FROM music;

Expected result (2 rows):

member

Alice

Bob

Exercise 4 · level 4

Show the genres in which every movie has a rating of at least 7: take the genres that have a movie rated at least 7, except those that have a movie rated below 7. Use EXCEPT.

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), UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM …
WHERE … >= …
EXCEPT SELECT …
FROM …
WHERE … < …

Show the solution

SELECT genre
FROM movies
WHERE rating >= 7
EXCEPT SELECT genre
FROM movies
WHERE rating < 7;

Expected result (2 rows):

genre
Drama
Sci-Fi

genre

Drama

Sci-Fi

Exercise 5 · level 4

Show the title of the books borrowed both by a member from Paris and by a member from Lille. Use INTERSECT.

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

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

Show the hint

Topics to use: WHERE (filters), JOIN, UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
WHERE … = …
INTERSECT SELECT …
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
WHERE … = …

Show the solution

SELECT b.title
FROM books b
JOIN loans l ON l.book_id = b.id
JOIN members m ON m.id = l.member_id
WHERE m.city = 'Paris'
INTERSECT SELECT b.title
FROM books b
JOIN loans l ON l.book_id = b.id
JOIN members m ON m.id = l.member_id
WHERE m.city = 'Lille';

Expected result (1 row):

title
The Glass Hive

title

The Glass Hive

Exercise 6 · level 4

Show, without duplicates, the name of the teams that won at least one match, at home or away. Use UNION.

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

Query structure:

SELECT …
FROM … …
JOIN … … ON … = …
WHERE … > …
UNION SELECT …
FROM … …
JOIN … … ON … = …
WHERE … > …

Show the solution

SELECT t.name
FROM teams t
JOIN matches m ON m.home_id = t.id
WHERE m.home_goals > m.away_goals
UNION SELECT t.name
FROM teams t
JOIN matches m ON m.away_id = t.id
WHERE m.away_goals > m.home_goals;

Expected result (4 rows):

name
Blue Owls
Gold Hawks
Grey Wolves
Red Foxes

name

Blue Owls

Gold Hawks

Grey Wolves

Red Foxes

Exercise 7 · level 4

Show the arrival cities served by both SkyJet and AirNova. Use INTERSECT.

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

Query structure:

SELECT …
FROM … …
JOIN … … ON … = …
WHERE … = …
INTERSECT SELECT …
FROM … …
JOIN … … ON … = …
WHERE … = …

Show the solution

SELECT a.city
FROM flights f
JOIN airports a ON a.code = f.dest
WHERE f.airline = 'SkyJet'
INTERSECT SELECT a.city
FROM flights f
JOIN airports a ON a.code = f.dest
WHERE f.airline = 'AirNova';

Expected result (2 rows):

city
Madrid
Paris

city

Madrid

Paris

Exercise 8 · level 4

Show the days on which it rained both in Paris and in Lyon. Use INTERSECT.

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), UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM …
WHERE … = … AND … > …
INTERSECT SELECT …
FROM …
WHERE … = … AND … > …

Show the solution

SELECT day
FROM readings
WHERE city = 'Paris' AND rain_mm > 0
INTERSECT SELECT day
FROM readings
WHERE city = 'Lyon' AND rain_mm > 0;

Expected result (1 row):

day
2025-07-04

day

2025-07-04

Exercise 9 · level 4

Using EXCEPT, show the countries of the guests, except those of the guests who stayed in a hotel in Paris.

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

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 `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

Show the hint

Topics to use: WHERE (filters), JOIN, UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM …
EXCEPT SELECT …
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
JOIN … … ON … = …
WHERE … = …

Show the solution

SELECT country
FROM guests
EXCEPT SELECT g.country
FROM guests g
JOIN bookings b ON b.guest_id = g.id
JOIN rooms r ON r.id = b.room_id
JOIN hotels h ON h.id = r.hotel_id
WHERE h.city = 'Paris';

Expected result (2 rows):

country
Morocco
Portugal

country

Morocco

Portugal

Exercise 10 · level 4

Using INTERSECT, show the name of the customers who ordered both an Office product and a Kitchen product.

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, UNION, INTERSECT, EXCEPT.

Query structure:

SELECT …
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
JOIN … … ON … = …
WHERE … = …
INTERSECT SELECT …
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
JOIN … … ON … = …
WHERE … = …

Show the solution

SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE p.category = 'Office'
INTERSECT SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE p.category = 'Kitchen';

Expected result (2 rows):

name
Alba
Eva

name

Alba

Eva

Exercise 11 · level 4

Using UNION, show without duplicates the owners who received a salary (label 'salary') and those who have a savings account (kind 'savings').

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

Query structure:

SELECT …
FROM … …
JOIN … … ON … = …
WHERE … = …
UNION SELECT …
FROM …
WHERE … = …

Show the solution

SELECT a.owner
FROM accounts a
JOIN transactions t ON t.account_id = a.id
WHERE t.label = 'salary'
UNION SELECT owner
FROM accounts
WHERE kind = 'savings';

Expected result (4 rows):

owner

Alice

Bruno

Chloe

David

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 13 “UNION, INTERSECT, EXCEPT” questions