CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Write a scalar subquery (15 minutes)

Write a query finding books priced below the average price of all books.

Done when: a correct WHERE price < (SELECT AVG(price) FROM books) query is given.

Hint

SELECT title, price FROM books WHERE price < (SELECT AVG(price) FROM books);

Easy 2: Write a nested subquery with IN (15 minutes)

Write a query finding every customer who has placed at least one order, using IN.

Done when: a correct WHERE customer_id IN (SELECT customer_id FROM orders) query is given.

Hint

SELECT full_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);

Easy 3: Correlated or not (15 minutes)

State whether (SELECT AVG(price) FROM books) and (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) are each correlated.

Done when: the first is correctly marked uncorrelated and the second correlated.

Hint

The AVG subquery needs nothing from the outer query, so it is uncorrelated; the COUNT one references c.customer_id, so it is correlated.

Medium

Medium 1: Rewrite IN as EXISTS (25 minutes)

Rewrite the Easy 2 query to use EXISTS instead of IN, keeping the same result.

Done when: a correct WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) query is given.

Hint

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id).

Medium 2: Customers with no orders, safely (25 minutes)

Write a query finding customers with no orders at all, using NOT EXISTS rather than NOT IN.

Done when: a correct WHERE NOT EXISTS (...) query is given, with a brief note on why NOT IN would be risky here.

Hint

WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); NOT IN risks returning nothing if any customer_id in orders is NULL.

Medium 3: Subquery versus join (25 minutes)

Rewrite the Easy 2 query as an equivalent join instead of a subquery.

Done when: a correct JOIN or DISTINCT based rewrite producing the same customer list is given.

Hint

SELECT DISTINCT c.full_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

Hard

Hard 1: Diagnose a NOT IN bug (30 minutes)

A query using WHERE customer_id NOT IN (SELECT customer_id FROM orders) unexpectedly returns zero rows, even though several customers clearly have no orders. Diagnose the likely cause.

Done when: the diagnosis correctly identifies a NULL value present in orders.customer_id as the likely cause.

Hint

A NULL customer_id anywhere in orders.customer_id makes every NOT IN comparison unknown, so the whole WHERE clause matches nothing.

Hard 2: A correlated subquery in SELECT, not WHERE (35 minutes)

Write a query listing every customer's name alongside their order count, computed with a correlated subquery in the SELECT list rather than with GROUP BY.

Done when: a correct query with a correlated (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) in the SELECT list is given.

Hint

SELECT c.full_name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count FROM customers c;

Hard 3: When a subquery cannot be rewritten as a simple join (40 minutes)

Explain why "find customers whose order count is above the average order count across all customers" is naturally expressed with a subquery, and is awkward to express as a single plain join.

Done when: the explanation correctly notes that the comparison needs an aggregate computed once across all customers, compared against each customer's own aggregate, which a single join level cannot express directly.

Hint

The comparison needs one aggregate computed once (the overall average) held alongside each customer's own aggregate, which requires either a subquery or a window function, not a plain row-level join.