|
/* |
|
* SocialNetwork.sql |
|
* Aditya Jhanwar |
|
* |
|
* My solutions for Stanford Lagunita's SQL course, exercise section 'Social-Network Query Exercises' |
|
* Feedback would be greatly appreciated! |
|
* Please feel free to leave a comment below or you can reach me at ajhanwar@berkeley.edu |
|
*/ |
|
|
|
-- Q1: Find the names of all students who are friends with someone named Gabriel. |
|
select name |
|
from Highschooler join Friend |
|
on Highschooler.ID = Friend.ID1 |
|
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. |
|
select A.name, A.grade, B.name, B.grade |
|
from Highschooler A, Highschooler B, Likes |
|
where A.ID = Likes.ID1 and |
|
B.ID = Likes.ID2 and |
|
A.grade - B.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. |
|
select A.name, A.grade, B.name, B.grade |
|
from (Likes join Highschooler on ID = ID1) A, |
|
(Likes join Highschooler on ID = ID1) B |
|
where A.ID2 = B.ID1 and |
|
A.ID1 = B.ID2 and |
|
A.name < B.name |
|
order by A.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. |
|
select name, grade |
|
from Highschooler |
|
where ID not in (select ID1 from Likes union 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. select A.name, A.grade, B.name, B.grade |
|
select A.name, A.grade, B.name, B.grade |
|
from Highschooler A, |
|
Highschooler B, |
|
(select * from Likes where ID2 not in (select ID1 from Likes)) C |
|
where A.ID = C.ID1 and |
|
B.ID = C.ID2; |
|
|
|
-- 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. |
|
order by grade, name; |
|
select distinct A.name, A.grade |
|
from Friend F1, Highschooler A |
|
where A.ID = F1.ID1 and |
|
A.grade = ALL(select grade |
|
from Friend join Highschooler on ID2 = ID |
|
where F1.ID1 = ID1) |
|
order by A.grade, A.name; |
|
|
|
/* ALTERNATE SOLUTION: */ |
|
select name, grade |
|
from Highschooler A |
|
where ID not in (select ID1 |
|
from Friend join Highschooler on ID2 = ID |
|
where A.ID = ID1 and |
|
A.grade <> grade) |
|
|
|
-- 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. |
|
/* TO BE UPDATED */ |
|
|
|
-- Q8: Find the difference between the number of students in the school and the number of different first names. |
|
select count(ID)-count(distinct name) as difference |
|
from Highschooler; |
|
|
|
-- Q9: Find the name and grade of all students who are liked by more than one other student. |
|
select name, grade |
|
from Likes join Highschooler |
|
on Likes.ID2 = Highschooler.ID |
|
group by ID2 |
|
having count(ID1) > 1; |
