in-class exercise used the sailors/boats/reserves tables. I have
put DDL statements for these at ~cs186/fa03/sailors. If inclined,
you can load these into postgres and practice writing queries.
Just 'cd' to that directory, and run loadDB.sh.
Use the DDL to define
relations for Reserves and Sailors, defining keys/foreign keys, with a
constraint that age is between 16 and 110.
create table sailors (
Find boat ids for boats which
have never been reserved
sid int primary key,
age float check (age > 16 and age < 110)
create table boats (
bid int primary key,
create table reserves (
foreign key (sid) references sailors (sid),
foreign key (bid) references boats (bid)
copy sailors from '/cygdrive/c/Eben/cs186/sailors/sailors_data' USING
copy boats from '/cygdrive/c/Eben/cs186/sailors/boats_data' USING
copy reserves from '/cygdrive/c/Eben/cs186/sailors/reserves_data' USING
Find sailor names sorted by
their earliest reservation date.
There are several ways to do
this. One is set difference, subtracting the set of all boats
from the set of boats that have been reserved:
(select bid from boats) except
(select bid from reserves);
Another way to do this is an outer join between boats and reservations,
finding boats with null reservations:
select b.bid from boats b left outer join reserves
on b.bid = reserves.bid
where sid is null;
Yet another was is to use a nested query:
select bid from boats where not exists
(select * from reserves where boats.bid = reserves.bid);
Note: if you want the list of bids to have no duplicates, you may need
to specify 'select DISTINCT'
select sname from sailors natural join
Change all sailors named “Bob”
to have the name “Robert”
group by sailors.sid, sname
order by min(day);
update sailors set sname =
'Robert' where sname = 'Bob';
List each sailor’s name, along
with the number of reservations made by that sailor.
The following will give the count for
sailors who have made at least one reservation:
select sname, count(*)
from sailors, reserves
where sailors.sid = reserves.sid
group by sailors.sid, sname;
If you want the sailors who have made zero reservations, you should
union the above query with a query to get sailors with no reservations.