Find department with maximum number of employees

PL/SQL While Loop: Exercise-21 with Solution

Write a PL/SQL program to display the department name with the maximum number of employees.

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

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
v_max_employees  NUMBER := 0;
v_dept_namedepartments.department_name%TYPE;
v_dept_iddepartments.department_id%TYPE;
v_employee_count NUMBER;
v_cursor         SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR
    SELECT department_id, department_name
    FROM departments;
  WHILE TRUE LOOP
    FETCH v_cursor INTO v_dept_id, v_dept_name;
    EXIT WHEN v_cursor%NOTFOUND;
v_employee_count := 0;

    DECLARE
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_employee_count := v_employee_count + 1;
      END LOOP;
      CLOSE v_employee_cursor;
    END;

    IF v_employee_count>v_max_employees THEN
v_max_employees := v_employee_count;
v_dept_name := v_dept_name;
    END IF;
  END LOOP;
  CLOSE v_cursor;

  DBMS_OUTPUT.PUT_LINE('Department with the maximum number of employees: ' || v_dept_name);
  DBMS_OUTPUT.PUT_LINE('Number of employees: ' || v_max_employees);
END;
/

Sample Output:

Department with the maximum number of employees: Payroll
Number of employees: 45

Explanation:

The said code in Oracle's PL/SQL that provides the department name with the maximum number of employees.

The variables v_max_employees that stores the maximum employee count, v_dept_name stores the department name with the maximum employees, v_dept_id stores the current department ID, v_employee_count counts the number of employees in each department, and a cursor v_cursor as that fetches department details are declared.

In the outer WHILE loop v_cursor selects the department_id and department_name from the departments table into v_dept_id and v_dept_name and continues until there are no more records to fetch. Inside this loop, the number of employees within the current department to be counts. The outer loop continues to the next department until all departments have been processed.

In the inner loop a cursor v_employee_cursor, fetches all employees from the employees table who belong to the current department into v_employee_rec and increments v_employee_count by 1 for each record fetched.

The v_employee_count compares with the current maximum employee count and if found greater, the v_max_employees updates with the new maximum and assign the department name to v_dept_name.

The DBMS_OUTPUT.PUT_LINE statement display the department name with the maximum number of employees.

Flowchart:

Previous: Find the employee with the lowest salary in each department.