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.

These should be executed in the DB Browser

CREATE TABLE Marina (MarinaID INT PRIMARY KEY, MarinaName CHAR(20), Address CHAR(15), City CHAR(15), State CHAR(2), Zip CHAR(5) );

CREATE TABLE MarinaSlip (SlipID INT PRIMARY KEY, MarinaID INT, SlipNumber CHAR(2), Length DECIMAL(4,0), RentalFee DECIMAL(8,2), BoatName CHAR(50), BoatType CHAR(50), OwnerID CHAR(4) );

CREATE TABLE Owner (OwnerID INT PRIMARY KEY, LastName CHAR(50), FirstName CHAR(20), Address CHAR(15), City CHAR(15), State CHAR(2), Zip CHAR(5) );

CREATE TABLE ServiceCategory (CategoryID INT PRIMARY KEY, CategoryDescription CHAR(255) );

CREATE TABLE ServiceRequest (ServiceID INT PRIMARY KEY, SlipID INT, CategoryID INT, Description CHAR(255), Status CHAR(255), EST_Hours DECIMAL(4,2), SpentHours DECIMAL(4,2), NextServiceDate DATE );

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

INSERT INTO MarinaSlip VALUES (1,'1','A1',40,3800.00,'Anderson II','Sprite 4000','5002'); INSERT INTO MarinaSlip VALUES (2,'1','A2',40,4000.00,'Our Toy','Ray 4025','5004'); INSERT INTO MarinaSlip VALUES (3,'1','A3',40,4200.00,'Escape','Sprite 4000','5007'); INSERT INTO MarinaSlip VALUES (4,'1','B1',30,2400.00,'Gypsy','Dolphin 28','5006'); INSERT INTO MarinaSlip VALUES (5,'1','B2',30,2800.00,'Anderson III','Sprite 3000','5002'); INSERT INTO MarinaSlip VALUES (6,'2','1',25,2000.00,'Bravo','Dolphin 25','5001'); INSERT INTO MarinaSlip VALUES (7,'2','2',25,2000.00,'Chinook','Dolphin 22','5005'); INSERT INTO MarinaSlip VALUES (9,'2','4',30,2900.00,'Mermaid','Dolphin 28','5003'); INSERT INTO MarinaSlip VALUES (10,'2','5',40,4800.00,'Axxon II','Dolphin 40','5008');

INSERT INTO Owner VALUES ('5001','Adney','Bruce and Jean','208 Citrus','Macon','GA','31313'); INSERT INTO Owner VALUES ('5002','Anderson','Bill','18 Wilcox','Atlanta','GA','31044'); INSERT INTO Owner VALUES ('5003','Blake','Mary','2672 Commodore','Macon','GA','31313'); INSERT INTO Owner VALUES ('5004','Elend','Sandy and Bill','462 Riverside','Eatonton','GA','31062'); INSERT INTO Owner VALUES ('5005','Feenstra','Daniel','7822 Coventry','Dublin','GA','32521'); INSERT INTO Owner VALUES ('5006','Juarez','Maria','8922 Oak','Athens','GA','31062'); INSERT INTO Owner VALUES ('5007','Kelly','Alyssa','5271 Waters','Macon','GA','31313'); INSERT INTO Owner VALUES ('5008','Norton','Peter','2811 Lakewood','Lewiston','FL','32765');

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

INSERT INTO ServiceRequest 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','2022-10-12'); INSERT INTO ServiceRequest VALUES (2,5,4,'Fuse on port motor blown on two occasions. Diagnose and repair.','Open','2','0','2022-10-12'); INSERT INTO ServiceRequest VALUES (3,4,1,'Oil change and general routine maintenance (check fluid levels, clean sea strainers etc.).','Service call has been scheduled.','1','0','2022-12-16'); INSERT INTO ServiceRequest VALUES (4,1,2,'Engine oil level has been dropping drastically. Diagnose and repair.','Open','2','0','2023-04-13'); INSERT INTO ServiceRequest VALUES (6,10,4,'Electric-flush system periodically stops functioning. Diagnose and repair.','Open','3','0','2022-12-31'); INSERT INTO ServiceRequest VALUES (9,7,6,'Canvas severely damaged in windstorm. Order and install new canvas.','Open','8','0','2023-4-16'); INSERT INTO ServiceRequest VALUES (10,2,8,'Install new GPS and chart plotter','Scheduled','7','0','2022-09-17'); INSERT INTO ServiceRequest 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 ServiceRequest 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','2022-11-13'); INSERT INTO ServiceRequest 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','2022-11-12'); INSERT INTO ServiceRequest VALUES (14,7,5,'Owner accident caused damage to forward portion of port side.','Technician has scheduled repair.','6','0','2023-3-13'); INSERT INTO ServiceRequest 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','2022-10-17');

After you have run the statements, you now have the Middle Georgia Marina database in your student account database engine

Use DB Browser to write queries for the following 9 questions and return it back showing your txt. document.

1. Return the next service date, slip ID, description, and status for all service requests with a next service date in 2023.
2. Return the slip ID, service description, status, and category description for all service requests that are engine repair, fiberglass repair, or canvas repair. You must use the IN operator in your query.
3. 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 April 2023 in alphabetical order by owner name.
4. List the owner names, boat names, rental fees, and marina name for all boats with rental fees more than 2500 and not more than 4500 in alphabetical order by owner within each marina.
5. List the owner name (in alphabetical order), boat name, and rental fee for all boats with a fee less than 3000 or owned by someone from Macon, GA who has a Sprite 4000 boat type.
6. List the owner names, boat names, and category descriptions for all service requests that are not currently open.
7. List the boat type and description for service requests on any Dolphin model boat
8. Write a Question that requires a GROUP BY clause in the query to answer it. Include the SELECT statement
9. Write a question that requires a GROUP BY clause while also using the HAVING clause in the query to answer it. Include the SELECT statement