You are a client service engineer for a medium size Managed Service Provider (MSP) in Macon, GA. Your company provides IT/IS services to small and medium sized companies. One of your clients is Middle Georgia Marinas. Middle Georgia Marinas owns boat marinas on Lake Sinclair and Lake Oconee. As part of your services to them, you host and manage a small database. You have provided them a front-end interface to enter data into the database but they rely on you for ad hoc queries. In this assignment, you will provide them information by writing SQL queries based on their requests.

1. Display the slip ID, description, status, and next service date for all service requests with a next service date before July 17, 2017. 2. Display the owner names of owners with boats in the Sinclair Marina. 3. Display the owner names, boat names, and slip ID for all the boats in the Oconee Marina. 4. Display the owner names, boat names, and marina name for all the boats that are longer than 30 feet. 5. Display the service category description, service description, status, boat name, and owner names for all the boats with an Open service request status whose next service date is in December 2022 in alphabetical order by owner name. 6. List the owner names, boat names, rental fees, and marina name for all boats with rental fees more than 2000 and not more than 3600 in alphabetical order by owner within each marina. 7. List the owner name (in alphabetical order), boat name, and rental fee for all boats with a fee less than 2000 or owned by someone from Macon, GA who has a Sprite 4000 boat type. 8. List the owner names, boat names, and category descriptions for all service requests that are not currently open. 9. List the boat type and description for service requests on any Dolphin model boat

Table Inserts

CREATE TABLE Marina (MARINA_NUM CHAR(4) PRIMARY KEY, Name CHAR(20), Address CHAR(15), City CHAR(15), State CHAR(2), Zip CHAR(5) );

CREATE TABLE Marina_Slip (Slip_ID DECIMAL(4,0) PRIMARY KEY, Marina_Num CHAR(4), Slip_Num CHAR(4), Length DECIMAL(4,0), Rental_Fee DECIMAL(8,2), Boat_Name CHAR(50), Boat_Type CHAR(50), Owner_Num CHAR(4) );

CREATE TABLE Owner (Owner_Num CHAR(4) PRIMARY KEY, Last_Name CHAR(50), First_Name CHAR(20), Address CHAR(15), City CHAR(15), State CHAR(2), Zip CHAR(5) );

CREATE TABLE Service_Category (Category_Num DECIMAL(4,0) PRIMARY KEY, Category_Description CHAR(255) );

CREATE TABLE Service_Request (Service_ID DECIMAL(4,0) PRIMARY KEY, Slip_ID DECIMAL(4,0), Category_Num DECIMAL(4,0), Description CHAR(255), Status CHAR(255), EST_Hours DECIMAL(4,2), Spent_Hours DECIMAL(4,2), Next_Service_Date DATE );

INSERT INTO Marina VALUES ('1','Sinclair Marina','108 2nd Ave.','Milledgeville','GA','32273'); INSERT INTO Marina VALUES ('2','Oconee Marina','283 Lake Rd','Greensboro','GA','32274');

INSERT INTO Marina_Slip VALUES (1,'1','A1',40,3800.00,'Anderson II','Sprite 4000','AN75'); INSERT INTO Marina_Slip VALUES (2,'1','A2',40,3800.00,'Our Toy','Ray 4025','EL25'); INSERT INTO Marina_Slip VALUES (3,'1','A3',40,3600.00,'Escape','Sprite 4000','KE22'); INSERT INTO Marina_Slip VALUES (4,'1','B1',30,2400.00,'Gypsy','Dolphin 28','JU92'); INSERT INTO Marina_Slip VALUES (5,'1','B2',30,2600.00,'Anderson III','Sprite 3000','AN75'); INSERT INTO Marina_Slip VALUES (6,'2','1',25,1800.00,'Bravo','Dolphin 25','AD57'); INSERT INTO Marina_Slip VALUES (7,'2','2',25,1800.00,'Chinook','Dolphin 22','FE82'); INSERT INTO Marina_Slip VALUES (9,'2','4',30,2500.00,'Mermaid','Dolphin 28','BL72'); INSERT INTO Marina_Slip VALUES (10,'2','5',40,4200.00,'Axxon II','Dolphin 40','NO27');

