Exercices SQL corrigés : NULL et COALESCE

Exercices SQL corrigés : NULL et COALESCE

Mis à jour le

NULL = valeur absente : on la teste avec IS NULL / IS NOT NULL ; COALESCE la remplace. Ces 14 exercices vont du niveau 4 au niveau 8 ; ils utilisent la syntaxe de SQLite, le moteur SQL de SpeedQL.

Conseil : col = NULL ne marche jamais : il faut écrire col IS NULL.

Relire la fiche « NULL, COALESCE » de l’aide-mémoire

Exercice 1 · niveau 4

Affiche le nom et l'email de chaque contact, en remplaçant les emails manquants par 'unknown'. Utilise COALESCE.

Table contacts (5 lignes)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Voir l’indice

Notions à utiliser : NULL, COALESCE.

Structure de la requête :

SELECT …, COALESCE(…, …)
FROM …
Voir la correction
SELECT name, COALESCE(email, 'unknown')
FROM contacts;

Résultat attendu (5 lignes) :

nameCOALESCE(email, 'unknown')
Alice Martinalice@mail.com
bob durandunknown
CLAIRE ROUXclaire@work.org
David Lefevreunknown
Emma Petitemma@mail.com

Exercice 2 · niveau 4

Affiche le nom de chaque contact et, dans une colonne contact, son téléphone s'il existe, sinon son email, sinon 'none'. Utilise COALESCE.

Table contacts (5 lignes)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Voir l’indice

Notions à utiliser : NULL, COALESCE.

Structure de la requête :

SELECT …, COALESCE(…, …, …) AS …
FROM …
Voir la correction
SELECT name, COALESCE(phone, email, 'none') AS contact
FROM contacts;

Résultat attendu (5 lignes) :

namecontact
Alice Martin0612345678
bob durand0698765432
CLAIRE ROUXclaire@work.org
David Lefevrenone
Emma Petit0611223344

Exercice 3 · niveau 4

Affiche le nom des contacts qui n'ont pas d'email.

Table contacts (5 lignes)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Voir l’indice

Notions à utiliser : WHERE (filtres), NULL, COALESCE.

Structure de la requête :

SELECT …
FROM …
WHERE … IS NULL
Voir la correction
SELECT name
FROM contacts
WHERE email IS NULL;

Résultat attendu (2 lignes) :

name
bob durand
David Lefevre

Exercice 4 · niveau 4

Affiche le nombre de contacts pour lesquels il manque l'email, le téléphone, ou les deux.

Table contacts (5 lignes)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, NULL, COALESCE.

Structure de la requête :

SELECT COUNT(*)
FROM …
WHERE … IS NULL OR … IS NULL
Voir la correction
SELECT COUNT(*)
FROM contacts
WHERE email IS NULL OR phone IS NULL;

Résultat attendu (1 ligne) :

COUNT(*)
3

Exercice 5 · niveau 4

Pour les contacts qui ont un email, affiche l'email et son domaine (la partie après le @).

Table contacts (5 lignes)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Voir l’indice

Notions à utiliser : WHERE (filtres), NULL, COALESCE, Texte et dates.

Structure de la requête :

SELECT …, SUBSTR(…, INSTR(…, …) + …)
FROM …
WHERE … IS NOT NULL
Voir la correction
SELECT email, SUBSTR(email, INSTR(email, '@') + 1)
FROM contacts
WHERE email IS NOT NULL;

Résultat attendu (3 lignes) :

emailSUBSTR(email, INSTR(email, '@') + 1)
alice@mail.commail.com
claire@work.orgwork.org
emma@mail.commail.com

Exercice 6 · niveau 4

Affiche le titre de chaque film et le nom de son réalisateur, avec 'unknown' à la place du nom quand le réalisateur est inconnu. Utilise COALESCE.

