CASE WHEN condition THEN value ... ELSE value END checks the conditions in order and uses the first one that is true. It works like nested IF formulas in Excel.

For every product, show the name and a column price_band: 'budget' under 3.00, 'standard' from 3.00 up to 4.00 (both included), and 'premium' above 4.00. Order by product_id.