CSE2/4DBF Assignment 2 Task 2

Task 2 [35 marks]

Provide the implementation of the following stored procedures and function. For submission, please include both the PL/SQL code and code to execute (for the procedure)/SQL statement (for the function) to demonstrate the functionality.
a. Write a stored procedure that accepts a particular year as input, and as output displays the number of cars sold grouped by the 3 mileage groups (Low Mileage: <50000km, Medium Mileage: >=50000km & <150000km, High Mileage: >=150000km). Also display the total number of cars sold overall.
b. Write a stored function that uses a senior agent’s ID as input and calculates the total commission owed to date for that agent. You also need to show an SQL statement to display the total amount of commission (i.e., the sum) owed to all senior agents in the database.

- Note: the commission policy states that a senior agent receives an additional 1% commission multiplier for each year since they became a senior agent, rounding down to the nearest full year. This means that a senior agent who was promoted 4.25 years before the date of sale, would have their commission calculated as (agreed Price – askingPrice) × 4%. Also, a senior agent receives the commission only when the agreed Price is greater than the asking Price stored in the database. The asking price in the database is not visible to customers.

[a: 20 marks, b: 15 marks – 35%]