I have been working on this homework problem for two days now and at the conclusion that I cannot figure it out. Before I pull all of my hair out, could someone give me a hand? I would appreciate it very much!!

Question #1:

Create PL/SQL function (EMP_SAL_DIFF) that accepts an employee number (P_EMPLOYEE_ID) and returns the difference between the average salary in the department the employee works for and the employee’s salary.

In addition, create a driver program that runs the function against every employee in the EMPLOYEES table. Show all work including the output from running the procedure.
__________________________

Here is the work I have done so far. It isn't much. Its just the part where the employee salary is obtained from his/her department. I have no clue as to what to do with the rest. Thanks very much in advance!!

Create or replace function get_Sal ( P_EID IN NUMBER) RETURN NUMBER
IS
V_SAL EMPLOYEES.SALARY%TYPE;
BEGIN
SELECT SALARY
INTO V_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EID;
RETURN V_SAL;
END;
/
variable g_sal NUMBER
EXECUTE :g_sal := get_Sal (199)
Print g_sal


Thanks, Cyndi

Create or replace function get_Sal ( P_EID IN NUMBER) RETURN NUMBER
IS
V_DEPARTMENT EMPLOYEES.DEPARTMENT%TYPE;
V_SAL EMPLOYEES.SALARY%TYPE;
V_AVG_SAL EMPLOYEES.SALARY%TYPE;
BEGIN

-- LOOKUP SALARY, DEPARTMENT OF EMPLOYEE
SELECT SALARY, DEPARTMENT
INTO V_SAL, V_DEPARTMENT
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EID;

-- LOOKUP AVG SALARY OF EMPLOYEES DEPARTEMENT
SELECT AVG(SALARY)
INTO V_AVG_SAL
FROM EMPLOYEES
WHERE DEPARTMENT =V_DEPARTMENT;

RETURN V_SAL - V_AVG_SAL;
END;

Thank you very much for your help. I ran the code by my professor, and he stated the following:

"You need to perform a correlated subquery to find the average salary for the department that the employee works.

A correlated subquery looks like the following:"

Select e.last_name
from employees e
where salary > (select max(salary) from employees where department_id =
e.department_id);


Also, would you know how I would loop this to query all the employees?

I appreciate all the help I can get and am very grateful for the coding!!!

Cyndi

CORRELATED SUBQUERY ?

Maybe this could work.

select avg(salary)
from employees
where department = (select department from employees where employee = p_eid);

=> where p_eid is the input argument of your function.



LOOP THROUGH ALL EMPLOYEES ?

select employee.id, get_Sal (employee_id) from employees;

=> where get_sal is the name of your function

Thank you verrry much!!!

Cyndi