CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Write an inner join (15 minutes)

Write a query joining orders and order_items on order_id, returning only matching rows.

Done when: a correct JOIN ... ON query is given.

Hint

SELECT * FROM orders JOIN order_items ON orders.order_id = order_items.order_id;

Easy 2: Keep the unmatched side (15 minutes)

Rewrite the previous query as a left join from orders, so an order with no items still appears once.

Done when: a correct LEFT JOIN is given, with orders as the left table.

Hint

SELECT * FROM orders LEFT JOIN order_items ON orders.order_id = order_items.order_id;

Easy 3: Spot the cross join (15 minutes)

A query joins customers and books with no ON clause at all. Name the join type and what it produces.

Done when: cross join is correctly named, with the pairing behavior described.

Hint

A cross join pairs every row of customers with every row of books, with no filtering at all.

Medium

Medium 1: Find customers with no orders (25 minutes)

Using a left join and a WHERE clause checking for NULL, write a query returning only customers who have never placed an order.

Done when: a correct LEFT JOIN ... WHERE o.order_id IS NULL query is given.

Hint

SELECT c.* FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;

Medium 2: Left join versus inner join on row count (25 minutes)

Given 400 customers where 50 have never ordered, state how many rows an inner join between customers and orders returns compared to a left join, assuming each of the other 350 has exactly one order.

Done when: the inner join count (350) and left join count (400) are both correctly computed.

Hint

Inner join returns 350, since the 50 customers with no orders are dropped; left join returns 400, since every customer stays with NULLs filled in for those 50.

Medium 3: Choose the right join type (25 minutes)

A report needs every book in the catalog listed, including books that have never been ordered, alongside their total times ordered (zero if never). Name the join type needed between books and order_items.

Done when: left join (from books) is correctly identified, with a reason tied to keeping unordered books in the result.

Hint

A left join from books keeps every book, even ones with zero matching order_items rows.

Hard

Hard 1: Diagnose an accidental row explosion (30 minutes)

A report joining orders, order_items and books returns far more rows than expected. Describe the most likely cause and how to find it.

Done when: a missing or incorrect join condition, producing an unintended cross-join-like effect, is correctly diagnosed as the likely cause.

Hint

A missing join condition between one pair of tables, or a condition on the wrong columns, effectively turns part of the query into a cross join, multiplying row counts far beyond expectation.

Hard 2: Full join for a reconciliation report (35 minutes)

Two systems each track customer records independently, and need reconciling. Explain why a full join between the two systems' customer tables, on a shared identifier, is the right tool for finding every mismatch in both directions.

Done when: the explanation correctly notes that a full join surfaces rows present in only one system, in either direction, which an inner join would hide entirely.

Hint

An inner join would silently drop every customer present in only one system; a full join keeps them, with NULL marking exactly which side is missing each one.

Hard 3: Rewrite a right join as a left join (40 minutes)

Rewrite SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id; as an equivalent left join, swapping which table is written first.

Done when: a correct FROM customers LEFT JOIN orders ON ... equivalent is given.

Hint

SELECT * FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id; keeps the same meaning, with the "every row kept" side now written first.