SQL Social-Network Exercises


Students at your hometown high school have decided to organize their social network using databases. So far, they have collected information about sixteen students in four grades, 9-12. A SQL file to set up the schema and data for these exercises is downloadable here. This schema and data can be loaded as specified in the file into SQLite, MySQL, or PostgreSQL; see our quick guide for installing and using these systems.

Schema:
Highschooler ( ID, name, grade )
English: There is a high school student with 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.

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.

Social graph
 
The questions are organized into sections. If you’ve watched the videos up to the point indicated in the section title, then you should be able to write every query in the section. (A few queries may become easier once you’ve learned more advanced methods, but you should be able to write all of the queries using the constructs presented in the videos up to that point.) Unless a specific result ordering is asked for, you can return results in any order.

As a guide to test the accuracy of your SQL queries, the correct query results over the provided data can be seen by pressing the button at the bottom of the page.


Basic SELECT, Table Variables, Set Operators

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

2.  Like, scandalous! For every student who likes someone 2 or more grades below their own grade, return that student's name and grade, and the name and grade of the student they like.

3.  Should totally be dating: For every pair of students who both like each other, return the name and grade of both students. Include each pair only once.

4.  Alas: For every situation where student A likes student B, but student B likes a different student C, return the names and grades of A, B, and C.

5.  Need more intel: 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. (Hint: Without subqueries, you will need to use the EXCEPT operator.)


Subqueries in WHERE, FROM, SELECT

6.  Have a chance: 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.

7.  Narrow-minded: 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.

8.  Broad-minded: Find names and grades of students who only have friends in different grades.

9.  Could you introduce me? For each student A who likes a student B where the two are not friends, find if they have a friend C in common. For all such trios, return the name and grade of A, B, and C.


Aggregation

10.  Duplication: Find the difference between the number of students in the school and the number of different first names.

11.  Average affability: What is the average number of friends per student?

12.  Social circle: Find the number of students who are either friends with Cassandra or are friends of friends of Cassandra. Do not count Cassandra, even though technically she is a friend of a friend.

13.  It must be their brains, not their looks: Find the name and grade of all students who are liked by more than one other student.

14.  Most gregarious: Find the name and grade of the student(s) with the greatest number of friends.


Data Modification Statements

Each of the questions in this section (except the first) assumes the database is in the state after the modifications from the previous question. If you find you’ve made a mistake or want to restart, just reload the schema and data from scratch.

15.  End of the school year: Increment the year of all students by 1, such that all 9th graders become 10th graders, all 10th graders become 11th graders, and so on. To check your modification, find the names of all students who are in a grade higher than 12 (we'll deal with them later).

16.  Graduating seniors: After the previous update, some students ended up in 13th grade. They're actually graduating, so remove all tuples in Highschooler with a grade higher than 12. To check your modification, find the total number of students remaining in the Highschooler table.

17.  Now clean up this mess: While friendships may continue after graduation, we can't model them for students no longer in our database. Remove all tuples in Friend and all tuples in Likes that refer to nonexistent students. (This will require two separate commands.) To check your modification, find the names of all students whom Austin likes or who are friends with Austin.

18.  Summertime: Now that they're on vacation, our students have more time to spend with one another. As a result, their social circles have expanded: For all cases where A is friends with B, and B is friends with C, add a new friendship for the pair A and C. (Do not add duplicate friendships, friendships that already exist, or friendships with oneself.) To check your modification, for the now 10th-grader Jordan, return the names of all her friends.