The goal of this assignment is to practice writing SQL select statements.
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);
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:
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.)
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?
You should submit your 16 .sql files. We will run your code and check the answers.