SQL exercises with solutions: CASE WHEN

Updated on 22 September 2026

CASE picks a value depending on conditions, row by row. These 15 exercises range from level 4 to level 7; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: Conditions are tested in order: the first true one wins.

Read the “CASE” card in the cheat sheet

Exercise 1 · level 4

Show each employee's name and a column called level that is 'high' if their salary is at least 40000, otherwise 'low'. Use CASE.

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

Query structure:

SELECT …, CASE WHEN … >= … THEN … ELSE … END AS …
FROM …

Show the solution

SELECT name, CASE WHEN salary >= 40000 THEN 'high' ELSE 'low' END AS level
FROM employees;

Expected result (8 rows):

name	level
Alice	low
Bob	high
Claire	low
David	low
Emma	high
Farid	high
Gaelle	low
Hugo	high

name

level

Alice

low

Bob

high

Claire

low

David

low

Emma

high

Farid

high

Gaelle

low

Hugo

high

Exercise 2 · level 4

Show each invoice's id and a column called status that is 'paid' if the invoice has a payment date, otherwise 'unpaid'. Use CASE.

Table `invoices` (6 rows)
id	client	issued	due	amount	paid_on
1	Acme	2025-01-15	2025-02-14	1200	2025-02-10
2	Acme	2025-03-01	2025-03-16	800	NULL
3	Bolt	2025-01-20	2025-03-06	450	2025-03-01
4	Bolt	2025-02-25	2025-03-27	950	2025-04-02
5	Cyan	2025-03-05	2025-05-04	300	NULL
6	Cyan	2024-12-10	2024-12-30	600	2025-01-05

Show the hint

Topics to use: CASE, NULL, COALESCE.

Query structure:

SELECT …, CASE WHEN … IS NULL THEN … ELSE … END AS …
FROM …

Show the solution

SELECT id, CASE WHEN paid_on IS NULL THEN 'unpaid' ELSE 'paid' END AS status
FROM invoices;

Expected result (6 rows):

id	status
1	paid
2	unpaid
3	paid
4	paid
5	unpaid
6	paid

Exercise 3 · level 4

Show the title of each movie and a column called length that is 'short' if it lasts less than 100 minutes, 'medium' if it lasts at most 120 minutes, otherwise 'long'. Use CASE.

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

Query structure:

SELECT …, CASE WHEN … < … THEN … WHEN … <= … THEN … ELSE … END AS …
FROM …

Show the solution

SELECT title, CASE WHEN duration < 100 THEN 'short' WHEN duration <= 120 THEN 'medium' ELSE 'long' END AS length
FROM movies;

Expected result (10 rows):

title	length
Night Train	medium
Blue Harbor	medium
Paper Moon City	short
Silent Peak	long
Last Signal	long
Summer Keys	short
Iron Garden	long
Dust and Gold	medium
The Quiet Hour	short
Deep Current	medium

title

length

Night Train

medium

Blue Harbor

medium

Paper Moon City

short

Silent Peak

long

Last Signal

long

Summer Keys

short

Iron Garden

long

Dust and Gold

medium

The Quiet Hour

short

Deep Current

medium

Exercise 4 · level 4

Show the date of each match and a column called result that is 'home' if the home team wins, 'away' if the away team wins, otherwise 'draw' (columns: played_on, result). Use CASE.

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

Query structure:

SELECT …, CASE WHEN … > … THEN … WHEN … < … THEN … ELSE … END AS …
FROM …

Show the solution

SELECT played_on, CASE WHEN home_goals > away_goals THEN 'home' WHEN home_goals < away_goals THEN 'away' ELSE 'draw' END AS result
FROM matches;

Expected result (10 rows):

played_on	result
2025-08-02	home
2025-08-03	draw
2025-08-09	away
2025-08-10	draw
2025-08-16	home
2025-08-17	home
2025-08-23	away
2025-08-24	away
2025-08-30	draw
2025-08-31	away

Exercise 5 · level 4

Show the id of each flight and a column called slot that is 'morning' if it departs before 12:00, 'afternoon' if it departs before 18:00, otherwise 'evening' (columns: id, slot). Use CASE.

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: CASE, Text and dates.

Query structure:

SELECT …, CASE WHEN STRFTIME(…, …) < … THEN … WHEN STRFTIME(…, …) < … THEN … ELSE … END AS …
FROM …

Show the solution

SELECT id, CASE WHEN strftime('%H:%M', departs) < '12:00' THEN 'morning' WHEN strftime('%H:%M', departs) < '18:00' THEN 'afternoon' ELSE 'evening' END AS slot
FROM flights;

Expected result (12 rows):

id	slot
1	morning
2	morning
3	morning
4	evening
5	morning
6	afternoon
7	morning
8	afternoon
9	evening
10	morning
11	afternoon
12	afternoon

Exercise 6 · level 4

