Mysql Query – Set 6

We have 3 tables Highschooler, Friend, Likes as shown below: Highschooler ( ID, name, grade ) There is a high school student with unique ID and a given first name in a certain grade.

Friend ( ID1, ID2 ) The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123).

Likes ( ID1, ID2 ) The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also present.

Q1. Find the name and grade of all students who are liked by more than one other student. Q2. Find the names of all students who are friends with someone named Gabriel.

Solution:

Ans 1.

SELECT NAME,GRADE
FROM HIGHSCHOOLER AS HS, LIKES AS L
WHERE HS.ID=L.ID2
GROUP BY ID2 HAVING COUNT(NAME)>=2

Ans 2

SELECT NAME
FROM FRIEND AS F, HIGHSCHOOLER AS H
WHERE F.ID1=H.ID AND ID2 IN (SELECT ID FROM HIGHSCHOOLER WHERE NAME='Gabriel'