HW6: DQL SQL

For all of the below assignments you must first run the scripts provided in D2L in order for your commands to

work in Oracle SQL Developer. These are also the scripts I will be running when grading.

Run the scripts in this order:

1. IS451 HW6_SQL_HOTEL_DDL.sql

2.

IS451_HW6_SQL_HOTEL_INSERTS.sql

Now you will have the full Hotel database with data in all of the tables.

Submit a single sql file with multiple queries in it, one for each assignment below.

Each query should end with a semicolon so that the entire script can be run at once with no errors.

Use a comment above each query to indicate which number of the assignment each query goes with.

1. Write a query that will return all fields for all records in the Hotel table.

2. Write a query that will return all fields in the Reservation table for reservations with children.

3. Write a query that will return hotel name, city, and country for all Hotels in Italy (country code: ITA).

4. Write a query that will return hotel ID, hotel name, and amenity name for all hotels that have any kind of

pool amenity

5.

Write a query for the marketing department who wants the email addresses of all guests who have

stayed at any hotels in the state of Florida.

6. Write a query for the guest loyalty department who wants to identify guests booking the most profitable

reservations. For every reservation with a total price larger than $15,000, they want the guest's first and

last name, email address, and the name, city, and country for the hotel where they stayed.