Use the following schema for this problem:



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

key - sid

Boats(bid: integer, bname: string, color: string)

key - bid

Reserves(sid: integer, bid: integer, day: date)

key - sid, bid, day

(a) Output the names of all sailors who have only rented a single boat (although they might
have rented that boat several times).
(b) Define a view giving the reservation history of junior sailors (sailors under 16 years old).
For each reservation that a junior sailor has made, the view has a row giving the sailor's
name and Id, the boat name and Id, and the day the boat was reserved.
(c) Using the view of (10b), give a SELECT statement that returns the name of each sailor,
the name of the boat that that sailor has rented, and the number of times the sailor has
rented that boat.

(a) Output the names of all sailors who have only rented a single boat (although they might
have rented that boat several times).
Solution:



SELECT sname

FROM Sailors S

WHERE ( SELECT COUNT DISTINCT R.bid

FROM Reserves R

WHERE R.sid = S.sid) = 1

(b) Define a view giving the reservation history of junior sailors (sailors under 16 years old).
For each reservation that a junior sailor has made, the view has a row giving the sailor's
name and Id, the boat name and Id, and the day the boat was reserved.
Solution:



CREATE VIEW V AS

SELECT S.sid, S.sname, B.bid, B.name, R.day

FROM Sailors S, Reserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND S.age < 16

(c) Using the view of (10b), give a SELECT statement that returns the name of each sailor,
the name of the boat that that sailor has rented, and the number of times the sailor has
rented that boat.
Solution:



SELECT V.sname, V.bname, COUNT (*)

FROM V

GROUP BY V.sid, V.sname, V.bname, V.bid