NULL: the missing value

SQL · Unit 4: NULL and expressions

Full plan
SQL
10 exercises

When you learned to filter, one count didn't add up: three customers in Denver, four elsewhere, and the table has eight. The eighth is Chloë, and today the store wants to call her and ask where she lives.

Who's missing a city? NULL is the mark left where nobody entered anything, and IS NULL asks about exactly that. It's written with IS, not with =.

SELECT name, city
FROM customers
WHERE city IS NULL;

Returns

name	city
Chloë	NULL

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. Complete the code
Someone else in the table is missing a different value. Fill in the blank with the column that finds that person.
2. Multiple choice
The Speaker's stock is 0 and Hannah's age is NULL. Which statement is true?
3. Find the bug
Of the customers in Austin, who needs to be asked their age? Hannah should be on the list. The query runs without an error and returns nobody. Which line has the problem?
4. Predict the output
Chloë is 41 and Valerie is 52. Someone wants to know which of the two has a city on file, and figures that if = doesn't work with NULL, <> will. What does this query return?
5. Complete the code
The store mails out its printed catalog, so it can only use customers with a city on file. Fill in the blank.
6. Put the lines in order
Last unit, the list of cities came with an empty row, Chloë's. Put in order the query that shows each city once, in alphabetical order and without that row. Two lines don't belong.
7. Predict the output
Today there are envelopes ready for customers 1, 3, 5, and 8: Ann, Chloë, Valerie, and Brian. Who can get theirs by mail? What does this query return?
8. Find the case that fails
The store calls every customer with no city, to ask for it. Sometimes the gap is a NULL and sometimes someone saved empty text, and the list should have both. Which of these changes lets someone slip past this query?
9. Put the lines in order
This month's age survey goes to Denver and Austin, and it only works with customers who have an age on file. Put in order the query that shows their name and age, in alphabetical order. One line doesn't belong.
10. Write the code
The store is going to complete its customer profiles. Write a query that shows the name, the city, and the age, in that order, of the customers who are missing the city, the age, or both.

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 →