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

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

Problem 15

Problem: Add the reviewer Roger Ebert to your database, with an rID of 209.

-- It's time for the seniors to graduate. Remove all 12th graders from Highschooler.  
Delete FROM Highschooler
WHERE grade = 12;
##      id      name grade
## 1  1025      John    12
## 2  1101     Haley    10
## 3  1247    Alexis    11
## 4  1304    Jordan    12
## 5  1316    Austin    11
## 6  1381   Tiffany     9
## 7  1468      Kris    10
## 8  1501   Jessica    11
## 9  1510    Jordan     9
## 10 1641  Brittany    10
## 11 1661     Logan    12
## 12 1689   Gabriel     9
## 13 1709 Cassandra     9
## 14 1782    Andrew    10
## 15 1911   Gabriel    11
## 16 1934      Kyle    12

Problem 16

Problem: If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.

-- If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple. 

DELETE FROM Likes
WHERE ID2 IN (SELECT ID2 FROM Friend WHERE Likes.ID1 = ID1) AND
ID2 NOT IN (SELECT L.ID1 FROM Likes L WHERE Likes.ID1 = L.ID2);
##         name grade      name grade
## 1     Alexis    11      Kris    10
## 2     Andrew    10 Cassandra     9
## 3     Austin    11    Jordan    12
## 4   Brittany    10      Kris    10
## 5  Cassandra     9   Gabriel     9
## 6    Gabriel     9 Cassandra     9
## 7    Gabriel    11    Alexis    11
## 8    Jessica    11      Kyle    12
## 9       John    12     Haley    10
## 10      Kyle    12   Jessica    11

Problem 17

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

-- Find the titles of all movies that have no ratings. 
INSERT INTO Friend
SELECT DISTINCT F1.ID1, F2.ID2
FROM Friend F1, Friend F2
WHERE F1.ID2 = F2.ID1 AND F1.ID1<>F2.ID2 
      AND F1.ID1 NOT IN (SELECT F3.ID1 FROM Friend F3 WHERE F3.ID2=F2.ID2);
##      id      name grade count
## 1  1025      John    12     2
## 2  1101     Haley    10     3
## 3  1247    Alexis    11     7
## 4  1304    Jordan    12     8
## 5  1316    Austin    11     6
## 6  1381   Tiffany     9     6
## 7  1468      Kris    10     6
## 8  1501   Jessica    11     7
## 9  1510    Jordan     9     5
## 10 1641  Brittany    10     3
## 11 1661     Logan    12     4
## 12 1689   Gabriel     9     8
## 13 1709 Cassandra     9     7
## 14 1782    Andrew    10    10
## 15 1911   Gabriel    11     5
## 16 1934      Kyle    12     7