Create a text file (with a .sql extension) containing SQL commands that will:
- (2 pts) Establish a schema called hoteldb and set the search path to
- (8 pts) Create tables to implement the hotel database you've been working in
for the last two assignments.
- (8 pts) Populate the tables with at least 7 reservations with additional
corresponding information as follows:
- one customer should have three separate reservations with
two payment methods (one payment method for one stay and another
payment method for the other two stays)
- the data should include stays with children and without children
- should have multiple customers staying on same day as well as
days with only one customer
- should have rooms that have not been used and rooms that have
been used on multiple nights
- should have a single customer with multiple reservations on
the same day
- Write the following select queries:
- (2 pts) a select query that will display (in order of date and then
room number) customer name and amount paid for room.
- (2 pts) same as above except group by customer showing the total
the customer paid rather than per-room
- (2 pts) list of rooms that haven't been reserved on a given date
- (2 pts) Demonstrate a delete query (of your choice).
- (2 pts) Demonstrate an update query (of your choice).
Once you have completed the document upload your hoteldb.sql file into canvas.