You are to prepare SQL query statements that will provide answers to the following 12 requests

for information.

1. Create Guests, Room and Booking tables as described in page 3. Identify PRIMARY KEYS

and NUT NULL fields.

2. Insert 10 records in each table.

3. List all bookings during June 2016 of guests that are between 18 and 30 years old. Show the

full name, sex, room number and age.

4. Show the number of bookings, for each month of the 2016 calendar year.

5. 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.

6. 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.

7. List the details of rooms which have been booked more than 5 times.

8. Display the names of guests that have stayed at the hotel more than 4 times.

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

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 9, show the postcode that has the most guests.

12. 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.

Read less