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

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.ResID as ResNum, R1.RoomNum as RoomNum, R2.RoomTypeDesc as RoomTypeDesc,

R3.RateTypeDesc as RateTypeDesc, R1.RateAmt as RateAmt

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

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

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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