Find the employee with the lowest salary in each department

PL/SQL While Loop: Exercise-20 with Solution

Write a PL/SQL program to display the employees who have the lowest salary in 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_min_salary   NUMBER;
v_employee_idemployees.employee_id%TYPE;
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_min_salary := NULL;
    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;
        IF v_min_salary IS NULL OR v_employee_rec.salary<v_min_salary THEN
v_min_salary := v_employee_rec.salary;
v_employee_id := v_employee_rec.employee_id;
        END IF;
      END LOOP;
      CLOSE v_employee_cursor;
    END;
    DBMS_OUTPUT.PUT_LINE('Department ' || v_dept_id || '   : Employee ID = ' || v_employee_id || ',       Lowest Salary = ' || v_min_salary);
  END LOOP;
  CLOSE v_cursor;
END;
/

Sample Output:

Department 50   : Employee ID = 132,       Lowest Salary = 2100
Department 40   : Employee ID = 203,       Lowest Salary = 6500
Department 110   : Employee ID = 206,       Lowest Salary = 8300
Department 90   : Employee ID = 101,       Lowest Salary = 17000
Department 30   : Employee ID = 119,       Lowest Salary = 2500
Department 70   : Employee ID = 204,       Lowest Salary = 10000
Department    : Employee ID = 204,       Lowest Salary = 
Department 10   : Employee ID = 200,       Lowest Salary = 4400
Department 20   : Employee ID = 202,       Lowest Salary = 6000
Department 60   : Employee ID = 107,       Lowest Salary = 4200
Department 100   : Employee ID = 113,       Lowest Salary = 6900
Department 80   : Employee ID = 173,       Lowest Salary = 6100

Explanation:

The said code in Oracle's PL/SQL that retrieves the employee with the lowest salary in each department.

A cursor, v_cursor, is declared to store distinct department IDs from the employees table.

The outer loop fetches the department ID into the variable v_dept_id and continues until there are no more rows to fetch from the cursor. Inside this loop, the lowest salary within the current department be calculated.

An other cursor, v_employee_cursor, fetches all employees from the employees table who belong to the current department.

The inner loop iterates through the employees in the department and fetches the employee record into v_employee_rec and exits when there are no more records.

Within the inner loop, an IF statement compares the salary of the current employee with the lowest salary found so far. If v_min_salary is NULL or the current employee's salary is lower, the current employee's salary and ID are assigned to v_min_salary and v_employee_id, respectively.

The DBMS_OUTPUT.PUT_LINE statement displays the department, employee ID and the lowest for this department.

Flowchart:

Previous: PL/SQL code to find highest salary employee in each department.
Next: Find department with maximum number of employees.