Assignment 4 – Database Management, 90-746

Using SQL

Due: Thursday, November 11, 1999

For this assignment, you will create a set of tables, fill them with data, and execute SELECT statements based on them. A description of the sample case and the tables’ structures is given in the TABLE DESCRIPTIONS section of this assignment. The data with which to populate the tables is given in the DATA section and the list of questions from which to generate your SELECT statements is given in the QUESTIONS section. This assignment is based on the exercises in your Connolly and Begg text book at the end of Chapter 13, pp. 437-439.

What to Hand In:

For each table, you must show the SQL CREATE TABLE statement and any ALTER TABLE statements you use to set up your tables along with the results of those statements (i.e., the SQL Plus feedback statement that says “Table Created” or “Table Altered”). You must also hand in a DESCRIBE statement for each one of the tables you create along with the SQL Plus feedback from that statement that shows a list of the columns in the table and their datatypes and lengths.

The best way to capture this information (the CREATE, ALTER, and DESCRIBE statements and their results) is to copy and paste your SQL commands and their results to a Notepad file. When you are done with all the parts of the assignment, you can print out the Notepad file and hand it in.

As an example of what to hand in for the CREATE TABLE portion of the assignment, let’s say you create the TEST3 table. The primary key is a NUMBER with a maximum length of 6. The primary key column name is TEST3COL1. The table has two other columns: TEST3COL2 which is a VARCHAR2 of length 20 and TEST3COL3 which is a DATE. Here is what you would hand in for the creation of this table. It includes a copy of your SQL CREATE TABLE statement, the SQL result saying that the table has been created, your execution of the DESC statement, and its result showing the definition of all the columns in that table.

SQL> create table test3

2 (test3col1 number (6),

3 test3col2 varchar2 (20),

4 test3col3 date,

5 constraint pk_test3

6 primary key (test3col1));

Table created.

SQL> desc test3

Name Null? Type

------------------------------- -------- ----

TEST3COL1 NOT NULL NUMBER(6)

TEST3COL2 VARCHAR2(20)

TEST3COL3 DATE

You will use the data in the DATA section of this assignment to populate the database, i.e., use INSERT and UPDATE statements to insert the rows of values into the tables. You do not need to hand in copies or feedback from any of your INSERT and UPDATE statements.

Once you have inserted all the data into your tables, you will create SELECT statements that will provide answers to the questions listed in the QUESTIONS section of this assignment. For each question, please hand in a copy of the SELECT statement you issue and the SQL Plus feedback. Again, the best way to do this is to copy and paste to a Notepad file. You can include this information in the same Notepad file you use to show your TABLE CREATE statements.

As an example of what to hand in for this portion of the assignment, let’s use the TEST3 table. Suppose it contains the following data:

TEST3COL1 TEST3COL2 TEST3COL3

--------- -------------------- ---------

556677 Bicycle Seat 02-JAN-97

443322 Air Pump 14-JAN-97

889977 Water Bottle 27-JAN-97

If the question is “List all the data in table TEST3,” here is what you would show in your Notepad file. It includes a copy of the question (it’s important that you identify the question your SELECT statement is attempting to answer), your SELECT statement and the results of that SELECT statement.

List all the data in table TEST3.

SQL> select * from test3;

TEST3COL1 TEST3COL2 TEST3COL3

--------- -------------------- ---------

556677 Bicycle Seat 02-JAN-97

443322 Air Pump 14-JAN-97

889977 Water Bottle 27-JAN-97

TABLE DESCRIPTIONS

The following set of tables make up a database that is used by a booking agency to book hotel reservations for their client hotels. Attributes that are underscored make up the primary key for that table. Attributes marked with @ denote a foreign key which refers to the primary key of another of the tables.

In the Room table, Hotel_No is a foreign key which refers to the primary key of the Hotel table. Type of room is a one-character code that refers to smoking (S) or non-smoking (N). Price refers to the price of the room per night.

In the Booking table, Guest_No refers to the primary key of the Guest table; Hotel_No and Room_No combined refer to the primary key of the Room table.

Hotel (Hotel_No, Name, City)

Room (Room_No, @Hotel_No, Type, Price)

Booking (@Hotel_No, @Guest_No, Date_From, Date_To, @Room_No)

Guest (Guest_No, Name, City)

For every table, it is up to you to decide which is the appropriate datatype for each one of the columns based on the column descriptions given above. If you make assumptions that affect the way you define the columns, please note those assumptions in your assignment.

TABLES

Hotel Table

Hotel_No	Name	City
H111	Empire Hotel	New York
H235	Park Place	New York
H432	Brownstone Hotel	Toronto
H498	James Plaza	Toronto
H193	Devon Hotel	Boston
H437	Clairmont Hotel	Boston

Room Table

Room_No	Hotel_No	Type	Price
313	H111	S	145.00
412	H111	N	145.00
1267	H235	N	175.00
1289	H235	N	195.00
876	H432	S	124.00
898	H432	S	124.00
345	H498	N	160.00
467	H498	N	180.00
1001	H193	S	150.00
1201	H193	N	175.00
257	H437	N	140.00
223	H437	N	155.00

Booking Table

Hotel_No	Guest_No	Date_From	Date_To	Room_No
H111	G256	10-AUG-99	15-AUG-99	412
H111	G367	18-AUG-99	21-AUG-99	412
H235	G879	05-SEP-99	12-SEP-99	1267
H498	G230	15-SEP-99	18-SEP-99	467
H498	G256	30-NOV-99	02-DEC-99	345
H498	G467	03-NOV-99	05-NOV-99	345
H193	G190	15-NOV-99	19-NOV-99	1001
H193	G367	12-SEP-99	14-SEP-99	1001
H193	G367	01-OCT-99	06-OCT-99	1201
H437	G190	04-OCT-99	06-OCT-99	223
H437	G879	14-SEP-99	17-SEP-99	223

Guest Table

Guest_No	Name	City
G256	Adam Wayne	Pittsburgh
G367	Tara Cummings	Baltimore
G879	Vanessa Parry	Pittsburgh
G230	Tom Hancock	Philadelphia
G467	Robert Swift	Atlanta
G190	Edward Cane	Baltimore

QUESTIONS

1. List full details of all hotels.

2. List full details of all hotels in New York.

3. List the names and cities of all guests, ordered according to their cities.

4. List all details for non-smoking rooms in ascending order of price.

5. List the number of hotels there are.

6. List the cities in which guests live. Each city should be listed only once.

7. List the average price of a room.

8. List hotel names, their room numbers, and the type of that room.

9. List the hotel names, booking dates, and room numbers for all hotels in New York.

10. What is the number of bookings that started in the month of September?

11. List the names and cities of guests who began a stay in New York in August.

12. List the hotel names and room numbers of any hotel rooms that have not been booked.

13. List the hotel name and city of the hotel with the highest priced room.

14. List hotel names, room numbers, cities, and prices for hotels that have rooms with prices lower than the lowest priced room in a Boston hotel.

15. List the average price of a room grouped by city.