ON or WHERE
SQL · Unit 8: More joins
- Full plan
- SQL
- 10 exercises
Starting this month, the big boxes, the ones for orders of 3 units or more, carry a flyer for the Denver shop. Which of the three Denver customers have already gotten one? The owner wants to see all three, each with their big box next to them if there was one.
This is the first try, with the size condition in the WHERE. Only Valerie shows up. Ann and Brian are from Denver, the query has a LEFT JOIN so it won't lose anyone, and it lost them anyway.
SELECT c.name, o.id, o.quantity
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
WHERE c.city = 'Denver'
AND o.quantity >= 3;Returns
| name | id | quantity |
|---|---|---|
| Valerie | 8 | 3 |
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. Multiple choice
Why isn't Brian there, if the
LEFT JOINkeeps every customer?2. Predict the output
Luke and Hannah live in Austin. Luke has three orders, for 5, 2, and 1 units. Hannah has none. What does this query return?
3. Put the lines in order
This week the owner is calling every customer. Anyone who has ordered something since January 12 gets a thank-you, and everyone else gets asked what they need. Put in order the query that gives each customer's name with the date of their orders since that day, by name and by date. None of the eight can be missing. Two lines don't belong.
4. Predict the output
Austin again, with Luke and his orders for 5, 2, and 1 units, and Hannah with none. This time the quantity condition is in the
ON. What does this query return?5. Find the bug
The big box report says how many each customer has gotten. It's supposed to list all eight, with a 0 for anyone who hasn't gotten one. The query runs without an error, and only three names show up. Which line has the problem?
6. Multiple choice
Someone learned the lesson backward and moved the city condition to the
ONtoo. The store has 8 customers, and 3 live in Denver. How many different customers are in the result?7. Put the lines in order
The Denver shop is going to offer the Keyboard, which is product 1, to the three customers there. First it wants to see who already bought it, and when. Put in order the query that gives the three names, in alphabetical order, with the date each one ordered a Keyboard, or empty if they haven't. Two lines don't belong.
8. Find the case that fails
The store is visiting Boston next week with the list of every customer there. Each one is listed with the orders they're still waiting on, the ones from January 12 on. Anyone who isn't waiting on anything is listed anyway, with the date empty. Today only Dylan lives in Boston, with orders from the 14th and the 15th. The query has the date condition in the
WHERE. Which of these changes makes it leave someone from Boston off the list?9. Complete the code
The last two lessons in one query. Who has never gotten a big box? The size condition decides which orders count as a match, and the
IS NULLkeeps whoever had none. Fill in the two words.10. Write the code
Who does the owner call to ask what they need? Write a query with a single column: the name of the customers who have no order dated January 12 or later. Luke's, dated the 20th, counts, because it's already been placed. In alphabetical order.
It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.