Project: recording a sale

SQL · Unit 11: Changing data

Full plan
SQL
Project
3 exercises

Someone entered each of the twelve rows in orders. You enter the next one. Hannah, customer 7, has been in customers since unit 1 and has never bought a thing. Today, January 15, she takes home 3 Mice, product 2.

Recording a sale starts with looking. Are there any Mice, and how many? It's the SELECT you run before a change, and this time it doesn't show which rows you're going to touch. It shows whether the sale can be made.

SELECT id, name, stock
FROM products
WHERE id = 2;

Returns

id	name	stock
2	Mouse	30

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Write the code
Record the sale. Hannah, customer 7, buys 3 Mice today, January 15, product 2. It's two statements inside a transaction. One puts the order into orders, without giving its id. The other lowers the Mouse's stock by what Hannah takes, whatever that stock is. The BEGIN; is already there. Close with COMMIT;. If you leave it open, the app will tell you a transaction is already open.
2. Find the case that fails
This is the stock step of a sale of 2 Monitors, product 3. There are 5 today, and after the sale 3 are left. The statement subtracts without checking how many there are. Which of these changes, made earlier, makes it store an impossible stock?
3. Predict the output
The sales report says how many units of each product have been ordered. Up to today, in the Audio section it said 3 for the Headphones, which are product 7 and have 15 in the warehouse, and 0 for the Speaker. This sale comes in, and nobody touches the report. What does it return?

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 →