Project: each customer's profile

SQL · Unit 4: NULL and expressions

Full plan
SQL
Project
3 exercises

The store wants a profile for each customer, with the name, the city, and the age group: Young through 29, Adult through 49, and Senior from there on. This is the first attempt, with three sample customers.

Two profiles come out wrong. Chloë's is missing the city. And Hannah shows up as Senior when nobody knows her age, because the ELSE took her.

SELECT name, city,
  CASE
    WHEN age < 30 THEN 'Young'
    WHEN age < 50 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM customers
WHERE id IN (3, 5, 7);

Returns

name	city	age_group
Chloë	NULL	Adult
Valerie	Denver	Senior
Hannah	Austin	Senior

Exercises in this lesson

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

1. Complete the code
First, the groups for the customers who do have an age on file. Fill in the two cutoffs.
2. Write the code
Now all eight. Finish the CASE so the query shows each customer's name and group, in that order: Young under 30, Adult under 50, Senior from there on, and Unknown when there's no age.
3. Write the code
The complete profile. Show three columns in this order: the name, the city, saying No city where it's missing, and the age group from the previous step. The profiles go by name, in alphabetical order.

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 →