w3resource

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:

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:

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?



Follow us on Facebook and Twitter for latest update.