PL/SQL Cursor Exercises: Show the uses of subquery in FROM clause of parent query in an explicit cursor

PL/SQL Cursor: Exercise-49 with Solution

Write a block in PL/SQL to show the uses of subquery in FROM clause of parent query in an explicit cursor.

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
  CURSOR emp_cur  IS
    SELECT d1.department_id, department_name, emp_no
    FROM departments d1,
         ( SELECT department_id, COUNT(*) AS emp_no 
           FROM employees
           GROUP BY department_id
         ) d2
    WHERE (d1.department_id = d2.department_id) AND emp_no >= 6
    ORDER BY emp_no;

BEGIN
 DBMS_OUTPUT.PUT_LINE (rpad('Department',25)||'No. of Employees');
 DBMS_OUTPUT.PUT_LINE ('----------------------------------------');
   FOR dept IN emp_cur
   LOOP
     DBMS_OUTPUT.PUT_LINE (rpad(dept.department_name,25) || dept.emp_no);
   END LOOP;
END;
/

Sample Output:

Department               No. of Employees
----------------------------------------
Purchasing               6
Finance                  6
Sales                    34
Shipping                 45

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to shows how are records are declared and initialized.
Next: Write a block in PL/SQL to show the uses of corelated subquery in an explicit cursor.