Complete and print the following SQL transactions. Log your statements and resul

Question

Complete and print the following SQL transactions. Log your statements and results by spooling your file (with echo on from Oracle if available).

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.

ResDetail Customer CustlD CustFName CustLName CustPhone CustType LoyaltylD Reservation ResID CheckinDate CheckOutDate CustID AgentID ReslD Room CustType CustType CustTypeDesc RoomNum RateType RateAmt RoomType Agent AgentlD AgentFName AgentLName AgentType RateType RateType RateTypeDesc RoomType RoomType RoomTypeDesc AgentType AgentType AgentTypeDesc

Explanation / Answer

Solution:

Query:

SELECT RoomNum, ResID, RoomType, RoomTypeDesc, RateType, RateTypeDesc, RateAmt FROM Reservation INNER JOIN ResDetail ON Reservation .ResID = ResDetail .ResID INNER JOIN Room ResDetail .RoomNum= Room.RooNum INNER JOIN RateType ON ResDetail .RateType = RateType.RateType INNER JOIN RoomType ON Room.RoomType= RoomType.RoomType GROUP BY (SELECT MIN(RateAmt) AS currency FROM ResDetails) ORDER BY RateAmt 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

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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