Show the name and city of each hotel, and a category column: 'luxury' for 5 stars, 'comfort' for 3 or 4 stars, 'budget' otherwise. Name the columns name, city and category.

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

Query structure:

SELECT …, …, CASE WHEN … = … THEN … WHEN … >= … THEN … ELSE … END AS …
FROM …

Show the solution

SELECT name, city, CASE WHEN stars = 5 THEN 'luxury' WHEN stars >= 3 THEN 'comfort' ELSE 'budget' END AS category
FROM hotels;

Expected result (5 rows):

name	city	category
Seaside Inn	Nice	comfort
Alpine Lodge	Annecy	comfort
City Loft	Paris	comfort
Old Mill	Bordeaux	budget
Grand Palace	Paris	luxury

name

city

Exercise 7 · level 4

Show the name of each product and a size column: 'small' under 10, 'medium' from 10 to under 50, 'large' otherwise. Name the columns name and size.

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

Query structure:

SELECT …, CASE WHEN … < … THEN … WHEN … < … THEN … ELSE … END AS …
FROM …

Show the solution

SELECT name, CASE WHEN price < 10 THEN 'small' WHEN price < 50 THEN 'medium' ELSE 'large' END AS size
FROM products;

Expected result (7 rows):

name	size
Desk Lamp	medium
Coffee Mug	medium
Notebook	small
Office Chair	large
Kettle	medium
Cushion	medium
Stapler	small

name

size

Desk Lamp

medium

Coffee Mug

medium

Notebook

small

Office Chair

large

Kettle

medium

Cushion

medium

Stapler

small

Exercise 8 · level 4

Show the id and amount of each transaction, and a direction column: 'credit' if the amount is positive, 'debit' otherwise. Name the columns id, amount and direction.

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

Query structure:

SELECT …, …, CASE WHEN … > … THEN … ELSE … END AS …
FROM …

Show the solution

SELECT id, amount, CASE WHEN amount > 0 THEN 'credit' ELSE 'debit' END AS direction
FROM transactions;

Expected result (14 rows):

id	amount	direction
1	2500	credit
2	-60	debit
3	-800	debit
4	500	credit
5	1900	credit
6	-120	debit
7	-950	debit
8	2100	credit
9	-45	debit
10	2600	credit
11	-75	debit
12	30	credit
13	500	credit
14	-600	debit

Exercise 9 · level 7

For each department, show the number of employees paid at least 45000 in a column called high and the number of those paid less than 45000 in a column called low (columns: department, high, low).

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

Query structure:

SELECT …, SUM(… >= …) AS …, SUM(… < …) AS …
FROM …
GROUP BY …

Show the solution

SELECT department, SUM(salary >= 45000) AS high, SUM(salary < 45000) AS low
FROM staff
GROUP BY department;

Expected result (3 rows):

department	high	low
Finance	1	3
HR	0	2
IT	3	1

Exercise 10 · level 7

Show one row per release decade (2010 for 2010 to 2019, 2020 for 2020 to 2029), with the number of Drama movies in a column called drama and the number of the other movies in a column called other (columns: decade, drama, other).

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

Query structure:

SELECT (… / …) * … AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … <> … THEN … ELSE … END) AS …
FROM …
GROUP BY …

Show the solution

SELECT (year / 10) * 10 AS decade, SUM(CASE WHEN genre = 'Drama' THEN 1 ELSE 0 END) AS drama, SUM(CASE WHEN genre <> 'Drama' THEN 1 ELSE 0 END) AS other
FROM movies
GROUP BY decade;

Expected result (2 rows):

decade	drama	other
2010	1	6
2020	2	1

Exercise 11 · level 7

Show one row per team that played at home (columns: name, wins, draws, losses), with its number of home wins, draws and losses.

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

Query structure:

SELECT …, SUM(CASE WHEN … > … THEN … ELSE … END) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … < … THEN … ELSE … END) AS …
FROM … …
JOIN … … ON … = …
GROUP BY …

Show the solution

SELECT t.name, SUM(CASE WHEN m.home_goals > m.away_goals THEN 1 ELSE 0 END) AS wins, SUM(CASE WHEN m.home_goals = m.away_goals THEN 1 ELSE 0 END) AS draws, SUM(CASE WHEN m.home_goals < m.away_goals THEN 1 ELSE 0 END) AS losses
FROM teams t
JOIN matches m ON m.home_id = t.id
GROUP BY t.id;

Expected result (5 rows):

name	wins	draws	losses
Red Foxes	2	0	0
Blue Owls	0	1	1
Green Bulls	0	1	1
Gold Hawks	1	1	0
Grey Wolves	0	0	2

Exercise 12 · level 7

Show one row per departure day (columns: day, skyjet, airnova, bluewing) with the number of flights of each airline that day (YYYY-MM-DD format for day).

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, CASE, Text and dates.

Query structure:

