Code Lab BetaBUILD. RUN. DISCOVER.
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
SQL / POSTGRESQL / Learn / LESSON 01
CASE WHEN turns a value into a category by rules — an "if … else …" inside a query. Conditions are tested top to bottom and the first true one wins. ELSE catches the rest; without ELSE, an unmatched row yields NULL. Every branch must return a compatible type (text here). Close with END and name the column with AS.
Boundaries: price >= 30 includes exactly 30, while 29.99 falls to the next branch. Stored data never changes — CASE only computes a new result column.
Common mistakes: putting the lowest threshold first (it swallows everything below); forgetting END; omitting ELSE and getting surprise NULLs; mixing up > and >= at a boundary.
Small example:
SELECT name, price, CASE WHEN price >= 20 THEN 'high' ELSE 'low' END AS price_band FROM products;
Working tables for this task. Grading also uses other datasets with the same schema.
| id | name | category | price |
|---|---|---|---|
| 1 | Atlas | books | 30 |
| 2 | Novel | books | 15 |
| 3 | Comic | books | 14.99 |
| 4 | Lamp | home | 29.99 |
| 5 | Vase | home | 30 |
| 6 | Rug | home | 45 |
| 7 | Hammer | tools | 15 |
| 8 | Drill | tools | 60 |
| 9 | Tape | tools | 15 |
| 10 | Candle | NULL | 8.5 |
| 11 | Gift Card | NULL | 25 |
| id | name | city |
|---|---|---|
| 1 | Mira | Sofia |
| 2 | Niko | Varna |
| 3 | Iva | Sofia |
| 4 | Alex | NULL |
| 5 | Boris | Plovdiv |
| 6 | Lena | Varna |
| 7 | Petya | Sofia |
| id | customer_id | total | order_date |
|---|---|---|---|
| 1 | 1 | 120 | 2024-01-05 |
| 2 | 1 | 35.5 | 2024-01-20 |
| 3 | 2 | 40 | 2024-01-20 |
| 4 | 3 | 99.99 | 2024-01-31 |
| 5 | NULL | 60 | 2024-02-01 |
| 6 | 1 | 40 | 2024-02-01 |
| 7 | 2 | 75.25 | 2024-02-14 |
| 8 | 3 | 100 | 2024-02-15 |
| 9 | 5 | 22 | 2024-02-15 |
| 10 | 1 | 39.99 | 2024-02-29 |
| 11 | 2 | 55 | 2024-03-01 |
| 12 | 3 | 18 | 2024-03-10 |
| 13 | 1 | 130 | 2024-03-15 |
| 14 | 2 | 40 | 2024-03-15 |
| 15 | 2 | 12.5 | 2024-03-31 |
| 16 | 3 | 47 | 2024-04-01 |
Expected columns: name, price, tier · row order is not required
Expected result (displayed data):
Loading…CASE WHEN turns a value into a category by rules — an "if … else …" inside a query. Conditions are tested top to bottom and the first true one wins. ELSE catches the rest; without ELSE, an unmatched row yields NULL. Every branch must return a compatible type (text here). Close with END and name the column with AS. Boundaries: price >= 30 includes exactly 30, while 29.99 falls to the next branch. Stored data never changes — CASE only computes a new result column. Common mistakes: putting the lowest threshold first (it swallows everything below); forgetting END; omitting ELSE and getting surprise NULLs; mixing up > and >= at a boundary.
QUERY STANDBY
›