Live ORACLE

DECLARE

CURSOR c1 IS

  SELECT t1.department_id,

         department_name,

         staff

  FROM   departments t1,

         (SELECT   department_id,

                   Count(* ) AS staff

          FROM     employees

          GROUP BY department_id) t2

  WHERE  t1.department_id = t2.department_id

         AND staff >= 5;

BEGIN

FOR dept IN c1 LOOP

  dbms_output.Put_line('Department = '

                       ||dept.department_name

                       ||', staff = '

                       ||dept.staff);

END LOOP;

END;

/

Department = Shipping, staff = 45
Department = Finance, staff = 6
Department = Sales, staff = 34
Department = IT, staff = 5
Department = Purchasing, staff = 6

PL/SQL procedure successfully completed.

Using a Subquery in a FROM Clause