Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

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

ID: 3712467 • 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

28. List the count of loyalty customers. Use “LoyaltyCount” as the column heading.

29

List the AgentID, AgentFName, AgentLName, and count of reservations for each agent. Combine the first and last names into one column, and sort by count in descending order. Use the following column headings: AgentID, Agent, ResCount. Hint: use a GROUP BY clause.

30

List the ID, first name, last name, and count of Reservtions for the agent(s) who have processed more than one reservation. Sort by count in descending order, then by agent ID in ascending order. Use the following column headings: AgentID, FirstName, LastName, ResCount. Hint: use a GROUP BY clause and a HAVING clause.

31

List the customer ID, first name, and last name of all customers whose first or last names start with the letter ‘W.’ Sort by last name and use the following column headings: Customer_ID, First_Name, Last_Name.

32

4List the customer ID, first name, and last name for any customers who do not have any reservations. Use the following column headings: CustID, FirstName, LastName. Hint: use a Nested Select.

33

List the ResNum and total charged for each room in reservation 1005. Use the following column headings: ResNum, Nights, Total. Hint: You will need to calculate the number of nights to calculate the total charged for each room, but you will not show the number of nights in your output.

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

Solution:

Note: 28 and 29 are answered, please repost others.

28)

SELECT COUNT(DISTINCT LoyaltyID) AS LoyaltyCount FROM Customer

Explanation:

The count of the LoyaltyID is counted and displayed as LoyaltyCount, DISTINCT is used only to make sure that repetitive customers are not counted.

29)

SELECT AgentID, CONCAT(AgentFName, AgentLName) AS name FROM Agent_abc INNER JOIN Reservation_abc ON Agent_abc.AgentID= Reservation_abc.AgentID GROUP BY AgentID ORDER BY AgentID, Agent, ResCount DESC

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Using Mysql I\'m supposed to.. 1. Create a new database/schema 2. Create tables
Question #3938520
Using Mysql write a script that will do the following: Also show the output sepa
Question #3699195
Using Mysql write a script that will do the following: Also show the output sepa
Question #3737249
Using NASM, Assembly language programming, (.ASM) Exercise 3: Characters manipul
Question #3603951
Using NASM, using jz, cmp, flags, loops also use the register 32 bit Task: Desig
Question #3588316
Using NASM, using jz, cmp, flags, loops also use the register 32 bit, ****** Ple
Question #3588949
Using NPV, evaluate an investment in a hog farrow-to-finish enterprise. It is a
Question #2783547
Using NPV, evaluate an investment in a hog farrow-to-finish enterprise. It is a
Question #2783548
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
Using Oracle, access the tables you created in Project 2 and complete the follow
Next
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.