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:

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 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:

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