Reference no: EM13337320

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)