sql · level 2 · unit 5 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
gives each row a value that depends on a test.
A shop wants a label beside each snack: sold out or in stock. CASE makes that choice for every row, like a fork in the road.
Write CASE, with a test, then THEN with the value to use if it passes. gives the value for every other row, and END closes the whole thing.
Name it with AS and it becomes a new column in the result. Leave out ELSE and rows that fail every test get NULL.
SELECT name, stock,
CASE WHEN stock = 0 THEN 'sold out' ELSE 'in stock' END AS status
FROM snacks
ORDER BY stock;CASE works anywhere a value can go, even inside an aggregate.
guess first
No ELSE this time. What does Ocean Cats get?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT title,
CASE WHEN minutes > 100 THEN 'long' END AS length
FROM films
WHERE year >= 2020
ORDER BY title;your turn
Your turn. Label the pets.
Type an age in the first blank. Pets older than it get the label in the second blank, and the rest get the label in the third.
result
Your answer appears here as a table.
fix it
The snack labels will not run. Fix it!
result
Your answer appears here as a table.
- Read the error. Which word is it near?
- Every
CASEneeds a closing word before its alias. - Add END before AS, then press run.
nailed it
every row got its own label. :D
CASE WHEN test THEN value ELSE other ENDpicks a value.- Name it with
ASto make a new column. - With no
ELSE, rows that fail every test getNULL.
new words in your collection
- CASE
- WHEN
- THEN
- ELSE
This lesson isn’t saved. A free account remembers every lesson you finish.