PL/SQL Cursor Exercises: Display the name of department and their head

PL/SQL Cursor: Exercise-31 with Solution

Write a block in PL/SQL to display the name of department and their head.

Display department name and HOD using 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_dep_cur  IS
      SELECT department_name,
             manager_id
      FROM   departments;
    emp_manager employees.first_name%TYPE;
	
BEGIN
dbms_output.Put_line(Rpad('Department', 30)||Rpad('Department Head', 30));
dbms_output.Put_line('----------------------------------------------');
    FOR emp_dep_rec  IN emp_dep_cur LOOP
        IF emp_dep_rec.manager_id IS NULL THEN
          emp_manager := 'yet to join';
        ELSE
          SELECT first_name
          INTO   emp_manager
          FROM   employees
          WHERE  employee_id = emp_dep_rec.manager_id;
        END IF;

        dbms_output.Put_line(Rpad(emp_dep_rec.department_name, 30)
                             || emp_manager);
    END LOOP;
END;
/ 

Sample Output:

SQL> /
Department                    Department Head
----------------------------------------------
Administration                Jennifer
Marketing                     Michael
Purchasing                    Den
Human Resources               Susan
Shipping                      Adam
IT                            Alexander
Public Relations              Hermann
Sales                         John
Executive                     Steven
Finance                       Nancy
Accounting                    Shelley
Treasury                      yet to join
Corporate Tax                 yet to join
Control And Credit            yet to join
Shareholder Services          yet to join
Benefits                      yet to join
Manufacturing                 yet to join
Construction                  yet to join
Contracting                   yet to join
Operations                    yet to join
IT Support                    yet to join
NOC                           yet to join
IT Helpdesk                   yet to join
Government Sales              yet to join
Retail Sales                  yet to join
Recruiting                    yet to join
Payroll                       yet to join

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: 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.
Next: Write a PL/SQL program to display the name of the employee and increment percentage of salary according to their working experiences.