INSERT INTO Owner VALUES ('AD57','Adney','Bruce and Jean','208 Citrus','Macon','GA','31313'); INSERT INTO Owner VALUES ('AN75','Anderson','Bill','18 Wilcox','Atlanta','GA','31044'); INSERT INTO Owner VALUES ('BL72','Blake','Mary','2672 Commodore','Macon','GA','31313'); INSERT INTO Owner VALUES ('EL25','Elend','Sandy and Bill','462 Riverside','Eatonton','GA','31062'); INSERT INTO Owner VALUES ('FE82','Feenstra','Daniel','7822 Coventry','Dublin','GA','32521'); INSERT INTO Owner VALUES ('JU92','Juarez','Maria','8922 Oak','Athens','GA','31062'); INSERT INTO Owner VALUES ('KE22','Kelly','Alyssa','5271 Waters','Macon','GA','31313'); INSERT INTO Owner VALUES ('NO27','Norton','Peter','2811 Lakewood','Lewiston','FL','32765');

INSERT INTO Service_Category VALUES (1,'Routine engine maintenance'); INSERT INTO Service_Category VALUES (2,'Engine repair'); INSERT INTO Service_Category VALUES (3,'Air conditioning'); INSERT INTO Service_Category VALUES (4,'Electrical systems'); INSERT INTO Service_Category VALUES (5,'Fiberglass repair'); INSERT INTO Service_Category VALUES (6,'Canvas installation'); INSERT INTO Service_Category VALUES (7,'Canvas repair'); INSERT INTO Service_Category VALUES (8,'Electronic systems (radar, GPS, autopilots, etc.)');

INSERT INTO Service_Request VALUES (1,1,3,'Air conditioner periodically stops with code indicating low coolant level. Diagnose and repair.','Technician has verified the problem. Air conditioning specialist has been called.','4','2','2010-07-12'); INSERT INTO Service_Request VALUES (2,5,4,'Fuse on port motor blown on two occasions. Diagnose and repair.','Open','2','0','2010-07-12'); INSERT INTO Service_Request VALUES (3,4,1,'Oil change and general routine maintenance (check fluid levels, clean sea strainers etc.).','Service call has been scheduled.','1','0','2021-07-16'); INSERT INTO Service_Request VALUES (4,1,2,'Engine oil level has been dropping drastically. Diagnose and repair.','Open','2','0','2021-07-13'); INSERT INTO Service_Request VALUES (6,10,4,'Electric-flush system periodically stops functioning. Diagnose and repair.','Open','3','0','2022-12-31'); INSERT INTO Service_Request VALUES (9,7,6,'Canvas severely damaged in windstorm. Order and install new canvas.','Open','8','0','2021-07-16'); INSERT INTO Service_Request VALUES (10,2,8,'Install new GPS and chart plotter','Scheduled','7','0','2021-07-17'); INSERT INTO Service_Request VALUES (11,2,3,'Air conditioning unit shuts down with HHH showing on the control panel.','Technician not able to replicate the problem. Air conditioning unit ran fine through multiple tests. Owner to notify technician if the problem recurs.','1','1','2022-12-31'); INSERT INTO Service_Request VALUES (12,4,8,'Both speed and depth readings on data unit are significantly less than the owner thinks they should be.','Technician has scheduled appointment with owner to attempt to verify the problem.','2','0','2021-07-16'); INSERT INTO Service_Request VALUES (13,8,2,'Customer describes engine as making a clattering sound.','Technician suspects problem with either propeller or shaft and has scheduled the boat to be pulled from the water for further investigation.','5','2','2021-07-12'); INSERT INTO Service_Request VALUES (14,7,5,'Owner accident caused damage to forward portion of port side.','Technician has scheduled repair.','6','0','2021-07-13'); INSERT INTO Service_Request VALUES (15,10,7,'Canvas leaks around zippers in heavy rain. Install overlap around zippers to prevent leaks.','Overlap has been created. Installation has been scheduled.','8','3','2021-07-17');