CIS 3010: Assignment 1

Due date: 18 September 2013 Value: 15% Please submit the CODE and OUTPUT for all programming questions. You will submit this assignment electronically in the ASSESSMENT area of the Study Desk.

In this assignment, you will use the CAR HIRE database. The CAR HIRE database including appropriate data will be made available on the USQ Oracle server. You can query these tables as if they are in your own schema but you are not permitted to insert or update data in these tables. The specification for the CAR HIRE database is as follows:
The table descriptions appear below, including the column names, data types and the meanings for values in the columns. Familiarise yourself with the tables and the data.
I_CAR

I_CARGROUP

I_MODEL

I_CUSTOMER

I_BOOKING

Question 1 (25 marks)

Write an anonymous block that places a substitution variable (&) into a local variable of type character. You should check the value entered in the local variable and output different messages depending on the value provided.
If value is ‘A’, output: ‘Please proceed to Level 1’
If value is ‘B’ or ‘C’, output: ‘Please proceed to Level 2’
If value is ‘D’, output: ‘Please proceed to Level 3’
In all other entries, output: ‘Invalid Entry’

Question 2 (25 marks)
Create a host (or global) variable named G_NEWVALUE.
Write an anonymous block that declares two local variables: A1RATE and B2RATE. The data types of these two variables should be the same as the data type of the column rate_per_mile in i_cargroup table. Also declare a constant called SURCHARGE with value 5.
In your BEGIN section, write two SELECT queries to obtain value of the rate_per_mile column where car_group_name is ‘A1’ and ‘B2’ and assign the corresponding values to A1RATE and B2RATE variables. Now use the IF-THEN statement to compare the two variables and add the constant SURCHARGE to the smaller value. Assign the new sum to the variable G_NEWVALUE.
Using the…