CASE

SQL · Unit 4: NULL and expressions

Full plan
SQL
10 exercises

The store ships anything that costs 100 or more for free. What does each product's label say: "Free" or "Charged"? That isn't stored in any column. It's decided row by row.

It reads almost the way you'd say it: CASE, WHEN this happens, THEN this value, ELSE this other one, END. The condition in the WHEN is like the ones in a WHERE. The whole thing is a single column, with its alias.

SELECT name,
  CASE
    WHEN price >= 100 THEN 'Free'
    ELSE 'Charged'
  END AS shipping
FROM products
WHERE category <> 'Accessories';

Returns

name	shipping
Monitor	Free
Laptop	Free
Tablet	Free
Headphones	Charged
Speaker	Charged

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
Orders of 3 units or more ship in a big box. Valerie, customer 5, has two orders: order 5 for 1 unit and order 8 for 3. What does this query return?
2. Complete the code
Each Audio product's sign should say "Sold out" or "Available." Fill in the two missing words.
3. Put the lines in order
The list for the new shop says what each customer gets: "Party" if they live in Denver and "Coupon" if not. Put the query in order. One line doesn't belong.
4. Predict the output
Each product's alert depends on how many are left. There are 0 Speakers, which also meets stock <= 5. What does this query return?
5. Multiple choice
Someone wrote the price signs with >=, arranged like this. The Tablet costs 320 and the Laptop costs 899. How many products get "Pricey"?
6. Put the lines in order
Fix those signs without touching the conditions. Put the query in order so each product gets the sign it should have. One line doesn't belong.
7. Predict the output
Someone rewrites the list for the new shop and swaps the ELSE for a second condition, sure that it's the same thing. Ann is from Denver, Luke is from Austin, and Chloë has no city. What does this query return?
8. Predict the output
The school secretary is putting together the Science list with each student's result. The grade for Julia, student 4, is NULL. What does this query return?
9. Find the bug
Someone notices and adds a case for the missing grades. The list runs without an error, but Julia is still listed as "Failed" instead of "Pending." Which line has the problem?
10. Write the code
Now the Math list, which is course 1. Today everyone has a grade there, so the query below already gives the right result. But it will also be tested with a missing grade, and it has to keep working then. Add the missing case so it shows the student_id and the result, in that order. The result is Pending if there's no grade, Passed if it's 6 or higher, and Failed if it's lower.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →