PL/SQL Cursor Exercises: Display the first department with more than five employees

Last update on November 23 2023 09:37:05 (UTC/GMT +8 hours)

PL/SQL Cursor: Exercise-46 with Solution

Write a block in PL/SQL to display the first department with more than five 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
  emp_depid departments.department_id%TYPE := 10;emp_max_depid departments.department_id%TYPE;no_emps NUMBER(3);emp_depname departments.department_name%TYPE;dep_mgname employees.first_name%TYPE;dep_mglname employees.last_name%TYPE;dep_mgid departments.manager_id%TYPE;emp_found_nfound BOOLEAN := FALSE;BEGIN
  SELECT Max(department_id)
  INTO   emp_max_depid
  FROM   departments;
  
  dbms_output.Put_line(Rpad('Department ID',20)
  || Rpad('Department',20)
  || Rpad('Manager',20)
  ||'No. of Employees');
  dbms_output.Put_line( '---------------------------------------------------------------------------------');
  WHILE emp_depid <= emp_max_depid
  LOOP
    SELECT manager_id
    INTO   dep_mgid
    FROM   departments
    WHERE  department_id = emp_depid;
    
    IF dep_mgid IS NOT NULL THEN
      SELECT Count(*)
      INTO   no_emps
      FROM   employees
      WHERE  department_id = emp_depid;
      
      IF no_emps > 5 THEN
        SELECT department_name,
               first_name,
               last_name
        INTO   emp_depname,
               dep_mgname,
               dep_mglname
        FROM   employees e
        join   departments d
        ON     (
                      d.manager_id = e.employee_id)
        WHERE  d.department_id = emp_depid;
        
        dbms_output.Put_line(Rpad(emp_depid,20)
        || Rpad(emp_depname,20)
        ||Rpad((dep_mgname
        ||' '
        ||dep_mglname),20)
        ||no_emps);
        emp_found_nfound := TRUE;
        EXIT;
      END IF;
    END IF;
    emp_depid := emp_depid + 10;
  END LOOP;
  IF NOT emp_found_nfound THEN
    dbms_output.Put_line('No department found with more than 5 employees');
  END IF;
END;
/

Sample Output:

SQL> /
Department ID       Department          Manager             No. of Employees
-----------------------------------------------------------------------------
30                  Purchasing          Den Raphaely        6

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to print a dotted line in every 6th line.
Next: Write a block in PL/SQL to print a report which shows that, the employee id, name, hire date, and the incentive amount they achieved according to their working experiences, who joined in the month of current date.