The files required for this assignment must be completed and uploaded to the HW5 moodle page by 11:00 PM on Sunday, 2 March.
Getting ready
Again, we are borrowing the database 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 following tasks.
- Generate a list of students who are in 9th or 10th grade. List the students and their grade and sort them first by grade and then by name.
- Generate a list of students where both students like each other. List only the names of the two students and do not list each pair more than once.
- Generate a list of students who have three or more friends. List only the names of the students and the number of friends they have.
- Generate a list of students whose names do not contain the letter ’e‘. List only the names of these students.
- Generate a list of students who are not friends with any student whose name contains the letter ’e‘. Again, list only the names of these students. By the way, they are Haley, John and Logan.
Midterm 1
Doing these problems will be good practice for the first exam.