Highest paid employees package in PL/SQL
PL/SQL Package: Exercise-15 with Solution
Write a code in PL/SQL that create a package that contains a function to retrieve the highest paid employee in each department.
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
PL/SQL Code:
Sample Output:
Package created. Package Body created
Flowchart:

To execute the package:
Sample Output:
statement processed. Department ID: 10 Employee ID: 200 Name: Jennifer Whalen Salary: 4400 ---------------------------------- Department ID: 20 Employee ID: 201 Name: Michael Hartstein Salary: 13000 ---------------------------------- Department ID: 30 Employee ID: 114 Name: Den Raphaely Salary: 11000 ---------------------------------- Department ID: 40 Employee ID: 203 Name: Susan Mavris Salary: 6500 ---------------------------------- .....
Flowchart:

Explanation:
The said code in Oracle's PL/SQL package that retrieve highest paid employee in each department and returns the information in a collection of employee records, where each record contains the department_id, employee_id, name, and salary of the highest paid employee in the respective department.
A function get_highest_paid_employees are declared in this package with no input parameters and it returns a collection of employee records representing the highest paid employees in each department.
Inside the function, one local variable highest_paid_employees of type employee_report_type is declared.
The function uses a SQL query with the RANK() window function to rank employees by salary within each department.
The query selects the department_id, employee_id, first_name, last_name, and salary from the "employees" table and assigns a ranking to each employee based on their salary within their respective department.
The outer query filters the results to include only those employees with a ranking of 1, which represents the highest-paid employee in each department.
Finally, the collection highest_paid_employees containing the highest paid employees from each department is returned as the result of the function.
Previous: Package to retrieve employees with the same manager in PL/SQL.
Next: Average tenure of employees by job category package in PL/SQL.
What is the difficulty level of this exercise?