Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Write a SELECT (15 minutes)
Write a query that returns the full_name and address of every customer in customers.
Done when: a correct SELECT with no WHERE clause is given, since every row is wanted.
Hint
SELECT full_name, address FROM customers; with no condition returns every row.
Easy 2: Write an INSERT (15 minutes)
Write a statement that inserts a new customer with customer_id 108, full_name "Rohan Mehta", and address "9 Park Street, Kolkata".
Done when: a correct INSERT INTO customers statement with all three values is given.
Hint
List all three columns in the same order as the values.
Easy 3: Name the statement (15 minutes)
For each of these, name which of the four statements it is: removing a cancelled order, reading all orders from last week, correcting a misspelled name, adding a new book to the catalog.
Done when: all four are correctly matched to DELETE, SELECT, UPDATE and INSERT.
Hint
DELETE removes, SELECT reads, UPDATE changes, INSERT adds.
Medium
Medium 1: Spot the dangerous statement (25 minutes)
Explain exactly what UPDATE orders SET placed_on = '2026-01-01'; does, with no WHERE clause, and why this is almost certainly a mistake.
Done when: the explanation correctly states every row in orders gets the new date, and names the missing WHERE clause as the cause.
Hint
Every row's placed_on becomes the same date, since nothing narrows which rows are affected.
Medium 2: Test before you delete (25 minutes)
Write the SELECT statement that should be run first to check before executing DELETE FROM orders WHERE customer_id = 107;, and explain what result would confirm it is safe to run.
Done when: the matching SELECT is given, and the safe result (only that customer's orders) is described.
Hint
SELECT * FROM orders WHERE customer_id = 107; should return only that customer's orders before the delete runs.
Medium 3: Combine filtering with a change (25 minutes)
Write a statement that marks every order placed before 2025 as status = 'archived', assuming orders has a status column.
Done when: a correct UPDATE with a WHERE placed_on < '2025-01-01' condition is given.
Hint
UPDATE orders SET status = 'archived' WHERE placed_on < '2025-01-01'; is the correctly scoped statement.
Hard
Hard 1: What happens to rows that reference a deleted row (30 minutes)
orders.customer_id is a foreign key referencing customers.customer_id. Explain what should happen when someone tries to run DELETE FROM customers WHERE customer_id = 107, if that customer still has orders.
Done when: the explanation correctly states the delete should be rejected, or the orders handled first, to preserve referential integrity.
Hint
A database enforcing referential integrity rejects the delete unless the dependent orders are deleted or reassigned first.
Hard 2: INSERT with a missing required column (35 minutes)
Given customers (customer_id PRIMARY KEY, full_name TEXT NOT NULL, address TEXT NOT NULL), explain what happens when an INSERT statement supplies only customer_id and full_name.
Done when: the explanation correctly states the insert is rejected, since address is NOT NULL and has no value supplied.
Hint
NOT NULL means every insert must supply a value for that column, so the statement fails.
Hard 3: Rewrite an unsafe UPDATE safely (40 minutes)
A junior developer wrote UPDATE orders SET status = 'shipped' intending to update only today's orders, but forgot the condition. Rewrite the statement correctly, and describe the one-line check that would have caught the mistake before running it.
Done when: a correctly scoped UPDATE with a date condition is given, along with the matching SELECT check.
Hint
Add WHERE placed_on = CURRENT_DATE (or the specific date meant), and test it first with the matching SELECT.