Exercises

Objectives

By the end of this lesson, you should be able to:

• Choose the right join type for a given question
• Build multi-table reports combining joins with GROUP BY and aggregates
• Query a self-referencing hierarchy

⚠️ A note on verification: every query and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

CREATE TABLE customers (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), order_date DATE NOT NULL, amount NUMERIC(10,2) NOT NULL);
INSERT INTO customers (name) VALUES ('Riley Nguyen'), ('Casey Brooks'), ('Morgan Alvarez');
INSERT INTO orders (customer_id, order_date, amount) VALUES
  (1, '2024-01-10', 120.00), (1, '2024-02-15', 45.00), (2, '2024-01-20', 75.00);

CREATE TABLE categories (id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE products (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, category_id INTEGER REFERENCES categories(id));
INSERT INTO categories (name) VALUES ('Electronics'), ('Home'), ('Office'), ('Outdoors');
INSERT INTO products (name, category_id) VALUES
  ('Wireless Mouse', 1), ('Desk Lamp', 2), ('Notebook', 3), ('Mystery Item', NULL);

CREATE TABLE order_items (id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES orders(id), product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2), (1, 2, 1), (2, 3, 3), (3, 4, 1);

CREATE TABLE staff (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INTEGER REFERENCES staff(id));
INSERT INTO staff (name, manager_id) VALUES
  ('Taylor Nakamura', NULL), ('Priya Desai', 1), ('Erin Castillo', 1), ('Jordan Blake', 2);

Order 1 and order 2 belong to Riley Nguyen, order 3 belongs to Casey Brooks (see the previous lesson for the full breakdown).

Exercise 1: Customers and Orders

a) Write an INNER JOIN showing every order’s amount alongside the customer’s name. Expected result: 3 rows, Morgan Alvarez doesn’t appear (no orders).

b) Write a LEFT JOIN with WHERE ... IS NULL that isolates customers with zero orders. Expected result: Morgan Alvarez only.

c) Write a query showing every customer’s total amount spent, including customers with no orders (use LEFT JOIN and COALESCE to show 0 instead of NULL). Expected result: Riley Nguyen 165.00, Casey Brooks 75.00, Morgan Alvarez 0.

Exercise 2: Products and Categories

a) Write a FULL JOIN between categories and products. Expected result: 5 rows, including Outdoors with a NULL product and a NULL category paired with Mystery Item.

b) Write a query isolating categories with no products, using LEFT JOIN and IS NULL. Expected result: Outdoors only.

c) Write a query isolating products with no category assigned. Expected result: Mystery Item only.

Exercise 3: Staff Hierarchy (Self Join)

a) Write a self join listing every employee’s name alongside their manager’s name, including employees with no manager. Expected result: 4 rows, Taylor Nakamura’s manager is NULL.

b) Write a query listing only the employees who report directly to Taylor Nakamura. Expected result: Priya Desai and Erin Castillo.

c) Write a query counting how many direct reports each manager has. Expected result: Taylor Nakamura 2, Priya Desai 1 (Jordan Blake and Erin Castillo don’t appear as managers, since nobody reports to them).

Exercise 4: Multi-Table Report

a) Write a four-table join (customers, orders, order_items, products) listing every customer, the product they ordered, and the quantity. Expected result includes Casey Brooks with Mystery Item (quantity 1), and Riley Nguyen with all three other products.

b) Extend the join to include categories as a fifth table, grouped by customer and category, summing quantity. Expected result: only Riley Nguyen appears, across Electronics (2), Home (1), and Office (3), explain in your own words why Casey Brooks’s order disappears from this version of the report even though it appeared in (a).

Recap

This module covered every core join type: INNER JOIN for matched rows only, LEFT/RIGHT JOIN for keeping one side’s unmatched rows, FULL JOIN for keeping both sides’ unmatched rows, CROSS JOIN for every combination, SELF JOIN for hierarchies within one table, and chaining multiple joins together for reports spanning several tables. Together with WHERE, GROUP BY, and aggregate functions, these are the core toolkit for querying real, related data.

Next module: subqueries, CTEs, and conditional logic, for questions a single flat join can’t answer on its own.