Question

Perform the following (each 3 Points): 1. List full details of all hotels. 2. List full details of all hotels in London 3. List the names and addresses of all guests in London, alphabetically ordered by name. 4. List all double or family rooms with a price below $40 per night, in ascending order of price. 5. List the bookings for which no dateTo has been specified. 6. How many hotels are there? 7. What is the average price of a room? 8. What is the total revenue per night from all double rooms? 9. How many different guests have made bookings for month of April? 10. List the price and type of all rooms at the Grosvenor Hotel. 11. List all guests currently staying at the Grosvenor Hotel (you need to insert at least one new record in order not to return a null value) 12. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied. 13. List the rooms that are currently unoccupied at the Grosvenor Hotel. 14. List the number of rooms in each hotel. 15. List the number of rooms in each hotel in London. 16. What is the lost income from unoccupied rooms at each hotel today? 17. Update the price of all rooms by 5%.

need help MYSQL # 9 to 17 please

Answer 1

Answer:-------------
9.
SELECT COUNT(DISTINCT guestno)
FROM booking WHERE (datefrom <= 2004-04-01’ AND
dateto >= ‘2004-04-30’);
10.
SELECT price, type
FROM room, hotel
WHERE hotel.hotelno = room.hotelno AND hotelname = 'Grosvenor';

11.
SELECT * FROM guest
WHERE guestno IN (SELECT guestno FROM booking WHERE datefrom <= ‘SYSTEM DATE’
AND dateto >= ‘SYSTEM DATE’ AND hotelno = (SELECT hotelno FROM hotel WHERE hotelname = ‘Grosvenor’));   

12.
SELECT (r.hotelno, r.roomno, r.type, r.price)
FROM room r, hotel h
WHERE r.hotelno = h.hotelno AND
h.hotelname = 'Grosvenor’ AND
roomno NOT IN (SELECT roomno FROM booking b, hotel h WHERE (datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’)
AND b.hotelno=h.hotelno AND hotelname = 'Grosvenor');
13.
SELECT r.* FROM room r LEFT JOIN
(SELECT g.guestname, h.hotelno, b.roomno FROM guest g, booking b, hotel h
WHERE g.guestno = b.guestno AND b.hotelno = h.hotelno AND h.hotelname= 'Grosvenor' AND
b.datefrom <= CURRENT_DATE AND b.dateto >= CURRENT_DATE) AS XXX
ON r.hotelno = XXX.hotelno AND r.roomno = XXX.roomno;

14.
SELECT hotelno, COUNT(roomno)
   FROM room
   GROUP BY hotelno;

15.
SELECT r.hotelno, COUNT(roomno)
   FROM room r, hotel h
   WHERE r.hotelno=h.hotelno AND city = 'London'
   GROUP BY r.hotelno;

16.
SELECT SUM(price)
FROM room r, hotel h
WHERE r.hotelno = h.hotelno AND h.hotelname = 'Grosvenor’ AND
roomno NOT IN (SELECT roomno FROM booking b, hotel h
WHERE (datefrom <= ‘SYSTEM DATE’ AND dateto >= ‘SYSTEM DATE’) AND
b.hotelno = h.hotelno AND r.hotelno=b.hotelno
AND r.roomno=b.roomno AND h.hotelname = 'Grosvenor');

17.
UPDATE room
   SET price = price*1.05;