The two files required for this assignment must be completed and uploaded to the HW4 moodle page by 11:00 PM on Monday, 17 February.
Getting ready
It takes some time to come up with a good database example,
so let’s just borrow one for
CS 145 at Stanford.
This particular database, representing the social network of a
high school, can be accessed in a few different
ways.
In this database, the Friend relation is
symmetric. If x is a Friend of y,
then y is a Friend of x.
However the Likes is not.
- A web page showing the relations — note the use of ID fields to identify students
- SQL statement to create the database
- A database file for sqlite3
If you want to use the database from a GUI, here are links to three we tried out in class.
The task
You are to write five SQL queries to solve the follow tasks.
- Generate a list of the students sorted by grade and name.
- Display a list of students that show who likes whom.
- Display a list of students where both students like each other.
- Display a list of students who are in 9th or 10th grade.
- Display a list of students who are friends with someone at least two years older then themselves. Do not list any pair more than once.