MediumSQL
Order Line Items with Customer and Product
SQLDatabases
Write a SQL query that returns each order's customer name, **product
name, quantity, and total cost** (quantity × price), by joining three
tables. Order by customer name, then product name.
Tables
| customers | id | name | city |
|---|---|---|---|
| 1 | Alice | NYC | |
| 2 | Bob | LA | |
| 3 | Carol | NYC |
| products | id | name | price |
|---|---|---|---|
| 1 | Laptop | 999 | |
| 2 | Mouse | 29 | |
| 3 | Keyboard | 79 |
| orders | id | customer_id | product_id | quantity |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | |
| 2 | 1 | 2 | 2 | |
| 3 | 2 | 3 | 1 | |
| 4 | 3 | 1 | 2 |
Expected output (columns: customer, product, qty, total)
Alice|Laptop|1|999
Alice|Mouse|2|58
Bob|Keyboard|1|79
Carol|Laptop|2|1998Sample tests
Test #14 orders across 3 customers and 3 products
Input: "CREATE TABLE customers (id INTEGER, name TEXT, city TEXT);\nCREATE TABLE products (id INTEGER, name TEXT, price INTEGER);\nCREATE TABLE orders (id INTEGER, customer_id INTEGER, product_id INTEGER, quantity INTEGER);\nINSERT INTO customers VALUES (1, 'Alice', 'NYC'), (2, 'Bob', 'LA'), (3, 'Carol', 'NYC');\nINSERT INTO products VALUES (1, 'Laptop', 999), (2, 'Mouse', 29), (3, 'Keyboard', 79);\nINSERT INTO orders VALUES (1, 1, 1, 1), (2, 1, 2, 2), (3, 2, 3, 1), (4, 3, 1, 2);"
Output: "Alice|Laptop|1|999\nAlice|Mouse|2|58\nBob|Keyboard|1|79\nCarol|Laptop|2|1998"