Solving tips
- Chain joins one table at a time, always joining the new table on a key already present in the growing result.
- Each ON clause connects exactly two tables; the orders table is the hub linking customers to products.
- Compute derived values like line totals in the SELECT after the joins have paired the rows.
An order links a customer to a product. Produce a readable line item for each order.
Schema
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE products (
id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
price NUMERIC(10,2) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id),
product_id INT REFERENCES products(id),
quantity INT NOT NULL
);
customers
products
| id | product_name | price |
|---|
| 100 | Keyboard | 25.00 |
| 200 | Mouse | 15.00 |
| 300 | Monitor | 150.00 |
orders
| id | customer_id | product_id | quantity |
|---|
| 1 | 1 | 100 | 2 |
| 2 | 1 | 200 | 1 |
| 3 | 2 | 300 | 1 |
| 4 | 3 | 100 | 3 |
Task
Return one row per order with columns customer_name, product_name, quantity, and line_total (equal to quantity * price). Order by customer_name ascending, then product_name ascending.
Expected output
| customer_name | product_name | quantity | line_total |
|---|
| Alice | Keyboard | 2 | 50.00 |
| Alice | Mouse | 1 | 15.00 |
| Bob | Monitor | 1 | 150.00 |
| Carol | Keyboard | 3 | 75.00 |
Approach
Start from orders, the fact table that carries both foreign keys, then join customers on customer_id and products on product_id. Each join connects exactly two tables, and orders is the hub that links the other two:
flowchart LR
customers -->|customer_id| orders
products -->|product_id| orders
With all three rows paired, the line total is the quantity * price expression in the SELECT list.
Query
SELECT c.name AS customer_name,
p.product_name AS product_name,
o.quantity AS quantity,
o.quantity * p.price AS line_total
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.id
INNER JOIN products AS p
ON o.product_id = p.id
ORDER BY c.name, p.product_name;
Walkthrough
- Order 1: customer 1 (Alice), product 100 (Keyboard, 25.00), quantity 2 gives line_total 50.00.
- Order 2: Alice, product 200 (Mouse, 15.00), quantity 1 gives 15.00.
- Order 3: customer 2 (Bob), product 300 (Monitor, 150.00), quantity 1 gives 150.00.
- Order 4: customer 3 (Carol), product 100 (Keyboard, 25.00), quantity 3 gives 75.00.
ORDER BY c.name, p.product_name sorts Alice’s two rows (Keyboard before Mouse), then Bob, then Carol.
Complexity & notes
- Each join resolves through a primary key on the dimension side; indexes on
orders.customer_id and orders.product_id help when the fact table is large.
- The order of the joined tables does not change the result here because all joins are INNER; the optimizer is free to reorder them. It would matter if any join were an outer join.
- Pitfall: if an order could reference a missing customer or product and you still want the order shown, switch that join to LEFT JOIN; with INNER joins any unmatched order silently disappears.