SQL interview question: Join Four Tables
Complete Order Detail Report
Finance needs a complete order breakdown showing customer, product, and pricing details all in one report.
Join all four tables to create a detailed order report.
Return customer_name, order_date, product_name, quantity, unit_price, and line_total (quantity × unit_price). Order by order_date DESC, customer_name. Limit to 15.
Skills: multi-table JOINs (4 tables)
Tables
orders 15 rows
- order_id integer PK
- customer_id integer → customers
- order_date text
- total_amount real
- status text
| order_id | customer_id | order_date | total_amount | status |
|---|---|---|---|---|
| 1 | 101 | 2024-01-20 | 249.99 | delivered |
| 2 | 101 | 2024-03-15 | 1999.99 | delivered |
| 4 | 101 | 2024-09-05 | 79.99 | delivered |
order_items 19 rows
- id integer PK
- order_id integer → orders
- product_id integer → products
- quantity integer
| id | order_id | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 3 | 1 |
| 2 | 2 | 1 | 1 |
| 3 | 2 | 6 | 1 |
products 20 rows
- product_id integer PK
- product_name text
- category text
- price real
- rating real
| product_id | product_name | category | price | rating |
|---|---|---|---|---|
| 1 | MacBook Pro | Electronics | 1999.99 | 4.8 |
| 2 | iPad Air | Electronics | 599.99 | 4.8 |
| 3 | AirPods Pro | Electronics | 249.99 | 4.7 |
customers 12 rows
- customer_id integer PK
- name text
- email text
- city text
- signup_date text
| customer_id | name | city | signup_date | |
|---|---|---|---|---|
| 101 | Alice Mitchell | [email protected] | San Francisco | 2024-01-15 |
| 102 | Bob Chen | [email protected] | New York | 2024-02-20 |
| 103 | Carol Davis | [email protected] | Los Angeles | 2024-03-10 |