Q:

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

0

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

Displays department name and costliest employee

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

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. 

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now