lesson 14 Calculations & data quality · intermediate

Make decisions with CASE

Choose a result for each row using conditional logic.

the idea

CASE creates a value based on conditions. SQL checks WHEN branches in order and uses the first match. ELSE covers remaining rows.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  name,
  CASE
    WHEN price >= 80 THEN 'Premium'
    ELSE 'Standard'
  END AS tier
FROM products;

Watch for: Without ELSE, unmatched rows become NULL.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Read the prices you will classify. Each product has a price. CASE will label rows at or above 80 as Premium and the rest as Standard.

   step-1.sql Run

   sql›

   SELECT name, price
   FROM products
   ORDER BY price;

2. 2

   Assign a tier with CASE. WHEN branches are checked in order. The first true branch wins; ELSE covers everything else.

   step-2.sql Run

   sql›

   SELECT
     name,
     CASE
       WHEN price >= 80 THEN 'Premium'
       ELSE 'Standard'
     END AS tier
   FROM products;