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

Queries
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 20 times.
8. Display the names of guests that have stayed at the hotel more than 10 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.