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