Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Why the comparison fails (15 minutes)
Explain why WHERE discount = NULL returns zero rows, even for a book whose discount actually is NULL.
Done when: the explanation correctly states the comparison evaluates to unknown, not true, even when discount is NULL.
Hint
NULL = NULL is unknown, not true, by the rules of three-valued logic, so the comparison never matches.
Easy 2: Write the correct test (15 minutes)
Write a query finding every book that does have a recorded discount.
Done when: a correct WHERE discount IS NOT NULL query is given.
Hint
SELECT * FROM books WHERE discount IS NOT NULL;
Easy 3: Use COALESCE (15 minutes)
Write a query showing every customer's address, displaying "Unknown" instead of a blank when the address is NULL.
Done when: a correct COALESCE(address, 'Unknown') expression is given.
Hint
SELECT COALESCE(address, 'Unknown') FROM customers;
Medium
Medium 1: Catch both NULL and zero (25 minutes)
Write a query finding every book with either no recorded discount or a discount of exactly zero.
Done when: a correct discount IS NULL OR discount = 0 condition is given.
Hint
WHERE discount IS NULL OR discount = 0.
Medium 2: Predict the AND result (25 minutes)
State the result of unknown AND false, and explain why, even though one operand is unknown.
Done when: false is correctly given, with the reasoning that AND cannot be true if either side is definitely false.
Hint
unknown AND false is false, since no matter what the unknown side turns out to be, the AND can never become true once one side is false.
Medium 3: NULL in a NOT IN list (25 minutes)
Explain what happens to customer_id NOT IN (107, NULL) for a customer_id of 108, and why the intuitive answer (true) is wrong.
Done when: the explanation correctly states the result is unknown, not true, because of how NOT IN expands against a NULL in the list.
Hint
NOT IN expands to a chain of AND NOT comparisons, one of which compares against NULL and becomes unknown, which poisons the whole AND chain to unknown.
Hard
Hard 1: Three-valued OR, worked through (30 minutes)
State the result of unknown OR true, and explain the reasoning in terms of what "unknown" is allowed to mean.
Done when: true is correctly given, with reasoning that OR is already satisfied regardless of what the unknown side turns out to be.
Hint
unknown OR true is true, since OR only needs one side to be true, and the known side already is.
Hard 2: COALESCE versus a default in the schema (35 minutes)
Explain the difference between using COALESCE(discount, 0) at query time and instead giving the discount column a DEFAULT 0 at the schema level.
Done when: the explanation correctly distinguishes a display-time substitution from a stored, as-written default applied at insert time.
Hint
COALESCE only changes what is displayed by that one query; a DEFAULT changes what actually gets stored when no value is given at insert time.
Hard 3: Diagnosing a missing-rows bug (40 minutes)
A report using WHERE discount <> 0 is supposed to list every discounted book, but a book known to have a NULL discount silently never appears, and the team cannot figure out why. Diagnose the bug and fix the query.
Done when: the diagnosis correctly identifies <> 0 as excluding NULL rows via unknown, and the fix adds OR discount IS NULL or reframes the condition using COALESCE.
Hint
discount <> 0 evaluates to unknown for a NULL discount, excluding it; adding OR discount IS NULL (or using COALESCE to treat NULL as a known value first) fixes the gap.