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
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
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 PRINT command, output the value in the host variable (G_NEWVALUE) after the anonymous block has run.
Question 3
Write an anonymous block that meets the following specification:
1. Using an explicit cursor, retrieve each row from the i_car table.
2. Create a local variable that inherits the structure of the i_car table (using %ROWTYPE attribute). Place each row returned into this variable.
3. Using an implicit cursor, calculate the number of bookings that belong to each car.
4. Display the car registration, car group name, model name, cost and the number of bookings for each car.
Question 4
Write a PL/SQL that displays the maximum miles driven from the odometer reading for all the car bookings. Your program must use at least one explicit cursor and must NOT use any functions, for example SUM, MAX, COUNT, etc.
(Hint: miles driven from the odometer reading for each booking can be found by subtracting miles_out column from miles_in column, i.e. miles_in - miles_out)
MARKING CRITERIA
1. The code executes without error messages.
2. The code produces the required output.
3. The code addresses the specification and provides a solution to every element in the specification.
4. The code is well structured and, where applicable, adopts an optimal and sophisticated approach to PL/SQL.