DECLARE

emp_min_salary NUMBER(6,0);

emp_max_salary NUMBER(6,0);

emp_mid_salary NUMBER(6,2);

tmp_salary EMPLOYEES.SALARY%TYPE;

tmp_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 167;

tmp_emp_name EMPLOYEES.FIRST_NAME%TYPE;

BEGIN

SELECT  min_salary, max_salary

INTO emp_min_salary, emp_max_salary

FROM JOBS

WHERE

JOB_ID = (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = tmp_emp_id);

emp_mid_salary := (emp_min_salary + emp_max_salary) / 2;

SELECT salary,first_name INTO tmp_salary,tmp_emp_name

FROM employees

WHERE employee_id = tmp_emp_id;

 IF tmp_salary < emp_mid_salary

THEN UPDATE employees SET salary = emp_mid_salary

WHERE employee_id = tmp_emp_id;

ELSE UPDATE employees SET salary = salary + salary * 10 /100

WHERE employee_id = tmp_emp_id;

END IF;

IF tmp_salary > emp_mid_salary

THEN DBMS_OUTPUT.PUT_LINE(‘The employee ‘||tmp_emp_name||‘ ID ‘ || TO_CHAR(tmp_emp_id) || ‘ works in salary ‘ || TO_CHAR(tmp_salary) || ‘ which is higher than mid-range of salary ‘ || TO_CHAR(emp_mid_salary));

ELSIF tmp_salary < emp_mid_salary THEN DBMS_OUTPUT.PUT_LINE(‘The employee ‘||tmp_emp_name||‘ ID ‘ || TO_CHAR(tmp_emp_id) || ‘ works in salary ‘ || TO_CHAR(tmp_salary) || ‘ which is lower than mid-range of salary ‘ || TO_CHAR(emp_mid_salary));

ELSE DBMS_OUTPUT.PUT_LINE(‘The employee ‘||tmp_emp_name||‘ ID ‘ || TO_CHAR(tmp_emp_id) || ‘ works in salary ‘ || TO_CHAR(tmp_salary) || ‘ which is equal to the mid-range of salary ‘ || TO_CHAR(emp_mid_salary));

END IF;

END;