﻿ PL/SQL Control Statement: 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 - w3resource

# 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:

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

What is the difficulty level of this exercise?

﻿