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

PL/SQL Cursor: Exercise-36 with Solution

Write a PL/SQL block to display the name of the department and their costliest employee.

Displays department name and costliest employee

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 department_cur IS
      SELECT department_id,
             department_name,
             Max(salary) maxsalary
      FROM   employees
             join departments USING (department_id)
      GROUP  BY department_id,
                department_name;
    emp_first_name employees.first_name%TYPE;
BEGIN
dbms_output.Put_line('------------------------------------------------------------');

dbms_output.Put_line(Rpad('Name of the Department', 35)
                     ||Rpad('First Name', 25));

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

FOR emp_dept_cur IN department_cur LOOP
    BEGIN
        SELECT first_name
        INTO   emp_first_name
        FROM   employees
        WHERE  department_id = emp_dept_cur.department_id
               AND salary = emp_dept_cur.maxsalary;

        dbms_output.Put_line(Rpad(emp_dept_cur.department_name, 35)
                             || Rpad(emp_first_name, 25));
    EXCEPTION
        WHEN too_many_rows THEN
          dbms_output.Put_line(Rpad(emp_dept_cur.department_name, 35)
                               || ' - More than one employee');
    END;
END LOOP;
END; 
/

Sample Output:

SQL> /
------------------------------------------------
Name of the Department             First Name
------------------------------------------------
Finance                            Nancy
Shipping                           Adam
Public Relations                   Hermann
Purchasing                         Den
Executive                          Steven
Administration                     Jennifer
Accounting                         Shelley
Human Resources                    Susan
Marketing                          Michael
IT                                 Alexander
Sales                              John

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 employee ID, first name, job title and the start date of present job.
Next: Write a PL/SQL block to display the last name, first name and overpaid amount by using parameters.