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 over 20.
(b) Output the names of all sailors who have reserved red boats.
(c) Output the names of all sailors who have made exactly ve reservations.


(a) Output the names of all sailors over 20.
Solution:


SELECT S.sname
FROM Sailors S
WHERE S.age > 20

(b) Output the names of all sailors who have reserved red boats.
Solution:

SELECT S.sname
FROM Sailors S
WHERE S.sid IN
( SELECT R.sid
FROM Reserves R, Boats B
WHERE B.bid = R.bid AND B.color = ``red'')

(c) Output the names of all sailors who have made exactly ve reservations.
Solution:

SELECT S.sname
FROM Sailors S
WHERE S.sid IN
( SELECT R.sid
FROM Reserves R
GROUP BY R.sid
HAVING COUNT(*) = 5 )

Computer Science & Information Technology

You might also like to view...

If a worksheet is set to print in portrait orientation, where the page is taller than it is wide, you can change this to ____________________ orientation, where the page is wider than it is tall.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology

____ lets you store your data remotely and access it with any device connected to the Internet.

A. Cloud storage B. Volatile memory C. ROM D. Flash memory

Computer Science & Information Technology

As DES became known as being too weak for highly classified communications, Double DES was created to provide a level of security far beyond that of DES. _________________________

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

Computer Science & Information Technology

In a relational database, relationships are implemented by having common columns in two or more tables.

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

Computer Science & Information Technology