CASE expressions

CASE WHEN stock = 0 THEN 'out' WHEN stock < 10 THEN 'low' ELSE 'ok' END evaluates the conditions in order and gives the first matching value, like an if/else chain inside a query. It works anywhere an expression does: in the select list with an alias, inside aggregates (SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts matching rows), or in ORDER BY for custom sort orders.

The project that follows joins this view to the others: a label per product is exactly what a report wants next to each order line.

SELECT name, CASE WHEN salary >= 7000 THEN 'senior' ELSE 'staff' END AS level FROM employees;

Your task

Show each product's id, name, and an availability column that is out when stock is 0, low when it is below 10, and ok otherwise.

Part of the final project. availability is the view that labels each product out, low, or ok. It is saved in this browser and loaded into Project: the order report when you get there.

SELECT id, name,
  CASE
    WHEN stock = 0 THEN 'out'
    WHEN stock < 10 THEN 'low'
    ELSE 'ok'
  END AS availability
FROM products;