Write SQL queries to answer the following questions using a

Write SQL queries to answer the following questions using a relational data base:

Sailors (sid {key}: integer, sname: string, rating: integer, age: real)

Boats (bid {key}: integer, bname: string, color: string)

Reserves (sid {foreign key}: integer, bid {foreign key}: integer, day {key}: date)

- - - - - - - - - -

A) Find the names of the oldest sailors. (Note: there may be more than one)

B) Find the name of sailors who never reserved any boat.

C) For each month of 2015, list the month and the number of reservations made during the month (rename as reservations). Assume the Year(x) and Month(x) functions are available for a Date type of parameter for x.

Solution

SELECT S.sid, S.sname FROM Sailors S where S.sid NOT IN (SELECT DISTINCT R.sid FROM Reserves R);

SELECT distinct Month(day), count(*) FROM Reserves WHERE Year(day)=\'2015\';