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

# 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;

|| To_char(emp_count, '9999')
|| To_char(job_hist_count, '9999'));
END LOOP;
END;
/

``````

Sample Output:

```SQL> /
Public Relations    Hermann            1    0
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
Sales               John              34    2
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:

Improve this sample solution and post your code through Disqus

What is the difficulty level of this exercise?

﻿

Inviting useful, relevant, well-written and unique guest posts