Quizz

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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
Options
• 'Weak'
• NULL
• 'Strong'
• An error

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.

Items to match
• CASE
• WHEN
• THEN
• ELSE
• END
Definitions
• Starts the conditional expression
• Introduces a condition
• The value returned when the condition is true
• The value used when no condition is true
• Closes the expression

Exercise 4 — Nothing matches

Aquachu has a power of 75. What is the value of this expression for Aquachu?

CASE WHEN power > 100 THEN 'Legendary' END
Options
• NULL
• '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?

Options
• 0
• 5
• NULL
• An error

Hint : 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?

Options
• NULL
• 0
• 10
• 'Pyrochu'

Hint : NULLIF returns NULL when the two values are equal.

Exercise 8 — Evaluate the functions

Match each expression with its result.

Items to match
• COALESCE(NULL, 'x')
• NULLIF('a', 'a')
• IFNULL('b', 'c')
• NULLIF('d', 'e')
Definitions
• '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;
Options
• 2
• 1
• 8
• 0

Hint : 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;
Options
• 8
• 2
• 0
• NULL

Hint : 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.

Items to match
• 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)
Definitions
• Quantity of Battle items sold
• Number of Battle sales
• Highest price among Battle items
• Quantity sold, all categories together

Exercise 14 — Where can CASE go?

Where can you use a CASE expression?

Options
• In SELECT, but also in ORDER BY, in WHERE and inside aggregate functions
• Only in SELECT
• Only in WHERE
• Only inside aggregate functions

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?

Options
• SELECT type, SUM(CASE WHEN nickname IS NOT NULL THEN 1 ELSE 0 END) FROM codemon GROUP BY type;
• SELECT type, SUM(CASE WHEN nickname != NULL THEN 1 ELSE 0 END) FROM codemon GROUP BY type;
• SELECT type, COUNT(*) FROM codemon GROUP BY type;
• SELECT type, COUNT(CASE WHEN nickname IS NOT NULL THEN 1 ELSE 0 END) FROM codemon GROUP BY type;

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?

Options
• To avoid a division by zero: the divisor becomes NULL, and so does the result, instead of an error in most databases
• To make the division faster
• To round the result
• To replace NULL losses with 0

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.

Items to match
• COALESCE(nickname, name)
• NULLIF(losses, 0)
• CASE WHEN wins > losses THEN 'Winning' ELSE 'Not winning' END
• COALESCE(power, 0)
Definitions
• 'Pyrochu'
• NULL
• 'Winning'
• 80

Exercise 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)
Options
• 5
• 7
• 12
• 0

Hint : 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' END
Options
• Nobody can ever be 'Strong': the >= 70 test catches them first
• It is missing an END
• ELSE is not allowed after two WHEN
• Nothing, it works as intended

Hint : 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.

Items to match
• 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)
Definitions
• Counts the matching rows by adding 1s and 0s
• Counts every row, because 0 is not NULL
• Counts the matching rows, the others being NULL
• Average power of the matching rows only

Exercise 25 — A dash for the shy ones

Which query lists each Codémon with its nickname, or - when it has none?

Options
• SELECT name, COALESCE(nickname, '-') FROM codemon;
• SELECT name, nickname FROM codemon WHERE nickname IS NOT NULL;
• SELECT name, NULLIF(nickname, '-') FROM codemon;
• SELECT name, nickname OR '-' FROM codemon;

Hint : You need every Codémon in the result, with a fallback value for the missing nicknames.

Back to the course