Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unhappy Unanswered: Need help in Functions

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    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;

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    Talking Thanks very much

    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

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    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

  5. #5
    Join Date
    Oct 2003
    Posts
    6

    Smile

    Thank you verrry much!!!

    Cyndi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •