16

List the customer type, customer type description, and count of customers for each customer type. Use the following column headings: CustType, Description, Count. Sort by count in descending order. Hint: use a GROUP BY clause.

17

List the ResNum, RoomNum, RoomType, RoomTypeDesc, and Rate Amt for rooms reserved with a rate amount less than or equal to $119. Sort by rate amount in descending order, then by room number in ascending order. Show the rate amount formatted as currency.

18. For each Reservation, list the Res number, check-in date, check-out date, customer ID, customer first name, customer last name, and count of rooms; sort by Res ID. Show the dates formatted as mm-dd-yyyy. Hint: use a GROUP BY clause.

19

List the ResNum, RoomNum, RoomTypeDesc, RateTypeDesc, and RateAmt for the room with the highest rate amount in reservation 1005. Show the rate amount formatted as currency and use the following column headings: ResNum, RmNum, RoomType, RateType, Rate.

20

List the agent type, agent type description, and count of agents for each agent type. Sort by count. Use the following column headings: AgentType, Desc, Count. Hint: use a GROUP BY clause.

21

List the ResNum, RoomNum, RoomTypeDesc, RateTypeDesc, RateAmt, and RateAmt for all room reservations that have a rate amount greater than the average rate for all rooms reserved. Sort by room number. Hint: use a nested SELECT.

Project 2 is below

spool Project2_abc.txt;

set echo on

-- Drop tables DROP TABLE ResDetail_abc; DROP TABLE Reservation_abc; DROP TABLE Customer_abc; DROP TABLE Agent_abc; DROP TABLE AgentType_abc; DROP TABLE RateType_abc; DROP TABLE RoomType_abc; DROP TABLE CustType_abc; DROP TABLE Room_abc;

--Part I

Create TABLE Room_abc( RoomNum CHAR(6), RoomType CHAR(6), NOT NULL PRIMARY KEY (RoomNum), FOREIGN KEY (RoomType) REFERENCES RoomType_abc );

CREATE TABLE CustType_abc( CustType CHAR(6), CustTypeDesc VARCHAR(25), NOT NULL PRIMARY KEY (CustType) );

CREATE TABLE RoomType_abc( RoomType CHAR(6), RoomTypeDesc VARCHAR(25), NOT NULL PRIMARY KEY (RoomType) );

CREATE TABLE RateType_abc( RateType CHAR(6), RateTypeDesc VARCHAR(25), NOT NULL PRIMARY KEY (RateType) );

CREATE TABLE AgentType_abc( AgentType CHAR(6), AgentTypeDesc VARCHAR(25), PRIMARY KEY (AgentType) );

CREATE TABLE Agent_abc( AgentID Number(5), AgentFName VARCHAR(20), AgentLName VARCHAR(20), AgentType CHAR(6), PRIMARY KEY (AgentID), FOREIGN KEY (AgentType) REFERENCES AgentType_abc );

CREATE TABLE Customer_abc( CustID Number(5), CustFName VARCHAR(20), CustLName VARCHAR(20), CustPhone VARCHAR(10), CustType CHAR(6), LoyaltyID Number(5), PRIMARY KEY (CustID), FOREIGN KEY (CustType) REFERENCES CustType_abc );

CREATE TABLE Reservation_abc( ResID Number(5), CheckInDate DATE, CheckOutDate DATE, CustID Number(5), AgentID Number(5), PRIMARY KEY (ResID), FOREIGN KEY (CustID) REFERENCES Customer_abc(CustID), FOREIGN KEY (AgentID) REFERENCES Agent_abc(AgentID) );

CREATE TABLE ResDetail_abc( ResID Number(5), RoomNum Number(5), RateType CHAR(15), RateAmt DECIMAL(5,2), PRIMARY KEY (ResID, RoomNum), FOREIGN KEY (ResID) REFERENCES Reservation_abc , FOREIGN KEY (RoomNum) REFERENCES Room_abc , FOREIGN KEY (RateType) REFERENCES RateType_abc );

DESCRIBE ResDetail_abc; DESCRIBE Reservation_abc; DESCRIBE CustType_abc; DESCRIBE Customer_abc; DESCRIBE AgentType_abc; DESCRIBE Agent_abc; DESCRIBE RateType_abc; DESCRIBE RoomType_abc; DESCRIBE Room_abc;

--Part II-

--Room table

INSERT INTO Room_abc VALUES(224, 'K'); INSERT INTO Room_abc VALUES(225, 'D'); INSERT INTO Room_abc VALUES(305, 'D'); INSERT INTO Room_abc VALUES(409, 'D'); INSERT INTO Room_abc VALUES(320, 'D'); INSERT INTO Room_abc VALUES(302, 'K'); INSERT INTO Room_abc VALUES(501, 'KS'); INSERT INTO Room_abc VALUES(502, 'KS'); INSERT INTO Room_abc VALUES(321, 'K');

