COMP 3421 PDA #4

(30 Points)

Due 10AM, Feb 9, 2016

Step 4 of Your PDA

Note: this assignment is a slight modification of material developed by the Stanford Database Group

1. (15 pts.) Write three queries on your PDA database, using the select-from-where construct of SQL.  To receive full credit your queries should be as given below and you should explain in English what the query does. None of your queries should return more than 20 tuples.
Upload a file containg a the select statemetns for your queries, a sentence or two for each describing the query, and a the execution of each of the three queries, where the three queries satisfy:
• One query must involve a two-way or three-way join with a where clause that limits the results to 20 or fewer tuples.
• One query must be an aggregate using a group by clause.
• One query must be an aggregate using a group by clause and a having clause.
2. (15 pts.) Write three data modification commands on your PDA database. One should be a simple insert, one a simple update, and one an update that updates several tuples at once. Each of these commands should be legal, given the constraints your created for your database in parts (1) and (2). You might want to try out your commands on small data before trying it on your full database. Upload a script that shows your modification commands running in a convincing fashion, in other words show the relevant part of a relation pre/post the insertion and update commands.

Here is an example of an update and insert command for the Sailors example used in class:

insert into sailors values (999999,'bob999999',35,5) ;

update sailors
set rating = rating - 1
where age = 18 ;