IPT - A Virtual Approach - eXercises SQL X #13

The Hotel database contains managment information from a fictitous hotel for your delight and edification.

Hotel Database Structure

You can CREATE this database for yourself by getting the MySQL Script and running it in your copy of PHPMyAdmin

You can use My Query Engine Page (or more interestingly create your own)

Questions:

Write the CREATE TABLE command (without cheating and looking at the script) to define the BOOKINGS table, taking particular care to define your foreign keys.
What is the PRIMARY KEY of the SERVICES table and why is it a good idea. Is there an ALTERNATIVE that would work as a PK using a combination of columns instead of the artificially created one?
Explain the connection between the bookings table and the guests table - be particular in terms of existence, who has to have data first to make sense.

List all room numbers with their rate.
List the names of guests who are listed as VIP visitors.
Which rooms do not have an ocean view?
List all the guest contact details (name address phone) in alphabetical order (based on surname).
What is the rate for the cheapest room?
How many rooms have a colour TV?
List the room numbers that are less than $100 per night and have an ocean view.
A guest requires a room, which will cost no less than $90 per night and no more than $120 per night. Write a query to list all those room numbers and their costs.
How many rooms in each of the categories (1 bed, 2 bed, 3 bed) have ocean views?
For all the guests, list their names and room numbers. Make sure each room number is only listed once for each guest.
List the guest name, room number and total services charge for each booking.
For all those guests who are VIP’s list their name and room numbers where the cost of the room is less than the average cost of all rooms.
For the guest Lin Wong, list the rooms she has stayed in, the rate for each room, and the number of times she has stayed in the room.
List the room number and the number of times it has been booked for all rooms with a view that have been stayed in more than 4 times over the period that this data has been collected.

Suppose someone is on the phone and wants to make a booking for March 3, 3 beds, 2 nights, ocean view and no telly

(a) Identify the PROCESSES an operator of this system need to consider to check if this booking is possible

(b) write the necessary INSERT INTO commands to record such a booking (make up relevent details not already provided)
Suppose we upgrade every room to have a colour telly - what commands would you issue to make this change. Good solutions would find all rooms and add the telly only if it was none
Suppose you are wanting to mailout to all customers that have not stayed with you for a year - identify those costomers and retrieve all the necessary postal information to allow the mailout

Suppose I want to keep an inventory for each room's Minibar. You should assum a standard mix of products is in each minibar, but want to track consumption of each product in each room for the guests that are using the stuff
Suppose you wanted to start a REWARDS program, keeping track of the number of nights spent by guests. If we offer a FREE night for every 5 nights spent in the hotel, how would this be managed/stored.