Update employees' salary if they're in the sales department, as a manager or a sales representative pl/sql

Question

I need to display employees that worked in the 'sales' department. I want to update the salary, when the employee is a manager, than add 30% salary and display the average salary of 'sales' department, but when the employee is a 'sales representative' then add 50% salary and display the average salary of 'sales'

Output example:
Manager John Russell 27817
Sales Jonathon Taylor 22517

Here's what I've done so far, any idea?

declare 
    i number := 0;
    avg_sal int;
begin
  for v_id in (select employee_id, first_name, manager_id, salary, job_id from employees
  where department_id=(select department_id from departments where department_name='Sales'))
  loop
    update employees
    set salary = case when (employee_id=(select distinct manager_id from employees)) then (salary+(salary*30/100)) when (job_id=(select job_id from jobs where job_title='Sales Representative')) then (salary+(salary*50/100)) end
    where employee_id = v_id.employee_id;
    i := i+1;
  end loop;
  loop
    select avg(salary) from employees into avg_sal;
    case when (employee_id=(select distinct manager_id from employees)) then dbms_output.put_line('Manager '||first_name||' '||avg_sal) when (job_id=(select job_id from jobs where job_title='Sales Representative')) then dbms_output.put_line('Sales '||first_name||' '||avg_sal) end
end;
/

The employee id, name, salary, manager, and job id are from the 'employees' table. While the job title like 'sales representative' is from the 'jobs' table. I need to use pl/sql control structure, record, and cursor. Thanks!

No, this is not how you do it. Why are you doing it row-by-row meaning slow-by-slow ? I mean why do you need to use control structure, record and cursor ? — Ranagal

hkandpal hkandpal · Accepted Answer · 2020-10-14T15:15:12

you can try this for the update statement, and once they are updated you can have a seperate select statement.

-- Untested

UPDATE HR.EMPLOYEES 
SET SALARY = CASE WHEN JOB_ID = 'SA_MAN' THEN SALARY + (SALARY*.50)
                  WHEN JOB_ID IN ('AD_PRES','AD_VP') THEN  SALARY + (SALARY*.30)
                  ELSE SALARY
             END
WHERE JOB_ID IN ('AD_PRES','AD_VP','SA_MAN');

Update employees' salary if they're in the sales department, as a manager or a sales representative pl/sql

1 Answers