SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 22 of 250

Moderate

Label stock levels

Your task

Return each title and the label 'sold out' when stock is zero, or 'available' otherwise.

CASE evaluates its branches in order and returns the value from the first match.

Sample data

Table:albums
idtitlegenrepricestock
1Night BusJazz187
2Static BloomIndie224
3Soft CircuitElectronic240
4Blue HourJazz163
5CoastlineIndie198
6Low TideAmbient212

Expected output

titleCASE WHEN stock = 0 THEN 'sold out' ELSE 'available' END
Night Busavailable
Static Bloomavailable
Soft Circuitsold out
Blue Houravailable
Coastlineavailable
Low Tideavailable
Show a hint

Use CASE WHEN stock = 0 THEN 'sold out' ELSE 'available' END.

Expressions and missing values guide

Calculate a value for each row

Expressions can combine columns, constants, and functions. This query estimates how much stock is worth at the listed price. AS gives the calculation a name in the result.

SELECT title, price * stock AS stock_value
FROM albums;

Build a category from conditions

CASE tests branches from top to bottom. Put the most specific test first: zero would also satisfy stock < 5. ELSE catches the remaining rows.

SELECT title,
       CASE
         WHEN stock = 0 THEN 'out of stock'
         WHEN stock < 5 THEN 'low stock'
         ELSE 'stocked'
       END AS availability
FROM albums;

Account for missing values

NULL represents an unknown or missing value. Compare it with IS NULL rather than equality. NULLIF can also turn a known sentinel into NULL. AVG ignores NULL, so these two averages use different sets of albums.

SELECT AVG(stock) AS all_albums,
       AVG(NULLIF(stock, 0)) AS in_stock_only
FROM albums;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…