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:
usersholds the customer’s name.ordersholds the date of the purchase. It links tousersviauser_id.order_itemsholds the quantity and price of the individual items purchased. It links toordersviaorder_id.productsholds the human-readable name of the product. It is linked toorder_itemsviaproduct_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:
- The customer’s name (aliased as
customer). - The order date.
- The product’s name (aliased as
product). - The total cost of that specific line item (calculated as
quantity * unit_priceand aliased asline_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!