Write the PL-SQL and SQL statements for all the following questions

(40 Points) In this question you will create a Trigger. The trigger watches any UPDATE on the EMPLO table and record them in the SAL_HIST table. View the schema of both tables before you start. (Code for table creation attached)

Create a trigger that add a new record in the SAL_ HIST table ONLY WHEN an update is made on the SALARY field of the EMPLOYEES table.

Reject any increase more than 75% of the old salary with a proper application error message.

Trigger code here

Update the SALARY of employee_id 110 to 8900.

What statement would show that your trigger work successfully?

(30 Points) Using HR Schema, create a Function named GET_DEPT_FULL_ADDRESS.

The function returns a string that contains all the following (concatenated as one string) : department_name, city, country name as an output.

The function receives the department_id as an input parameter.

Function code here

Use the GET_DEPT_FULL_ADDRESS. function in a SQL SELECT statement to list last_name, job_id and department full address of all employees avoiding using join in the query.

(30 Points) Using EMPLO table, create a procedure named UPDATE_COMM.

The procedure will update the COMMISSION_PCT column according the following:

Salary Commission %

Less than 5,000 0.15

Between 5,000 and 10,000 0.1

Greater than 10,000 0.075

You can solve this problem in any way that works. With or without functions, using CURSOR or NOT. At the end you need to update the contents on the COMMISSION_PCT column according to the table above.

Code here