COALESCE

SQL · Unit 4: NULL and expressions

Full plan
SQL
10 exercises

Chloë's label came out blank, and her package has to go out anyway. What goes where the city would be? COALESCE takes several values and returns the first one that isn't NULL.

Here it tries city. If there is one, it keeps it. If not, it moves on to the next value, which is the text you chose.

SELECT name,
  COALESCE(city, 'No city')
  AS lives_in
FROM customers
WHERE id <= 4;

Returns

name	lives_in
Ann	Denver
Luke	Austin
Chloë	No city
Mason	Tampa

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
COALESCE takes as many values as you want and checks them left to right. What does this query return?
2. Complete the code
Someone is going to call Chloë and Valerie, customers 3 and 5, and wants the list to show where each one is from. Where the city is missing it should say "No city." Fill in the blank.
3. Find the bug
Where does the store send packages? The list should have the four cities and a row that says "No city." It has a single row, the one that says "No city." Which line has the problem?
4. Predict the output
Here someone wrapped the whole profile, and not only the piece that can be missing. Chloë is customer 3 and Mason is customer 4. What does this query return?
5. Put the lines in order
Envelopes go out today for Chloë, Valerie, and Brian, customers 3, 5, and 8. Each one is addressed with the name and the city in parentheses, like "Brian (Denver)." Put the query in order so that no envelope goes out blank. One line doesn't belong.
6. Multiple choice
Someone runs this query to fix Chloë's profile. After running it, who does a filter with WHERE city IS NULL return?
7. Multiple choice
In which of these cases does swapping a NULL for 0 not lie to anyone?
8. Find the case that fails
This query looks for the youngest customer in Denver, among those with an age on file. Whoever wrote it got the NULLs out of the way with a COALESCE. Which of these changes makes it give the wrong answer?
9. Put the lines in order
Last lesson's coupons, written another way. Put in order the query that shows everyone who isn't from Denver, counting anyone with no city too. One line doesn't belong.
10. Write the code
The complete directory, this time leaving nobody out. Write a query with two columns, in this order: each customer's city, saying No city where it's missing, and their name. The rows go by that city in alphabetical order and, within each one, by name.

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 →