Display the names of guests that have stayed at the hotel

Question

Background

You will be working with a set of tables for a hotel.

To access these tables in sql create the following synonyms:

SQL> CREATE SYNONYM GUEST FOR VBRUNO.GUEST; SQL> CREATE SYNONYM BOOKING FOR VBRUNO.BOOKING; SQL> CREATE SYNONYM ROOM FOR VBRUNO.ROOM;

Queries

You are to prepare 13 SQL query statements that will provide answers to the following 13 requests for information.

1. List all bookings (using only date_in) during June 2015 of guests that are between 30 and 50 years old. Show the full name, sex, room number and age.

2. Show the number of bookings, for each month of the 2015 calendar year.

3. Management want to know the usage of the hotel based on floors. Write a query that will show how may bookings have been made for each floor.

4. For rooms that have a linked room, show details of the room and the linked room together (hint: self join).

5. Give a breakdown of how many bookings each sex has made in the hotel - ie. How many bookings have been made by males and how many bookings were made by females.

6. List the details of rooms which have been booked more than 20 times.

7. Display the names of guests that have stayed at the hotel more than 10 times.

8. Display details of the room(s) with the most bedrooms in the hotel.

9. Display guests (if there are any) that have never made a booking.

10. The hotel wishes to analyse the origin of it's guests. Write a view that displays a summary of the number of guests that live in each distinct postcode.

11. Using the view in question 10, show the postcode that has the most guests.

12. Display a summary of the number of individual bookings made for each room. If there are any rooms that have never been booked, they should be included and shown with a count of zero.

13. Create your own query. It must include a nested query. Submit the following:
• question your query is answering
• the SQL query
• The mark for this question will depend on the complexity of the query.
• Higher marks will be given for queries that are more complex and/or innovative.
• If you do not provide a description of what question the query is answering, you will get zero for this query.

Table descriptions for the "hotel" database

GUEST

 

Name                  Type

--------------------- ------------

GUEST_NO              CHAR(8)

SURNAME               VARCHAR2(30)

GIVEN                 VARCHAR2(30)

DOB                   DATE

SEX                   CHAR(1)

HOME_PHONE            VARCHAR2(20)

WORK_PHONE            VARCHAR2(20)

FAX                   VARCHAR2(20)

ADDRESS               VARCHAR2(40)

SUBURB                VARCHAR2(40)

STATE                 VARCHAR2(40)

POSTCODE              NUMBER(4)

ROOM

 

 

Name                  Type

--------------------- ------------

ROOM_NO               CHAR(10)

FLOOR_NO              NUMBER(2)

SPA_BATH              CHAR(1)

NUM_BEDS              NUMBER(1)

PRICE                 NUMBER(10,2)

LINKED_ROOM_NO        CHAR(10)

BOOKING

 

 

Name                  Type

--------------------- -----------

BOOKING_NO            CHAR(10)

GUEST_NO              CHAR(8)

ROOM_NO               CHAR(10)

DATE_IN               DATE

DATE_OUT              DATE

Answer 1

This assignment is all about preparing SQL query statements for the following statements. 1) List all the bookings (by using just date_in) throughout June 2015 of guests which are between 30 and 50 years old. Illustrate the full name, sex, room number and age. 2) Illustrate the number of bookings, for each and every month of the year 2015 calendar. 3) Management wishes to know the usage of the hotel based on floors. Write a query which will illustrate how might bookings have been made for each floor. 4) For rooms which have a linked room, represent details of the room and the linked room together. 5) Give a breakdown of how many bookings each sex has made in the hotel, that is, how many bookings have been made via males and how many bookings were prepared by females. 6) List all the details of differ rooms which have been booked more than 20 times.