Delete the items of cancelled orders
Medium · Online shop database · DELETE, Subqueries
The shop wants to clear out the items of cancelled orders. Delete from order_items every row that belongs to an order with status cancelled.
Keep the orders themselves, and keep the items of delivered and shipped orders.
After your SQL, this query shows
SELECT * FROM order_items ORDER BY id;
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 22.99 |
| 2 | 1 | 9 | 3 | 3.99 |
| 3 | 2 | 4 | 1 | 189 |
| 4 | 2 | 16 | 2 | 15.99 |
| 5 | 3 | 2 | 1 | 89.5 |
| 8 | 5 | 15 | 2 | 27.5 |
| 9 | 5 | 16 | 1 | 15.99 |
| 10 | 6 | 5 | 1 | 45 |
| 11 | 6 | 6 | 2 | 18.5 |
| 12 | 7 | 13 | 1 | 54.99 |
| 13 | 7 | 12 | 4 | 12 |
| 14 | 8 | 3 | 2 | 39 |
| 16 | 10 | 1 | 2 | 24.99 |
| 17 | 10 | 8 | 5 | 6.49 |
| 18 | 11 | 17 | 1 | 119 |
| 19 | 12 | 9 | 10 | 3.99 |
| 20 | 12 | 8 | 2 | 6.49 |
| 21 | 13 | 10 | 1 | 20 |
| 22 | 13 | 12 | 2 | 12 |
| 24 | 15 | 6 | 1 | 18.5 |
| 25 | 16 | 14 | 1 | 39 |
| 26 | 16 | 13 | 1 | 54.99 |
| 28 | 18 | 3 | 1 | 39 |
| 29 | 19 | 15 | 1 | 27.5 |
| 30 | 19 | 16 | 2 | 15.99 |
| 31 | 20 | 5 | 2 | 48 |
| 32 | 21 | 12 | 3 | 12 |
| 33 | 22 | 4 | 1 | 199 |
| 34 | 23 | 8 | 3 | 6.49 |
| 35 | 24 | 14 | 1 | 39 |
First 30 of 35 rows.
The online shop tables
customers: 18 rows, first 5 shown
| id | name | city | country | signup_date | phone |
|---|---|---|---|---|---|
| 1 | Ravi Kumar | Bengaluru | India | 2025-01-12 | +91 98450 12345 |
| 2 | Emily Johnson | Austin | USA | 2025-01-25 | +1 512 555 0142 |
| 3 | Hiroshi Sato | Osaka | Japan | 2025-02-08 | NULL |
| 4 | Ana Souza | Sao Paulo | Brazil | 2025-02-19 | +55 11 91234 5678 |
| 5 | Priya Sharma | Mumbai | India | 2025-03-03 | +91 99200 54321 |
order_items: 40 rows, first 5 shown
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 22.99 |
| 2 | 1 | 9 | 3 | 3.99 |
| 3 | 2 | 4 | 1 | 189 |
| 4 | 2 | 16 | 2 | 15.99 |
| 5 | 3 | 2 | 1 | 89.5 |
orders: 28 rows, first 5 shown
| id | customer_id | order_date | status |
|---|---|---|---|
| 1 | 1 | 2025-02-03 | delivered |
| 2 | 2 | 2025-02-14 | delivered |
| 3 | 3 | 2025-03-01 | delivered |
| 4 | 1 | 2025-03-22 | cancelled |
| 5 | 4 | 2025-04-05 | delivered |
products: 18 rows, first 5 shown
| id | name | category | price | stock |
|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 24.99 | 120 |
| 2 | Mechanical Keyboard | Electronics | 89.5 | 45 |
| 3 | USB-C Hub | Electronics | 39 | 80 |
| 4 | Noise Cancelling Headphones | Electronics | 199 | 25 |
| 5 | Intro to Statistics | Books | 48 | 60 |
-- Write your SQL here