Assignment 3 – SQL
This assignment should be completed individually.
To get started, create a database named “PSD” and run the scripts that start on page 5 of this document. Before you attempt to write any SQL queries, familiarize yourself with the database structure and the data.
Write queries to address each of the problems below. For each problem, place in this Word document under the question a copy/paste of your SQL code followed by a screenshot that shows the result set from SQL Server. The grader will copy/paste your SQL code into SQL Server Mgt Studio to confirm the same result set is produced – if this copy/paste is not possible for the grader, the assignment will not be graded. Also, the grader will not modify your SQL code in any way. Save this file as “4301Assign3_#####”, where #### is your name and submit the file into eLearning. Your results should never show duplicate records and you must complete the questions in order.
Case Description and ERD
In the early 18th century, a group of Caribbean pirates discovered they could increase their profits by sharing sailing vessels. Upon completion of a mission of booty and plunder, a pirate’s ship was normally idle while the captain and crew enjoyed their spoils. If during this idle period the ship were utilized by other pirates, all involved parties would save on ship overhead costs. To accomplish this mutual benefit, a secret society named the “Pirate Ship Depository”, or PSD, was formed. In its charter, PSD established a reservation system for the reservation and use of ships. PSD also established certain ground rules, including only one pirate can use a single ship at a time (to avoid fighting over the spoils), a ship may never have been “checked out” but can be checked out many times by the same or different pirates, plundered treasure belongs to the acquiring pirate with one plundering trip potentially yielding many different types of treasure. Below is the ERD that visually describes this scenario:
- First, write SQL to insert the following records into the PIRATE table.
| Name | Age | HasParrot | HasPegLeg |
| Red Legs Greaves | 37 | 0 | 1 |
| Francois L’Ollonais | 32 | 0 | 0 |
| Henry Jennings | 31 | 0 | 0 |
Then, write a query to display these 3 rows only after they have been inserted. Your query result should be reader-friendly by displaying the following values and column headings:
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to insert the 3 specified rows):
SQL CODE (to display the inserted rows, showing “Yes” or “No” values, per above):
YOUR RESULT SET SCREENSHOT:
- Delete the 3 rows you just added in #1. Show your delete worked by afterwards running a a query that selects all columns, all records from PIRATE, sorted by Name.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to delete the specified records):
SQL CODE (to display the table after the records have been deleted):
RESULT SET SCREENSHOT:
- Due to an error, the number of cannons stored for all of the schooners was understated by 10 cannons. Write a single SQL command to increase the number of cannons by 10 for just the schooners. Then write a query to show all columns for all ships – sort by ShipID.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to fix the problem):
SQL CODE (to display the result set after the fix):
RESULT SET SCREENSHOT:
- Write a single query that will list the only pirate who did NOT sail on a Galleon. The result set should have 1 row and display the “Name” column only. The only hard-coded value allowed in your query is the word “Galleon”. Hint: Your query can use subqueries/ table joins/ or both.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to display the result set):
RESULT SET SCREENSHOT:
- Write a single query that will list the Pirate ID and Name of the pirates who reserved a Galleon but never reserved a Sloop. The only hard-coded values allowed in your query are the words “Galleon” and “Sloop”. The result set should have 2 rows. Hint: Set operator.
Hint: Result set provided
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to display the result set):
RESULT SET SCREENSHOT:
- Write 3 separate queries to show 1) the name and age of the youngest pirate with a peg leg, 2) the name and age of the oldest pirate, and 3) the number of pirates with parrots. Where needed, change the column headers to be descriptive/reader-friendly.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to display the result set):
1)
2)
3)
RESULT SET SCREENSHOTS:
1)
2)
3)
- Write a query that will list all ships that may have gone to Davey Jones’ Locker (i.e., the last time they were checked out, they were never checked back in). List the Ship ID, Ship Name, Ship Type, check out date, Return due date, Check in date. Sort by Ship ID. No hard-coded values are allowed in your query.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to display the result set):
RESULT SET SCREENSHOT:
- Write a query to display Pirate ID, Pirate Name, Age, and whether the pirate has a parrot for pirates that are either under the age of 40 and have a parrot or are over the age of 39 and do not have a parrot. Sort by Pirate ID.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to display the result set):
RESULT SET SCREENSHOT:
- Which pirates hauled in the most jewels? Which ones did not haul in any jewels? Write a single query to display all 10 pirates with the number of treasure hauls of jewels for each, sorted by the number of hauls in descending order. Display pirate name and # of hauls, ensuring descriptive column names. Any pirates with no jewels hauls should show a value of “0”. No hard-coded values are allowed in your queries.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to display the result set):
RESULT SET SCREENSHOT:
- Write a query to display all ships that were checked back in late. List the Ship ID, Ship Name, days late, check out date, check in date and return due date. Use the DATEDIFF function to calculate the days late and label the column ‘Days Late’. Order the query first by the number of days late in descending order then by the ship name in ascending order. No hard-coded values are allowed in your query.
##Paste below: 1) copy your SQL code out of SSMS 2) screenshot of the result set displayed
SQL CODE (to display the result set):
RESULT SET SCREENSHOT:
Scripts
USE PSD;
CREATE TABLE PIRATE(
PirateID smallint not null,
Name VARCHAR(30) NOT NULL,
Age smallint,
HasParrot bit,
HasPegLeg bit,
CONSTRAINT pirate_pk PRIMARY KEY(PirateID)
);
CREATE TABLE TREASURE(
TreasureID INT NOT NULL Identity(1,1),
Category varchar(20) CHECK (Category IN(‘Gold’,’Jewels’,’Spices’,’Silver’,’Art’,’Other’)),
DateOfPlunder date,
PirateID smallint,
constraint plunder_fk FOREIGN KEY(PirateID) REFERENCES PIRATE(PirateID) ON DELETE CASCADE
);
CREATE TABLE SHIP(
ShipID SMALLINT NOT NULL,
ShipName VARCHAR(30),
NumOfCannons smallint,
ShipType varchar(20) CHECK (ShipType IN(‘Sloop’,’Schooner’,’Galleon’,’Man of War’,’Frigate’)),
CONSTRAINT ship_pk PRIMARY KEY(ShipID)
);
CREATE TABLE RESERVATION(
ReservationNum INT NOT NULL Identity(1,1),
CheckOutDate DATE,
CheckInDate DATE,
ReturnDueDate DATE,
PirateID smallint,
ShipNum smallint,
CONSTRAINT reservation_pk PRIMARY KEY(ReservationNum),
CONSTRAINT reservation_fk1 FOREIGN KEY (PirateID) REFERENCES PIRATE(PirateID) ON DELETE CASCADE,
CONSTRAINT reservation_fk2 FOREIGN KEY (ShipNum) REFERENCES SHIP(ShipID) ON DELETE CASCADE
) ;
–PIRATE rows
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (1,’Blackbeard’,41,0,0);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (2,’Calico Jack’,30,0,0);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (3,’Black Bart’,38,1,1);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (4,’Anne Bonny’,24,0,0);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (5,’Henry Morgan’,40,0,1);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (6,’Captain Kidd’,42,0,0);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (7,’Thomas Tew’,51,0,0);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (8,’Jean Lafitte’,43,0,0);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (9,’Jack Sparrow’,26,1,0);
INSERT INTO PIRATE(PirateID, Name, Age,HasParrot, HasPegLeg) VALUES (10,’Long John Silver’,34,1,1);
–SHIP rows
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1000,’Black Pearl’,18,’Schooner’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1001,’Jolly Roger’,36,’Sloop’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1002,’Adventure Galley’,34,’Galleon’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1003,’Queen Anne”s Revenge’,40,’Frigate’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1004,’Fancy’,46,’Galleon’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1005,’Whydah’,30,’Schooner’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1006,’Royal Fortune’,40,’Frigate’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1007,’Flying Dutchman’,18,’Man of War’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1008,’Satisfaction’,12,’Sloop’);
INSERT INTO SHIP(ShipID, ShipName, NumOfCannons,ShipType) VALUES (1009,’Golden Hind’,18,’Galleon’);
–RESERVATION rows
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’03/31/1716′,’04/07/1716′,’04/05/1716′,4,1000);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’03/31/1716′,’04/14/1716′,’04/09/1716′,1,1008);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’03/31/1716′,’04/14/1716′,’04/03/1716′,8,1000);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’03/31/1716′,’04/07/1716′,’04/08/1716′,4,1003);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’03/31/1716′,’04/14/1716′,’04/09/1716′,8,1001);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/05/1716′,’04/12/1716′,’04/13/1716′,10,1005);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/05/1716′,’04/12/1716′,’04/08/1716′,5,1002);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/05/1716′,’04/12/1716′,’04/06/1716′,4,1009);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/05/1716′,’04/19/1716′,’04/09/1716′,2,1003);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/05/1716′,’04/19/1716′,’04/05/1716′,6,1005);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/05/1716′,’04/12/1716′,’04/11/1716′,7,1008);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/08/1716′,’04/15/1716′,’04/12/1716′,1,1002);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/10/1716′,’04/24/1716′,’04/19/1716′,8,1000);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/11/1716′,’04/18/1716′,’04/19/1716′,5,1001);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/11/1716′,’04/18/1716′,’04/13/1716′,9,1004);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/13/1716′,’04/27/1716′,’04/14/1716′,3,1003);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/14/1716′,’04/21/1716′,’04/22/1716′,3,1002);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/14/1716′,’04/28/1716′,’04/14/1716′,3,1005);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/14/1716′,’04/21/1716′,’04/17/1716′,9,1009);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/16/1716′,’04/23/1716′,’04/23/1716′,7,1008);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/16/1716′,’04/23/1716′,’04/21/1716′,1,1006);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/16/1716′,’04/23/1716′,’04/19/1716′,9,1008);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/16/1716′,’04/23/1716′,’04/19/1716′,2,1002);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/21/1716′,’04/28/1716′,’04/29/1716′,5,1000);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/21/1716′,’04/28/1716′,’04/27/1716′,4,1004);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/21/1716′,’05/04/1716′,’04/26/1716′,2,1005);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/21/1716′,’05/04/1716′,’04/26/1716′,2,1006);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/22/1716′,’05/05/1716′,’04/30/1716′,8,1001);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/22/1716′,’04/29/1716′,’04/26/1716′,6,1001);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/22/1716′,’04/29/1716′,’04/30/1716′,2,1007);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/23/1716′,’05/06/1716′,’04/30/1716′,1,1007);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/23/1716′,’04/30/1716′,’04/26/1716′,8,1001);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/23/1716′,’04/30/1716′,’04/23/1716′,8,1004);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/23/1716′,’04/30/1716′,’05/01/1716′,4,1009);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/24/1716′,’05/07/1716′,’04/25/1716′,6,1006);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/24/1716′,’04/30/1716′,’04/28/1716′,7,1008);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/28/1716′,’05/04/1716′,’05/01/1716′,4,1000);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/28/1716′,’05/04/1716′,’04/30/1716′,6,1003);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/28/1716′,’05/04/1716′,’05/06/1716′,10,1003);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/28/1716′,’05/04/1716′,’05/05/1716′,4,1009);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/28/1716′,’05/04/1716′,’04/30/1716′,8,1007);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/29/1716′,’05/05/1716′,’05/05/1716′,10,1001);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/29/1716′,’05/05/1716′,’05/07/1716′,5,1007);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/30/1716′,’05/07/1716′,’05/08/1716′,9,1006);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/30/1716′,’05/07/1716′,’05/04/1716′,6,1002);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/30/1716′,’05/07/1716′,’05/03/1716′,10,1004);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’04/30/1716′,’05/07/1716′,’05/07/1716′,1,1005);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/04/1716′,’05/11/1716′,’05/06/1716′,1,1000);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/04/1716′,’05/11/1716′,’05/04/1716′,5,1006);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/08/1716′,’05/15/1716′,’05/13/1716′,5,1009);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/08/1716′,’05/15/1716′,’05/15/1716′,5,1000);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/08/1716′,’05/15/1716′,’05/08/1716′,6,1001);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/09/1716′,’05/16/1716′,NULL,2,1006);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/09/1716′,’05/16/1716′,’05/11/1716′,4,1003);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/09/1716′,’05/16/1716′,’05/10/1716′,5,1004);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/10/1716′,’05/17/1716′,’05/15/1716′,4,1002);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/10/1716′,’05/17/1716′,’05/11/1716′,1,1007);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/10/1716′,’05/17/1716′,’05/15/1716′,8,1006);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/10/1716′,’05/17/1716′,’05/15/1716′,4,1004);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/15/1716′,’05/22/1716′,’05/18/1716′,3,1007);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/15/1716′,’05/22/1716′,NULL,9,1002);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/15/1716′,’05/22/1716′,’05/16/1716′,1,1003);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/15/1716′,’05/22/1716′,’05/20/1716′,1,1005);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/17/1716′,’05/31/1716′,’05/21/1716′,5,1007);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/17/1716′,’05/24/1716′,’05/17/1716′,4,1008);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/19/1716′,’05/26/1716′,NULL,6,1009);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/24/1716′,’05/31/1716′,NULL,5,1005);
INSERT INTO RESERVATION(CheckOutDate,ReturnDueDate, CheckInDate ,PirateID,ShipNum) VALUES (’05/24/1716′,’05/31/1716′,NULL,4,1004);
–TREASURE rows
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 04/03/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 04/05/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 04/05/1716′,6);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 04/06/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/08/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 04/08/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/09/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 04/09/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 04/09/1716′,2);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 04/11/1716′,7);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 04/12/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/13/1716′,10);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 04/13/1716′,9);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/14/1716′,3);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 04/14/1716′,3);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 04/17/1716′,9);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 04/19/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/19/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/19/1716′,9);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 04/19/1716′,2);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 04/21/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 04/22/1716′,3);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 04/23/1716′,7);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 04/23/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/25/1716′,6);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 04/26/1716′,2);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 04/26/1716′,2);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 04/26/1716′,6);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 04/26/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 04/27/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 04/28/1716′,7);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 04/29/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 04/30/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 04/30/1716′,2);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 04/30/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 04/30/1716′,6);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 04/30/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 05/01/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 05/01/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 05/03/1716′,10);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 05/04/1716′,6);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 05/04/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 05/05/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 05/05/1716′,10);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 05/06/1716′,10);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 05/06/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 05/07/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 05/07/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 05/08/1716′,9);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 05/08/1716′,6);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 05/10/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 05/11/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 05/11/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Gold’,’ 05/13/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 05/15/1716′,5);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 05/15/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Silver’,’ 05/15/1716′,8);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Other’,’ 05/16/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Spices’,’ 05/17/1716′,4);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 05/18/1716′,3);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Jewels’,’ 05/20/1716′,1);
INSERT INTO TREASURE(Category,DateofPlunder,PirateID) VALUES (‘Art’,’ 05/21/1716′,5);
