Assignment 5 — SQL queries

The goal of this assignment is to practice writing SQL select statements.

Schema

You will be using a schema very similar to that used in Assignment 4:


create table member(
    member_id serial primary key, 
    name varchar not null,
    birth_date date);

create table message(
    message_id serial primary key,
    message_date date not null,
    message_text varchar not null);

create table routing(
    from_member_id int not null references member,
    to_member_id int not null references member,
    message_id int not null references message);

Setting up the database

The first thing you need to do is to set up the database to be used in this assignment. Download initdb.sql and run it through psql to set up your database:
psql DBNAME < initdb.sql

where DBNAME is the name of your database. This script will:

Drop tables member, message, and routing.
Create the tables as declared above.
Populate the tables with data.

Writing the queries

You need to write 16 queries, q0 through q15.

q0 through q7 are identical or very similar to the similarly named queries in Assignment 4. The others are new, and may require you to use SQL facilities that cannot be expressed in Relational Algebra.

Unlike Assignment 4, the expected results are not provided. You need to play with the data yourself, and convince yourself that your query is correct. (This is, of course, what actually happens when writing SQL queries in the real world.)

General guidelines:

Most of the queries require that the rows of the result be ordered. Don't forget to add ordering to the queries that require it.
There are some similarities between the actual data provided by the initdb.sql script, and the database of Assignment 4. However, the data is different, so in general, query results from Assignment 1 will be different from those here, even when the query descriptions match.
In Assignment 4, query results were always distinct because the Relational Algebra produces sets of rows with no duplicates. The same is not true of SQL. None of the query results should have duplicates. While you don't need to specify select distinct in every query, you should do so when you would otherwise get duplicate rows.
Remember that there are different ways to write joins. The join conditions can be in the WHERE clause, or in the FROM clause. For many of these queries it doesn't matter which approach you use. Sometimes it does, though.

The queries you need to write

q0

What are the names of users who have not sent a message to themselves? Order alphabetically.

q1

What is the birth date of Tweetii?

q2

What are the send dates of messages sent by Zyrianyhippy? Order by descending date.

q3

What are the names of members who received messages on their birthdays? Order alphabetically.

q4

What are the send dates of messages from Unguiferous to Froglet? Order by date.

q5

Who sent messages on 2016/05/17? Order alphabetically.

q6

What are the names of members who received messages from Cephalophore? Order alphabetically.

q7

What are the names of senders and receivers of messages sent on 2016/05/17? Order by sender name and receiver name, alphabetically.

q8

Print the number of messages sent by Lucarne to every other member (i.e. omit messages from Lucarne to Lucarne), identified by name. Be sure to include members who received no message. Order by message count descending.

Hint: This one is tricky. Think carefully about what it means to place conditions in an ON clause vs. a WHERE clause.

q9

Who has sent at least 20 distinct messages? Print name and message count, ordered by count descending. (If a member sends the same message to multiple members, that counts as one distinct message.)

q10

What are the minimum and maximum birth dates among the members?

q11

How many messages have been received by Abderian?

q12

Find the dates of the oldest and newest messages sent for each user. Print name and the two dates, ordered by name.

Hint: Make sure to include members who have never sent a message.

q13

On what dates did Unguiferous and Abderian both send messages? Print dates in order.

q14

What are the names of members with unknown birth dates? Order by name.

q15

What are the names of members who have never sent a message? Order alphabetically.

Submitting your assignment

You should submit your 16 .sql files. We will run your code and check the answers.