Three tables

SQL · Unit 8: More joins

Full plan
SQL
10 exercises

Last unit, Luke's receipt didn't say "Luke" anywhere. It had the product, and the buyer's name lived in a third table. Who bought what?

After one JOIN there can be another. The first puts the customer next to each order, and the second adds the product to that same row. Each one has its ON underneath. customers and products each have a name column, and in the result each gets its own heading. This is the receipt, with a name on it.

SELECT c.name AS customer,
  p.name AS product, o.quantity
FROM orders o
JOIN customers c
  ON o.customer_id = c.id
JOIN products p
  ON o.product_id = p.id
WHERE c.id = 2;

Returns

customer	product	quantity
Luke	Mouse	5
Luke	Monitor	2
Luke	Tablet	1

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. Find the bug
The Cable is running late, and last unit the query for that notice returned two customer numbers. This one looks up their names and how many Cables each is waiting on. Here SQL answers ambiguous column name and points at name. Which line has the problem?
2. Predict the output
There were two orders on January 12. Order 7 says customer_id 1 and product_id 2. Order 8 says customer_id 5 and product_id 4. In customers, 1 is Ann and 5 is Valerie. In products, 1 is the Keyboard, 2 the Mouse, 4 the Cable, and 5 the Laptop. What does this query return?
3. Put the lines in order
What has Chloë bought? Put in order the query that looks her up by name and gives the product and the quantity of her orders. Two lines don't belong.
4. Complete the code
Which city buys the most accessories? Fill in the sum with the units of each order.
5. Multiple choice
Why can't students be joined to courses directly, without going through grades?
6. Complete the code
Who does Irene teach, and what grade did she give them? Fill in each ON with the foreign key of grades that points to that table.
7. Predict the output
Leo and Sara are students 6 and 7. Leo takes Math and Science. Sara takes no courses, so the LEFT JOIN leaves her with g.course_id as NULL. Joins are worked out from top to bottom, and the second one works on what the first one left. What does this query return?
8. Put the lines in order
The third-year list for parents' night, with each student and the courses they take. All three third-year students have to be on it, whether they take courses or not. Put the query in order, with the students in alphabetical order and, within each one, their courses too. Two lines don't belong.
9. Find the case that fails
The second-year list, with each student and the courses they take. Everyone in that year has to be on it, whether they take courses or not. Today there are two: Henry, with three courses, and Julia, with two. Someone left a plain JOIN in the second join. Which of these changes makes the query leave a second-year student off the list?
10. Write the code
At this school a 6 passes, and passing a course earns its credits. Write a query with two columns, in this order: the name of each student who has passed at least one course and the total credits of the courses they passed, which are in the credits column of courses. Most credits first, and ties 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 →