SQL exercises with solutions: NULL and COALESCE

SQL exercises with solutions: NULL and COALESCE

Updated on

NULL = missing value: test it with IS NULL / IS NOT NULL; COALESCE replaces it. These 14 exercises range from level 4 to level 8; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: col = NULL never works: write col IS NULL.

Read the “NULL, COALESCE” card in the cheat sheet

Exercise 1 · level 4

Show each contact's name and email, replacing missing emails with 'unknown'. Use COALESCE.

Table contacts (5 rows)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Show the hint

Topics to use: NULL, COALESCE.

Query structure:

SELECT …, COALESCE(…, …)
FROM …
Show the solution
SELECT name, COALESCE(email, 'unknown')
FROM contacts;

Expected result (5 rows):

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

Exercise 2 · level 4

Show each contact's name and, in a column called contact, their phone if it exists, otherwise their email, otherwise 'none'. Use COALESCE.

Table contacts (5 rows)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Show the hint

Topics to use: NULL, COALESCE.

Query structure:

SELECT …, COALESCE(…, …, …) AS …
FROM …
Show the solution
SELECT name, COALESCE(phone, email, 'none') AS contact
FROM contacts;

Expected result (5 rows):

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

Exercise 3 · level 4

Show the name of the contacts who have no email.

Table contacts (5 rows)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Show the hint

Topics to use: WHERE (filters), NULL, COALESCE.

Query structure:

SELECT …
FROM …
WHERE … IS NULL
Show the solution
SELECT name
FROM contacts
WHERE email IS NULL;

Expected result (2 rows):

name
bob durand
David Lefevre

Exercise 4 · level 4

Show the number of contacts whose email, phone, or both are missing.

Table contacts (5 rows)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, NULL, COALESCE.

Query structure:

SELECT COUNT(*)
FROM …
WHERE … IS NULL OR … IS NULL
Show the solution
SELECT COUNT(*)
FROM contacts
WHERE email IS NULL OR phone IS NULL;

Expected result (1 row):

COUNT(*)
3

Exercise 5 · level 4

For the contacts who have an email, show the email and its domain (the part after the @).

Table contacts (5 rows)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Show the hint

Topics to use: WHERE (filters), NULL, COALESCE, Text and dates.

Query structure:

SELECT …, SUBSTR(…, INSTR(…, …) + …)
FROM …
WHERE … IS NOT NULL
Show the solution
SELECT email, SUBSTR(email, INSTR(email, '@') + 1)
FROM contacts
WHERE email IS NOT NULL;

Expected result (3 rows):

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

Exercise 6 · level 4

Show the title of each movie and its director's name, with 'unknown' instead of the name when the director is unknown. Use COALESCE.

Table movies (10 rows)
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 rows)
idnamecountry
1Nora EllisUK
2Paulo ReisBrazil
3Kenji MoriJapan
4Anna BergSweden
5Luc MartinFrance
6Sara DiazSpain
Show the hint

Topics to use: JOIN, NULL, COALESCE.

Query structure:

SELECT …, COALESCE(…, …)
FROM … …
LEFT JOIN … … ON … = …
Show the solution
SELECT m.title, COALESCE(d.name, 'unknown')
FROM movies m
LEFT JOIN directors d ON d.id = m.director_id;

Expected result (10 rows):

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

Exercise 7 · level 4

For each returned loan, show the loan id and the number of days between the loan and the return.

Table loans (12 rows)
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
Show the hint

Topics to use: WHERE (filters), NULL, COALESCE, Text and dates.

Query structure:

SELECT …, CAST(JULIANDAY(…) - JULIANDAY(…) AS INTEGER)
FROM …
WHERE … IS NOT NULL
Show the solution
SELECT id, CAST(julianday(return_date) - julianday(loan_date) AS INTEGER)
FROM loans
WHERE return_date IS NOT NULL;

Expected result (9 rows):

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

Exercise 8 · level 4

Show the title of the books currently on loan (loans with no return date).

Table books (10 rows)
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 rows)
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
Show the hint

Topics to use: WHERE (filters), JOIN, NULL, COALESCE.

Query structure:

SELECT …
FROM … …
JOIN … … ON … = …
WHERE … IS NULL
Show the solution
SELECT b.title
FROM books b
JOIN loans l ON l.book_id = b.id
WHERE l.return_date IS NULL;

Expected result (3 rows):

title
Harbor Lights
Night Garden
The Glass Hive

Exercise 9 · level 4

Using an outer join, show the name and price of the products that have never been ordered.

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

Topics to use: WHERE (filters), JOIN, NULL, COALESCE.

Query structure:

SELECT …, …
FROM … …
LEFT JOIN … … ON … = …
WHERE … IS NULL
Show the solution
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;

Expected result (1 row):

nameprice
Stapler9

Exercise 10 · level 4

Using COALESCE, show the title of each task and its end date, or 'pending' if it is not finished (done_on empty).

Table tasks (10 rows)
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
Show the hint

Topics to use: NULL, COALESCE.

Query structure:

SELECT …, COALESCE(…, …)
FROM …
Show the solution
SELECT title, COALESCE(done_on, 'pending')
FROM tasks;

Expected result (10 rows):

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

Exercise 11 · level 4

For each finished task, show its title, the project name and the number of days between its end date and the project's deadline (deadline − done_on).

Table tasks (10 rows)
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 rows)
idnameclientbudgetdeadline
1AtlasAcme200002025-06-30
2BeaconBolt120002025-05-15
3CometAcme80002025-04-30
4DeltaCyan150002025-07-31
Show the hint

Topics to use: WHERE (filters), JOIN, NULL, COALESCE, Text and dates.

Query structure:

SELECT …, …, CAST(JULIANDAY(…) - JULIANDAY(…) AS INTEGER)
FROM … …
JOIN … … ON … = …
WHERE … IS NOT NULL
Show the solution
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;

Expected result (6 rows):

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

Exercise 12 · level 8

For each invoice, show its id and its number of days late: days between the due date and the payment date (or 2025-04-15 if it is unpaid), and 0 if it is not late. Use COALESCE.

Table invoices (6 rows)
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
Show the hint

Topics to use: NULL, COALESCE, Text and dates.

Query structure:

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

Expected result (6 rows):

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

Exercise 13 · level 8

Show the clients who have at least one unpaid invoice, except those who have already paid an invoice late (payment after the due date).

Table invoices (6 rows)
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
Show the hint

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

Query structure:

SELECT …
FROM …
WHERE … IS NULL
EXCEPT SELECT …
FROM …
WHERE … > …
Show the solution
SELECT client
FROM invoices
WHERE paid_on IS NULL
EXCEPT SELECT client
FROM invoices
WHERE paid_on > due;

Expected result (1 row):

client
Acme

Exercise 14 · level 8

For each project with at least 2 finished tasks, show its name, the first and the last end date, and the number of days between them.

Table projects (4 rows)
idnameclientbudgetdeadline
1AtlasAcme200002025-06-30
2BeaconBolt120002025-05-15
3CometAcme80002025-04-30
4DeltaCyan150002025-07-31
Table tasks (10 rows)
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
Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, HAVING, NULL, COALESCE, Text and dates.

Query structure:

SELECT …, MIN(…), MAX(…), CAST(JULIANDAY(MAX(…)) - JULIANDAY(MIN(…)) AS INTEGER)
FROM … …
JOIN … … ON … = …
WHERE … IS NOT NULL
GROUP BY …
HAVING COUNT(*) >= …
Show the solution
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;

Expected result (2 rows):

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

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 11 “NULL, COALESCE” questions