nKh1HffB

1/* Delete the tables if they already exist */
2drop table if exists Highschooler;
3drop table if exists Friend;
4drop table if exists Likes;
5
6/* Create the schema for our tables */
7create table Highschooler(ID int, name text, grade int);
8create table Friend(ID1 int, ID2 int);
9create table Likes(ID1 int, ID2 int);
10
11/* Populate the tables with our data */
12insert into Highschooler values (1510, 'Jordan', 9);
13insert into Highschooler values (1689, 'Gabriel', 9);
14insert into Highschooler values (1381, 'Tiffany', 9);
15insert into Highschooler values (1709, 'Cassandra', 9);
16insert into Highschooler values (1101, 'Haley', 10);
17insert into Highschooler values (1782, 'Andrew', 10);
18insert into Highschooler values (1468, 'Kris', 10);
19insert into Highschooler values (1641, 'Brittany', 10);
20insert into Highschooler values (1247, 'Alexis', 11);
21insert into Highschooler values (1316, 'Austin', 11);
22insert into Highschooler values (1911, 'Gabriel', 11);
23insert into Highschooler values (1501, 'Jessica', 11);
24insert into Highschooler values (1304, 'Jordan', 12);
25insert into Highschooler values (1025, 'John', 12);
26insert into Highschooler values (1934, 'Kyle', 12);
27insert into Highschooler values (1661, 'Logan', 12);
28
29insert into Friend values (1510, 1381);
30insert into Friend values (1510, 1689);
31insert into Friend values (1689, 1709);
32insert into Friend values (1381, 1247);
33insert into Friend values (1709, 1247);
34insert into Friend values (1689, 1782);
35insert into Friend values (1782, 1468);
36insert into Friend values (1782, 1316);
37insert into Friend values (1782, 1304);
38insert into Friend values (1468, 1101);
39insert into Friend values (1468, 1641);
40insert into Friend values (1101, 1641);
41insert into Friend values (1247, 1911);
42insert into Friend values (1247, 1501);
43insert into Friend values (1911, 1501);
44insert into Friend values (1501, 1934);
45insert into Friend values (1316, 1934);
46insert into Friend values (1934, 1304);
47insert into Friend values (1304, 1661);
48insert into Friend values (1661, 1025);
49insert into Friend select ID2, ID1 from Friend;
50
51insert into Likes values(1689, 1709);
52insert into Likes values(1709, 1689);
53insert into Likes values(1782, 1709);
54insert into Likes values(1911, 1247);
55insert into Likes values(1247, 1468);
56insert into Likes values(1641, 1468);
57insert into Likes values(1316, 1304);
58insert into Likes values(1501, 1934);
59insert into Likes values(1934, 1501);
60insert into Likes values(1025, 1101);
61
62.mode column
63.headers ON
64
65
66/*----------- 1. SQL Social-Network Query Exercises  -----------*/
67-- Q1 Find the names of all students who are friends with someone named Gabriel.
68select distinct name from Highschooler where ID in (select ID1 from Friend where ID2 in (select ID from Highschooler where name="Gabriel"));
69
70-- Q2 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. 
71select distinct sName, sGrade, lName, lGrade
72from (select h1.name as sName, h1.grade sGrade, h2.name as lName, h2.grade as lGrade, h1.grade-h2.grade as gradeDiff 
73     from Highschooler h1, Likes, Highschooler h2
74     where h1.ID=ID1 and h2.ID=ID2)
75where gradeDiff>1;
76
77-- Q3 For every pair of students who both like each other, return the name and grade of both students. Include each pair only once, with the two names in alphabetical order.
78select h1.name, h1.grade, h2.name, h2.grade  from Likes l1, Likes l2, Highschooler h1, Highschooler h2
79where l1.ID1=l2.ID2 and l2.ID1=l1.ID2 and l1.ID1=h1.ID and l1.ID2=h2.ID and h1.name<h2.name;
80
81-- Q4 Find all students who do not appear in the Likes table (as a student who likes or is liked) and return their names and grades. Sort by grade, then by name within each grade.
82select name,grade from Highschooler where ID not in (select ID1 from Likes union select ID2 from Likes) order by grade, name;
83
84-- Q5 For every situation where student A likes student 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. 
85select distinct H1.name, H1.grade, H2.name, H2.grade
86from Highschooler H1, Likes, Highschooler H2
87where H1.ID = Likes.ID1 and Likes.ID2 = H2.ID and H2.ID not in (select ID1 from Likes);
88
89-- Q6 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. 
90select name, grade from Highschooler
91where ID not in (
92  select ID1 from Highschooler H1, Friend, Highschooler H2
93  where H1.ID = Friend.ID1 and Friend.ID2 = H2.ID and H1.grade <> H2.grade)
94order by grade, name;
95
96-- Q7 For each student A who likes a student B where the two are not friends, find if they have a friend C in common (who can introduce them!). For all such trios, return the name and grade of A, B, and C. 
97select distinct H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade
98from Highschooler H1, Likes, Highschooler H2, Highschooler H3, Friend F1, Friend F2
99where H1.ID = Likes.ID1 and Likes.ID2 = H2.ID and
100  H2.ID not in (select ID2 from Friend where ID1 = H1.ID) and
101  H1.ID = F1.ID1 and F1.ID2 = H3.ID and
102  H3.ID = F2.ID1 and F2.ID2 = H2.ID;
103
104-- Q8 Find the difference between the number of students in the school and the number of different first names. 
105select st.sNum-nm.nNum from 
106(select count(*) as sNum from Highschooler) as st,
107(select count(distinct name) as nNum from Highschooler) as nm;
108
109-- Q9 Find the name and grade of all students who are liked by more than one other student. 
110select name, grade 
111from (select ID2, count(ID2) as numLiked from Likes group by ID2), Highschooler
112where numLiked>1 and ID2=ID;
113
114
115/*----------- Social Network Modification exercises -----------*/
116
117-- Q1 It's time for the seniors to graduate. Remove all 12th graders from Highschooler.
118delete from Highschooler
119where grade = 12;
120
121-- Q2 If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.
122delete from Likes
123where ID2 in (select ID2 from Friend where Likes.ID1 = ID1) and
124      ID2 not in (select L.ID1 from Likes L where Likes.ID1 = L.ID2);