Schema and sample data
| customer_id | name |
|---|---|
| 1 | Alice Chen |
| 2 | Bob Martin |
| 3 | Clara Diaz |
| 4 | David Owusu |
| product_id | product_name | price |
|---|---|---|
| 101 | Wireless Mouse | 25.00 |
| 102 | Keyboard | 45.00 |
| 103 | Monitor | 220.00 |
| 104 | USB Cable | 8.50 |
| order_id | customer_id | product_id | quantity |
|---|---|---|---|
| 1 | 2 | 101 | 3 |
| 2 | 1 | 103 | 1 |
| 3 | 3 | 102 | 2 |
| 4 | 1 | 104 | 5 |
| 5 | 4 | 101 | 2 |
| 6 | 2 | 103 | 1 |
Show setup SQL (copy to run)
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL
);
INSERT INTO Customers (customer_id, name) VALUES
(1, 'Alice Chen'),
(2, 'Bob Martin'),
(3, 'Clara Diaz'),
(4, 'David Owusu');
INSERT INTO Products (product_id, product_name, price) VALUES
(101, 'Wireless Mouse', 25.00),
(102, 'Keyboard', 45.00),
(103, 'Monitor', 220.00),
(104, 'USB Cable', 8.50);
INSERT INTO Orders (order_id, customer_id, product_id, quantity) VALUES
(1, 2, 101, 3),
(2, 1, 103, 1),
(3, 3, 102, 2),
(4, 1, 104, 5),
(5, 4, 101, 2),
(6, 2, 103, 1);You have three tables: Customers, Orders and Products. For every order, return the customer name, the product_name, the quantity and a computed column line_total equal to quantity * price. Join all three tables so each order is matched to its customer and its product. Sort the result by customer name in ascending order.
Expected result
| name | product_name | quantity | line_total |
|---|---|---|---|
| Alice Chen | Monitor | 1 | 220.00 |
| Alice Chen | USB Cable | 5 | 42.50 |
| Bob Martin | Wireless Mouse | 3 | 75.00 |
| Bob Martin | Monitor | 1 | 220.00 |
| Clara Diaz | Keyboard | 2 | 90.00 |
| David Owusu | Wireless Mouse | 2 | 50.00 |
Show hint
Chain two JOIN clauses off Orders: one to Customers on customer_id and one to Products on product_id. The line_total is just an arithmetic expression quantity * price that you alias in the SELECT list.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT c.name,
p.product_name,
o.quantity,
o.quantity * p.price AS line_total
FROM Orders AS o
JOIN Customers AS c ON o.customer_id = c.customer_id
JOIN Products AS p ON o.product_id = p.product_id
ORDER BY c.name, o.order_id;The query starts from Orders because that is the table with one row per fact we want to report. Each order carries a customer_id and a product_id, which are the foreign keys we use to reach the other two tables. The first JOIN matches every order to exactly one customer, and the second matches it to exactly one product. Because these are inner joins, only orders that have a matching customer and a matching product appear - which here is all six of them. For a deeper look at how join types differ, see the guide to SQL joins.
The line_total column is a plain arithmetic expression, o.quantity * p.price, aliased with AS line_total. SQL Server multiplies the integer quantity by the DECIMAL(10,2) price and returns a decimal, so 5 * 8.50 gives 42.50. You do not need a subquery or an aggregate here: each output row corresponds to a single order, so the value is computed row by row.
A common mistake is to forget the join predicate (the ON clause) or to join on the wrong column, which produces a cross join style explosion of rows. Always join Orders to Customers on customer_id and to Products on product_id. The ORDER BY c.name sorts alphabetically by customer; the secondary o.order_id only breaks ties so that a customer with several orders lists them in a stable, predictable sequence.