SQL 2 Homework

Description:

Assume that you have a database that tracks sailors, boats in your fleet and reservations by the sailors to use a boat for a specified date. Assume that you have the following schema:

Sailor_T:
SailorID
SailorName
BirthDate
RatePerDay
				
Boat_T:
BoatID
BoatType
BoatLength
				
Reserves_T:
SailorID
BoatID
Day

Example BoatType values are:

'Motorboat'
'Pontoon'
'Rowboat'
'Sailboat'
'Yacht'

Requirements

Write SQL statements to answer the following questions:

/*
 * Name:
 * Date:
 * Database (for example, SQL Server, Postgres, or MySQL): 
 */	  


/*
 * Test data
*/




/* Query 1: Retrieve the name(s) of the sailor(s) who reserved both Pontoon and Sailboat boats (meaning they reserved at least one Pontoon and one Sailboat). */



/* Query 2: Retrieve the name(s) of the sailor(s) who have reserved every type of boat.  Want a hint: Can you write a query that returns the number of distinct boat types?  Also, consider using HAVING. */



/* Query 3: Retrieve the name(s) of the sailor(s) who reserved the most Yachts. */

Additionally, write 1) a meaningful question that involves RatePerDay and 2) a complex query that provides the answer to your question. Your query needs to require more than one table.

/* Query 4:             */

/* SQL */

Include a comment near the top of your SQL file indicating which database you are using (e.g., SQL Server, Postgres, and MySQL). If you create test data (which you're encouraged to do), include it with your submission (but commented out). Also, do not hard code any values (for example, a boat ID).

Submission

Submit a single .sql file to CougarVIEW.