Question

We have provided you the Hotel database to be used with SQLite DBMS. You should use this database in SQLite to extract the necessary information as per the following query requirements.

The sqlite script is based on the following relational schema:

•Hotel (hotelNo, hotelName, city)

• Room (roomNo, hotelNo, type, price)

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

•Guest (guestNo, guestName, guestAddress)

Note the following details of the hotel database;

•Hotel contains hotel details and hotelNo is the primary key;

•Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;

•Booking contains details of bookings and (hotelNo,guestNo,dateFrom) forms the primary key;

•Guest contains guest details and guestNo is the primary key.

Write an SQLite script for querying data.

•List hotelNo, type and price of each double or deluxe room with a price more than $99.

•List hotelNo who have more than 2 double rooms.

•List number of different guests who visited Ridge Hotel.

•What is the total income from bookings for the Grosvenor Hotel? .

•List all the guests who have stayed in a hotel.

Task 3 [3 marks]

Perform the following tasks.

•Write commands to insert rows in each of the Hotel database tables .

•Write a command to delete the row you inserted in the table Guest .

•Write a command to update the price of all rooms by 10% (1 mark).

Answer 1

1. List hotelNo, type and price of each double or deluxe room with a price more than $99(2 marks).

Answer: INNER JOIN is required here, so joining of 2 tables, Hotel and Room is required on hotelNo to list the hotelNo, type and price with roomtype as double or deluxe.

select hotelNo, type, price from

Hotel JOIN Room on Hotel.hotelNo = Room.HotelNo

where (Room.type= 'double' OR Room.type='deluxe') and price > 99;

2. List hotelNo who have more than 2 double rooms(2 marks).

Answer: GROUPBY clause is required here to group rows based on Room.hotelNo and aggregate function count(hotelNo) to identify hotel with more than 2 double rooms.

select hotelNo from Room where roomType='double' GROUP BY hotelNo HAVING COUNT(hotelNo) > 2;

3. List number of different guests who visited Ridge Hotel(3 marks).

Answer: Subquery is required to identify hotelNo where hotel name is Ridge in Hotel table. Aggregate function COUNT() is required here to identify the number of guests from Booking table.

select COUNT(guestNo) from Booking where hotelNo = (select hotelNo from Hotel where hotelNo='Ridge');

4. What is the total income from bookings for the Grosvenor Hotel? (4 marks).

Answer: Here first subquery is required to identify the HotelNo with hotelName as "Grosvenor". Secondly aggregate function SUM() is required to identify the total income from bookings.

select SUM(Room.price) from Booking Join Room on Booking.hotelNo = Room.hotelNo where

Booking.hotelNo=(select Room.HotelNo from Room Join Hotel on Hotel.hotelNo=Room.hotelNo where Hotel.hotelName = 'Grosvenor');

5. List all the guests who have stayed in a hotel(4 marks).

Answer: INNER JOIN is required on Guest and Booking table on guestNo to identify the list of guestNo, guestName and keyword DISTINCT is required to identify the unique Guest.

select distinct Guest.guestNo, Guest.guestName from Guest JOIN Booking on Guest.guestNo = Booking.guestNo;

As per Chegg guidelines, when multiple questions are part of one single question, initial 4 questions needs to be answered, I have answered the first part of question with 5 sub questions.