Stanford SQL exercise solutions

1. 

SELECT DISTINCT H1.name
FROM Highschooler H1, Friend F, Highschooler H2
WHERE (H1.ID = F.ID1 AND F.ID2 = H2.ID)
AND H2.name = 'Gabriel';

2. 

SELECT h1.name, h1.grade, h2.name, h2.grade
FROM (Highschooler h1 JOIN LIKES l ON l.ID1 = h1.ID) JOIN Highschooler h2
ON h2.ID = l.ID2
WHERE h1.grade >= h2.grade + 2;

3.

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 H2.name>H1.name and
ID1 in
(SELECT ID2
FROM Likes )
and ID2 in
(SELECT ID1
FROM Likes)
ORDER BY H1.name;

4. 

Select name,grade
from Highschooler h
where h.id not in
(select id1 from likes)
and h.id not in
(select id2 from likes)
order by grade,name;