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:

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

location_id			integer
street_address			varchar(50)
postal_code			varchar(13)
city				varchar(25)
state_province			varchar(25)
country_id			varchar(2)

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.