Exercices SQL corrigés : les CTE récursives

Mis à jour le 22 septembre 2026

Une CTE récursive s’appelle elle-même pour générer une suite ou parcourir une hiérarchie. Toujours prévoir une condition d’arrêt. Ces 11 exercices vont du niveau 7 au niveau 8 ; ils utilisent la syntaxe de SQLite, le moteur SQL de SpeedQL.

Conseil : Une partie de départ, UNION ALL, puis la partie qui se rappelle avec un WHERE qui s’arrête.

Relire la fiche « CTE récursives » de l’aide-mémoire

Exercice 1 · niveau 7

Affiche chaque employé avec son niveau hiérarchique : 0 pour celui qui n'a pas de manager, 1 pour ses subordonnés directs, etc. (colonnes : name, level). Utilise une CTE récursive.

Table `staff` (10 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), JOIN, NULL, COALESCE, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…, …, …) AS (
  SELECT …, …, …
  FROM …
  WHERE … IS NULL
  UNION ALL SELECT …, …, … + …
  FROM … …
  JOIN … ON … = …)
SELECT …, …
FROM …

Voir la correction

WITH RECURSIVE t(id, name, level) AS (
  SELECT id, name, 0
  FROM staff
  WHERE manager_id IS NULL
  UNION ALL SELECT s.id, s.name, t.level + 1
  FROM staff s
  JOIN t ON s.manager_id = t.id)
SELECT name, level
FROM t;

Résultat attendu (10 lignes) :

name	level
Alice	0
Bob	1
Claire	1
David	1
Emma	2
Farid	2
Hugo	2
Jules	2
Gaelle	2
Iris	3

Exercice 2 · niveau 7

Pour chaque année de 2012 à 2022, affiche l'année et le nombre de films sortis cette année-là (0 s'il n'y en a pas). Utilise une CTE récursive pour générer les années.

Table `movies` (10 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT … + …
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … WHERE … = …)
FROM …

Voir la correction

WITH RECURSIVE y(n) AS (
  SELECT 2012
  UNION ALL SELECT n + 1
  FROM y
  WHERE n < 2022)
SELECT n, (SELECT COUNT(*) FROM movies WHERE year = n)
FROM y;

Résultat attendu (11 lignes) :

n	(SELECT COUNT(*) FROM movies WHERE year = n)
2012	1
2013	0
2014	1
2015	1
2016	1
2017	1
2018	1
2019	1
2020	1
2021	1
2022	1

Exercice 3 · niveau 7

Pour chaque jour du 2025-03-01 au 2025-03-10, affiche la date et le nombre d'emprunts commencés ce jour-là (0 s'il n'y en a pas). Utilise une CTE récursive pour générer les dates.

Table `loans` (12 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, Texte et dates, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … WHERE … = …)
FROM …

Voir la correction

