Assignment 3 — SQL queries

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


You will be using a schema very similar to that used in the test_queries part of Assignment 1:
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:

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 1. The others are new, and may require you to use SQL facilities that cannot be expressed in Relational Algebra.

Unlike Assignment 1, 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:

The queries you need to write


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


What is the birth date of Tweetii?


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


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


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


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


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


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


Print the number of messages sent by Lucarne to every other member, 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.


Who has sent more than 60 messages? Print name and message count, ordered by count descending.


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


How many messages have been received by Abderian?


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.


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


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


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

Submitting your assignment

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