In Class SQL Exercise

The 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 (
    sid int primary key,
    sname varchar(38),
    rating int,
    age float check (age > 16 and age < 110)
);

create table boats (
    bid int primary key,
    bname varchar(25),
    color varchar(21)
);

create table reserves (
    sid int,
    bid int,
    day date,
    foreign key (sid) references sailors (sid),
    foreign key (bid) references boats (bid)
);

copy sailors from '/cygdrive/c/Eben/cs186/sailors/sailors_data' USING DELIMITERS '|';
copy boats from '/cygdrive/c/Eben/cs186/sailors/boats_data' USING DELIMITERS '|';
copy reserves from '/cygdrive/c/Eben/cs186/sailors/reserves_data' USING DELIMITERS '|';

Find boat ids for boats which have never been reserved

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'

Find sailor names sorted by their earliest reservation date.

select sname from sailors natural join reserves
group by sailors.sid, sname
order by min(day);

Change all sailors named “Bob” to have the name “Robert”

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.