Table movies (10 lignes)
idtitlegenreyeardurationratingdirector_id
1Night TrainThriller20151187.81
2Blue HarborDrama20181027.12
3Paper Moon CityComedy2012956.45
4Silent PeakDrama20201318.23
5Last SignalSci-Fi20191427.51
6Summer KeysComedy2016885.94
7Iron GardenSci-Fi202112583
8Dust and GoldWestern20141106.82
9The Quiet HourDrama2022977.45
10Deep CurrentThriller20171056.9NULL
Table directors (6 lignes)
idnamecountry
1Nora EllisUK
2Paulo ReisBrazil
3Kenji MoriJapan
4Anna BergSweden
5Luc MartinFrance
6Sara DiazSpain
Voir l’indice

Notions à utiliser : JOIN, NULL, COALESCE.

Structure de la requête :

SELECT …, COALESCE(…, …)
FROM … …
LEFT JOIN … … ON … = …
Voir la correction
SELECT m.title, COALESCE(d.name, 'unknown')
FROM movies m
LEFT JOIN directors d ON d.id = m.director_id;

Résultat attendu (10 lignes) :

titleCOALESCE(d.name, 'unknown')
Night TrainNora Ellis
Blue HarborPaulo Reis
Paper Moon CityLuc Martin
Silent PeakKenji Mori
Last SignalNora Ellis
Summer KeysAnna Berg
Iron GardenKenji Mori
Dust and GoldPaulo Reis
The Quiet HourLuc Martin
Deep Currentunknown

Exercice 7 · niveau 4

Pour chaque emprunt rendu, affiche l'id de l'emprunt et le nombre de jours entre l'emprunt et le retour.

Table loans (12 lignes)
idbook_idmember_idloan_datereturn_date
1112025-01-052025-01-19
2322025-01-102025-02-02
3512025-02-012025-02-10
4732025-02-03NULL
5342025-02-152025-03-01
6222025-03-022025-03-30
7852025-03-05NULL
8132025-03-102025-03-18
9612025-03-202025-04-15
10352025-04-01NULL
11942025-04-052025-04-12
121022025-04-082025-04-20
Voir l’indice

Notions à utiliser : WHERE (filtres), NULL, COALESCE, Texte et dates.

Structure de la requête :

SELECT …, CAST(JULIANDAY(…) - JULIANDAY(…) AS INTEGER)
FROM …
WHERE … IS NOT NULL
Voir la correction
SELECT id, CAST(julianday(return_date) - julianday(loan_date) AS INTEGER)
FROM loans
WHERE return_date IS NOT NULL;

Résultat attendu (9 lignes) :

idCAST(julianday(return_date) - julianday(loan_date) AS INTEGER)
114
223
39
514
628
88
926
117
1212

Exercice 8 · niveau 4

Affiche le titre des livres actuellement empruntés (emprunts sans date de retour).

Table books (10 lignes)
idtitleauthor_idgenrepagesyear
1Cold River1Novel3202011
2Salt Roads2Travel2102016
3The Glass Hive3Sci-Fi4122019
4Winter Ledger1Crime2882014
5Desert Letters2Novel3562020
6Small Engines4Sci-Fi1982022
7Harbor Lights5Novel4452008
8Night Garden3Crime3012017
9Paper Birds5Poetry962012
10Open Maps4Travel2402021
Table loans (12 lignes)
idbook_idmember_idloan_datereturn_date
1112025-01-052025-01-19
2322025-01-102025-02-02
3512025-02-012025-02-10
4732025-02-03NULL
5342025-02-152025-03-01
6222025-03-022025-03-30
7852025-03-05NULL
8132025-03-102025-03-18
9612025-03-202025-04-15
10352025-04-01NULL
11942025-04-052025-04-12
121022025-04-082025-04-20
Voir l’indice

Notions à utiliser : WHERE (filtres), JOIN, NULL, COALESCE.

Structure de la requête :

SELECT …
FROM … …
JOIN … … ON … = …
WHERE … IS NULL
Voir la correction
SELECT b.title
FROM books b
JOIN loans l ON l.book_id = b.id
WHERE l.return_date IS NULL;

Résultat attendu (3 lignes) :

title
Harbor Lights
Night Garden
The Glass Hive

Exercice 9 · niveau 4

