-
Notifications
You must be signed in to change notification settings - Fork 0
/
challenge11-FriendBook.sql
57 lines (47 loc) · 2.11 KB
/
challenge11-FriendBook.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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
/*Challenge: FriendBook
- We've created a database for a friend networking site, with a table storing data on each person,
a table on each person's hobbies, and a table of friend connections between the people.
*/
CREATE TABLE persons (
id INTEGER PRIMARY KEY AUTOINCREMENT,
fullname TEXT,
age INTEGER);
INSERT INTO persons (fullname, age) VALUES ("Bobby McBobbyFace", "12");
INSERT INTO persons (fullname, age) VALUES ("Lucy BoBucie", "25");
INSERT INTO persons (fullname, age) VALUES ("Banana FoFanna", "14");
INSERT INTO persons (fullname, age) VALUES ("Shish Kabob", "20");
INSERT INTO persons (fullname, age) VALUES ("Fluffy Sparkles", "8");
CREATE table hobbies (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person_id INTEGER,
name TEXT);
INSERT INTO hobbies (person_id, name) VALUES (1, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (1, "coding");
INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
INSERT INTO hobbies (person_id, name) VALUES (2, "coding");
INSERT INTO hobbies (person_id, name) VALUES (3, "skating");
INSERT INTO hobbies (person_id, name) VALUES (3, "rowing");
INSERT INTO hobbies (person_id, name) VALUES (3, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (4, "coding");
INSERT INTO hobbies (person_id, name) VALUES (4, "dilly-dallying");
INSERT INTO hobbies (person_id, name) VALUES (4, "meowing");
CREATE table friends (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person1_id INTEGER,
person2_id INTEGER);
INSERT INTO friends (person1_id, person2_id)
VALUES (1, 4);
INSERT INTO friends (person1_id, person2_id)
VALUES (2, 3);
-- Step 1
/*In this first step, use a JOIN to display a table showing people's names with their hobbies*/
SELECT persons.fullname AS FullName, hobbie.name AS Hobbie FROM persons
JOIN hobbies AS hobbie ON persons.id = hobbie.person_id
-- Step 2
/*Now, use another SELECT with a JOIN to show the names of each pair of friends,
based on the data in the friends table.*/
SELECT a.fullname, b.fullname FROM friends
JOIN persons a
ON friends.person1_id = a.id
JOIN persons b
ON friends.person2_id = b.id;