PL/SQL Cursor Exercises: Print the department name, head of the department, city and number of employees are wroking in that department

PL/SQL Cursor: Exercise-43 with Solution

Write a block in PL/SQL to print the department name, head of the department, city and number of employees are wroking in that department.

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 cur_for_dep IS
      SELECT *
      FROM   departments
      WHERE  manager_id IS NOT NULL;
    FUNCTION Count_for_emp (p_deptid NUMBER)
    RETURN NUMBER
    IS
      count_of_emp NUMBER(3);
    BEGIN
        SELECT Count(*)
        INTO   count_of_emp
        FROM   employees
        WHERE  department_id = p_deptid;

        RETURN count_of_emp;
    END;
    FUNCTION Name_of_hod (p_deptid NUMBER)
    RETURN VARCHAR2
    IS
      emp_as_hod employees.first_name%TYPE;
    BEGIN
        SELECT first_name
        INTO   emp_as_hod
        FROM   employees
        WHERE  employee_id = (SELECT manager_id
                              FROM   departments
                              WHERE  department_id = p_deptid);

        RETURN emp_as_hod;
    END;
    FUNCTION City_of_dep (p_deptid NUMBER)
    RETURN VARCHAR2
    IS
      city_head_off locations.city%TYPE;
    BEGIN
        SELECT city
        INTO   city_head_off
        FROM   locations
        WHERE  location_id = (SELECT location_id
                              FROM   departments
                              WHERE  department_id = p_deptid);

        RETURN city_head_off;
    END;
BEGIN
    dbms_output.Put_line(Rpad('Department Name', 30)
                         ||Rpad('Department Head', 20)
                         ||Rpad('Head Office', 20)
                         ||'Number of Employees');

dbms_output.Put_line('----------------------------------------------------------------------------------------');

FOR rows_of_dep IN cur_for_dep LOOP
    dbms_output.Put_line(Rpad(rows_of_dep.department_name, 30)
                         || Rpad(Name_of_hod(rows_of_dep.department_id), 20)
                         || Rpad(City_of_dep(rows_of_dep.department_id), 20)
                         || Count_for_emp(rows_of_dep.department_id));
END LOOP;
END; 

Sample Output:

SQL> /
Department Name               Department Head     Head Office         Number of Employees
----------------------------------------------------------------------------------------
Administration                Jennifer            Seattle             1
Marketing                     Michael             Toronto             2
Purchasing                    Den                 Seattle             6
Human Resources               Susan               London              1
Shipping                      Adam                South San Francisco 45
IT                            Alexander           Southlake           5
Public Relations              Hermann             Munich              1
Sales                         John                Oxford              34
Executive                     Steven              Seattle             3
Finance                       Nancy               Seattle             6
Accounting                    Shelley             Seattle             2

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to display the first name, job title and start date of employees.
Next: Write a block in PL/SQL to print the specifc number of rows from a table.