Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — The unknown power
Shadowchu's power is NULL. Which label does this expression give it?
CASE
WHEN power >= 80 THEN 'Strong'
WHEN power >= 70 THEN 'Medium'
ELSE 'Weak'
END'Weak'NULL'Strong'Hint : A comparison with NULL is never true, so no WHEN matches — what is left?
Exercise 2 — A simple CASE
Complete the simple CASE so that fire Codémons are labelled Hot and all the others Other.
Hint : In the simple form, the column being compared comes right after CASE.
Exercise 3 — The CASE keywords
Match each keyword with its role.
CASEWHENTHENELSEENDExercise 4 — Nothing matches
Aquachu has a power of 75. What is the value of this expression for Aquachu?
CASE WHEN power > 100 THEN 'Legendary' ENDNULL'Legendary'0''Hint : The condition is false and there is no ELSE.
Exercise 5 — Nickname or name
Complete the query so that it displays the nickname, or the name when there is no nickname.
Hint : The fallback must be the column, not a text between quotes.
Exercise 6 — Zero is a value
What does COALESCE(NULL, 0, 5) return?
05NULLHint : 0 is a real value, not NULL.
Exercise 7 — Undefeated
Pyrochu has 10 wins and 0 losses. What does NULLIF(losses, 0) return for Pyrochu?
NULL010'Pyrochu'Hint : NULLIF returns NULL when the two values are equal.
Exercise 8 — Evaluate the functions
Match each expression with its result.
COALESCE(NULL, 'x')NULLIF('a', 'a')IFNULL('b', 'c')NULLIF('d', 'e')'x'NULL'b''d'Exercise 9 — No division by zero
Complete the query so that the ratio becomes NULL instead of dividing by zero when a Codémon has no losses.
Hint : Which value of losses is dangerous, and which two-argument function turns it into NULL?
Exercise 10 — Count the plants
Florachu and Plantachu are the two plant Codémons of the table (8 rows). What does this query return?
SELECT SUM(CASE WHEN type = 'plant' THEN 1 ELSE 0 END)
FROM codemon;2180Hint : Each plant row adds 1, every other row adds 0.
Exercise 11 — The COUNT trap
Same table, 8 rows, 2 plant Codémons. What does this query return?
SELECT COUNT(CASE WHEN type = 'plant' THEN 1 ELSE 0 END)
FROM codemon;820NULLHint : COUNT counts every non-NULL value, and 0 is a value.
Exercise 12 — Sales in the East
Complete the query so that it returns, for each category, the quantity sold in the East region.
Hint : Text comparisons with = are case-sensitive, and the report needs one line per category.
Exercise 13 — Battle items report
Match each expression with what it computes in the sales table.
SUM(CASE WHEN category = 'Battle' THEN quantity ELSE 0 END)COUNT(CASE WHEN category = 'Battle' THEN 1 END)MAX(CASE WHEN category = 'Battle' THEN price END)SUM(quantity)Exercise 14 — Where can CASE go?
Where can you use a CASE expression?
Hint : CASE is an expression: it fits anywhere a value is expected.
Exercise 15 — Ghost first
Complete the query so that the ghost Codémon comes first, then all the others in alphabetical order.
Hint : The CASE must single out the ghost type; the second sort key gives the alphabetical order.
Exercise 16 — Nicknames per type
Which query returns, for each type, the number of Codémons that have a nickname?
Hint : Remember how to test for NULL correctly, and what COUNT does with a 0.
Exercise 17 — Share of nicknames
Complete the query so that it returns the percentage of Codémons that have a nickname, with one decimal (expected: 37.5).
Hint : Integer division would lose the decimals; divide by the number of rows.
Exercise 18 — Why NULLIF?
Why write wins * 1.0 / NULLIF(losses, 0) instead of wins * 1.0 / losses?
Hint : Think about what happens to a Codémon with 0 losses.
Exercise 19 — Evaluate for Pyrochu
Pyrochu has no nickname, a power of 80, 10 wins and 0 losses. Match each expression with its result for Pyrochu.
COALESCE(nickname, name)NULLIF(losses, 0)CASE WHEN wins > losses THEN 'Winning' ELSE 'Not winning' ENDCOALESCE(power, 0)'Pyrochu'NULL'Winning'80Exercise 20 — Read the pivot cell
In the sales table, the Other category contains two sales: 5 Training Manuals in the North and 7 in the South. What does this expression compute for the Other row of a pivot grouped by category?
SUM(CASE WHEN region = 'North' THEN quantity ELSE 0 END)57120Hint : Only the North sale contributes its quantity; the South sale contributes 0.
Exercise 21 — Winning, losing or even
Complete the expression so that each Codémon is labelled Winning (more wins than losses), Losing (fewer wins than losses) or Even.
Hint : Two comparisons, then a default for the equality case.
Exercise 22 — Spot the bug
What is wrong with this expression?
CASE
WHEN power >= 70 THEN 'Medium'
WHEN power >= 80 THEN 'Strong'
ELSE 'Weak'
ENDHint : Follow a power of 85 through the conditions, from top to bottom.
Exercise 23 — Average of the fire Codémons
Complete the query so that it returns the average power of the fire Codémons only (the other rows must be ignored, not counted as 0).
Hint : Without ELSE the other rows become NULL, and AVG ignores NULL.
Exercise 24 — ELSE 0 or no ELSE
x is a condition. Match each expression with what it computes.
SUM(CASE WHEN x THEN 1 ELSE 0 END)COUNT(CASE WHEN x THEN 1 ELSE 0 END)COUNT(CASE WHEN x THEN 1 END)AVG(CASE WHEN x THEN power END)Exercise 25 — A dash for the shy ones
Which query lists each Codémon with its nickname, or - when it has none?
Hint : You need every Codémon in the result, with a fallback value for the missing nicknames.