CSC330S2020MultipleSQLHomework

From BU Computer Science
Jump to navigation Jump to search

Multiple Tables Homework

Overview

This homework uses the sailors data.

Only turn in the sql statements (not the results of the query). You may test your queries out online. (But remember, just because you get the right data back, that does not mean your query is correct!). If a query cannot be answered with the method specified, explain why not.

This is due March 24th as a hard copy at the start of class.

Questions:

  • Use joins (if needed)
    1. Find the names of sailors who have reserved boat number 103.
    2. Find the sids of sailors who have reserved a red boat.
    3. Find the names of sailors who have reserved a red boat.
    4. Find the colors of boats reserved by Lubber.
    5. Find the names of sailors who have reserved at least one boat.
    6. Find the ages of sailors whose name begins and ends with B and has at least three characters.
    7. Find the names of sailors who have reserved a red or a green boat.
    8. Find the names of sailors who have reserved both a red and a green boat.
    9. Find the sids of all sailors who have reserved red boats but not green boats. This cannot be done with a join. Extra credit if you can explain why. (Don't give an sql for this -> skip if you are not doing extra credit)
    10. Find all sids of sailors who have a rating of 10 or reserved boat 104.
  • Use nested queries for the following:
    1. Find the names of sailors who have reserved boat 103.
    2. Find the names of sailors who have reserved a red boat.
    3. Find the names of sailors who have not reserved a red boat.
    4. Find the names of sailors who have reserved both a red boat and a green boat.
    5. Find the names of sailors who have reserved a red boat but not a green boat.
  • Use nested queries and exists.
    1. Find the names of sailors who have reserved boat number 103.
  • Answer any way you want.
    1. Find the names of sailors whose rating is better than some sailor called Horatio.
    2. Find the names of sailors whose rating is better than every sailor called Horatio.
    3. Find the names of sailors with the highest rating.
    4. Find the names of sailors who have reserved all boats.
    5. Find the average age of all sailors.
    6. Find the average age of all sailors with a rating of 10.
    7. Find the name and age of the oldest sailor.
    8. Find the names of sailors who are older than the oldest sailor with a rating of 10
    9. Find the age of the youngest sailor for each rating level
    10. Find the age of the youngest sailor who is eligible to vote (at least 18 years old) for each rating level with at least two sailors.
    11. Find the age of the youngest sailor who is eligible to vote (at least 18 years old) for each rating level with at least two such sailors.
    12. For each red boat, find the number of reservations for this boat.
    13. Find the average age of sailors for each rating level that has at least two sailors.
    14. Find those ratings for which the average age of sailors is the minimum over all ratings.