Lesson 9 of 10
Labeling with conditions
- 11 min
- 5 checks
- 3 hints
- Go to task
Goal
The CASE WHEN expression
01
Explanation
CASE lets you label values inside the query itself. It tries the WHEN conditions in order, returns the value of the first one that's true, and stops there.
CASE WHEN price >= 500 THEN 'premium' WHEN price >= 100 THEN 'mid-range' ELSE 'budget' END AS band
That's why you write the narrowest condition first. If you put price >= 100 first, the premium products would land in it too, because anything priced at 500 or more is also over 100. And if you leave out ELSE, anything that matches no condition gets NULL.
02
Your task
Show name, stock and a column named status whose value is 'out of stock' when the stock is zero, 'low' when it's under 10, and 'in stock' otherwise.
Checks
5 checks- Columns are name, stock and status
- Every product is there
- A product with zero stock is 'out of stock', not 'low'
- Stock from 1 to 9 is 'low'
- Stock of 10 or more is 'in stock'
Tables in this lesson
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price INTEGER NOT NULL,
stock INTEGER NOT NULL
);
INSERT INTO products (name, category, price, stock) VALUES
('27-inch monitor', 'tech', 899, 4),
('Wireless headphones', 'tech', 249, 12),
('Keyboard', 'tech', 189, 0),
('Fast charger', 'tech', 79, 40),
('Office chair', 'home', 649, 3),
('Bookshelf', 'home', 249, 7),
('Desk lamp', 'home', 100, 18),
('Backpack', 'stationery', 180, 22),
('Notebook', 'stationery', 15, 200),
('Colored pencils', 'stationery', 25, 0),
('Dictionary', 'books', 95, 10),
('Novel', 'books', 45, 30);