SQL for the OA
Six original exercises with a runnable database, expected rows, and explained joins, grouping, NULL, and subqueries.
Research checked: 13 November 2026. Recent internship candidate reports emphasize joins and selecting the correct tables. This original practice set adds grouping and subqueries for coverage; it is not an official syllabus.
Read the schema before writing the query
Identify the output's grain: one row per customer, order, item, or category? Then follow primary/foreign keys. Here, a customer has many orders; an order has many items; a product appears in many items. Joining an order to its items duplicates the order's columns once per item. Summing orders.amount after that join would overcount orders with multiple items.
INNER JOIN returns matching rows. LEFT JOIN preserves every left row, filling unmatched right columns with NULL. A right-table condition in WHERE can remove those unmatched rows; put it in ON when you must preserve the left rows. Test NULL with IS NULL, never = NULL.
Think in this order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation. COUNT(*) counts rows, while COUNT(column) ignores NULL values. After a LEFT JOIN, count a right-table key to count matches. Select or group every non-aggregated output column for portable queries.
Shared practice database
Copy this into a fresh SQLite database or a SQL playground that supports SQLite. The exercises use portable SELECT syntax; confirm the real platform's SQL dialect. Amounts are whole currency units here to keep the examples small.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
amount INTEGER NOT NULL,
status TEXT NOT NULL
);
CREATE TABLE products (
id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id),
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL,
unit_price INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES
(101, 1, 100, 'paid'), (102, 1, 50, 'paid'),
(103, 2, 80, 'paid'), (104, 2, 20, 'pending');
INSERT INTO products VALUES
(10, 'Pen set', 'Tools'), (11, 'Notebook', 'Books'),
(12, 'Lamp', 'Tools'), (13, 'Bag', 'Travel');
INSERT INTO order_items VALUES
(101, 10, 2, 25), (101, 12, 1, 50),
(102, 12, 1, 50), (103, 11, 2, 40), (104, 10, 1, 20);| Relationship | Join condition |
|---|---|
| Customer → orders | c.id = o.customer_id |
| Order → items | o.id = i.order_id |
| Product → items | p.id = i.product_id |
There is deliberately a customer with no orders, a product never ordered, a pending order, and a paid order with multiple items. These expose common query mistakes. All expected outputs below use exactly this data. Run one setup per fresh database; do not reinsert the rows before every exercise.
S1 — Paid orders with customer names
Return order ID, customer name, and amount for paid orders of at least 80. Sort by order ID. Expected (order_id, name, amount): (101, 'Asha', 100), (103, 'Ben', 80).
Solution and explanation
SELECT o.id AS order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid' AND o.amount >= 80
ORDER BY o.id;This output has one row per order. Both conditions apply to rows, so they belong in WHERE. Join on IDs, not names or amounts.
- Solve S1 and identify the output grain and join keys.
S2 — Count paid orders for every customer
Return customer ID, name, and paid_orders, including zero. Sort by customer ID. Expected: (1, 'Asha', 2), (2, 'Ben', 1), (3, 'Chen', 0).
Solution and explanation
SELECT c.id AS customer_id, c.name, COUNT(o.id) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;Filtering paid orders inside ON preserves Chen. COUNT(o.id) sees NULL and returns zero for Chen; COUNT(*) would incorrectly count the preserved row as one.
- Solve S2 and explain the difference between COUNT(*) and COUNT(o.id).
S3 — Product-level paid revenue
Using orders, order_items, and products, return product name and revenue from paid orders only. Sort revenue descending, then product name ascending. Expected: ('Lamp', 100), ('Notebook', 80), ('Pen set', 50).
Solution and explanation
SELECT p.name, SUM(i.quantity * i.unit_price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.id, p.name
ORDER BY revenue DESC, p.name ASC;Use item quantity and price; the full order amount is not attributable to every product. Group by product ID as well as name so distinct products sharing a name are not accidentally merged.
- Solve S3 without double-counting a multi-item order.
S4 — Customers with substantial paid spending
Return customer ID, name, and paid_total for customers whose paid total is at least 100. Sort by customer ID. Expected: (1, 'Asha', 150).
Solution and explanation
SELECT c.id AS customer_id, c.name, SUM(o.amount) AS paid_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
HAVING SUM(o.amount) >= 100
ORDER BY c.id;Filter status before aggregation, then filter totals with HAVING. A condition such as WHERE amount >= 100 would discard Asha's 50-unit order before computing her total.
- Solve S4 and distinguish WHERE from HAVING.
S5 — Customers with no orders
Return customer ID and name for customers with no orders of any status. Sort by ID. Expected: (3, 'Chen').
Solution and explanation
SELECT c.id AS customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;The right primary key cannot be NULL for a real match. Here NULL therefore identifies a missing order. If the task instead said no paid orders, that status condition would need to be in ON.
- Solve S5 and explain why = NULL does not work.
S6 — Categories priced above the paid-order average
Return distinct product categories appearing in a paid order whose order amount is strictly greater than the average paid-order amount. Sort categories alphabetically. Paid-order average is (100 + 50 + 80) / 3 = 76.666…. Expected: ('Books'), ('Tools').
Solution and explanation
SELECT DISTINCT p.category
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.status = 'paid'
AND o.amount > (SELECT AVG(amount) FROM orders WHERE status = 'paid')
ORDER BY p.category;The scalar subquery computes the average over orders before item duplication. DISTINCT is appropriate because the requested output is unique categories, not an item list. Without any paid orders, the average is NULL and no rows qualify.
- Solve S6 with the average computed at the correct grain.
Before submitting a query
Check output aliases and column order, the intended join path, whether missing rows must survive, and whether an item join multiplies totals. Add the requested ORDER BY; table rows have no implicit display order. Test zero matches, duplicate names, NULLs, and one-to-many joins. SQL performance depends on indexes and the database's plan, so do not assume all joins have one universal complexity.
Targeted resources
- SQLBolt: lessons 6–8 for joins and NULL; lessons 10–12 for aggregation and execution order. Use the interactive exercises, then return to S1–S6.
- LeetCode SQL 50: choose Replace Employee ID With The Unique Identifier, Customer Who Visited but Did Not Make Any Transactions, and Average Time of Process per Machine for extra join/grouping practice. Some platform features require sign-in.
- SQLite SELECT reference: check join/filter/group semantics if a query surprises you. Adapt dialect-specific functions to your actual platform.
Next: reserve the two unseen SQL tasks in the mixed mocks for the timed rehearsal.