Social network Assignment Solution in SQL

Social Network

Students at your hometown high school have decided to organize their social networks using databases. So far, they have collected information about sixteen students in four grades, 9-12. Here's the schema:

Highschooler( ID, name, grade )

English: There is a high school student with a unique ID and a given first name in a certain grade.

Friend ( ID1, ID2 )

English: 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 )

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

Your queries will run over a small data set conforming to the schema.

For your convenience, here is a graph showing the various connections between the students in our database. 9th graders are blue, 10th graders are green, 11th graders are yellow, and 12th graders are purple. Undirected black edges indicate friendships, and directed red edges indicate that one student likes another student.

Database

Find the names of all students who are friends with someone named Gabriel.
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.
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.
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.
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.
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.
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.
Find the difference between the number of students in the school and the number of different first names.
Find the name and grade of all students who are liked by more than one other student.

Solution:

Question 1

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

SELECT Highschooler_1.name

FROM Highschooler Highschooler_1

INNER JOIN Friend ON Highschooler_1.ID = Friend.ID1

INNER JOIN Highschooler Highschooler_2 ON Highschooler_2.ID = Friend.ID2

WHERE Highschooler_2.name = "Gabriel";

Q 1

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 Highschooler_1.name, Highschooler_1.grade, Highschooler_2.name, Highschooler_2.grade

FROM Highschooler Highschooler_1

INNER JOIN Likes

ON Highschooler_1.ID = Likes.ID1

INNER JOIN Highschooler Highschooler_2

ON Highschooler_2.ID = Likes.ID2

WHERE (Highschooler_1.grade >= (Highschooler_2.grade+2));

Q 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 Highschooler_1.name, Highschooler_1.grade, Highschooler_2.name, Highschooler_2.grade

FROM Highschooler Highschooler_1, Highschooler Highschooler_2, Likes L1, Likes L2

WHERE (Highschooler_1.ID = L1.ID1 AND Highschooler_2.ID = L1.ID2)

AND (Highschooler_2.ID = L2.ID1 AND Highschooler_1.ID = L2.ID2)

AND Highschooler_1.name < Highschooler_2.name

ORDER BY Highschooler_1.name, Highschooler_2.name;

Q 3

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

WHERE ID NOT IN (

SELECT DISTINCT ID1

FROM Likes

UNION

SELECT DISTINCT ID2

FROM Likes

)

ORDER BY grade, name;

Q 4

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 Highschooler_1.name, Highschooler_1.grade, Highschooler_2.name, Highschooler_2.grade

FROM Highschooler Highschooler_1

INNER JOIN Likes ON Highschooler_1.ID = Likes.ID1

INNER JOIN Highschooler Highschooler_2 ON Highschooler_2.ID = Likes.ID2

WHERE (Highschooler_1.ID = Likes.ID1 AND Highschooler_2.ID = Likes.ID2) AND Highschooler_2.ID NOT IN (

SELECT DISTINCT ID1

FROM Likes

);

Q 5

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 name, grade

FROM Highschooler Highschooler_1

WHERE ID NOT IN (

SELECT ID1

FROM Friend, Highschooler Highschooler_2

WHERE Highschooler_1.ID = Friend.ID1 AND Highschooler_2.ID = Friend.ID2 AND Highschooler_1.grade <> Highschooler_2.grade

)

ORDER BY grade, name;

Q 6

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 DISTINCT Highschooler_1.name, Highschooler_1.grade, Highschooler_2.name, Highschooler_2.grade, Highschooler_3.name, Highschooler_3.grade

FROM Highschooler Highschooler_1, Highschooler Highschooler_2, Highschooler Highschooler_3, Likes Like1, Friend Friend1, Friend Friend2

WHERE (Highschooler_1.ID = Like1.ID1 AND Highschooler_2.ID = Like1.ID2)AND Highschooler_2.ID NOT IN (

SELECT ID2

FROM Friend

WHERE ID1 = Highschooler_1.ID

) AND (Highschooler_1.ID = Friend1.ID1 AND Highschooler_3.ID = Friend1.ID2) AND (Highschooler_2.ID = Friend2.ID1 AND Highschooler_3.ID = Friend2.ID2);

Q 7