Project: an order's receipt
SQL · Unit 7: Joining tables
- Full plan
- SQL
- Project
- 3 exercises
Luke, customer 2, asks for a receipt for all his orders, including the one dated the 20th. A receipt has one line per order, with the product, the quantity, and what it adds up to, and at the end a total with the 16% tax.
This is what orders knows about his purchases. It's three rows of numbers, and none says which product it is or how much it costs.
SELECT id, product_id, quantity
FROM orders
WHERE customer_id = 2;Returns
| id | product_id | quantity |
|---|---|---|
| 3 | 2 | 5 |
| 6 | 3 | 2 |
| 12 | 6 | 1 |
Exercises in this lesson
You do them in the app, which checks them on the spot and explains why.
1. Put the lines in order
First, a dry run with a single order, order 9. Put in order the query that shows its product, the quantity, and the total, which is the price times the quantity ordered. Two lines don't belong.
2. Write the code
The lines of the receipt for Luke, customer 2. Write a query with four columns: the product's name, then the quantity, then the price of one unit, then the line total, which is that price times the quantity. One row per order, all three, from oldest to newest.
3. Write the code
The last line of Luke's receipt, the totals. Write a query with three columns: first how many units he ordered across all his orders, then how much they add up to without tax, then how much they add up to with the 16% tax, rounded to two decimals. The tax is calculated on the sum.
It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.