-
Notifications
You must be signed in to change notification settings - Fork 30
/
outer_joins.sql
35 lines (26 loc) · 1.13 KB
/
outer_joins.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
CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT);
INSERT INTO customers (name, email) VALUES ("Doctor Who", "doctorwho@timelords.com");
INSERT INTO customers (name, email) VALUES ("Harry Potter", "harry@potter.com");
INSERT INTO customers (name, email) VALUES ("Captain Awesome", "captain@awesome.com");
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);
select customers.name, customers.email, orders.item, orders.price from customers
left outer join orders
on customers.id = orders.customer_id;
select customers.name, customers.email, orders.price as total from customers
left outer join orders
/* on customers.id = orders.customer_id */
group by customers.id
ORDER BY COUNT(customers.id) DESC;