w3resource
PL/SQL Cursor Exercises

PL/SQL Cursor Exercises: Display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history

PL/SQL Cursor: Exercise-29 with Solution

Write a PL/SQL block to display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history.

Display department name, manager, employees count

Sample Solution:

PL/SQL Code:

DECLARE
    CURSOR dpt_cur IS
      SELECT d.department_id      id,
             department_name      name,
             Nvl(first_name, ' ') manager
      FROM   departments d
             left outer join employees e
                          ON ( d.manager_id = e.employee_id );
    emp_count      NUMBER(3);
    job_hist_count NUMBER(3);
BEGIN
    FOR dept_all IN dpt_cur LOOP
        SELECT Count(*)
        INTO   emp_count
        FROM   employees
        WHERE  department_id = dept_all.id;

        SELECT Count(*)
        INTO   job_hist_count
        FROM   job_history
        WHERE  department_id = dept_all.id;

        dbms_output.Put_line(Rpad(dept_all.name, 20)
                             || Rpad(dept_all.manager, 15)
                             || To_char(emp_count, '9999')
                             || To_char(job_hist_count, '9999'));
    END LOOP;
END; 
/ 

Sample Output:

SQL> /
Public Relations    Hermann            1    0
Shipping            Adam              45    2
Finance             Nancy              6    0
Marketing           Michael            2    1
Accounting          Shelley            2    2
IT                  Alexander          5    1
Executive           Steven             3    2
Human Resources     Susan              1    0
Purchasing          Den                6    0
Sales               John              34    2
Administration      Jennifer           1    0
Payroll                                0    0
Recruiting                             0    0
Retail Sales                           0    0
Government Sales                       0    0
IT Helpdesk                            0    0
NOC                                    0    0
IT Support                             0    0
Operations                             0    0
Contracting                            0    0
Construction                           0    0
Manufacturing                          0    0
Benefits                               0    0
Shareholder Services                   0    0
Control And Credit                     0    0
Corporate Tax                          0    0
Treasury                               0    0

PL/SQL procedure successfully completed. 

Flowchart:

Flowchart: PL/SQL Cursor Exercises - Display the department name, name of the manager, number of employees in each department, and number of employees listed in job_history

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display department name, head of the department,city, and employee with highest salary.
Next: Write a block in PL/SQL to displays employee name and number of jobs he or she done at past. Displays employee name and job count by job

What is the difficulty level of this exercise?