Write a program in PL/SQLto update the salary of a specific employee by 10% if the salary exceeds themid range of the salary against this job and update up to mid range if thesalary is less than the mid range of the salary, and display a suitable messagefor users.
update the salary of a specific employee by 10% if the salary exceeds themid range of the salary against this job
Share
CREATE AN ACCOUNT TO GET NOTIFICATIONS ABOUT
NEW QUESTIONS AND ANSWERS.
DOWNLOAD ATTACHMENT FOR ANSWER:
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;