Q:

Write a block in PL/SQL to display the first department with more than five employees

0

Write a block in PL/SQL to display the first department with more than five employees.

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

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.

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now