· 8 years ago · Nov 22, 2017, 02:42 PM
1DROP TABLE IF EXISTS items_tags;
2DROP TABLE IF EXISTS tags;
3DROP TABLE IF EXISTS items;
4DROP TABLE IF EXISTS users;
5
6
7CREATE TABLE users (
8 id serial PRIMARY KEY,
9 email text NOT NULL,
10 username text NOT NULL
11);
12
13
14CREATE TABLE items (
15 id serial PRIMARY KEY,
16 name text NOT NULL,
17 description text,
18 completed boolean DEFAULT false,
19 created timestamp DEFAULT now(),
20 user_id integer REFERENCES users ON DELETE RESTRICT
21);
22
23
24CREATE TABLE tags (
25 id serial PRIMARY KEY,
26 tag text NOT NULL
27);
28
29
30CREATE TABLE items_tags (
31 item_id integer REFERENCES items(id) ON DELETE CASCADE,
32 tag_id integer REFERENCES tags(id) ON DELETE RESTRICT,
33 PRIMARY KEY (item_id, tag_id)
34);
35
36
37INSERT INTO users
38 (id, username, email)
39VALUES
40 (31, 'Foo', 'foo@example.com'),
41 (42, 'Bar', 'bar@example.com'),
42 (53, 'Qux', 'qux@example.com'),
43 (64, 'Baz', 'baz@example.com');
44
45
46INSERT INTO items
47 (id, name, description, user_id)
48VALUES
49 (1001, 'Avocados', 'avacado mash', 31),
50 (1002, 'Orange juice', 'not just for breakfast', 42),
51 (1003, 'Ice Cream', 'Ben & Jerry''s', 53),
52 (1004, 'Oil Change', 'stop at dealership', 31),
53 (1005, 'Cat Food', null, 42),
54 (1006, 'Sun Glasses', 'for vacation', 53),
55 (1007, 'Dark roast', 'nectar of the gods', 31),
56 (1008, 'Running Shoes', null, 42),
57 (1009, 'Espresso', 'nectar of the gods', 53),
58 (1010, 'Merlot', 'for dinner', null);
59
60
61INSERT INTO tags
62 (id, tag)
63VALUES
64 (171, 'Grocery'),
65 (172, 'Coffee'),
66 (173, 'Pet');
67
68
69INSERT INTO items_tags (item_id, tag_id) VALUES
70(1001, 171),
71(1002, 171),
72(1003, 171),
73(1005, 171), (1005, 173),
74(1006, 171),
75(1007, 171), (1007, 172),
76(1009, 171), (1009, 172);
77
78
79SELECT users.id, email, username, items.id,
80name, description, completed, user_id
81FROM users JOIN items
82ON users.id = items.user_id;
83
84
85SELECT users.id, email, username, items.id,
86name, description, completed, user_id
87FROM users LEFT JOIN items
88ON users.id = items.user_id;
89
90
91SELECT users.id, email, username, items.id,
92name, description, completed, user_id
93FROM users RIGHT JOIN items
94ON users.id = items.user_id;
95
96
97SELECT users.id, email, username, items.id,
98name, completed, user_id, tag
99FROM users
100JOIN items ON users.id = items.user_id
101JOIN items_tags ON items.id = items_tags.item_id
102JOIN tags ON items_tags.tag_id = tags.id;