Return to CS G102 home page

Consider the following database schema

Sailors(sid:integer, sname: string, rating: integer, age: real)
Boats(bid:integer, bname: string, color: string)
Reserves(sid:integer, bid: integer, day: string)

Write the following questions in SQL

• UNION-INTERSECTION-MINUS
  1. Find the sids of all sailors who have reserved red boats but not greens boats.

  2. Find all sids of sailors who have a rating of 10 or have reserved boat 104.

  
  

• NESTED QUERIES
  1. Find the name of the sailors who have reserved a red boat (use IN/NOT IN).
  2. Find the name of the sailors who have not reserved a red boat (use IN/NOT IN).
  3. Find the name of the sailors who have reserved boat number 103 (use EXISTS).
  4. Find sailors whose rating is better than any sailor called Horatio.
  5. Find the sailors with the highest rating
  6. Find the name of the sailors who have reserved both a red and a green boat.
  7. Find the name of sailors who have reserved all boats.

  
  

• AGGREGATE OPERATIONS
  1. Find the name and the age of the oldest sailor
  2. Find the name of sailors who are older than the oldest sailor with a rating of 10.
  3. Find the age of the youngest sailor for each rating level.
  4. For each red boat, find the number of reservations for this boat.
  5. Find the average age of sailors who are at least 18 years old for each rating level that has at least two sailors.

  
  

• NULL VALUES
  1. Find all sailors whose rating is unknown

  
  

• OUTER JOINS
  1. List all sailors and the number of boats each has reserved. If a sailor has not reserve any boat the sailor should still appear in the result table.