UPDATE

SQL · Unit 11: Changing data

Full plan
SQL
11 exercises

In unit 4, Chloë's label came out blank, and COALESCE put "No city" in the result without touching the table. To really fill the gap, someone had to ask Chloë. Someone asked. She lives in Omaha.

UPDATE names the table. SET names the column and its new value. WHERE says which rows, the same as in a SELECT. The = in SET doesn't compare anything. It stores. The one in the WHERE still compares.

UPDATE customers
SET city = 'Omaha'
WHERE id = 3;
SELECT id, name, city
FROM customers
WHERE id = 3;

Returns

id	name	city
3	Chloë	Omaha

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. Predict the output
The stock clerk counted the Audio section, and there are 10 of each product. The table said 15 Headphones and 0 Speakers. No other product has 10 in stock. What does the SELECT return?
2. Complete the code
Hannah, customer 7, calls to complete her profile. She's 31. Fill in the blanks.
3. Find the bug
20 Cables and 20 Speakers came in, products 4 and 8, which were out of stock. The statement runs without an error, and the SELECT shows all eight products with 20 units. Which line has the problem?
4. Predict the output
The SELECT from before showed two rows: the Cable, product 4, and the Speaker, product 8. Now the change runs, and under it, that same SELECT again. What does it return?
5. Predict the output
Every January the store adds a year to each customer's age. This UPDATE has no WHERE on purpose, because the change is for everyone. Dylan is 27, Brian is 38, and Hannah's age is NULL. What does the SELECT return?
6. Multiple choice
In a real database, 24 Cables come in. They were out of stock. You run the UPDATE, the connection drops, and you don't know whether it got stored. Which of these statements can you run again without looking at the table first?
7. Put the lines in order
Everything in Audio goes up 10%, and the new price is stored with two decimals. Put in order the UPDATE and the SELECT that shows products 5 through 8: the two devices, which don't change, and the two in Audio. Two lines don't belong.
8. Predict the output
A customer returns 2 Cables, which go back to the warehouse, and someone enters them like this. The Cable is product 4 and was out of stock. The statement runs without an error. What does the SELECT return?
9. Put the lines in order
The owner restocks what moves the most, adding 10 units to every product that has ever been ordered in a big box, 3 units or more. Put in order the UPDATE, which pulls those products from orders, and the SELECT that shows the stock of all of them. Two lines don't belong.
10. Find the case that fails
The Mouse, which is product 2, drops from 19.9 to 15. Whoever wrote the change looked the row up by its name. Which of these changes, made earlier, makes the statement change the price of a product it shouldn't?
11. Write the code
Since unit 2 there's been an order dated January 20, number 12, and nobody knew whether it was a scheduled delivery or a typo in the date. Luke clears it up. He placed it today, January 15. Fix the date on that order and on no other, even if more orders are dated the 20th.

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 →