The following tables form part of a database held in a relational DBMS: Hotel (h

Question

The following tables form part of a database held in a relational DBMS:

            Hotel               (hotelNo, hotelName, city)

            Room              (roomNo, hotelNo, type, price)

            Booking           (hotelNo, guestNo, dateFrom, dateTo, roomNo)

            Guest              (guestNo, guestName, guestAddress)

Write the SQL statements for the following:

1. List full details of all hotels in London.

2. List all double or family rooms with a price below £40.00 per night, in ascending order of price.

3. How many hotels are there?

4. What is the total revenue per night from all double rooms?

5. How many different guests have made bookings for August?

6. List the price and type of all rooms at the Grosvenor Hotel.

7. List the number of rooms in each hotel.

8. What is the average number of bookings for each hotel in August?

9. Insert records into each of these tables.

10. Update the price of all rooms by 5%.

Explanation / Answer

Solution:

The first four subparts have been answered as per Chegg guidelines, please repost others.

1)

SELECT * FROM Hotel WHERE city = 'London'

Explanation:

The above query will result in all the rows with all the details of the hotels which are situated in London.

2)

SELECT roomNo FROM Room WHERE (type = 'double' or type = 'family') AND price<40.00 ORDER BY ASC price

Explanation:

ASC is used to order by the price the conditions are used to return the right type and price of the room.

3)

SELECT COUNT(hotelNo) FROM Hotel.

Explanation:

the above query will result in the total number of hotels.

4)

SELECT TOTAL(price) FROM Room WHERE TYPE = 'double'

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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