Using Oracle, access the tables you created in Project 2 and complete the follow

ID: 3712464 • Letter: U

Question

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

List the unique room number, room type description, rate type description, and rate amount for all rooms that have been reserved with a rate amount greater than $115 (do not include duplicate rows). Sort by rate amount in descending order. Use the following column headings: RmNum, RmType, RateType, RateAmt.

List the room number, room type, and count of reservations for each room number. Be sure to include ALL rooms (not just those for which reservations exist). Sort by count.

List the CustID, CustFName, CustLName, and CustPhone for all “loyalty” customers. Show the phone number formatted as ‘(###) ###-####’ and use the following column headings: CustID, FirstName, LastName, Phone.

25.List the RoomNum, RoomTypeDesc, RateTypeDesc, and RateAmt for the room with the highest rate in Reservation 1005. Show the rate amount formatted as currency, and use the following column headings: Room, RoomType, RateType, Amt. Hint: use a nested SELECT.

List the ResNum, CheckInDate, CheckOutDate, CustID, CustLName, AgentID, and AgentLName for all reservations on or before 2/28/18. Sort by CheckInDate, then by ResNum, both in ascending order.

List the RoomNum, RoomType, RoomTypeDesc, and RateAmt for all rooms that have been reserved at a rate higher than the average rate of all existing rate amounts. Sort by rate amount in descending order, then by room number in ascending order. 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

Explanation / Answer

/*22 List the unique room number, room type description, rate type description, and rate amount for all rooms that have
been reserved with a rate amount greater than $115 (do not include duplicate rows). Sort by rate amount in descending order.
Use the following column headings: RmNum, RmType, RateType, RateAmt.*/
SELECT r.RoomNum AS RmNum,rt.RoomTypeDesc AS RmType,ra.RateTypeDesc AS RateType,rd.RateAmt AS RateAmt FROM Room_abc r INNER JOIN RoomType_abc rt ON r.RoomType=rt.RoomType
INNER JOIN ResDetail_abc rd on r.RoomNum=rd.RoomNum
INNER JOIN RateType_abc ra ON rd.RateType=ra.RateType
WHERE rd.RateAmt>115
ORDER BY rd.RateAmt DESC

/*23 List the room number, room type, and count of reservations for each room number.
Be sure to include ALL rooms (not just those for which reservations exist). Sort by count.*/
SELECT r.RoomNum ,r.RoomType,COUNT(rd.ResID) FROM Room_abc r LEFT JOIN ResDetail_abc rd on r.RoomNum=rd.RoomNum
GROUP BY r.RoomNum ORDER BY COUNT(rd.ResID);

/*24 List the CustID, CustFName, CustLName, and CustPhone for all “loyalty” customers.
Show the phone number formatted as ‘(###) ###-####’ and use the following column headings: CustID, FirstName, LastName, Phone.*/
SELECT CustID ,
CustFName AS FirstName,
CustLName AS LastName,
'(' || SUBSTR(CustPhone) || ') '||SUBSTR(CustPhone, 4, 2)||'-'||CustPhone(data, 6, 4) AS Phone
FROM Customer_abc
WHERE LoyaltyID IS NOT NULL;

/*25.List the RoomNum, RoomTypeDesc, RateTypeDesc, and RateAmt for the
room with the highest rate in Reservation 1005. Show the rate amount formatted as currency,
and use the following column headings: Room, RoomType, RateType, Amt. Hint: use a nested SELECT.*/
SELECT r.RoomNum ,rt.RoomTypeDesc ,ra.RateTypeDesc ,to_char( rd.RateAmt, '$9,999.99' ) FROM Room_abc r INNER JOIN RoomType_abc rt ON r.RoomType=rt.RoomType
INNER JOIN ResDetail_abc rd on r.RoomNum=rd.RoomNum
INNER JOIN RateType_abc ra ON rd.RateType=ra.RateType
WHERE rd.ResID IN (SELECT ResID FROM Reservation_abc)
AND rd.RateAmt=1005;

/*26List the ResNum, CheckInDate, CheckOutDate, CustID, CustLName, AgentID, and AgentLName for all reservations on or before 2/28/18.
Sort by CheckInDate, then by ResNum, both in ascending order.*/
select R.ResID ,R.CheckInDate ,R.CheckOutDate ,c.CustID ,c.CustLName,AgentID ,AgentFName FROM Reservation_abc r INNER JOIN Customer_abc c ON r.CustID=c.CustID
INNER JOIN Agent_abc a ON r.AgentID=a.AgentID
WHERE R.CheckInDate<='28-FEB-2018'
ORDER BY R.CheckInDate,R.ResID ASC;

/*27 List the RoomNum, RoomType, RoomTypeDesc, and RateAmt for all rooms that have been reserved
at a rate higher than the average rate of all existing rate amounts. Sort by rate amount in descending order,
then by room number in ascending order. Hint: use a nested SELECT.*/
SELECT r.RoomNum ,rt.RoomTypeDesc ,ra.RateTypeDesc ,rd.RateAmt FROM Room_abc r INNER JOIN RoomType_abc rt ON r.RoomType=rt.RoomType
INNER JOIN ResDetail_abc rd on r.RoomNum=rd.RoomNum
INNER JOIN RateType_abc ra ON rd.RateType=ra.RateType
GROUP BY r.RoomNum HAVING rd.RateAmt > (SELECT avg(RateAmt) FROM ResDetail_abc)
ORDER BY rd.RateAmt DESC

Navigate

Using Oracle, access the tables you created in Project 2 and complete the follow

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

Using Oracle, access the tables you created in Project 2 and complete the follow

Question

Explanation / Answer

Related Questions

Navigate