Creating and Populating Databases due Tue 26 Sep 10:30

\begin{purpose}
This assignment provides practice in using SQL scripts to create, populate, and
modify databases.
\end{purpose}

Description

Create a text file (with a .sql extension) containing SQL commands that will:
  1. (2 pts) Establish a schema called hoteldb and set the search path to it.
  2. (8 pts) Create tables to implement the hotel database you've been working in for the last two assignments.
  3. (8 pts) Populate the tables with at least 7 reservations with additional corresponding information as follows:
    1. 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)
    2. the data should include stays with children and without children
    3. should have multiple customers staying on same day as well as days with only one customer
    4. should have rooms that have not been used and rooms that have been used on multiple nights
    5. should have a single customer with multiple reservations on the same day
  4. Write the following select queries:
    1. (2 pts) a select query that will display (in order of date and then room number) customer name and amount paid for room.
    2. (2 pts) same as above except group by customer showing the total the customer paid rather than per-room
    3. (2 pts) list of rooms that haven't been reserved on a given date

  5. (2 pts) Demonstrate a delete query (of your choice).
  6. (2 pts) Demonstrate an update query (of your choice).

Submission and Grading

Once you have completed the document upload your hoteldb.sql file into canvas.

Quick Links