The following relations form part of a database held in a RDBMS: Hotel(HotelNo,

Question

The following relations form part of a database held in a RDBMS:
Hotel(HotelNo, Name, Location)
Room (RoomNo, HotelNo, Type, Price)
Booking(HotelNo, GuuestNo, DateFrom, DateTo, RoomNo)
Guest (GuestNo, Name, Address)


Queries:

8.List all guests(their names)currently staying at the ABC Hotel.
9.List the number of rooms in each hotel.
10.List the number of rooms in each hotel in Atlanta.

Updates
1. Increase the price of all single rooms by 10%
2. Increase the price of all other rooms by 5%.
3. Change the address of the guest “John Smith” to 111 Elm Street, Boston, MA 12345.

Explanation / Answer

Dear.... 8.List all guests(their names)currently staying at the ABC Hotel. SELECT * FROM Guest WHERE guestNo IN (SELECT guestNo FROM Booking WHERE dateFrom = SYSDATE OR dateTo IS NULL) AND hotelNo = (SELECT hotelNo FROM Hotel WHERE Name = 'ABC Hotel’)); 9.List the number of rooms in each hotel. SELECT hotelNo, COUNT(roomNo) AS Room_Count FROM Room r, Hotel h WHERE r.hotelNo = h.hotelNo GROUP BY hotelNo; 10.List the number of rooms in each hotel in Atlanta. SELECT hotelNo, COUNT(roomNo) AS Room_Count FROM Room r, Hotel h WHERE r.hotelNo = h.hotelNo AND city = 'Atlanta' GROUP BY hotelNo; Updates 1. Increase the price of all single rooms by 10% UPDATE Room SET price = price*1.10 WHERE TYPE =’single’; 2. Increase the price of all other rooms by 5%. UPDATE Room SET price = price*1.05 WHERE TYPE =’double’; Or Update room SET price = CASE WHEN (type= ‘double’) THEN price *1.05 WHEN (type=’suite’) THEN price *1.05 WHEN (type=’Delux’) THEN price *1.05 ELSE price END 3. Change the address of the guest “John Smith” to 111 Elm Street, Boston, MA 12345. UPDATE GUEST SET address=’ 111 Elm Street, Boston, MA 12345.’ Where name=’ John Smith’

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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