Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Write a filtered query (15 minutes)
Write a query returning every book in books priced under 500.
Done when: a correct SELECT ... WHERE price < 500 is given.
Hint
SELECT * FROM books WHERE price < 500;
Easy 2: Combine two conditions (15 minutes)
Write a query returning orders placed by customer 107 on or after 2026-01-01.
Done when: a correct query using AND to combine both conditions is given.
Hint
WHERE customer_id = 107 AND placed_on >= '2026-01-01'
Easy 3: Sort descending (15 minutes)
Write a query listing all books from most expensive to least expensive.
Done when: a correct ORDER BY price DESC is used.
Hint
ORDER BY price DESC with no WHERE needed.
Medium
Medium 1: Multi-column sort (25 minutes)
Write a query listing orders sorted by customer_id ascending, and within each customer, by placed_on descending.
Done when: a correct ORDER BY customer_id ASC, placed_on DESC is given.
Hint
List both columns in ORDER BY, each with its own ASC or DESC.
Medium 2: Page three of a result (25 minutes)
Assuming ten rows per page, write the query for page three of books sorted by price ascending.
Done when: the correct OFFSET value (20) is computed and used with LIMIT 10.
Hint
Page one is OFFSET 0, page two is OFFSET 10, page three is OFFSET 20.
Medium 3: Mixing AND and OR correctly (25 minutes)
Write a query returning orders where the customer is 107, and the order was either placed in 2026 or has an order_id above 9000. Use parentheses to group correctly.
Done when: the OR condition is correctly parenthesized so it does not accidentally apply to the whole WHERE clause.
Hint
WHERE customer_id = 107 AND (placed_on >= '2026-01-01' OR order_id > 9000).
Hard
Hard 1: Why unordered paging breaks (30 minutes)
Explain what could go wrong if an application pages through orders using LIMIT 10 OFFSET 10, LIMIT 10 OFFSET 20, and so on, with no ORDER BY at all, while new orders are being inserted at the same time.
Done when: the explanation correctly identifies that rows can appear twice, be skipped, or shift between pages, since no order is guaranteed.
Hint
Without a fixed order, the database can return rows differently on each call, causing duplicates or gaps across pages as data changes.
Hard 2: Rewriting an OR-heavy condition (35 minutes)
Rewrite WHERE customer_id = 107 OR customer_id = 108 OR customer_id = 109 using a single, equivalent operator that reads more clearly for a longer list of values.
Done when: the condition is correctly rewritten using IN (107, 108, 109).
Hint
WHERE customer_id IN (107, 108, 109) is equivalent and reads better for more than two or three values.
Hard 3: Diagnosing a wrong tie-break (40 minutes)
A report lists orders by ORDER BY placed_on DESC only, and the team is confused that two orders placed on the exact same date appear in a different order each time the report runs. Diagnose the cause and fix the query.
Done when: the diagnosis correctly identifies the missing tie-breaker, and the fix adds a second sort column such as order_id.
Hint
Rows with an identical placed_on have no tie-breaker, so their relative order is unstable; adding , order_id DESC fixes it.