[iggyntangs]

I have a couple of questions with a database I created for a class, we were supposed to create a sort of mock system for hotel reservations and processes that a made up hotel could go through, complete with multiple tables.

I've created all the relevant tables and put in some mock data into it so that I can test it out and so far so good, however one of the guidelines was to create multiple queries of our choosing that we think would be relevant to typical processes a hotel would go through (e.g. reservations).

So my thought process was to create a query for:

1. If a guest wanted to reserve a certain type of room to see if that type of room is even available or if all are occupied (you know like see if a suite or other type of room was available)?

2. Maybe another one could be to take the time the guest wishes to stay and multiply it by the room rate to get a total $ for their entire stay?

For the 1st query I think I'd have to use 3 of my tables; the room table, reservation table and rental table, which have the following columns:

Room Table: Room_ID, Room Type
Reservation Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_Type
Rental Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_Type

Basically I'd like to figure out a query to find out if a specific room type is available during certain days.

So perhaps an output showing a list of all the rooms (by room_ID) from a certain room_type available between particular check_in and check_out days?

For the 2nd query I think I'd have to use 3 tables again; the room table, price table and reservation table having the following columns:

Room Table: Room_ID, Room_Type, Price_Plan
Price Table: Price_Plan, Rate
Reservation Table: Check_In_Date, Check_Out_Date, Room_Type, Confirmation_No

Basically I'd like to figure out a query to give a total cost of stay by Confirmation_No.

So perhaps an output showing a list of confirmation numbers and then the check_out_date minus the check_in_date to give a duration of stay and then that column multiplied by the corresponding rate and a last column showing a total price?

Any ideas/suggestions of how to do this 'cuz I'm a total n00b?

Looking for ideas for what sort of functions I could/should use to get this started.