DELETE

SQL · Unit 11: Changing data

Full plan
SQL
9 exercises

The Cable is still running late. Valerie is tired of waiting and cancels her order for 3, number 8. This store deletes an order that gets canceled. Not every store does.

DELETE FROM names the table, and WHERE says which rows. It takes no list of columns, because it doesn't delete single values. It deletes whole rows. Valerie had two orders, and she's left with one. Nothing is left of row 8 anywhere, not an empty row, not a mark.

DELETE FROM orders
WHERE id = 8;
SELECT id, product_id, quantity
FROM orders
WHERE customer_id = 5;

Returns

id	product_id	quantity
5	1	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. Predict the output
Luke, customer 2, asks the store to delete his order history, which is orders 3, 6, and 12. The big boxes, 3 units or more, were orders 3, 4, and 8. What does the SELECT return?
2. Complete the code
Brian, customer 8, asks the store to forget his age. Hannah, customer 7, asks to be deleted altogether. Fill in the blanks in the two statements.
3. Find the bug
Someone ran Zoe's signup twice, and the store has two identical customers, 9 and 10. The first five lines leave the table that way, and the next two were supposed to delete the second one. Here SQL answers near "*": syntax error. Which line has the problem?
4. Put the lines in order
The Cable, product 4, isn't coming this month, and the store cancels the orders that were waiting on it. Put the three statements in order. The first counts how many orders are going to be deleted, the second deletes them, and the third counts how many are left in the table. Two lines don't belong.
5. Write the code
Dylan, customer 6, is leaving on a trip and cancels what he still has coming, which is his orders from January 12 on. What he already received stays. Write the DELETE. It has to work even if Dylan had older orders.
6. Find the bug
Mason cancels his only order, which is number 4. Here SQL answers FOREIGN KEY constraint failed. Which line has the problem?
7. Put the lines in order
Ann, customer 1, asks the store to delete her data, and her three orders with it. Put in order the two statements that do it and the SELECT that counts the customers who are left. Two lines don't belong.
8. Multiple choice
In a real database, you deleted order 8 when Valerie canceled it. At the end of the month, the owner asks how many orders were canceled in January. What's your answer?
9. Write the code
The owner is cleaning up the catalog. Out go the products that are out of stock and have also never been sold. An out-of-stock product that someone once ordered stays, and so does one that nobody has ordered but has stock. Write a single DELETE, without typing in any product's id by hand. The ones that have been sold are the ones that show up in the product_id column of orders.

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 →