SQL: Stanford’s Exercises - Social Network - PART II

You’ve started a new movie-rating website, and you’ve been collecting data on reviewers’ ratings of various movies.

Problem 10

Problem: 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.

-- 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. 
SELECT H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade
FROM Likes L1, Likes L2, Highschooler H1, Highschooler H2, Highschooler H3
WHERE L1.ID2 = L2.ID1
AND L2.ID2 <> L1.ID1
AND L1.ID1 = H1.ID AND L1.ID2 = H2.ID AND L2.ID2 = H3.ID;
##      name grade      name grade    name grade
## 1 Gabriel    11    Alexis    11    Kris    10
## 2  Andrew    10 Cassandra     9 Gabriel     9

Problem 11

Problem: Find those students for whom all of their friends are in different grades from themselves. Return the students’ names and grades.

-- Find those students for whom all of their friends are in different grades from themselves. Return the students' names and grades. 
SELECT name, grade
FROM Highschooler, (
  SELECT ID1 FROM Friend
  except
  -- students have friends with same grade
  SELECT DISTINCT Friend.ID1
  FROM Friend, Highschooler H1, Highschooler H2
  WHERE Friend.ID1 = H1.ID AND Friend.ID2 = H2.ID
  AND H1.grade = H2.grade
) AS Sample
WHERE Highschooler.ID = Sample.ID1;
## data frame with 0 columns and 0 rows

Problem 12

Problem: What is the average number of friends per student? (Your result should be just one number.)

-- What is the average number of friends per student? (Your result should be just one number.)  
SELECT avg(students)
FROM (SELECT ID1, COUNT (ID2) students FROM friend GROUP BY ID1) D;
##     avg
## 1 5.875

Problem 13

Problem: 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.

-- 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. 
SELECT count(id2) FROM friend WHERE id1 IN (
  SELECT id2 FROM friend WHERE id1 IN (SELECT id FROM highschooler WHERE name='Cassandra')
)
and id1 NOT IN (SELECT id FROM highschooler WHERE name='Cassandra');
##   count
## 1    48

Problem 14

Problem: Find the name and grade of the student(s) with the greatest number of friends.

-- Find the name and grade of the student(s) with the greatest number of friends.  
SELECT h.name, h.grade FROM highschooler h, friend f WHERE
h.id = f.id1 GROUP BY f.id1, h.name, h.grade HAVING count(f.id2) = (
SELECT max(r.c) FROM
(SELECT count(id2) AS c FROM friend GROUP BY  id1) AS r);
##     name grade
## 1 Andrew    10