Using Oracle, access the tables you created in Project 2 and complete the following SQL transactions. Log your statements and results by spooling your file (with echo on). All column headings must show in their entirety

11A

List the customer ID, customer first name, customer last name, and count of reservations for each customer. Combine the first and last name into one column. Sort by reservation count in descending order, then by customer ID in ascending order. Use the following column headings: CustomerID, CustomerName, ResCount. Hint: Use a GROUP BY clause.

11B

List all rows and all columns from the ResDetail table; sort by ResNum then by Room Num, both in ascending order. Use the following column headings: ResNum, RmNum, RateType, RateAmt.

12.

List the rate type, rate type description, and count of rooms reserved for each rate type. Sort by count in descending order. Use the following column headings: RateType, Description, ResCount. Hint: Use a GROUP BY clause.

13.

List the customer ID, customer first name, customer last name, and customer phone number for all customers. Show the phone number formatted as (###) ###-#### and sort by customer ID. Use the following column headings: Customer_ID, First_Name, Last_Name, Phone.

14

List the reservation number, room number, room type, room type description, rate type, rate type description, and rate amount of the room(s) with the lowest rate amount in each reservation. Sort by rate amount in descending order. Show the rate amount formatted as currency, and use the following column headings: ResNum, RmNum, RmType, RmDesc, RateType, RateDesc, RateAmt. Hint: use a GROUP BY clause and a nested SELECT.

15

List the room number, room type, room type description, rate type, and rate amount for each room for which reservations exist. Sort by room number then by rate amount.

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