Project

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). Directions for creating and running SQL files are available in the Assignments and Exams page in Blackboard (in the Project 2 group of files).All column headings must show in their entirety.

Be sure to include a cover sheet with your full name, section, and date submitted.

1. Change the Room Num to 321 with a Rate Type of "C" and Rate Amt of $110 for Res 1010.

2. Change the Cust Type for Customer 120 to "C."

3. Add 3 new rooms:
Room Num Room Type
301 D
303 D
304 KS

4. Add the following rooms to reservation 1005:
Room Num Rate Type Rate Amt
303 W $119
304 S $149

5. Add 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__ ___ CustType
Susan White 214-555-2020 C

Note: Commit all changes above before proceeding to the next step.

6. List the customer ID, first name, and last name for all customers for whom no phone number exists. Sort the output by customer ID.

7. List the average rate amount for all rows in the ResDetailtable. Use AvgRate as the column heading.

8. List the count of unique room numbersin the Res Detail table. Use RoomResCount as the column heading.

9. List the RoomType and Count of rooms in eachtype. Use the following column headings: RoomType, RoomCount. Hint: Use a GROUP BY clause.

10. List reservation number, check-in date, check-out date, and the total number of roomsreserved for each reservation. Use the following column headings: ResNum, CheckIn, CheckOut, RoomCount. Hint: Use a GROUP BY clause.

11. List the customer ID, customer first name, customer last name, and count of reservationsfor eachcustomer. 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. 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 reservedfor 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 reservationnumber, 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 eachreservation. Sortby 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.

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 reservedwith 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 Resnumber, 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 inreservation 1005. Show the rate amount formatted as currency and use the following column headings: ResNum, RmNum, RoomType, RateType, Rate. Hint: Use a nested SELECT.

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 ratefor all rooms reserved. Sort by room number. Hint: use a nested SELECT.

22. List the unique room number, room type description, rate type description, and rate amount for all roomsthat have been reservedwith 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.

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.

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.

25. List the RoomNum, RoomTypeDesc, RateTypeDesc, and RateAmt forthe 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.

26. 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.

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 existingrateamounts. Sort by rate amount in descending order, then by room number in ascending order. Hint: use a nested SELECT.

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 Reservations 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. List 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, RmNum, and total charged for each room in reservation 1005. Use the following column headings: ResNum, RoomNum, TotalCharge. 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.