PL/SQL Cursor Exercises: Display department name, head of the department,city, and employee with highest salary

PL/SQL Cursor: Exercise-28 with Solution

Write a PL/SQL block to display department name, head of the department,city, and employee with highest salary. Display name, city, hod, emp with highest salary

Sample Solution:

PL/SQL Code:

DECLARE
    CURSOR dpt_cur IS
      SELECT d.department_id      id,
             department_name      dptname,
             city,
             Nvl(first_name, '...') manager
      FROM   departments d
             left outer join employees e
                          ON ( d.manager_id = e.employee_id )
             join locations l USING(location_id)
      ORDER  BY 2;
    emp_name       employees.first_name%TYPE;
    emp_max_salary employees.salary%TYPE;
BEGIN
    FOR dept_all IN dpt_cur LOOP
        SELECT Max(salary)
        INTO   emp_max_salary
        FROM   employees
        WHERE  department_id = dept_all.id;

        IF emp_max_salary IS NULL THEN
          emp_name := '...';
        ELSE
          SELECT first_name
          INTO   emp_name
          FROM   employees
          WHERE  department_id = dept_all.id
                 AND salary = emp_max_salary;
        END IF;

        dbms_output.Put_line(Rpad(dept_all.dptname, 20)
                             || Rpad(dept_all.manager, 15)
                             || Rpad(dept_all.city, 20)
                             || Rpad(emp_name, 20));
    END LOOP;
END;
/ 

Sample Output:

SQL> /
Accounting          Shelley        Seattle             Shelley
Administration      Jennifer       Seattle             Jennifer
Benefits            ...            Seattle             ...
Construction        ...            Seattle             ...
Contracting         ...            Seattle             ...
Control And Credit  ...            Seattle             ...
Corporate Tax       ...            Seattle             ...
Executive           Steven         Seattle             Steven
Finance             Nancy          Seattle             Nancy
Government Sales    ...            Seattle             ...
Human Resources     Susan          London              Susan
IT                  Alexander      Southlake           Alexander
IT Helpdesk         ...            Seattle             ...
IT Support          ...            Seattle             ...
Manufacturing       ...            Seattle             ...
Marketing           Michael        Toronto             Michael
NOC                 ...            Seattle             ...
Operations          ...            Seattle             ...
Payroll             ...            Seattle             ...
Public Relations    Hermann        Munich              Hermann
Purchasing          Den            Seattle             Den
Recruiting          ...            Seattle             ...
Retail Sales        ...            Seattle             ...
Sales               John           Oxford              John
Shareholder Services...            Seattle             ...
Shipping            Adam           South San Francisco Adam
Treasury            ...            Seattle             ...

PL/SQL procedure successfully completed. 

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to show the uses of a variable in explicit cursor query, and no result set is affected despite that value of the variable is incremented after every fetch.
Next: 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.