SQL exercises with solutions: HAVING

Updated on 22 September 2026

HAVING filters groups after GROUP BY (WHERE filters rows before). These 12 exercises range from level 2 to level 3; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: A condition on COUNT, SUM or AVG goes in HAVING, never in WHERE.

Read the “HAVING” card in the cheat sheet

Exercise 1 · level 2

Show the departments that have more than 2 employees, with their number of 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: COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING.

Query structure:

SELECT …, COUNT(*)
FROM …
GROUP BY …
HAVING COUNT(*) > …

Show the solution

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Expected result (2 rows):

department	COUNT(*)
Finance	3
IT	3

department

COUNT(*)

Finance

Exercise 2 · level 3

Show the name and the total order amount of the customers whose total is above 160.

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, HAVING.

Query structure:

SELECT …, SUM(…)
FROM …
INNER JOIN … ON … = …
GROUP BY …
HAVING SUM(…) > …

Show the solution

SELECT customers.name, SUM(orders.amount)
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id
HAVING SUM(orders.amount) > 160;

Expected result (2 rows):

name	SUM(orders.amount)
Alice	200
Bruno	200

name

SUM(orders.amount)

Alice

200

Bruno

200

Exercise 3 · level 3

Show each city whose total order amount is above 250, with that total.

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, HAVING.

Query structure:

SELECT …, SUM(…)
FROM …
INNER JOIN … ON … = …
GROUP BY …
HAVING SUM(…) > …

Show the solution

SELECT customers.city, SUM(orders.amount)
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.city
HAVING SUM(orders.amount) > 250;

Expected result (1 row):

city	SUM(orders.amount)
Paris	355

city

SUM(orders.amount)

Paris

355

Exercise 4 · level 3

Show the name of the directors who made at least 2 movies, with their number of movies and the average rating of their movies.

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, HAVING.

Query structure:

SELECT …, COUNT(*), AVG(…)
FROM … …
JOIN … … ON … = …
GROUP BY …
HAVING COUNT(*) >= …

Show the solution

SELECT d.name, COUNT(*), AVG(m.rating)
FROM directors d
JOIN movies m ON m.director_id = d.id
GROUP BY d.id
HAVING COUNT(*) >= 2;

Expected result (4 rows):

name	COUNT(*)	AVG(m.rating)
Nora Ellis	2	7.65
Paulo Reis	2	6.949999999999999
Kenji Mori	2	8.1
Luc Martin	2	6.9

Exercise 5 · level 3

Show the title of the books borrowed at least twice, with their number of loans.

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

Query structure:

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

Show the solution

SELECT b.title, COUNT(*)
FROM books b
JOIN loans l ON l.book_id = b.id
GROUP BY b.id
HAVING COUNT(*) >= 2;

Expected result (2 rows):

title	COUNT(*)
Cold River	2
The Glass Hive	3

title

COUNT(*)

Cold River

The Glass Hive

Exercise 6 · level 3

For each team, show its name and the number of goals scored at home, only for the teams that scored at least 3 goals at home.

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

Query structure:

SELECT …, SUM(…)
FROM … …
JOIN … … ON … = …
GROUP BY …
HAVING SUM(…) >= …

Show the solution

SELECT t.name, SUM(m.home_goals)
FROM teams t
JOIN matches m ON m.home_id = t.id
GROUP BY t.id
HAVING SUM(m.home_goals) >= 3;

Expected result (2 rows):

name	SUM(m.home_goals)
Red Foxes	3
Gold Hawks	4

name

SUM(m.home_goals)

Red Foxes

Gold Hawks

Exercise 7 · level 3

For each departure airport, show its code and the total number of seats sold, only for the airports with more than 250 seats sold in total.

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

Query structure:

SELECT …, SUM(…)
FROM …
GROUP BY …
HAVING SUM(…) > …

Show the solution

SELECT origin, SUM(seats_sold)
FROM flights
GROUP BY origin
HAVING SUM(seats_sold) > 250;

Expected result (2 rows):

origin	SUM(seats_sold)
CDG	605
MAD	265

origin

SUM(seats_sold)

CDG

605

MAD

265

Exercise 8 · level 3

For each city, show the city and the total rain, only for the cities that received at least 5 mm in total.

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

Query structure:

SELECT …, SUM(…)
FROM …
GROUP BY …
HAVING SUM(…) >= …

Show the solution

SELECT city, SUM(rain_mm)
FROM readings
GROUP BY city
HAVING SUM(rain_mm) >= 5;

Expected result (2 rows):

city	SUM(rain_mm)
Lyon	11.5
Paris	16.5

city

SUM(rain_mm)

Lyon

11.5

Paris

16.5

Exercise 9 · level 3

Show the name of the hotels with at least 3 bookings (all rooms together), with their number of bookings.

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

Query structure:

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

Show the solution

SELECT h.name, COUNT(*)
FROM hotels h
JOIN rooms r ON r.hotel_id = h.id
JOIN bookings b ON b.room_id = r.id
GROUP BY h.id
HAVING COUNT(*) >= 3;

Expected result (1 row):

name	COUNT(*)
City Loft	4

name

COUNT(*)

City Loft

Exercise 10 · level 3

Show the name of the products ordered in at least 3 units in total, with the total quantity.

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

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

Show the hint

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

Query structure:

SELECT …, SUM(…)
FROM … …
JOIN … … ON … = …
GROUP BY …
HAVING SUM(…) >= …

Show the solution

SELECT p.name, SUM(oi.qty)
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id
HAVING SUM(oi.qty) >= 3;

Expected result (4 rows):

name	SUM(oi.qty)
Coffee Mug	8
Notebook	8
Kettle	3
Cushion	3

Exercise 11 · level 3

Show the name of the projects with at least 2 finished tasks ('done'), with that number of tasks.

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

Query structure:

SELECT …, COUNT(*)
FROM … …
JOIN … … ON … = …
WHERE … = …
GROUP BY …
HAVING COUNT(*) >= …

Show the solution

SELECT p.name, COUNT(*)
FROM projects p
JOIN tasks t ON t.project_id = p.id
WHERE t.status = 'done'
GROUP BY p.id
HAVING COUNT(*) >= 2;

Expected result (2 rows):

name	COUNT(*)
Atlas	2
Beacon	2

name

COUNT(*)

Atlas

Beacon

Exercise 12 · level 3

For each label that appears at least 3 times, show the label, the number of transactions and their sum.

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

Query structure:

SELECT …, COUNT(*), SUM(…)
FROM …
GROUP BY …
HAVING COUNT(*) >= …

Show the solution

SELECT label, COUNT(*), SUM(amount)
FROM transactions
GROUP BY label
HAVING COUNT(*) >= 3;

Expected result (3 rows):

label	COUNT(*)	SUM(amount)
groceries	3	-255
rent	3	-2350
salary	4	9100

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 20 “HAVING” questions