Transactions

SQL · Unit 11: Changing data

Full plan
SQL
9 exercises

Another database, the company. Nobody works in Legal, department 4, and management moves 5000 of its 20000 to Design, department 3, which has 30000. That's two UPDATEs, one that subtracts and one that adds. Between the two there's a moment when the company is 5000 short. If something cuts out right there, that money isn't in any department.

A transaction groups several statements so they're stored as one. BEGIN; opens it, and COMMIT; closes it and stores everything at once. Until the COMMIT nothing is stored, and if the connection drops before it, the database throws away everything in between.

BEGIN;
UPDATE departments
SET budget = budget - 5000
WHERE id = 4;
UPDATE departments
SET budget = budget + 5000
WHERE id = 3;
COMMIT;
SELECT id, name, budget
FROM departments;

Returns

id	name	budget
1	Sales	50000
2	Tech	80000
3	Design	35000
4	Legal	15000

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. Complete the code
Tech, department 2, moves 8000 to Sales, department 1. Fill in the blanks with the two words that open and close the transaction, so that the two UPDATEs are stored together.
2. Predict the output
Legal moves 5000 to Design. Design had 30000 and Legal had 20000. Someone opens the transaction, runs the two UPDATEs, and closes it like this. What does the SELECT return?
3. Put the lines in order
Management wants to see what the budgets would add up to if every department kept half, without the cut being stored. Put the dry run in order. It has the change inside a transaction, a SELECT that looks at it, the undo, and a last SELECT that checks everything is still in place. Two lines don't belong.
4. Multiple choice
In a real database, HR gives the two sales reps a raise of 200. Whoever does it opens a transaction, runs the UPDATE and, before closing, looks. The SELECT shows that Carol, the CEO, is making 200 more too. What's the next line?
5. Find the bug
Sales, department 1, moves 6000 to Tech, department 2. Before the COMMIT, whoever wrote it looks at the total, which should still be 180000. It says 174600. Which line has the problem?
6. Multiple choice
In a real database you open a transaction, run the two UPDATEs of the transfer, look at the total, which is fine, and the connection drops before you've typed COMMIT. What ends up stored?
7. Complete the code
Valerie, customer 5, buys 2 Tablets today, product 6. There were 7 Tablets and 3 Laptops, which are the store's two devices. Fill in the blanks to complete the sale.
8. Multiple choice
Which of these changes needs a transaction?
9. Write the code
Luke, customer 2, asks the store to delete his data, and all his orders with it. Write the two statements inside the transaction and close it so that the change is stored. The BEGIN; is already there. If you leave it open, the app will tell you a transaction is already open.

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 →