PL/SQL Control Statement Exercises: Update the salary of a specifc employee by 8% if the salary exceeds the mid range of the salary against this job and update up to mid range if the salary is less than the mid range of the salary, and display a suitable message

PL/SQL Control Statement: Exercise-19 with Solution

Write a program in PL/SQL to update the salary of a specifc employee by 8% if the salary exceeds the mid range of the salary against this job and update up to mid range if the salary is less than the mid range of the salary, and display a suitable message.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

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);
 
  -- calculate mid-range
  emp_mid_salary := (emp_min_salary + emp_max_salary) / 2;
  -- get salary of the given employee
  SELECT salary,first_name
  INTO tmp_salary,tmp_emp_name
  FROM employees
  WHERE employee_id = tmp_emp_id;
 
  -- update salary 
  
  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 * 8 /100
    WHERE employee_id = tmp_emp_id;
  END IF;
   --display message
  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;
/

Flowchart:

Sample Output:

The employee Amit ID 167 works in salary 6200 which is lower than mid-range of salary 9004

PL/SQL procedure successfully completed.

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to show the uses of nested loop.
Next: Write a program in PL/SQL using nested loop with EXIT WHEN statement.