Question 1

Write a stored procedure that accepts the model name of a car as the input parameter and provides the number of cars belonging to that model as the output parameter. The procedure should also display the description of the car model.

Procedure:

1. Create the procedure that should have one IN parameter (p_model) and one OUT parameter (p_noofcars)

2. Use an implicit cursor and a SQL function to calculate the number of cars belonging to the particular car model and assign that value to the OUT parameter.

3. Use another implicit cursor to display the description belonging to the particular car model.

Submit the code to create the procedure and the code to call the procedure in a PL/SQL block. Value for the input parameter for the procedure should be ‘FERR TR'. You should display the value of the OUT parameter returned, i.e. the number of cars belonging to the car model.

Provide a screen dump of the output when the calling PL/SQL block is run.

Question 2

Write a function that displays the most recent date of rental for a particular car (registration) for all completed bookings made for that car.

Write a second function that displays the maximum rental period for a particular car (registration) for all completed bookings made for that car.

For both the functions, you should check for completed bookings, i.e. miles_in is not null.

Write ONE SQL statement that displays the registration, model name and cost for each car and calls the two functions to display the most recent date of rental and the maximum rental period for that car.

Display the output of the SQL statement.

Question 3

The car hire company is concerned about possible unauthorised changes to customer details that may compromise the company's security and privacy policies. You will create a database trigger to monitor such changes to some important columns in the I_CUSTOMER table.

1. Create a copy of the I_CUSTOMER table in your own schema by using the following command:

CREATE TABLE N_CUSTOMER AS SELECT * FROM I_CUSTOMER;

2. Also create a table to store change logs (CUSTOMER_CHANGELOG) by using the following command:

CREATE TABLE CUSTOMER_CHANGELOG

(

cust_no NUMBER(5),

cust_name VARCHAR2(20),

contact VARCHAR2(20),

log_date DATE);

3. Create a database trigger with the following specification:

• fires AFTER an UPDATE on N_CUSTOMER table.

• fires if there is any change to cust_name or contact columns of the N_CUSTOMER table.

• fires for each row.

• inserts a first row: the affected cust_no column, the OLD values of the cust_name and contact columns and the current date into the table CUSTOMER_CHANGELOG.

• inserts a second row: the affected cust_no column, the NEW values of the cust_name and contact columns and the current date into the table CUSTOMER_CHANGELOG.

4. Test the trigger with the following specification:

• Go ahead and make a change to N_CUSTOMER table by issuing the following command:

UPDATE N_CUSTOMER

SET cust_name = 'Peter Davis', contact = 'Sam Bogdanovich'

WHERE cust_no = 2338;

5. Now, select all rows from the CUSTOMER_CHANGELOG table. This should display the old and new values of the changes you made due to firing of the trigger. Output all the rows of CUSTOMER_CHANGELOG table.

Question 4

Create a PL/SQL function called findtotalcarmodels to return the total number of cars belonging to a particular model. The function should have a single IN parameter as model_name. You should then use an explicit cursor to count the number of cars belonging to that car model and return the final count. You must NOT use any implicit cursors, table joins, subqueries, set operators, group functions or SQL functions (such as COUNT) to create this function.

Now, write ONE PL/SQL anonymous block that provides a report about car details.

The full specification to create the PL/SQL anonymous block is as follows:

• Using an explicit cursor, retrieve all car registration, cost and model name details (registration, cost & model_name) from the I_CAR table.

• If the cost of the car is less than or equal to $50,000, we determine car cost category as "Budget Car". If the car cost is between $50,000 and $100,000, the car cost category is "Standard Car". For all other cars costing more than $100,000; the car cost category is "Premium Car". Declare a local variable v_carcategory as VARCHAR2(40) to store car cost category.

• Call the function findtotalcarmodels to get the total number of cars belonging to the model name for the car and store them in a local variable named v_totalcars.

• Use an explicit cursor with a parameter that accepts car registration to find the most recent reservation made on the car. You will have to look at the date_reserved column from I_BOOKING table here. You cannot use the MAX function. Compare all the relevant dates to find the most recent one.

• Display a report showing car registration, car cost category, car model name, total number of cars belonging to that model and the most recent reservation made on that car.

• Finally, create an exception handler which fires when no rows are found. The exception handler should output the following message to the screen: "No rows found".

Important Notes:

• You must NOT use any implicit cursors, table joins, subqueries, set operators, group functions or SQL functions (such as COUNT) to create the PL/SQL function or the PL/SQL anonymous block.
• The PL/SQL anonymous block must be ONE block only. Do NOT write a block to perform each task of the specification above.