Checkpoint — NULLs and expressions

Q: What does COALESCE(NULLIF(x, ''), 'none') return for an empty string?

'none'. NULLIF turns the empty string into NULL, and COALESCE then skips that NULL and returns the next argument, so blanks and NULLs both get the default.

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3

Quiz · 70% to clear12 q

This checkpoint covers the whole module: what NULL means, how it spreads through arithmetic and comparisons, three-valued logic in WHERE, AND, OR, NOT and NOT IN, IS NULL and IS NOT NULL, MySQL's NULL-safe <=>, COALESCE, IFNULL and NULLIF, searched and simple CASE, MySQL's IF(), the main data types, / versus DIV, implicit conversion, and CAST/CONVERT.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

What are NULL = NULL, NULL + 1 and NULL OR TRUE?
Why does WHERE col <> 'x' drop NULL rows, and how do you keep them?
Why does NOT IN (1, 2, NULL) never match anything?
What is the difference between IS NULL, = NULL and <=>?
What do COALESCE, IFNULL and NULLIF return, and what are SQL Server's and Oracle's names for IFNULL?
In a searched CASE, which branch wins, and what is returned when none matches?
What is 7 / 2 in MySQL, and in PostgreSQL? What is 7 DIV 2?
How do you sort a VARCHAR column of numbers by value?

The three queries are a delivery fee computed with CASE and reduced by a discount that may be missing, a contact list that treats blank cities as unknown and lists reachable contacts first, and an audit that labels each change as added, removed, changed or the same.

Common questions

Why does WHERE dept <> 'Sales' leave out employees with no department?

For a NULL department the comparison is UNKNOWN, and WHERE keeps only rows whose condition is TRUE. Add the NULL case explicitly: WHERE dept <> 'Sales' OR dept IS NULL.

What does COALESCE(NULLIF(x, ''), 'none') return for an empty string?

'none'. NULLIF turns the empty string into NULL, and COALESCE then skips that NULL and returns the next argument, so blanks and NULLs both get the default.

Why should a CASE that maps NULLs test IS NULL first?

Every comparison with NULL is UNKNOWN, so a NULL input skips every comparison branch and lands in the ELSE. A WHEN col IS NULL branch at the top gives it its own result.

SQL NULL Quiz: IS NULL, COALESCE, CASE and CAST Practice Test

Test yourself: 12 questions and three queries on SQL NULL and three-valued logic, IS NULL, COALESCE, IFNULL, NULLIF, CASE expressions, DIV and CAST.

Course: SQL study plan
Module: NULLs and expressions
Kind: Checkpoint — cleared at 70%
Reading time: 25 min
Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — NULLs and expressions is the checkpoint that closes the NULLs and expressions module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

What are NULL = NULL, NULL + 1 and NULL OR TRUE?
Why does WHERE col <> 'x' drop NULL rows, and how do you keep them?
Why does NOT IN (1, 2, NULL) never match anything?
What is the difference between IS NULL, = NULL and <=>?
What do COALESCE, IFNULL and NULLIF return, and what are SQL Server's and Oracle's names for IFNULL?
In a searched CASE, which branch wins, and what is returned when none matches?
What is 7 / 2 in MySQL, and in PostgreSQL? What is 7 DIV 2?
How do you sort a VARCHAR column of numbers by value?

Common questions

Why does WHERE dept <> 'Sales' leave out employees with no department?

For a NULL department the comparison is UNKNOWN, and WHERE keeps only rows whose condition is TRUE. Add the NULL case explicitly: WHERE dept <> 'Sales' OR dept IS NULL.

What does COALESCE(NULLIF(x, ''), 'none') return for an empty string?

'none'. NULLIF turns the empty string into NULL, and COALESCE then skips that NULL and returns the next argument, so blanks and NULLs both get the default.

Why should a CASE that maps NULLs test IS NULL first?

Every comparison with NULL is UNKNOWN, so a NULL input skips every comparison branch and lands in the ELSE. A WHEN col IS NULL branch at the top gives it its own result.

Exercises

Delivery fees with optional discounts

The deliveries table; weight_kg is NULL when the parcel was not weighed and discount is NULL when there is none:

id	city	weight_kg	discount
1	Pune	0.5	NULL
2	Delhi	3	10
3	Goa	NULL	5
4	Agra	12	25
5	Kochi	1	0

Return id, fee and final_fee, ordered by id. The fee is 40 up to 1 kg, 80 up to 5 kg, 150 above 5 kg, and NULL for an unweighed parcel. final_fee is the fee minus the discount, a missing discount counting as 0.

id | fee | final_fee
1 | 40 | 40
2 | 80 | 70
3 | NULL | NULL
4 | 150 | 125
5 | 40 | 40

Contact list, reachable first

The contacts table; city and phone may be NULL or left blank (''):

id	name	city	phone
1	Ravi	Pune	9876
2	Anu	(empty)	NULL
3	Mo	NULL	9123
4	Lia	Goa	NULL

Return name, city and has_phone: the city, or 'Unknown' when it is NULL or blank; and 1 if the contact has a phone that is neither NULL nor blank, else 0. List contacts with a phone first, then by name, then by id.

name | city | has_phone
Mo | Unknown | 1
Ravi | Pune | 1
Anu | Unknown | 0
Lia | Goa | 0

Audit labels

The audit table holds a field's value before and after an edit; NULL means the field was empty:

id	before_value	after_value
1	NULL	5
2	5	NULL
3	5	6
4	5	5
5	NULL	NULL

Return id and change_type, ordered by id: 'added' when a value appeared, 'removed' when it disappeared, 'changed' when both exist and differ, and 'same' otherwise (including NULL to NULL).

id | change_type
1 | added
2 | removed
3 | changed
4 | same
5 | same

In this module: NULLs and expressions

← Data types, implicit conversion and CAST · String functions — CONCAT, SUBSTRING, TRIM and friends →