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

Evner: Database Administration, Oracle Database

Se mere: project call someone execute person, project process procedures, project processes procedures, xml schema project, access db student project, db merger project, db schema graphic, db tier project application web application, multilanguage db schema, mysql multilanguage db schema, schema project microcontroller, usb key electronic schema project, validating schema project, vb net db layer project code, algorithm project -- 2, algorithm project 2, excel project 2/2, gamele foundation project 2, management project 2, private project 2

Om arbejdsgiveren:
( 3 bedømmelser ) dubai, United Arab Emirates

Projekt ID: #31767636

5 freelancere byder i gennemsnit ₹5850 timen for dette job

(10 bedømmelser)

I have more than 16 years of experience in SQL, plsql, data modeling and performance tuning. I have experience in database-oracle, mysql and postgresql. I can do this work

₹7000 INR in 3 dage
(4 bedømmelser)

Cover letter I am applying for this Oracle DB task. I have an associate degree in information systems, and I possess an oracle certificate, which helps qualify me for this role. I have extensive knowledge of Oracle dat Flere

₹10000 INR in 10 dage
(0 bedømmelser)

Iam a Oracle SQL certified Associate Have a immense knowledge in the Oracle field so for the further collaboration kindly text me back sir/ mam

₹2250 INR in 2 dage
(0 bedømmelser)

Hi All, I am varun sharma. I have 6+ year experience in Oracle DBA I can handle ORACLE DBA TASK. I am hard worker.

₹5000 INR in 7 dage
(0 bedømmelser)