Ops needs a full order breakdown showing who ordered what. Using customers, orders, order_items, and products, return customer_name, order_id, product_name, quantity, and unit_price for every order line item, ordered by order_id then product_name.
customers
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| TEXT |
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| created_at | DATE |
order_items
| column | type |
|---|---|
| id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | NUMERIC |
products
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| category | TEXT |
| price | NUMERIC |
customers
| id | name | |
|---|---|---|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
orders
| id | customer_id | created_at |
|---|---|---|
| 1 | 1 | 2024-01-10 |
| 2 | 2 | 2024-02-01 |
products
| id | name | category | price |
|---|---|---|---|
| 1 | Widget | Tools | 9.99 |
| 2 | Gadget | Electronics | 149.99 |
| 3 | Gizmo | Electronics | 79.99 |
order_items
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 2 | 2 | 149.99 |
| 2 | 1 | 1 | 3 | 9.99 |
| 3 | 2 | 3 | 1 | 79.99 |
| customer_name | order_id | product_name | quantity | unit_price |
|---|---|---|---|---|
| Alice | 1 | Gadget | 2 | 149.99 |
| Alice | 1 | Widget | 3 | 9.99 |
| Bob | 2 | Gizmo | 1 | 79.99 |
Alice's order (id 1) has two line items — Gadget and Widget — sorted alphabetically by product name. Bob's order (id 2) has one item. The customer with no order (if any) would be excluded by the INNER JOINs.