WITH RECURSIVE d(day) AS (
  SELECT '2025-03-01'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-03-10')
SELECT day, (SELECT COUNT(*) FROM loans WHERE loan_date = day)
FROM d;

Résultat attendu (10 lignes) :

day	(SELECT COUNT(*) FROM loans WHERE loan_date = day)
2025-03-01	0
2025-03-02	1
2025-03-03	0
2025-03-04	0
2025-03-05	1
2025-03-06	0
2025-03-07	0
2025-03-08	0
2025-03-09	0
2025-03-10	1

Exercice 4 · niveau 7

Le championnat compte 5 journées : la journée 1 commence le 2025-08-02 et chaque journée commence 7 jours après la précédente. Pour chaque journée, affiche son numéro, sa date de début et le nombre de buts marqués ce jour-là ou le lendemain (0 s'il n'y en a pas). Utilise une CTE récursive.

Table `matches` (10 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, NULL, COALESCE, Texte et dates, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…, …) AS (
  SELECT …, …
  UNION ALL SELECT … + …, DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, …, COALESCE((SELECT SUM(… + …) FROM … WHERE … BETWEEN … AND DATE(…, …)), …)
FROM …

Voir la correction

WITH RECURSIVE r(n, d) AS (
  SELECT 1, '2025-08-02'
  UNION ALL SELECT n + 1, date(d, '+7 day')
  FROM r
  WHERE n < 5)
SELECT n, d, COALESCE((SELECT SUM(home_goals + away_goals) FROM matches WHERE played_on BETWEEN d AND date(d, '+1 day')), 0)
FROM r;

Résultat attendu (5 lignes) :

n	d	COALESCE((SELECT SUM(home_goals + away_goals) FROM matches WHERE played_on BETWEEN d AND date(d, '+1 day')), 0)
1	2025-08-02	3
2	2025-08-09	8
3	2025-08-16	5
4	2025-08-23	6
5	2025-08-30	4

Exercice 5 · niveau 7

Depuis CDG, trouve les aéroports atteignables en prenant au plus 2 vols (sans tenir compte des horaires) : affiche le code de chaque aéroport, autre que CDG, et le nombre minimal de vols pour l'atteindre. Utilise une CTE récursive.

Table `flights` (12 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…, …) AS (
  SELECT …, …
  UNION SELECT …, … + …
  FROM …
  JOIN … … ON … = …
  WHERE … < …)
SELECT …, MIN(…)
FROM …
WHERE … <> …
GROUP BY …

Voir la correction

WITH RECURSIVE r(code, n) AS (
  SELECT 'CDG', 0
  UNION SELECT f.dest, r.n + 1
  FROM r
  JOIN flights f ON f.origin = r.code
  WHERE r.n < 2)
SELECT code, MIN(n)
FROM r
WHERE code <> 'CDG'
GROUP BY code;

Résultat attendu (5 lignes) :

code	MIN(n)
BER	1
FCO	1
LIS	1
LYS	2
MAD	1

Exercice 6 · niveau 7

Pour chaque jour du 2025-06-29 au 2025-07-05, affiche le jour et la pluie totale relevée toutes villes confondues (0 s'il n'y a aucun relevé ce jour-là). Utilise une CTE récursive pour générer les jours.

Table `readings` (15 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, NULL, COALESCE, Texte et dates, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, COALESCE((SELECT SUM(…) FROM … … WHERE … = …), …)
FROM …

Voir la correction

WITH RECURSIVE d(day) AS (
  SELECT '2025-06-29'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-07-05')
SELECT day, COALESCE((SELECT SUM(rain_mm) FROM readings r WHERE r.day = d.day), 0)
FROM d;

Résultat attendu (7 lignes) :

day	COALESCE((SELECT SUM(rain_mm) FROM readings r WHERE r.day = d.day), 0)
2025-06-29	0
2025-06-30	0
2025-07-01	0
2025-07-02	0
2025-07-03	4.5
2025-07-04	20.5
2025-07-05	3

Exercice 7 · niveau 7

Pour chaque nuit du 2025-07-01 au 2025-07-10, affiche la date et le nombre de chambres occupées (arrivée au plus tard ce jour-là et départ après ce jour-là). Utilise une CTE récursive pour générer les dates.

Table `bookings` (11 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, Texte et dates, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … … WHERE … <= … AND … > …)
FROM …

Voir la correction

WITH RECURSIVE d(day) AS (
  SELECT '2025-07-01'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-07-10')
SELECT day, (SELECT COUNT(*) FROM bookings b WHERE b.check_in <= d.day AND b.check_out > d.day)
FROM d;

Résultat attendu (10 lignes) :

day	(SELECT COUNT(*) FROM bookings b WHERE b.check_in <= d.day AND b.check_out > d.day)
2025-07-01	1
2025-07-02	2
2025-07-03	3
2025-07-04	2
2025-07-05	2
2025-07-06	2
2025-07-07	2
2025-07-08	2
2025-07-09	2
2025-07-10	2

Exercice 8 · niveau 7

Pour chaque mois de 2025-01 à 2025-06 (format AAAA-MM), affiche le mois et le nombre de commandes passées (0 si aucune). Utilise une CTE récursive pour générer les mois.

Table `orders` (8 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, Texte et dates, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT STRFTIME(…, … || …, …)
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … … WHERE STRFTIME(…, …) = …)
FROM …

Voir la correction

WITH RECURSIVE m(month) AS (
  SELECT '2025-01'
  UNION ALL SELECT strftime('%Y-%m', month || '-01', '+1 month')
  FROM m
  WHERE month < '2025-06')
SELECT month, (SELECT COUNT(*) FROM orders o WHERE strftime('%Y-%m', o.order_date) = m.month)
FROM m;

Résultat attendu (6 lignes) :

month	(SELECT COUNT(*) FROM orders o WHERE strftime('%Y-%m', o.order_date) = m.month)
2025-01	2
2025-02	3
2025-03	3
2025-04	0
2025-05	0
2025-06	0

Exercice 9 · niveau 7

Pour chaque mois de 2025-02 à 2025-06 (format AAAA-MM), affiche le mois et le nombre d'heures des tâches terminées ce mois-là (0 si aucune). Utilise une CTE récursive pour générer les mois.

Table `tasks` (10 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, NULL, COALESCE, Texte et dates, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT STRFTIME(…, … || …, …)
  FROM …
  WHERE … < …)
SELECT …, COALESCE((SELECT SUM(…) FROM … … WHERE STRFTIME(…, …) = …), …)
FROM …

Voir la correction

WITH RECURSIVE m(month) AS (
  SELECT '2025-02'
  UNION ALL SELECT strftime('%Y-%m', month || '-01', '+1 month')
  FROM m
  WHERE month < '2025-06')
SELECT month, COALESCE((SELECT SUM(hours) FROM tasks t WHERE strftime('%Y-%m', t.done_on) = m.month), 0)
FROM m;

Résultat attendu (5 lignes) :

month	COALESCE((SELECT SUM(hours) FROM tasks t WHERE strftime('%Y-%m', t.done_on) = m.month), 0)
2025-02	0
2025-03	38
2025-04	26
2025-05	22
2025-06	0

Exercice 10 · niveau 7

Pour chaque jour du 2025-01-01 au 2025-01-07, affiche la date et la somme des opérations de ce jour, tous comptes confondus (0 s'il n'y en a aucune). Utilise une CTE récursive pour générer les dates.

Table `transactions` (14 lignes)
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

Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, Sous-requêtes, NULL, COALESCE, Texte et dates, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, COALESCE((SELECT SUM(…) FROM … … WHERE … = …), …)
FROM …

Voir la correction

WITH RECURSIVE d(day) AS (
  SELECT '2025-01-01'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-01-07')
SELECT day, COALESCE((SELECT SUM(amount) FROM transactions t WHERE t.made_on = d.day), 0)
FROM d;

Résultat attendu (7 lignes) :

day	COALESCE((SELECT SUM(amount) FROM transactions t WHERE t.made_on = d.day), 0)
2025-01-01	0
2025-01-02	2500
2025-01-03	1900
2025-01-04	0
2025-01-05	-60
2025-01-06	0
2025-01-07	0

Exercice 11 · niveau 8

Pour chaque employé qui a au moins un subordonné, affiche son nom et le nombre total de ses subordonnés, directs et indirects. Utilise une CTE récursive.

Table `staff` (10 lignes)
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

Voir l’indice

Notions à utiliser : COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, HAVING, CTE récursives.

Structure de la requête :

WITH RECURSIVE …(…, …) AS (
  SELECT …, …
  FROM …
  UNION ALL SELECT …, …
  FROM … …
  JOIN … ON … = …)
SELECT …, COUNT(*) - …
FROM …
JOIN … … ON … = …
GROUP BY …
HAVING COUNT(*) > …

Voir la correction

WITH RECURSIVE d(root, id) AS (
  SELECT id, id
  FROM staff
  UNION ALL SELECT d.root, s.id
  FROM staff s
  JOIN d ON s.manager_id = d.id)
SELECT m.name, COUNT(*) - 1
FROM d
JOIN staff m ON m.id = d.root
GROUP BY d.root
HAVING COUNT(*) > 1;

Résultat attendu (5 lignes) :

name	COUNT(*) - 1
Alice	9
Bob	3
Claire	2
David	1
Emma	1

S’entraîner avec correction automatique

Dans SpeedQL, tu écris ta requête et elle est vérifiée tout de suite, sur ces tables puis sur un jeu de données caché.

S’entraîner sur les 18 questions « CTE récursives »