CASE is SQL's if-then-else. It checks conditions in order and returns the value for the first one that's true:
SELECT name, price,
CASE
WHEN price >= 60 THEN 'Premium'
WHEN price >= 25 THEN 'Standard'
ELSE 'Budget'
END AS price_band
FROM products;
A product at $70 is Premium and stops there; it never reaches the second WHEN. So put the most specific conditions first. If no condition matches and there's no ELSE, the result is NULL.
CASE works anywhere a value can go: in SELECT, ORDER BY, GROUP BY, and inside aggregates. That last one is a reporting staple called conditional aggregation: SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) counts returned orders, and you can put several of these side by side in one row.