28. Complete and print your statements and results by spooling your file (with e

Question

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

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

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

SELECT R1.RoomNum as RoomNum, R3.RoomType as RoomType, R2.RoomTypeDesc as RoomTypeDesc, R1.RateAmt as RateAmt

FROM ResDetail R1, RoomType R2, Room R3

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

AND R1.ResID > (SELECT AVG(R.RateAmt) FROM ResDetail R);

ORDER BY R1.RateAmt DESC, R1.RoomNum;

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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