CS145 - Stanford - SQL Social-Network Modification Exercises

DATABASE: http://www.db-class.org/course/resources/index?page=socialdata

1. It's time for the seniors to graduate. Remove all 12th graders from Highschooler.

DELETE FROM Highschooler
WHERE Grade = '12'

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

DELETE FROM Likes
WHERE ID1 IN (SELECT L.ID1
                       FROM Friend F,Likes L
                       WHERE F.ID1 = L.ID1 AND F.ID2 = L.ID2
                       AND F.ID1 NOT IN (SELECT ID2 FROM Likes
                                                     WHERE ID1=L.ID2 AND ID2=L.ID1))

PART II BELOW:

1. Find the names of all students who are friends with someone named Gabriel.

Select H1.Name
From Highschooler H1, Highschooler H2, Friend
Where H1.ID = Friend.ID1 AND H2.name= 'Gabriel' AND H2.ID = Friend.ID2

2. 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 H1.Name, H1.Grade, H2.Name, H2.Grade
FROM Likes, Highschooler H1, Highschooler H2
WHERE H1.ID = Likes.ID1 AND H2.ID = Likes.ID2 AND H1.grade - H2.grade >=2

3. 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 h1name,h1grade,h2name,h2grade

from (
Select h1.name as h1name,h1.grade as h1grade,h2.name as h2name,h2.grade as h2grade
From Likes L1,  Highschooler h1,Highschooler h2
Where h1.ID = L1.ID1
AND h2.ID = L1.ID2
AND L1.ID1 in (Select ID2 From Likes Where ID2 = L1.ID1 AND ID1 = L1.ID2)
)
where h1name < h2name

4. 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 h1
Where h1.ID not in (Select ID1 from Likes )
AND h1.ID not in (Select ID2 from Likes)

5. 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 h1.name,h1.grade,h2.name,h2.grade
From Highschooler h1,Highschooler h2,Likes
Where h1.ID = Likes.ID1
AND h2.ID = Likes.ID2
AND h2.ID not in (Select ID1 from Likes )

6. 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.

Select distinct h1.name,h1.grade
From Highschooler h1,Highschooler h2,Friend
Where h1.ID = Friend.ID1
AND h2.ID = Friend.ID2
 AND h1.grade = h2.grade

EXCEPT
Select distinct h1.name,h1.grade
From Highschooler h1,Highschooler h2,Friend
Where h1.ID = Friend.ID1
AND h2.ID = Friend.ID2
 AND h1.grade <> h2.grade
order by h1.grade,h1.name

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

Select h1.name,h1.grade,h2.name,h2.grade,h3.name,h3.grade
From Highschooler h1,Highschooler h2,Highschooler h3,Likes L
Where h1.ID = L.ID1 AND h2.ID = L.ID2
and
L.ID1 not in (Select ID1 FROM Friend where (ID1=h1.ID and ID2=h2.ID) OR (ID1=h2.ID and ID2=h1.ID) )
AND h3.ID in
(
Select F1.ID2 from Friend F1,Friend F2
 where
((F1.ID1=h1.ID AND F1.ID2 = h3.ID)
  OR (F1.ID1=h3.ID AND F1.ID2 = h1.ID ))
AND
((F2.ID1=h2.ID AND F2.ID2 = h3.ID )
  OR (F2.ID1=h3.ID AND F2.ID2 = h2.ID))
)

8. Find the difference between the number of students in the school and the number of different first names.

select count(ID) - count(distinct name)
from Highschooler

9. Find the name and grade of all students who are liked by more than one other student.

Select h1.name,h1.grade
From Highschooler h1,Likes
Where h1.ID = Likes.ID2
group by Likes.ID2
having count(Likes.ID1) >1