DB schema project -- 2

Question 2: 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.

1. 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.

2. 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 (agreedPrice – askingPrice) × 4%. Also, a senior agent receives the commission only when the agreedPrice is greater than the askingPrice stored in the database. The asking price in the database is not visible to customers.

Question 3 : Provide the implementation of the following trigger. For submission, please include both the PL/SQL code and an insert statement to demonstrate the trigger functionality.

a. A Trigger which automatically stores in a separate table called ‘ExcellentSale’ the Sales Agent name, car model and manufacturer name, each time the agreed price of a SalesTransaction is more than 20% above the car’s asking price. (Note: You need to create the ‘ExcellentSale’ table before implementing this trigger. To create the primary key, use a sequence that starts at 1 and increments by 1).

Veri Tabanı Yönetimi Oracle Database

Proje NO: #31767636

Proje hakkında