Module 3 Lab: Multi-Table Queries

You’ve learned how databases eliminate redundancy by splitting data across tables, and you’ve learned how to stitch them back together using JOIN operations.

Now it’s time to build a truly complex query that chains multiple tables together.

The Scenario

To generate an itemized invoice for our E-Commerce customers, we need data from four different tables simultaneously.

Look at the schema setup for this lab:

1. users holds the customer’s name.
2. orders holds the date of the purchase. It links to users via user_id.
3. order_items holds the quantity and price of the individual items purchased. It links to orders via order_id.
4. products holds the human-readable name of the product. It is linked to order_items via product_id.

The finance team needs a report that flattens all of this out into a readable format.

Chaining Joins

You can join as many tables as you want in a single query! You simply chain the JOIN clauses one after another.

SELECT ...
FROM table_a
INNER JOIN table_b ON table_a.id = table_b.a_id
INNER JOIN table_c ON table_b.id = table_c.b_id
INNER JOIN table_d ON table_c.id = table_d.c_id;

Your task is to write a query that connects users -> orders -> order_items -> products.

The final output should contain:

1. The customer’s name (aliased as customer).
2. The order date.
3. The product’s name (aliased as product).
4. The total cost of that specific line item (calculated as quantity * unit_price and aliased as line_total).

Take your time. Building multi-table queries is a core skill for any backend developer or data analyst. Once you conquer this, you’ll be ready for Module 4: Subqueries!