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

id	name	city
1	Alice	NYC
2	Bob	LA
3	Carol	NYC

id	name	price
1	Laptop	999
2	Mouse	29
3	Keyboard	79

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|1998

Sample 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);"

← All challenges

·Auto-save on·Ctrl+Enter to run

Loading editor…