CSE Roadmap

Core tier · about 3 hours · 5 parts

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.