Avec une jointure externe, affiche le nom et le prix des produits qui n'ont jamais été commandés.

Table products (7 lignes)
idnamecategoryprice
1Desk LampHome35
2Coffee MugKitchen12
3NotebookOffice6
4Office ChairOffice149
5KettleKitchen45
6CushionHome22
7StaplerOffice9
Table order_items (14 lignes)
order_idproduct_idqty
111
122
241
335
321
451
562
511
624
633
741
761
852
821
Voir l’indice

Notions à utiliser : WHERE (filtres), JOIN, NULL, COALESCE.

Structure de la requête :

SELECT …, …
FROM … …
LEFT JOIN … … ON … = …
WHERE … IS NULL
Voir la correction
SELECT p.name, p.price
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

Résultat attendu (1 ligne) :

nameprice
Stapler9

Exercice 10 · niveau 4

Avec COALESCE, affiche le titre de chaque tâche et sa date de fin, ou 'pending' si elle n'est pas terminée (done_on vide).

Table tasks (10 lignes)
idproject_iddev_idtitlehoursstatusdone_on
111Landing page12done2025-03-10
213Data model20done2025-03-20
312Login8doingNULL
424ETL job16done2025-04-02
525CI pipeline6done2025-03-15
631Dashboard14todoNULL
733Report10done2025-04-25
842API18doingNULL
945Monitoring9todoNULL
1044Forecast22done2025-05-05
Voir l’indice

Notions à utiliser : NULL, COALESCE.

Structure de la requête :

SELECT …, COALESCE(…, …)
FROM …
Voir la correction
SELECT title, COALESCE(done_on, 'pending')
FROM tasks;

Résultat attendu (10 lignes) :

titleCOALESCE(done_on, 'pending')
Landing page2025-03-10
Data model2025-03-20
Loginpending
ETL job2025-04-02
CI pipeline2025-03-15
Dashboardpending
Report2025-04-25
APIpending
Monitoringpending
Forecast2025-05-05

Exercice 11 · niveau 4

Pour chaque tâche terminée, affiche son titre, le nom du projet et le nombre de jours entre sa date de fin et la date limite du projet (deadline − done_on).

Table tasks (10 lignes)
idproject_iddev_idtitlehoursstatusdone_on
111Landing page12done2025-03-10
213Data model20done2025-03-20
312Login8doingNULL
424ETL job16done2025-04-02
525CI pipeline6done2025-03-15
631Dashboard14todoNULL
733Report10done2025-04-25
842API18doingNULL
945Monitoring9todoNULL
1044Forecast22done2025-05-05
Table projects (4 lignes)
idnameclientbudgetdeadline
1AtlasAcme200002025-06-30
2BeaconBolt120002025-05-15
3CometAcme80002025-04-30
4DeltaCyan150002025-07-31
Voir l’indice

Notions à utiliser : WHERE (filtres), JOIN, NULL, COALESCE, Texte et dates.

Structure de la requête :

SELECT …, …, CAST(JULIANDAY(…) - JULIANDAY(…) AS INTEGER)
FROM … …
JOIN … … ON … = …
WHERE … IS NOT NULL
Voir la correction
SELECT t.title, p.name, CAST(julianday(p.deadline) - julianday(t.done_on) AS INTEGER)
FROM tasks t
JOIN projects p ON p.id = t.project_id
WHERE t.done_on IS NOT NULL;

Résultat attendu (6 lignes) :

titlenameCAST(julianday(p.deadline) - julianday(t.done_on) AS INTEGER)
Landing pageAtlas112
Data modelAtlas102
ETL jobBeacon43
CI pipelineBeacon61
ReportComet5
ForecastDelta87

Exercice 12 · niveau 8

Pour chaque facture, affiche son id et son nombre de jours de retard : jours entre l'échéance et la date de paiement (ou le 2025-04-15 si elle n'est pas payée), et 0 s'il n'y a pas de retard. Utilise COALESCE.

