The entirety of these exercises will be in SQLite.
SQL Social-Network Query Exercises (in SQLite)
Q1
Find the names of all students who are friends with someone named Gabriel.
A1
select name
from Highschooler
where ID in (select ID1 from Friend
where ID2 in (select ID from Highschooler where name = 'Gabriel'))
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.
A2
select h1.name, h1.grade, h2.name, h2.grade
from Highschooler as h1, Highschooler as h2, Likes
where h1.ID = Likes.ID1 and h2.ID = Likes.ID2 and h1.grade = h2.grade+2
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.
A3
select h1.name, h1.grade, h2.name, h2.grade
from Highschooler as h1, Highschooler as h2, Highschooler as h3, Highschooler as h4, Likes as l1, Likes as l2
where h1.ID = l1.ID1 and h2.ID = l1.ID2 and h3.ID = h2.ID and h4.ID = h1.ID
and h2.ID = l2.ID1 and h1.ID = l2.ID2 and h1.name < h2.name
Better solution:
select h1.name, h1.grade, h2.name, h2.grade
from Highschooler as h1, Highschooler as h2, Likes as l1, Likes as l2
where h1.ID = l1.ID1 and h2.ID = l1.ID2 and h2.ID = l2.ID1
and h1.ID = l2.ID2 and h1.name < h2.name
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.
A4
select name, grade
from Highschooler
where ID not in (select ID1 from Likes) and ID not in (select ID2 from Likes)
order by grade,name
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.
A5
select h1.name, h1.grade, h2.name, h2.grade
from Highschooler as h1, Highschooler as h2, Likes as l1
where h1.ID = l1.ID1 and h2.ID = l1.ID2 and h2.ID not in (select ID1 from Likes)
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.
A6
select name,grade
from Highschooler
where ID not in (select h1.ID from Highschooler as h1,Highschooler as h2,Friend as f1
where h1.ID = f1.ID1 and h2.ID = f1.ID2 and h1.grade <> h2.grade)
order by grade, name
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.
A7
select distinct h1.name, h1.grade, h2.name, h2.grade, h3.name, h3.grade
from Highschooler as h1, Highschooler as h2, Highschooler as h3, Likes as l1, Friend as f1
where h1.ID = l1.ID1 and h2.ID = l1.ID2 and
h2.ID not in (select ID2 from Friend where h1.ID = ID1)
and h3.ID in (select ID2 from Friend where h1.ID = ID1)
and h3.ID in (select ID2 from Friend where h2.ID = ID1)
Q8
Find the difference between the number of students in the school and the number of different first names.
A8
select count(ID)-count(distinct name)
from Highschooler
Q9
Find the name and grade of all students who are liked by more than one other student.
A9
select name, grade
from Highschooler
where ID in (select ID2 from Likes group by ID2 having count(distinct ID1)>1)
SQL Social-Network Query Exercises Extras (in SQLite)
Q1
For every situation where student A likes student B, but student B likes a different student C, return the names and grades of A, B, and C.
A1
select distinct h1.name, h1.grade, h2.name, h2.grade, h3.name, h3.grade
from Highschooler as h1, Highschooler as h2, Highschooler as h3, Likes as l1, Likes as l2
where h1.ID = l1.ID1 and h2.ID = l1.ID2 and h2.ID = l2.ID1 and h3.ID = l2.ID2
and h1.ID <> l2.ID2
Q2
Find those students for whom all of their friends are in different grades from themselves. Return the students' names and grades.
A2
select name,grade
from Highschooler
where ID not in (select h1.ID from Highschooler as h1,Highschooler as h2,Friend as f1
where h1.ID = f1.ID1 and h2.ID = f1.ID2 and h1.grade = h2.grade)
Q3
What is the average number of friends per student? (Your result should be just one number.)
A3
select avg(f)
from (select count(ID2) as f from Friend group by ID1)
Q4
Find 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.
A4
select count(distinct f1.ID1)
from Friend as f1, Friend as f2, (select ID from Highschooler where name = 'Cassandra') as C
where f1.ID2 = C.ID or (f1.ID1 <> C.ID and f1.ID2 = f2.ID1 and f2.ID2 = C.ID)
Q5
Find the name and grade of the student(s) with the greatest number of friends.
A5
select name, grade
from Highschooler join (select ID1, count(ID2)as numF from Friend group by ID1) as f1
on ID = f1.ID1
where f1.numF = (select max(f2.numF)
from (select count(ID2) as numF from Friend group by ID1) as f2)
or (from solution set. Interesting and nice using of select, from, on, group by and having but contingent on name + grade being a unique key)
select name, grade
from Highschooler join Friend
on ID = ID1
group by name, grade
having count(ID2) = (select max(f.numF)
from (select count(ID2) as numF from Friend group by ID1) as f)
SQL Social-Network Modification Exercises (in SQLite)
Q1
It's time for the seniors to graduate. Remove all 12th graders from Highschooler.
A1
delete from Highschooler
where grade = 12
Q2
If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.
A2
delete from Likes
where ID1 in (select l1.ID1
from Likes as l1, Friend as f
where l1.ID1 = f.ID1 and l1.ID2 = f.ID2
and l1.ID2 not in (select ID1 from Likes as l2 where l2.ID2 = l1.ID1))
Q3
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. (This one is a bit challenging; congratulations if you get it right.)
A3