Homework 1

General Instructions

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 Canvas. 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 *.*
```

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.

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. If the query is date-sensitive, such as question 6, then your answer may differ from my answer, and it may also differ if you have added additional data to your relations. What is important about the sample output is how it is formatted and which attributes we want.

6.8

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

6.10:

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

6.12:

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

6.14: The question 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 |
+------------+
```

6.16

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

6.19-The problem description means take the sum of all currently occupied rooms in the Grosvenor Hotel (i.e, today means today's date). Here are a number of clarifications for this problem:
- If the booking starts on the day in question, then the room is considered occupied.
- The booking ends (i.e., the dateTo column) on the day the guest checks out of the room. The room is considered occupied if the guest spends the night in the room. For example, if the guest checks in on Aug 8 and checks out on Aug 12, then the room is occupied for 4 nights--Aug. 8, 9, 10, and 11. The room is not considered occupied for Aug. 12, unless another guest checks in on that date.
- 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.
- Today means today's date. You can obtain today's date in SQL using the CURRENT_DATE function. See this link for details.
```
+------------+
| SUM(price) |
+------------+
|      89.99 |
+------------+
```

6.22: Name your computed column "count"

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

6.23: Name your computed column "count"

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

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 |
+--------+---------+-----------+--------+