-
Notifications
You must be signed in to change notification settings - Fork 22
/
Copy pathsql_social_network_hw.txt
210 lines (149 loc) · 11.3 KB
/
sql_social_network_hw.txt
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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
1) Find the names of all students who are friends with someone named Gabriel.
select distinct H2.name from Highschooler H1, Friend F1, Highschooler H2 where H1.ID = F1.ID1 and H1.name = 'Gabriel' and H2.ID = F1.ID2;
===============================================================================
2) For every student who likes someone 2 or more grades younger than themselves, return that student's name and grade, and the name and grade of the student they like.
select H1.name, H1.grade, H2.name, H2.grade from Highschooler H1, Likes L1, Highschooler H2 where H1.ID = L1.ID1 and H2.ID = L1.ID2 and (H1.grade-H2.grade)>=2;
===============================================================================
3) Should totally be dating: For every pair of people who both like each other, return the name and grade of both people. Include each pair only once.
select H1.name, H1.grade, H2.name, H2.grade from Highschooler H1, Highschooler H2, Likes L1, Likes L2 where H1.ID = L1.ID1 and H2.ID = L1.ID2 and L1.ID1 = L2.ID2 and L1.ID2 = L2.ID1 and L1.ID1 < L1.ID2;
+---------+-------+-----------+-------+
| name | grade | name | grade |
+---------+-------+-----------+-------+
| Gabriel | 9 | Cassandra | 9 |
| Jessica | 11 | Kyle | 12 |
+---------+-------+-----------+-------+
2 rows in set (0.00 sec)
===============================================================================
4) Alas: For every situation where person A likes person B, but person B likes a different person C, return the names and grades of A, B, and C.
select H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade from Highschooler H1, Highschooler H2, Highschooler H3, Likes L1, Likes L2 where H1.ID = L1.ID1 and H2.ID = L1.ID2 and H3.ID = L2.ID2 and L1.ID2 = L2.ID1 and L1.ID1 <> L2.ID2;
+---------+-------+-----------+-------+---------+-------+
| name | grade | name | grade | name | grade |
+---------+-------+-----------+-------+---------+-------+
| Andrew | 10 | Cassandra | 9 | Gabriel | 9 |
| Gabriel | 11 | Alexis | 11 | Kris | 10 |
+---------+-------+-----------+-------+---------+-------+
2 rows in set (0.00 sec)
===============================================================================
5. Need more intel: Find all students who do not appear in the Likes table (as a person who likes or is liked) and return their names and grades. Sort by grade, then by name within each grade. (Hint: Without subqueries, you will need to use the EXCEPT operator.)
select name, grade from Highschooler H1 where H1.ID not in (select ID1 from Likes union select ID2 from Likes) order by grade, name;
===============================================================================
6. Have a chance: For every situation where person A likes person B, but we have no information about whom B likes (that is, B does not appear as an ID1 in the Likes table), return A and B's names and grades.
select H1.name, H1.grade, H2.name, H2.grade from Highschooler H1, Highschooler H2, Likes L1 where H1.ID = L1.ID1 and H2.ID = L1.ID2 and L1.ID2 not in (select ID1 from Likes);
+----------+-------+--------+-------+
| name | grade | name | grade |
+----------+-------+--------+-------+
| John | 12 | Haley | 10 |
| Brittany | 10 | Kris | 10 |
| Alexis | 11 | Kris | 10 |
| Austin | 11 | Jordan | 12 |
+----------+-------+--------+-------+
4 rows in set (0.00 sec)
===============================================================================
7. Narrow-minded: Find names and grades of students who only have friends in the same grade. Return the result sorted by grade, then by name within each grade.
select distinct H1.name from Highschooler H1, Friend F1 where H1.ID = F1.ID1 and H1.grade = all(select H2.grade from Highschooler H2, Friend F2 where F2.ID1 = F1.ID1 and H2.ID = F2.ID2);
+----------+
| name |
+----------+
| Jordan |
| Kris |
| Haley |
| Gabriel |
| Logan |
| Brittany |
| John |
+----------+
7 rows in set (0.00 sec)
Alternate solution:
select H1.name, H1.grade from Highschooler H1 where H1.ID not in (select H2.ID from Highschooler H2, Friend F1, Highschooler H3 where H2.ID = F1.ID1 and H3.ID = F1.ID2 and H2.grade <> H3.grade) order by grade, name;
===============================================================================
8. Broad-minded: Find names and grades of students who only have friends in different grades.
select distinct H1.name from Highschooler H1, Friend F1 where H1.ID = F1.ID1 and H1.grade <> all(select H2.grade from Highschooler H2, Friend F2 where F2.ID1 = F1.ID1 and H2.ID = F2.ID2);
+--------+
| name |
+--------+
| Austin |
+--------+
===============================================================================
9. Could you introduce me? For each person A who likes a person B but the two are not friends, find if they have a friend C in common. For all such trios, return the name and grade of A, B, and C.
select H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade from Highschooler H1, Highschooler H2, Highschooler H3, Likes L1, Friend F1, Friend F2 where H1.ID = L1.ID1 and H2.ID = L1.ID2 and (H1.ID, H2.ID) not in (select ID1, ID2 from Friend) and (F1.ID1 = H3.ID and F1.ID2 = H1.ID and F2.ID1 = H3.ID and F2.ID2 = H2.ID);
+--------+-------+-----------+-------+---------+-------+
| name | grade | name | grade | name | grade |
+--------+-------+-----------+-------+---------+-------+
| Andrew | 10 | Cassandra | 9 | Gabriel | 9 |
| Austin | 11 | Jordan | 12 | Andrew | 10 |
| Austin | 11 | Jordan | 12 | Kyle | 12 |
+--------+-------+-----------+-------+---------+-------+
Sqlite doesn't allow searching for vectors, hence:
select H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade from Highschooler H1, Highschooler H2, Highschooler H3, Likes L1, Friend F1, Friend F2 where H1.ID = L1.ID1 and H2.ID = L1.ID2 and F1.ID1 = H3.ID and F1.ID2 = H1.ID and F2.ID1 = H3.ID and F2.ID2 = H2.ID and H1.ID not in (select ID1 from Friend where ID2 = H2.ID);
===============================================================================
10. Duplication: Find the difference between the number of students in the school and the number of different first names
select count(ID) - count(distinct name) from Highschooler;
+----------------------------------+
| count(ID) - count(distinct name) |
+----------------------------------+
| 2 |
+----------------------------------+
===============================================================================
11. Average affability: What is the average number of friends per person?
select avg(friends) from (select ID1,count(ID2) as friends from Friend group by ID1) G;
+--------------+
| avg(friends) |
+--------------+
| 2.5000 |
+--------------+
===============================================================================
12. Social circle: Return the number of students who are either friends with Cassandra or are friends of friends of Cassandra. Do not count Cassandra, even though technically she is a friend of a friend.
create table t1 as (select ID2 from Friend where ID1 = (select ID from Highschooler where name = 'Cassandra'));
create table t2 as select distinct ID2 from Friend where ID1 in (select * from t1)and ID2 <> (select ID from Highschooler where name = 'Cassandra');
select count(ID2) from ((select * from t1) union (select * from t2)) G;
+------------+
| count(ID2) |
+------------+
| 7 |
+------------+
===============================================================================
13. It must be their brains, not their looks: Return the name and grade of all students who are liked by more than one other student.
select name, grade from Highschooler where ID in (select ID2 from (select count(ID1) as liked,ID2 from Likes group by ID2) G where G.liked >= 2);
+-----------+-------+
| name | grade |
+-----------+-------+
| Cassandra | 9 |
| Kris | 10 |
+-----------+-------+
===============================================================================
14. Most gregarious: Return the name and grade of the student(s) with the greatest number of friends.
create table t1 as select ID1, count(ID2) as nof from Friend group by ID1;
select name, grade from Highschooler where ID in (select ID1 from t1 where t1.nof = (select max(nof) from t1));
+--------+-------+
| name | grade |
+--------+-------+
| Andrew | 10 |
| Alexis | 11 |
+--------+-------+
===============================================================================
15. End of the school year: Increment the year of all students by 1, such that all 9th graders become 10th graders, all 10th graders become 11th graders, and so on. To check your modification, return the names of all students who are in a grade higher than 12 (we'll deal with them later).
update Highschooler set grade = grade + 1;
===============================================================================
16. Graduating seniors: After the previous update, some students ended up in 13th grade. They're actually graduating, so remove all tuples in Highschooler with a grade higher than 12. To check your modification, return the total number of students remaining in the Highschooler table.
delete from Highschooler where grade > 12;
===============================================================================
17. Now clean up this mess: While friendships may continue after graduation, we can't model them for students no longer in our database. Remove all tuples in Friend and all tuples in Likes that refer to nonexistent students. (This will require two separate commands.) To check your modification, return the names of all students whom Austin likes or who are friends with Austin.
delete from Friend where ID1 not in (select ID from Highschooler) and ID2 not in (select ID from Highschooler);
delete from Likes where ID1 not in (select ID from Highschooler) and ID2 not in (select ID from Highschooler);
select H2.name from Highschooler H1, Friend, Highschooler H2 where H1.name = 'Austin' and H1.ID = Friend.ID1 and H2.ID = Friend.ID2;
===============================================================================
18. Summertime: Now that they're on vacation, our students have more time to spend with one another. As a result, their social circles have expanded: For all cases where A is friends with B, and B is friends with C, add a new friendship for the pair A and C. (Do not add duplicate friendships, friendships that already exist, or friendships with oneself.) To check your modification, for the now 10th-grader Jordan, return the names of all her friends.
insert into Friend select F1.ID1, F2.ID2 from Friend F1, Friend F2 where F1.ID2 = F2.ID1 and F1.ID1 <> F2.ID2 and (F1.ID1, F2.ID2) not in (select * from Friend);
select H2.name from Highschooler H1, Friend, Highschooler H2 where H1.name = 'Jordan' and H1.ID = Friend.ID1 and H2.ID = Friend.ID2;+-----------+
| name |
+-----------+
| Tiffany |
| Gabriel |
| Alexis |
| Cassandra |
| Andrew |
+-----------+
===============================================================================
19. If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.
delete from Likes where ID1 in (select F1.ID1 from Friend F1, Likes L1 where F1.ID1 = L1.ID1 and F1.ID2 = L1.ID2 and F1.ID2 not in (select ID1 from Likes where ID2 = F1.ID1));
===============================================================================