Provide the SQL queries necessary to produce the information requested or to make the modifications requested. Provide only the queries you used; I do not want the query results.. All of the questions refer to the HotelDB database. I have provided a zip file that contains a description of the database along with instructions to create the database in your Postgress DBMS. You do not need to run your queries,

1. Write a query that displays the room numbers and hotel IDs, and rates of all single rooms with rates between 115.00 and 120.00 inclusive. This query will involve a single table. Your query should produce a table with the following columns and headers:

Room Number	Hotel ID	Room Rate

2. Write a query that displays all bookings for hotels 110 and any of the guests with IDs 10066, 10002, 10093, or 10059 that start between the dates of March 3, 2016 and April 4, 2016. This query will involve a single table. our query should produce a table with the following columns and headers:

Guest ID	Room Number	Hotel ID	Start Date	End Date

3. Write a query that shows the number of hotel guests that come from each state. This query will require the use of an aggregate function on a single table. Your query should produce a table with the following columns and headers:

State Of Origin	Number Of Guests

4. Write a query that displays the hotel name stay at by a guest along with that guests name (first and last, concatenated together as one column). This query will require joining three tables. Your query should produce a table with the following columns and headers:

Guests Name (Full Name)	Hotel Name

5. Write a query that displays the longest stay of any guest at the hotel chain. The length of a guests stay is the end date of the stay minus the start date of the stay plus one. Again you'll need to use an aggregate function on a single table. Your query should produce a table with the following columns and headers:

Duration Of Longest Stay (in days)

6. Write a query that displays how many guests have stayed in hotels in each of the states. Count multiple stays by a single guest only once. This query will require an aggregate function and a join of tables. Your query should produce a table with the following columns and headers:

State	Number of guests staying in hotel in this state

7. Write a query that displays how many different hotels that the guest with full name Erasmo Randall has stayed at. If Erasmo Randall has stayed at a hotel more than once, count the hotel only once. This query will require an aggregate function with a join. Your query should produce a table with the following columns and headers:

State	Number of guests stayinh hotels in this state

8. Write a query that displays the following information about the hotel with the most expensive room rate: The name of the hotel, the room number of the room , the type of the room and the room rate. This query will require a join and a nested where query. Your query should produce a table with the following columns and headers:

Hotel Name	Room Number	Type of room	room rate

9. Write a query which displays the list of rooms (room number) at the Bar Harbor Hotel which are available during the period Sept 9, 2017 through Sept 25, 2017. To write this query, note that a room is available during this period if, for all of the room's bookings ( sdi,,edi), either sdi > 09/25/2017 or edi

Room number available at Bar Harbor Hotel during 09/09/2017 through 09/25/2017

10. Write a query to display the name of any guest who has stayed at all of the hotels in the chain. As in class, this query will require that you use a nested query in the where clause the the not exists test. The outer query and nested query must be correlated with the nested query testing the a column from the outer query with a column from the nested query. Your query should produce a table with the following columns and headers:

Guests who have stayed at all of our hotels

11. Write a query that display the names of of guest who have stayed at the Bar Harbor Hotel. Use this query in another query to display a list of names of guests who have not stayed at the Bar Harbor Hotel. You can using the except operator or by using a nested query in the where clause with the not in operator. Your first query should produce a table with the following columns and headers:

Guests who have stayed at Bar Harbor Hotel

Your second query should produce a table with the following columns and headers:

Guests who have not stayed at Bar Harbor Hotel

12. Write a query that displays the full names and phone numbers of guests who have stayed in single rooms at hotels in Massachusetts and Maine and where the guest is from New Hampshire. You query will have to join all of the tables except the States table. Your query should produce a table with the following columns and headers:

Full Name of Guest Phone Number of Guest

13. Write a query that inserts the following booking: guest 10074, hotel 114, room number 303, on startdateAugust 17, 2017 going through August 20, 2017. Will your insert statement enter the data in the Bookings table successfully? Why or why not?

14. Write a query that adds ten dollars (10.00) to the rates for all of the single rooms in hotels in the state of New Hampshire.

15. Write query that removes the information on hotel 105 from the Hotel table. Will your delete statement remove the information successfully? Why or why not?

Description of a Database for a Small, Regional Hotel Chain

The contents of the HotelDB.zip file contain a set of SQL scripts (HotelDBCreate.sql and HotelDBDelete.sql) and a set of PDFs (HotelDBDiagram.pdf and HotelDBDescription.pdf, this file) for a database that keeps track of the information for a small regional hotel chain.

The database is comprised of five tables: States, Hotel, Room, Guest, and Bookings. The database and the five tables (plus a new domain type) can be created on your Postgres server as done in class. Specifically, to create the database on your P drive of your Rivier University account, follow the steps below:

Log into a Computer Science VDI.

Download the HotelDB.zip file to your P drive.

Extract the HotelDB.zip file to P:\HotelDB.

Open a command line window and make sure you are in your P drive. If your command line prompt does not read "P:\", type P: and press the Enter key.

Start your Progres server in your cluster by typing: pg_ctl -D start

where you must replace with the correct folder name for your cluster. You can type the command

pg_ctl -D status to verify the server is running.

To create and populate the HotelDB database on your server, type psql -c "\i HotelDB/HotelDBCreate.sql" postgres

You will see lines for the creation of the new domain and five tables, then a series of five insertion numbers. You should now be able to see the HotelDB database in pgadmin3.

The five tables of the database are describe below:

States: This is a utility table that contains a single column, which is the primary key, that contains the two letter abbreviations for the US states. This table is use as a foreign key reference by other tables to ensure that only legal state abbreviations are used.

Hotel: This table contains information about the hotels in the chain. The columns are the hotel ID, the hotel name, the city that the hotel is in, and the state that the hotel is in. The hotel ID is the primary key. The table has no foreign keys.

Guest: This table contains information about the hotel chain's customers. The columns are the guest ID, the first and last name of the guest, the address, city, and state where the guest resides. The guest's phone number is also part of this table. The guest ID is the primary key. There are no foreign keys.

Room: This table contains an entry for each room in each hotel. For each room, the table has the room number (the first digit of the room number indicates the floor the room is on), the ID of the hotel that the room is part of, the type of the room (single, double, or suite) and the rate per night for the room. The combination of room number and hotel ID form the primary key for the table. The hotel ID is a foreign key referencing the primary key of the Hotel table.

Bookings: This table contains the booking information for the hotel chain. For each booking, the table contains a row with the following information:

the ID of the guest who made the booking

the room number for the booking

the hotel ID of the room

the starting date for the booking.

NormalNo Spaci. Heading 1 Heading 2 Subtitle Subtie L Foreign Kay Foreign Kay MA charc) Ber Harber Hotel 114 ME Grand GLD numarict Hotel test char(2) 112 Kittery Hotel Kttery Me Portsmouth Portsmouth Strawbeery Fuld 10000 0977) 576-7551Jmaica MD Jeri Sexton 182 Route 20 Kenton 12 Street277) 969-7639 EugeeN 10013 DasteIngam234 Homestead(528) 774-5908 Brocktos SD Drive Fereig Key Forsim Key Fereigs Key sh (United States S