CSE Roadmap

Core tier · about 2 hours · 5 parts

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.