My Solutions for Stanford Lagunita's 'Social-Network Query Exercises'

SocialNetwork.sql

/*
 * 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 [email protected]
 */

-- 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;