PL/SQL program: Average salary by job title

PL/SQL While Loop: Exercise-13 with Solution

Write a PL/SQL program to display the average salary for each job. Return job title and average salary in a row.

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:

SET SERVEROUTPUT ON;
DECLARE
v_job_titleemployees.job_title%TYPE;
v_avg_salary NUMBER;
v_total_salary NUMBER;
v_employee_count NUMBER;
    CURSOR c_job_titles IS
    SELECT DISTINCT job_title
    FROM employees;
      CURSOR c_employees (p_job_title IN employees.job_title%TYPE) IS
    SELECT salary
    FROM employees
    WHERE job_title = p_job_title;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Job Title' || CHR(9) || 'Average Salary');
  DBMS_OUTPUT.PUT_LINE('-----------------------------------');
    OPEN c_job_titles;
  FETCH c_job_titles INTO v_job_title;
    WHILE c_job_titles%FOUND LOOP
v_total_salary := 0;
v_employee_count := 0;
        OPEN c_employees(v_job_title);
    FETCH c_employees INTO v_avg_salary;
        WHILE c_employees%FOUND LOOP
v_total_salary := v_total_salary + v_avg_salary;
v_employee_count := v_employee_count + 1;
      FETCH c_employees INTO v_avg_salary;
    END LOOP;
        CLOSE c_employees;
        IF v_employee_count> 0 THEN
v_avg_salary := v_total_salary / v_employee_count;
    ELSE
v_avg_salary := 0;
    END IF;
        DBMS_OUTPUT.PUT_LINE(v_job_title || CHR(9) || v_avg_salary);
        FETCH c_job_titles INTO v_job_title;
  END LOOP;
    CLOSE c_job_titles;
END;
/

Sample Output:

Job Title Average Salary
-----------------------------------
AD_VP 17000
FI_ACCOUNT 7920
PU_CLERK 2780
SH_CLERK 3215
HR_REP 6500
PU_MAN 11000
AC_MGR 12000
ST_CLERK 2785
AD_ASST 4400
IT_PROG 5760
SA_MAN 12200
AC_ACCOUNT 8300
FI_MGR 12000
ST_MAN 7280
AD_PRES 24000
MK_MAN 13000

.....

Explanation:

The said code in Oracle's PL/SQL that calculates and displays the average salary for each job title in a tabular format.

The variables v_job_title, v_avg_salary, v_total_salary, and v_employee_count are declared to store the job title, average salary, total salary, and employee count, respectively.

The two cursors c_job_titles that selects distinct job titles from the employees table and c_employees that retrieves the salaries for a specific job title are defined.

The outer WHILE loop c_job_titles%FOUND LOOP starts, which iterates through each job title and continues fetching the next job title until no more rows are found.

Within the outer loop, the v_total_salary and v_employee_count variables are reset to zero for each job title.

The c_employees cursor is passing the current job title as a parameter and it retrieves the salaries for the current job title.

The inner WHILE loop c_employees%FOUND LOOP begins, which calculates the total salary and counts the number of employees for the current job title and the v_total_salary is incremented by the v_avg_salary for each employee, and the v_employee_count is incremented by 1 and it continues fetching the next salary until no more rows are found.

The average salary for the current job title is calculated by dividing the v_total_salary by v_employee_count.

The job title and average salary are displayed using the DBMS_OUTPUT.PUT_LINE statement.

Flowchart:

Previous: Display Departments and Employees.
Next: Department-wise total salary expense.