for seniorwriter – Online Assignment Help – savvyessaywriters.net
for seniorwriter – Online Assignment Help – savvyessaywriters.net
Get Information Systems Assignment Help Online
Project 3Using 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 entirety1 pointChange the Room Num to 321 with a Rate Type of “C” and Rate Amt of $110 for Res 1010.1 pointChange the Cust Type for Customer 120 to “C.”3 pointsAdd 3 new rooms:Room Num Room Type301 D303 D304 KS4.1 pointAdd the following rooms to reservation 1005:Room Num Rate Type Rate Amt303 W $119304 S $1492 pointsAdd a new customer to the Customer table. The customer ID should be one more than the max customer ID in the customer table. Hint: Use a nested SELECT and the MAX function. CustFName CustLName ___CustPh__ ___ CustTypeSusan White 214-555-2020 CNote: Commit all changes above before proceeding to the next step.2 pointsList the customer ID, first name, and last name for all customers for whom no phone number exists. Sort the output by customer ID.2 pointsList the average rate amount for all rows in the ResDetail table. Use AvgRate as the column heading.2 pointsList the count of unique room numbers in the Res Detail table. Use RoomResCount as the column heading and sort by Room Num.2 pointsList the RoomType and Count of rooms in each type. Use the following column headings: RoomType, RoomCount. Hint: Use a GROUP BY clause.3 pointsList reservation number, check-in date, check-out date, and the total number of rooms reserved for each reservation. Use the following column headings: ResNum, CheckIn, CheckOut, RoomCount. Hint: Use a GROUP BY clause.4 pointsList 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.11. 3 pointsList 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. 3 pointsList 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. 4 pointsList 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.4 pointsList 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.2 pointsList 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.3 pointsList 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.4 pointsList 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.4 pointsFor 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.4 pointsList 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.3 pointsList 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.4 pointsList 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.3 pointsList 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.2 pointsList 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.3 pointsList 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.4 pointsList 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.3 pointsList 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.4 pointsList 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.2 pointsList the count of loyalty customers. Use “LoyaltyCount” as the column heading.3 pointsList 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.4 pointsList 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.3 pointsList 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.4 pointsList 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.4 pointsList 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 is BELOWspool Project2_abc.txt;set echo on– Drop tablesDROP 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 ICreate TABLE Room_abc(RoomNum CHAR(6),RoomType CHAR(6), NOT NULLPRIMARY KEY (RoomNum),FOREIGN KEY (RoomType) REFERENCES RoomType_abc);CREATE TABLE CustType_abc(CustType CHAR(6),CustTypeDesc VARCHAR(25), NOT NULLPRIMARY KEY (CustType));CREATE TABLE RoomType_abc(RoomType CHAR(6),RoomTypeDesc VARCHAR(25), NOT NULLPRIMARY KEY (RoomType));CREATE TABLE RateType_abc(RateType CHAR(6),RateTypeDesc VARCHAR(25), NOT NULLPRIMARY 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 tableINSERT INTO Room_abcVALUES(224, ‘K’);INSERT INTO Room_abcVALUES(225, ‘D’);INSERT INTO Room_abcVALUES(305, ‘D’);INSERT INTO Room_abcVALUES(409, ‘D’);INSERT INTO Room_abcVALUES(320, ‘D’);INSERT INTO Room_abcVALUES(302, ‘K’);INSERT INTO Room_abcVALUES(501, ‘KS’);INSERT INTO Room_abcVALUES(502, ‘KS’);INSERT INTO Room_abcVALUES(321, ‘K’);–RoomTypeINSERT INTO RoomType_abcVALUES(‘K’,’KingBed’);INSERT INTO RoomType_abcVALUES(‘D’,’2Double’);INSERT INTO RoomType_abcVALUES(‘KS’,’KingSuite’);–RateTypeINSERT INTO RateType_abcVALUES(‘C’, ‘Corporate’);INSERT INTO RateType_abcVALUES(‘C’, ‘Corporate’);INSERT INTO RateType_abcVALUES(‘S’, ‘Standard’);INSERT INTO RateType_abcVALUES(‘W’, ‘Weekend’);INSERT INTO RateType_abcVALUES(‘C’, ‘Corporate’);INSERT INTO RateType_abcVALUES(‘S’, ‘Standard’);INSERT INTO RateType_abcVALUES(‘W’, ‘Weekend’);INSERT INTO RateType_abcVALUES(‘W’, ‘Weekend’);INSERT INTO RateType_abcVALUES(‘S’, ‘Standard’);INSERT INTO RateType_abcVALUES(‘W’, ‘Weekend’);INSERT INTO RateType_abcVALUES(‘W’, ‘Weekend’);INSERT INTO RateType_abcVALUES(‘W’, ‘Weekend’);INSERT INTO RateType_abcVALUES(‘W’, ‘Weekend’);–AgentINSERT INTO Agent_abcVALUES(20, ‘Megan’, ‘Smith’, ‘FD’);INSERT INTO Agent_abcVALUES(20, ‘Megan’, ‘Smith’, ‘FD’);INSERT INTO Agent_abcVALUES(5, ‘Janice’, ‘May’, ‘T’);INSERT INTO Agent_abcVALUES(14, ‘John’, ‘King’, ‘FD’);INSERT INTO Agent_abcVALUES(28, ‘Ray’, ‘Schulz’, ‘T’);INSERT INTO Agent_abcVALUES(20, ‘Megan’, ‘Smith’, ‘FD’);INSERT INTO Agent_abcVALUES(14, ‘John’, ‘King’, ‘FD’);INSERT INTO Agent_abcVALUES(14, ‘John’, ‘King’, ‘FD’);INSERT INTO Agent_abcVALUES(20, ‘Megan’, ‘Smith’, ‘FD’);INSERT INTO Agent_abcVALUES(5, ‘Janice’, ‘May’, ‘T’);INSERT INTO Agent_abcVALUES(5, ‘Janice’, ‘May’, ‘T’);INSERT INTO Agent_abcVALUES(14, ‘John’, ‘King’, ‘FD’);INSERT INTO Agent_abcVALUES(28, ‘Ray’, ‘Schulz’, ‘T’);–AgentTypeINSERT INTO AgentType_abcVALUES(‘FD’, ‘FrontDesk’);INSERT INTO AgentType_abcVALUES(‘FD’, ‘FrontDesk’);INSERT INTO AgentType_abcVALUES(‘T’, ‘Telephone’);INSERT INTO AgentType_abcVALUES(‘FD’, ‘FrontDesk’);INSERT INTO AgentType_abcVALUES(‘T’, ‘Telephone’);INSERT INTO AgentType_abcVALUES(‘FD’, ‘FrontDesk’);INSERT INTO AgentType_abcVALUES(‘RC’, ‘RecCenter’);INSERT INTO AgentType_abcVALUES(‘FD’, ‘FrontDesk’);INSERT INTO AgentType_abcVALUES(‘FD’, ‘FrontDesk’);INSERT INTO AgentType_abcVALUES(‘T’, ‘Telephone’);INSERT INTO AgentType_abcVALUES(‘RC’, ‘RecCenter’);INSERT INTO AgentType_abcVALUES(‘T’, ‘Telephone’);– CustomerINSERT INTO Customer_abcVALUES(85,’Wesley’, ‘Tanner’, ‘8175551193’, ‘C’, ‘Corporate’, 323);INSERT INTO Customer_abcVALUES(85,’Wesley’, ‘Tanner’, ‘8175551193’, ‘C’, ‘Corporate’, 323);INSERT INTO Customer_abcVALUES(100, ‘Breanna’, ‘Rhodes’, ‘2145559191’, ‘I’,’Individual’ 129);INSERT INTO Customer_abcVALUES(15, ‘Jeff’, ‘Minner’, NULL, ‘I’, ‘Individual’, NULL);INSERT INTO Customer_abcVALUES(77, ‘Kim’, ‘Jackson’, ‘8175554911’,’C’, ‘Corporate’, 210);INSERT INTO Customer_abcVALUES(119, ‘Mary’, ‘Vaughn’, ‘8175552334’, ‘I’, ‘Individual’ 118);INSERT INTO Customer_abcVALUES(97, ‘Chris’, ‘Mancha’, ‘4695553440’, ‘I’, ‘Individual’, 153);INSERT INTO Customer_abcVALUES(97, ‘Chris’, ‘Mancha’, ‘4695553440’, ‘I’, ‘Individual’, 153);INSERT INTO Customer_abcVALUES(100, ‘Breanna’, ‘Rhodes’, ‘2145559191’, ‘I’, ‘Individual’, 129);INSERT INTO Customer_abcVALUES(85, ‘Wesley’, ‘Tanner’, ‘8175551193’, ‘C’, ‘Corporate’, 323);INSERT INTO Customer_abcVALUES(85, ‘Wesley’, ‘Tanner’, ‘8175551193’, ‘C’, ‘Corporate’, 323);INSERT INTO Customer_abcVALUES(28, ‘Rennee’, ‘Walker’, ‘2145559285’, ‘I’, ‘Individual’, 135);INSERT INTO Customer_abcVALUES(23, ‘Shelby’, ‘Day’, NULL, ‘i’,’Individual’, NULL);–CustTypeINSERT INTO CustType_abcVALUES(‘C’, ‘Corporate’);INSERT INTO CustTyoe_abcVALUES(‘I’, ‘Individual’);–ReservationINSERT INTO Reservation_abcVALUES(1001,’5-FEB-2018′, ‘7-FEB-2018’, 85, 20);INSERT INTO Reservation_abcVALUES(1002, ‘1-FEB-2018’, ‘3-FEB-2018′, 100, 5);INSERT INTO Reservation_abcVALUES(1003,’9-FEB-2018′, ’11-FEB-2018′, 15, 14);INSERT INTO Reservation_abcVALUES(1004, ’22-FEB-2018′, ’23-FEB-2018′, 77, 28);INSERT INTO Reservation_abcVALUES(1005, ’15-FEB-2018′, ’18-FEB-2018′, 119, 20);INSERT INTO Reservation_abcVALUES(1006, ’24-FEB-2018′, ’26-FEB-2018′, 97, 14);INSERT INTO Reservation_abcVALUES(1007, ’20-FEB-2018′, ’25-FEB-2018′, 100, 20);INSERT INTO Reservation_abcVALUES(1008, ’23-MAR-2018′, ’25-MAR-2018’, 85, 5);INSERT INTO Reservation_abcVALUES(1009, ‘1-MAR-2018’, ‘4-MAR-2018’, 28, 14);INSERT INTO Reservation_abcVALUES(1010, ‘1-MAR-2018’, ‘3-MAR-2018′, 23, 28);– ResDetailINSERT INTO ResDetail_abcVALUES(1001,224,’C’,120);INSERT INTO ResDetail_abcVALUES(1001,225,’C’,125);INSERT INTO ResDetail_abcVALUES(1002,305,’S’,149);INSERT INTO ResDetail_abcVALUES(1003,409,’W’,99);INSERT INTO ResDetail_abcVALUES(1004,320,’C’,110);INSERT INTO ResDetail_abcVALUES(1005,302,’S’,139);INSERT INTO ResDetail_abcVALUES(1006,501,’W’,119);INSERT INTO ResDetail_abcVALUES(1006,502,’W’,119);INSERT INTO ResDetail_abcVALUES(1007,302,’S’,139);INSERT INTO ResDetail_abcVALUES(1008,320,’W’,89);INSERT INTO ResDetail_abcVALUES(1008,321,’W’,99);INSERT INTO ResDetail_abcVALUES(1009,502,’W’,129);INSERT INTO ResDetail_abcVALUES(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_abcSET CustPhone = ‘214551234’WHERE CustID = 85;INSERT INTO Customer_abcVALUES (120, ‘Amanda’, ‘Green’,NULL,NULL,NULL);UPDATE Reservation_abcSET CheckOutDate = ‘8-FEB-2018’WHERE ResID = 1001;INSERT INTO Reservation_abcVALUES (1011, ‘1-MAR-2018’, ‘4-MAR-2018’, 120, 14);UPDATE ResDetail_abcSET RateType = ‘C’WHERE ResID = 1003;UPDATE ResDetail_abcSET RateAmt = 89WHERE ResID = 1003;INSERT INTO ResDetail_abcVALUES (1011,224,’W’,119);INSERT INTO ResDetail_abcVALUES (1011,225,’W’,119);COMMIT;–Part IVSELECT * 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 offspool off
#for seniorwriter
Management Information Systems is one of the various business information systems. It is vital to look at the various kinds of information systems available to businesses to comprehend management information systems better. Savvyessaywriters.net offers management assignment help for all students.
PLACE YOUR ORDER NOW
