Posted - 04/06/2011 : 17:54:23
| I'm not sure if this would be the right forum but 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 THINK I've created all the relavant 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 the process.
So my though process was that 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?
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?
So for the first query I have a Guest table, a Room type table, then for each type of room I have separate tables for them (3), a guest reservation table, and a reservation table.
I'm assuming I'd somehow have to JOIN these tables together selectively to output the information however I'm having trouble wrapping my head around how I could do that?
For the 2nd query it seems somewhat simpler, I'm assuming I'd have to JOIN the reservation table and the price plan table and somehow get the two columns in one table (check in and check out date to subtract each other to get a duration) and then get that to multiply with the column of the other table which would be price of the specific room type to get a total.
I understand the logistics behind the second query but am a bit confused as to how to reach that end point?
Any suggestions or ideas?