Note:
This assignment is a slight modification of material developed by the
Stanford
Database Group.
-
(15
points)
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 containing the select statements for your
queries, a sentence or two for each describing the query, and the
execution of each of the three queries, where the three queries
satisfy the following:
-
One
must involve a two-way or three-way join with a where clause that
limits the results to 20 of fewer tuples.
- One
must be an aggregate using a group by clause.
-
One
must be an aggregate using a group by clause and a having clause.
-
(15
points)
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 you created for your database in
Parts 1 and 2. You might want to try out your commands on a small
dataset 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/postinsertion
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 ;
