Question

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

Complete and print your statements and results by spooling your file (with echo on from Oracle if available).

23] 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.

Answer 1

SELECT distinct R1.RoomNum as RmNum, R2.RoomTypeDesc as RmType, R3.RateTypeDesc as RateType,

R1.RateAmt as RateAmt

FROM ResDetail R1, RoomType R2, RateType R3, Room R4

WHERE R1.RoomNum = R4.RoomNum AND R4.RoomType = R2.RoomType

AND R1.RateType = R3.RateType AND R1.RateAmt > 115;