-
Notifications
You must be signed in to change notification settings - Fork 0
/
challenge9-Customer'sOrders.sql
43 lines (35 loc) · 1.79 KB
/
challenge9-Customer'sOrders.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/*Challenge: Customer's orders*/
/*We've created a database for customers and their orders. Not all of the customers have made orders.*/
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT);
INSERT INTO customers (name, email) VALUES ("Doctor Who", "[email protected]");
INSERT INTO customers (name, email) VALUES ("Harry Potter", "[email protected]");
INSERT INTO customers (name, email) VALUES ("Captain Awesome", "[email protected]");
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER,
item TEXT,
price REAL);
INSERT INTO orders (customer_id, item, price)
VALUES (1, "Sonic Screwdriver", 1000.00);
INSERT INTO orders (customer_id, item, price)
VALUES (2, "High Quality Broomstick", 40.00);
INSERT INTO orders (customer_id, item, price)
VALUES (1, "TARDIS", 1000000.00);
/* Step 1
- Come up with a query that lists the name and email of every customer followed by the item and price of orders they've made.
Use a LEFT OUTER JOIN so that a customer is listed even if they've made no orders, and don't add any ORDER BY
*/
SELECT customers.name, customers.email, orders.item, orders.price FROM customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id
/*
- Now, create another query that will result in one row per each customer, with their name, email, and total amount of money they've spent on orders.
- Sort the rows according to the total money spent, from the most spent to the least spent.
(Tip: You should always GROUP BY on the column that is most likely to be unique in a row.)*/
SELECT customers.name, customers.email, SUM(orders.price) AS total_amount_money
FROM customers LEFT OUTER JOIN orders
ON customers.id = orders.customer_id
GROUP BY name
ORDER BY total_amount_money DESC;