sql · level 2 · unit 5 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
CASE stops at the first WHEN that passes.
A CASE can hold many WHENs. SQLite tries them from the top down and stops at the first one that passes. The tests after it are never looked at.
That makes bands tidy to write. Test the top first, WHEN points >= 100, then WHEN points >= 50, then ELSE. A score of 120 stops at the first test, so it never reaches the second.
Put the tests the other way round and 120 stops at >= 50 instead. Order your WHENs from the top band down.
SELECT player, game, points,
CASE
WHEN points >= 100 THEN 'gold'
WHEN points >= 50 THEN 'silver'
ELSE 'bronze'
END AS medal
FROM scores
ORDER BY points DESC;Group by a CASE alias, like GROUP BY medal, to count how many rows land in each band.
guess first
The tests are upside down. What does 120 get?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT player, points,
CASE
WHEN points >= 50 THEN 'silver'
WHEN points >= 100 THEN 'gold'
ELSE 'bronze'
END AS medal
FROM scores
WHERE game = 'bowling'
ORDER BY points DESC;your turn
Your turn. Count the snacks in each band.
Type a price in pence above 50 in the blank. Snacks at that price or more are dear. The query counts how many snacks land in each band.
result
Your answer appears here as a table.
fix it
No film comes out long. Fix it!
result
Your answer appears here as a table.
- The Last Biscuit runs 118 minutes. Which
WHENdoes it pass first? - SQLite stops at the first test that passes, so the top band must be tested first.
- Swap lines 3 and 4 so minutes >= 110 comes first, then press run.
nailed it
you sorted rows into bands. :D
CASEstops at the firstWHENthat passes.- Test the top band first, then work down.
GROUP BYa band's alias to count each band.
new words in your collection
- band
This lesson isn’t saved. A free account remembers every lesson you finish.