Difference: CSC210S2024Home3 (1 vs. 2)

SQL Homework 3

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!). Turn in an electronic copy or your .sql file. The word "join" should not be used in any of your SQL statements. Include your name and label each question (using comments).

Questions:
Part A: Nested Queries: Use nested queries for the following: (Do not us any joins)

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.

1. Find the names of sailors who have reserved boat number 103.

1. Find the sids of sailors who have reserved a red or a green boat.
2. Find the sids of sailors who have reserved both a red and a green boat.
3. Find the sids of all sailors who have reserved red boats but not green boats.

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.