SELECT DATE(…) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …, SUM(CASE WHEN … = … THEN … ELSE … END) AS …
FROM …
GROUP BY …

Show the solution

SELECT date(departs) AS day, SUM(CASE WHEN airline = 'SkyJet' THEN 1 ELSE 0 END) AS skyjet, SUM(CASE WHEN airline = 'AirNova' THEN 1 ELSE 0 END) AS airnova, SUM(CASE WHEN airline = 'BlueWing' THEN 1 ELSE 0 END) AS bluewing
FROM flights
GROUP BY day;

Expected result (6 rows):

day	skyjet	airnova	bluewing
2025-06-01	1	1	0
2025-06-02	1	0	1
2025-06-03	0	1	1
2025-06-04	1	1	0
2025-06-05	1	0	1
2025-06-06	0	1	1

Exercise 13 · level 7

For each hotel, show its name and the number of single, double and suite rooms in three columns (0 if there are none).

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

Show the hint

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

Query structure:

SELECT …, SUM(CASE WHEN … = … THEN … ELSE … END), SUM(CASE WHEN … = … THEN … ELSE … END), SUM(CASE WHEN … = … THEN … ELSE … END)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

Show the solution

SELECT h.name, SUM(CASE WHEN r.type = 'single' THEN 1 ELSE 0 END), SUM(CASE WHEN r.type = 'double' THEN 1 ELSE 0 END), SUM(CASE WHEN r.type = 'suite' THEN 1 ELSE 0 END)
FROM hotels h
LEFT JOIN rooms r ON r.hotel_id = h.id
GROUP BY h.id;

Expected result (5 rows):

name	SUM(CASE WHEN r.type = 'single' THEN 1 ELSE 0 END)	SUM(CASE WHEN r.type = 'double' THEN 1 ELSE 0 END)	SUM(CASE WHEN r.type = 'suite' THEN 1 ELSE 0 END)
Seaside Inn	1	1	0
Alpine Lodge	0	1	1
City Loft	1	1	0
Old Mill	0	1	0
Grand Palace	1	1	1

Exercise 14 · level 7

For each customer with orders that are not cancelled, show their name and the amount spent in January, February and March 2025 in three columns (0 if nothing).

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), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE, Text and dates.

Query structure:

SELECT …, SUM(CASE WHEN … LIKE … THEN … * … ELSE … END), SUM(CASE WHEN … LIKE … THEN … * … ELSE … END), SUM(CASE WHEN … LIKE … THEN … * … ELSE … END)
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
JOIN … … ON … = …
WHERE … <> …
GROUP BY …

Show the solution

SELECT c.name, SUM(CASE WHEN o.order_date LIKE '2025-01%' THEN oi.qty * p.price ELSE 0 END), SUM(CASE WHEN o.order_date LIKE '2025-02%' THEN oi.qty * p.price ELSE 0 END), SUM(CASE WHEN o.order_date LIKE '2025-03%' THEN oi.qty * p.price ELSE 0 END)
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 o.status <> 'cancelled'
GROUP BY c.id;

Expected result (5 rows):

name	SUM(CASE WHEN o.order_date LIKE '2025-01%' THEN oi.qty * p.price ELSE 0 END)	SUM(CASE WHEN o.order_date LIKE '2025-02%' THEN oi.qty * p.price ELSE 0 END)	SUM(CASE WHEN o.order_date LIKE '2025-03%' THEN oi.qty * p.price ELSE 0 END)
Alba	59	42	0
Boris	149	0	171
Carla	0	0	102
Denis	0	79	0
Eva	0	0	66

Exercise 15 · level 7

For each account, show its id, its owner, the total credits, the total debits (as a positive number) and the balance, in separate columns (0 without transactions).

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

Query structure:

SELECT …, …, COALESCE(SUM(CASE WHEN … > … THEN … END), …), COALESCE(-SUM(CASE WHEN … < … THEN … END), …), COALESCE(SUM(…), …)
FROM … …
LEFT JOIN … … ON … = …
GROUP BY …

Show the solution

SELECT a.id, a.owner, COALESCE(SUM(CASE WHEN t.amount > 0 THEN t.amount END), 0), COALESCE(-SUM(CASE WHEN t.amount < 0 THEN t.amount END), 0), COALESCE(SUM(t.amount), 0)
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.id
GROUP BY a.id;

Expected result (6 rows):

id	owner	COALESCE(SUM(CASE WHEN t.amount > 0 THEN t.amount END), 0)	COALESCE(-SUM(CASE WHEN t.amount < 0 THEN t.amount END), 0)	COALESCE(SUM(t.amount), 0)
1	Alice	5100	935	4165
2	Alice	1000	0	1000
3	Bruno	1900	1070	830
4	Chloe	2100	645	1455
5	David	30	0	30
6	Emma	0	0	0

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 25 “CASE” questions