SQL Social-Network Query Exercises Solutions

SQL Social-Network Query Exercises

Pretty straightforward. Pay attention to the question. 

Question 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

Question 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

Question 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

Question 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)

Question 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 ) 

Question 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

Question 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))

) 

Question 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

Question 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