w3resource
PL/SQL Cursor Exercises

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

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:

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:

Flowchart: Display the first department with more than five employees.

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.

What is the difficulty level of this exercise?