Using Oracle SQL developer: Develop a user defined function to revise the databa

Question

Using Oracle SQL developer:

Develop a user defined function to revise the database to reflect the following business rules

1. All sales Employees with 10 years or less of employment will receive 3% COMMISSION

2.Employees with years of experience in the range of >10 but less than 20 will receive 5% commission

3.Employees with years of experience in the range of >20 but less than 25 will receive 7% commission

4.Employees with years of experience in the range of >25 but less than 30 will receive 8% commission

5.Employees with years of experience over 30 years will receive 10% commission

Explanation / Answer

CREATE OR REPLACE FUNCTION reviseDB()

IS

BEGIN

UPDATE EMPLOYEES SET COMMISSION=3 WHERE DEPARTMENT='SALES' AND EXPERIENCE<=10;

UPDATE EMPLOYEES SET COMMISSION=5 WHERE EXPERIENCE>10 AND EXPERIENCE<20;

UPDATE EMPLOYEES SET COMMISSION=7 WHERE EXPERIENCE>20 AND EXPERIENCE<25;

UPDATE EMPLOYEES SET COMMISSION=8 WHERE EXPERIENCE>25 AND EXPERIENCE<30;

UPDATE EMPLOYEES SET COMMISSION=10 WHERE EXPERIENCE>30;

END

/

**** Rule 1 mentions about all sales emloyees whereas rule 2-5 says only emloyees. Thus removed DEPARTMENT check form rules 2005.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.