General Instructions

1. When submitting your homework, please follow the following conventions as the homework will be graded using automatic grading scripts:
   • Each query exercise in the homework should be in a separate .sql file.
   • The name of the sql files should be the number of the question. For example, for this homework, 6.8, 6.10, 6.12, ... would be named as 1.sql, 2.sql, 3.sql, ... respectively.
   • Submit one single Zip file to Blackboard. You can zip all the files in a directory using this command on the Hydra machines:

     zip -r netid.zip *.*
     

     The name of the zip file should be your netid. For example, if your netid is smith1, you would run:

     zip -r smith1.zip *.*
     

1. Use the Hotel.sql file that is included with this homework assignment to create the hotel tables from the Hotel Schema example at the end of Chapter 4. The Hotel.sql file also contains some sample data. You can use these tables to test your queries in part 2 of this problem. This data is incomplete. The existing data will cause at least one of your queries to return an empty result and other queries to return a single tuple. For example, the current data only has one hotel in London and one of the queries asks you to list all the hotels in London. I strongly suggest that you add additional hotels that are located in London so that your query returns multiple tuples. In the past we have had problems with students thinking their queries were correct because they ran them only against the sample data and then being surprised to learn that their queries were written in a way that would only return 1 tuple, no matter how many tuples actually should have satisfied the query.

2. The following exercises. With each exercise I have shown a sample query result so that you know exactly which attributes we want you to print.
   1. 6.8

      +---------+-----------------+--------+
      | hotelNo | hotelName       | city   |
      +---------+-----------------+--------+
      |       1 | Grosvenor Hotel | London |
      +---------+-----------------+--------+
      

   2. 6.10:

      +--------+---------+--------+-------+
      | roomNo | hotelNo | type   | price |
      +--------+---------+--------+-------+
      |    450 |       2 | double | 27.95 |
      |    250 |       2 | family | 32.50 |
      |    110 |       1 | double | 35.00 |
      +--------+---------+--------+-------+
      

   3. 6.12:

      +----------+
      | COUNT(*) |
      +----------+
      |        4 |
      +----------+
      

   4. 6.14: The questin means to say "the total revenue that could be derived from double rooms if they were all booked for a single night." Hence the question refers to the maximum revenue that could be earned from double rooms for a single night.

      +------------+
      | SUM(price) |
      +------------+
      |     362.92 |
      +------------+
      

   5. 6.16

      +--------+-----------+
      | price  | type      |
      +--------+-----------+
      |  89.99 | double    |
      | 109.99 | family    |
      | 135.50 | king      |
      | 899.99 | penthouse |
      |  35.00 | double    |
      +--------+-----------+
      

   6. 6.19-The problem description means take the sum of all currently occupied rooms in the Grosvenor Hotel. Do not hard code the Grosvenor Hotel's id into your query. It is unreasonable to expect an end user, which is what you should assume you are, to know the id of the Grosvenor Hotel. Use the name of the hotel in the query.

      +------------+
      | SUM(price) |
      +------------+
      |      89.99 |
      +------------+
      

   7. 6.22: Name your computed column "count"

      +---------+-------+
      | hotelNo | count |
      +---------+-------+
      |       1 |     5 |
      |       2 |     7 |
      +---------+-------+
      

   8. 6.23: Name your computed column "count"

      +-----------------+-------+
      | hotelName       | count |
      +-----------------+-------+
      | Grosvenor Hotel |     5 |
      +-----------------+-------+
      

   9. 6.28

      Before the update

      +--------+---------+-----------+--------+
      | roomNo | hotelNo | type      | price  |
      +--------+---------+-----------+--------+
      |    100 |       1 | double    |  89.99 |
      |    200 |       1 | family    | 109.99 |
      |    300 |       1 | king      | 135.50 |
      |    500 |       2 | king      | 205.00 |
      |    100 |       2 | penthouse | 249.99 |
      +--------+---------+-----------+--------+
      

      After the update

      +--------+---------+-----------+--------+
      | roomNo | hotelNo | type      | price  |
      +--------+---------+-----------+--------+
      |    100 |       1 | double    |  94.49 |
      |    200 |       1 | family    | 115.49 |
      |    300 |       1 | king      | 142.28 |
      |    500 |       2 | king      | 215.25 |
      |    100 |       2 | penthouse | 262.49 |
      +--------+---------+-----------+--------+