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

Computer Science & Information Technology

You might also like to view...

Current Microsoft OSs include IPv6, but to use it, you must enable it first.

Answer the following statement true (T) or false (F)

Computer Science & Information Technology

Peachtree is an example of ________ software

A) course management B) accounting C) agricultural D) simulation

Computer Science & Information Technology

A Web page with frames is held together by a ____.

A. frameset B. table C. grid D. master document

Computer Science & Information Technology

You can back up your data at the same time that you create a system image

Indicate whether the statement is true or false

Computer Science & Information Technology