Table invoices (6 lignes)
idclientissueddueamountpaid_on
1Acme2025-01-152025-02-1412002025-02-10
2Acme2025-03-012025-03-16800NULL
3Bolt2025-01-202025-03-064502025-03-01
4Bolt2025-02-252025-03-279502025-04-02
5Cyan2025-03-052025-05-04300NULL
6Cyan2024-12-102024-12-306002025-01-05
Voir l’indice

Notions à utiliser : NULL, COALESCE, Texte et dates.

Structure de la requête :

SELECT …, MAX(…, CAST(JULIANDAY(COALESCE(…, …)) - JULIANDAY(…) AS INTEGER))
FROM …
Voir la correction
SELECT id, MAX(0, CAST(julianday(COALESCE(paid_on, '2025-04-15')) - julianday(due) AS INTEGER))
FROM invoices;

Résultat attendu (6 lignes) :

idMAX(0, CAST(julianday(COALESCE(paid_on, '2025-04-15')) - julianday(due) AS INTEGER))
10
230
30
46
50
66

Exercice 13 · niveau 8

Affiche les clients qui ont au moins une facture impayée, sauf ceux qui ont déjà payé une facture en retard (paiement après l'échéance).

Table invoices (6 lignes)
idclientissueddueamountpaid_on
1Acme2025-01-152025-02-1412002025-02-10
2Acme2025-03-012025-03-16800NULL
3Bolt2025-01-202025-03-064502025-03-01
4Bolt2025-02-252025-03-279502025-04-02
5Cyan2025-03-052025-05-04300NULL
6Cyan2024-12-102024-12-306002025-01-05
Voir l’indice

Notions à utiliser : WHERE (filtres), NULL, COALESCE, UNION, INTERSECT, EXCEPT.

Structure de la requête :

SELECT …
FROM …
WHERE … IS NULL
EXCEPT SELECT …
FROM …
WHERE … > …
Voir la correction
SELECT client
FROM invoices
WHERE paid_on IS NULL
EXCEPT SELECT client
FROM invoices
WHERE paid_on > due;

Résultat attendu (1 ligne) :

client
Acme

Exercice 14 · niveau 8

Pour chaque projet qui a au moins 2 tâches terminées, affiche son nom, la première et la dernière date de fin, et le nombre de jours entre les deux.

Table projects (4 lignes)
idnameclientbudgetdeadline
1AtlasAcme200002025-06-30
2BeaconBolt120002025-05-15
3CometAcme80002025-04-30
4DeltaCyan150002025-07-31
Table tasks (10 lignes)
idproject_iddev_idtitlehoursstatusdone_on
111Landing page12done2025-03-10
213Data model20done2025-03-20
312Login8doingNULL
424ETL job16done2025-04-02
525CI pipeline6done2025-03-15
631Dashboard14todoNULL
733Report10done2025-04-25
842API18doingNULL
945Monitoring9todoNULL
1044Forecast22done2025-05-05
Voir l’indice

Notions à utiliser : WHERE (filtres), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, HAVING, NULL, COALESCE, Texte et dates.

Structure de la requête :

SELECT …, MIN(…), MAX(…), CAST(JULIANDAY(MAX(…)) - JULIANDAY(MIN(…)) AS INTEGER)
FROM … …
JOIN … … ON … = …
WHERE … IS NOT NULL
GROUP BY …
HAVING COUNT(*) >= …
Voir la correction
SELECT p.name, MIN(t.done_on), MAX(t.done_on), CAST(julianday(MAX(t.done_on)) - julianday(MIN(t.done_on)) AS INTEGER)
FROM projects p
JOIN tasks t ON t.project_id = p.id
WHERE t.done_on IS NOT NULL
GROUP BY p.id
HAVING COUNT(*) >= 2;

Résultat attendu (2 lignes) :

nameMIN(t.done_on)MAX(t.done_on)CAST(julianday(MAX(t.done_on)) - julianday(MIN(t.done_on)) AS INTEGER)
Atlas2025-03-102025-03-2010
Beacon2025-03-152025-04-0218

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 11 questions « NULL, COALESCE »