Assignment 0 - CS120a
Due Friday, January 28, 10:30 AM
Note: Acceptable and prompt completion of this assignment is required for all
students who did not take or pass COSI 127, and who seek permission to take this course.
Consider the following relational schema (i.e., table structures) such that
underlined attributes are keys:
Sailors (sid: integer, sname: string, rating: integer, age: real),
Boats (bid: integer, bname: string, color: string),
Reserves (sid: integer, bid: integer, day: date).
Part A: Querying
Pose the following queries in (a) SQL, and (b) the Relational Algebra.
1. Find the names and ages of all sailors.
2. Find the names of sailors who have reserved boat 103.
3. Find the names of sailors who have reserved a red boat.
4. Find the names of sailors who have reserved at least one boat.
5. Find the names of sailors who have reserved a red or a green boat.
6. Find the names of sailors who have reserved at least two boats.
7. Find the sids of sailors who are older than 20 and who have not reserved a red boat.
8. Find the names of sailors who have reserved all boats.
9. Find the names of sailors who have reserved all boats called Interlake.
10. (SQL only) Find the average age of sailors for each rating level that has at least two sailors.
Part B: Query Optimization
Do question 12.9 (a, b, c) on page 435 of KSS.