Skip to the content
Start

NULL in calculations

SQL · Unit 4: NULL and expressions

Three packages go out today, for customers 2, 3, and 4, and each one carries a label with the name and the city. Look at the middle one. It doesn't even say "Chloë."

Gluing text to NULL gives NULL. The "I don't know" spreads to the whole calculation, and not only with ||: adding, subtracting, or multiplying with a NULL gives NULL too.

SELECT name || ', ' || city
  AS label
FROM customers
WHERE id IN (2, 3, 4);

Returns

label
Luke, Austin
NULL
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. 1. Predict the output

    Every January the store adds a year to each customer's age. Luke and Hannah live in Austin. What does this query return?

  2. 2. Predict the output

    There's no table here, only two bare calculations. What does this query return?

  3. 3. Predict the output

    Someone tries to rescue Chloë with a NOT, thinking that if she isn't from Denver she'll have to show up. But negating an "I don't know" gives another "I don't know." What does this query return?

  4. 4. Multiple choice

    The store splits its 8 customers into two lists, one with age < 30 and one with age >= 30. How many people are on the two lists combined?

  5. 5. Complete the code

    Delivery reaches Boston, Tampa, and Austin. Who doesn't it reach? Fill in the blank so that the query returns everyone else, including anyone with no city on file.

  6. 6. Find the bug

    The senior plan is for anyone 50 or older, and that's only Valerie. The other seven customers get the usual flyer. This list should have all seven and has six. Which line has the problem?

  7. 7. Put the lines in order

    How many years does each customer have to go before the senior plan, which starts at 50? Put the query in order, starting with whoever is closest. Dylan and Luke tie, and between them the name decides. One line doesn't belong.

  8. 8. Multiple choice

    This list of the two Austin customers, oldest to youngest, shows Luke on top and Hannah at the bottom here. What happens if you run it in PostgreSQL?

  9. 9. Put the lines in order

    The store's directory goes by city and, within each city, by name. To keep a row with no city off the top, only customers with a city on file go in. Put the query in order. One line doesn't belong.

  10. 10. Write the code

    Now for real. Who is the store's youngest customer? The query below is the one that came back with Hannah. Fix it so it shows the name and the age, in that order, of the youngest among those with an age on file.

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 →