PL/SQL Program to Display Average Salary for Each Department

PL/SQL While Loop: Exercise-18 with Solution

Write a PL/SQL program to display the average salary for each department.

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
v_dept_idemployees.department_id%TYPE;
v_avg_salary NUMBER;
v_cursor     SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR
    SELECT DISTINCT department_id
    FROM employees;
  WHILE TRUE LOOP
    FETCH v_cursor INTO v_dept_id;
    EXIT WHEN v_cursor%NOTFOUND;
v_avg_salary := 0;
    DECLARE
v_count NUMBER := 0;
v_total_salary NUMBER := 0;
v_employee_recemployees%ROWTYPE;
v_employee_cursor SYS_REFCURSOR;
    BEGIN
      OPEN v_employee_cursor FOR
        SELECT *
        FROM employees
        WHERE department_id = v_dept_id;
      WHILE TRUE LOOP
        FETCH v_employee_cursor INTO v_employee_rec;
        EXIT WHEN v_employee_cursor%NOTFOUND;
v_total_salary := v_total_salary + v_employee_rec.salary;
v_count := v_count + 1;
      END LOOP;
      CLOSE v_employee_cursor;
      IF v_count> 0 THEN
v_avg_salary := v_total_salary / v_count;
      END IF;
    END;
    DBMS_OUTPUT.PUT_LINE('Department ' || v_dept_id || ': Average Salary = ' || v_avg_salary);
  END LOOP;
  CLOSE v_cursor;
END;
/

Sample Output:

Department 50: Average Salary = 3475.55555555555555555555555555555555
Department 40: Average Salary = 6500
Department 110: Average Salary = 10150
Department 70: Average Salary = 10000
Department 90: Average Salary = 19333.3333333333333333333333333333333333
Department 30: Average Salary = 4150
Department 10: Average Salary = 4400
Department : Average Salary = 0
Department 20: Average Salary = 9500
Department 60: Average Salary = 5760
Department 100: Average Salary = 8600
Department 80: Average Salary = 8955.8823529411764705882352941176470

Explanation:

The said code in Oracle's PL/SQL that retrieves distinct department IDs from the 'employees' table and calculates the average salary for each department.

The variables "v_dept_id" that holds the department ID, "v_avg_salary" that stores the calculated average salary, and the cursor "v_cursor" fetches the distinct department IDs by a select statement from the 'employees' table are declared.

Within the outer WHILE loop the cursor fetches a department ID into "v_dept_id" and it continues until no more rows are found and the loop is terminated.

Within the inner loop the average salary is initialized to 0, and variables "v_count" and "v_total_salary" are initialized to keep track of the number of employees and their total salary, respectively.

A nested cursor "v_employee_cursor" fetches the employees for the current department using the "v_dept_id" value.

Within the inner WHILE loop each employee's salary is added to the "v_total_salary" variable, and the "v_count" variable is incremented and the loop continues until there are no more rows to fetch from the cursor.

The average salary is calculated by dividing the total salary by the count provided by there are one or more employees in the department.

The DBMS_OUTPUT.PUT_LINE() function displays the output, includes the department ID and the calculated average salary.

Flowchart:

Previous: Employee count by country.
Next: PL/SQL code to find highest salary employee in each department.