--RoomType INSERT INTO RoomType_abc VALUES('K','KingBed'); INSERT INTO RoomType_abc VALUES('D','2Double'); INSERT INTO RoomType_abc VALUES('KS','KingSuite');

--RateType INSERT INTO RateType_abc VALUES('C', 'Corporate'); INSERT INTO RateType_abc VALUES('C', 'Corporate'); INSERT INTO RateType_abc VALUES('S', 'Standard'); INSERT INTO RateType_abc VALUES('W', 'Weekend'); INSERT INTO RateType_abc VALUES('C', 'Corporate'); INSERT INTO RateType_abc VALUES('S', 'Standard'); INSERT INTO RateType_abc VALUES('W', 'Weekend'); INSERT INTO RateType_abc VALUES('W', 'Weekend'); INSERT INTO RateType_abc VALUES('S', 'Standard'); INSERT INTO RateType_abc VALUES('W', 'Weekend'); INSERT INTO RateType_abc VALUES('W', 'Weekend'); INSERT INTO RateType_abc VALUES('W', 'Weekend'); INSERT INTO RateType_abc VALUES('W', 'Weekend');

--Agent INSERT INTO Agent_abc VALUES(20, 'Megan', 'Smith', 'FD'); INSERT INTO Agent_abc VALUES(20, 'Megan', 'Smith', 'FD'); INSERT INTO Agent_abc VALUES(5, 'Janice', 'May', 'T'); INSERT INTO Agent_abc VALUES(14, 'John', 'King', 'FD'); INSERT INTO Agent_abc VALUES(28, 'Ray', 'Schulz', 'T'); INSERT INTO Agent_abc VALUES(20, 'Megan', 'Smith', 'FD'); INSERT INTO Agent_abc VALUES(14, 'John', 'King', 'FD'); INSERT INTO Agent_abc VALUES(14, 'John', 'King', 'FD'); INSERT INTO Agent_abc VALUES(20, 'Megan', 'Smith', 'FD'); INSERT INTO Agent_abc VALUES(5, 'Janice', 'May', 'T'); INSERT INTO Agent_abc VALUES(5, 'Janice', 'May', 'T'); INSERT INTO Agent_abc VALUES(14, 'John', 'King', 'FD'); INSERT INTO Agent_abc VALUES(28, 'Ray', 'Schulz', 'T');

--AgentType INSERT INTO AgentType_abc VALUES('FD', 'FrontDesk'); INSERT INTO AgentType_abc VALUES('FD', 'FrontDesk'); INSERT INTO AgentType_abc VALUES('T', 'Telephone'); INSERT INTO AgentType_abc VALUES('FD', 'FrontDesk'); INSERT INTO AgentType_abc VALUES('T', 'Telephone'); INSERT INTO AgentType_abc VALUES('FD', 'FrontDesk'); INSERT INTO AgentType_abc VALUES('RC', 'RecCenter'); INSERT INTO AgentType_abc VALUES('FD', 'FrontDesk'); INSERT INTO AgentType_abc VALUES('FD', 'FrontDesk'); INSERT INTO AgentType_abc VALUES('T', 'Telephone'); INSERT INTO AgentType_abc VALUES('RC', 'RecCenter'); INSERT INTO AgentType_abc VALUES('T', 'Telephone');

-- Customer INSERT INTO Customer_abc VALUES(85,'Wesley', 'Tanner', '8175551193', 'C', 'Corporate', 323); INSERT INTO Customer_abc VALUES(85,'Wesley', 'Tanner', '8175551193', 'C', 'Corporate', 323); INSERT INTO Customer_abc VALUES(100, 'Breanna', 'Rhodes', '2145559191', 'I','Individual' 129); INSERT INTO Customer_abc VALUES(15, 'Jeff', 'Minner', NULL, 'I', 'Individual', NULL); INSERT INTO Customer_abc VALUES(77, 'Kim', 'Jackson', '8175554911','C', 'Corporate', 210); INSERT INTO Customer_abc VALUES(119, 'Mary', 'Vaughn', '8175552334', 'I', 'Individual' 118); INSERT INTO Customer_abc VALUES(97, 'Chris', 'Mancha', '4695553440', 'I', 'Individual', 153); INSERT INTO Customer_abc VALUES(97, 'Chris', 'Mancha', '4695553440', 'I', 'Individual', 153); INSERT INTO Customer_abc VALUES(100, 'Breanna', 'Rhodes', '2145559191', 'I', 'Individual', 129); INSERT INTO Customer_abc VALUES(85, 'Wesley', 'Tanner', '8175551193', 'C', 'Corporate', 323); INSERT INTO Customer_abc VALUES(85, 'Wesley', 'Tanner', '8175551193', 'C', 'Corporate', 323); INSERT INTO Customer_abc VALUES(28, 'Rennee', 'Walker', '2145559285', 'I', 'Individual', 135); INSERT INTO Customer_abc VALUES(23, 'Shelby', 'Day', NULL, 'i','Individual', NULL);

--CustType INSERT INTO CustType_abc VALUES('C', 'Corporate'); INSERT INTO CustTyoe_abc VALUES('I', 'Individual');

--Reservation INSERT INTO Reservation_abc VALUES(1001,'5-FEB-2018', '7-FEB-2018', 85, 20); INSERT INTO Reservation_abc VALUES(1002, '1-FEB-2018', '3-FEB-2018', 100, 5); INSERT INTO Reservation_abc VALUES(1003,'9-FEB-2018', '11-FEB-2018', 15, 14); INSERT INTO Reservation_abc VALUES(1004, '22-FEB-2018', '23-FEB-2018', 77, 28); INSERT INTO Reservation_abc VALUES(1005, '15-FEB-2018', '18-FEB-2018', 119, 20); INSERT INTO Reservation_abc VALUES(1006, '24-FEB-2018', '26-FEB-2018', 97, 14); INSERT INTO Reservation_abc VALUES(1007, '20-FEB-2018', '25-FEB-2018', 100, 20); INSERT INTO Reservation_abc VALUES(1008, '23-MAR-2018', '25-MAR-2018', 85, 5); INSERT INTO Reservation_abc VALUES(1009, '1-MAR-2018', '4-MAR-2018', 28, 14); INSERT INTO Reservation_abc VALUES(1010, '1-MAR-2018', '3-MAR-2018', 23, 28);

-- ResDetail INSERT INTO ResDetail_abc VALUES(1001,224,'C',120); INSERT INTO ResDetail_abc VALUES(1001,225,'C',125); INSERT INTO ResDetail_abc VALUES(1002,305,'S',149); INSERT INTO ResDetail_abc VALUES(1003,409,'W',99); INSERT INTO ResDetail_abc VALUES(1004,320,'C',110); INSERT INTO ResDetail_abc VALUES(1005,302,'S',139); INSERT INTO ResDetail_abc VALUES(1006,501,'W',119); INSERT INTO ResDetail_abc VALUES(1006,502,'W',119); INSERT INTO ResDetail_abc VALUES(1007,302,'S',139); INSERT INTO ResDetail_abc VALUES(1008,320,'W',89); INSERT INTO ResDetail_abc VALUES(1008,321,'W',99); INSERT INTO ResDetail_abc VALUES(1009,502,'W',129); INSERT INTO ResDetail_abc VALUES(1010,225,'W',129);

SELECT * FROM ResDetail_abc; SELECT * FROM Reservation_abc; SELECT * FROM CustType_abc; SELECT * FROM Customer_abc; SELECT * FROM AgentType_abc; SELECT * FROM Agent_abc; SELECT * FROM RateType_abc; SELECT * FROM RoomType_abc; SELECT * FROM Room_abc;

COMMIT; --Part III-

UPDATE Customer_abc SET CustPhone = '214551234' WHERE CustID = 85;

INSERT INTO Customer_abc VALUES (120, 'Amanda', 'Green',NULL,NULL,NULL);

UPDATE Reservation_abc SET CheckOutDate = '8-FEB-2018' WHERE ResID = 1001;

INSERT INTO Reservation_abc VALUES (1011, '1-MAR-2018', '4-MAR-2018', 120, 14);

UPDATE ResDetail_abc SET RateType = 'C' WHERE ResID = 1003;

UPDATE ResDetail_abc SET RateAmt = 89 WHERE ResID = 1003;

INSERT INTO ResDetail_abc VALUES (1011,224,'W',119);

INSERT INTO ResDetail_abc VALUES (1011,225,'W',119);

COMMIT;

--Part IV

SELECT * FROM ResDetail_abc; SELECT * FROM Reservation_abc; SELECT * FROM Customer_abc; SELECT * FROM Agent_abc; SELECT * FROM AgentType_abc; SELECT * FROM RateType_abc; SELECT * FROM RoomType_abc; SELECT * FROM CustType_abc; SELECT * FROM Room_abc;

set echo off spool off