Finding the Highest Salary in a Department using PostgreSQL

Function to Get the Highest Salary in a Department

Write a PostgreSQL function to return the highest salary from a given department.

Solution:

-- Create a function named highest_salary that takes a department ID as input and returns a numeric value
CREATE FUNCTION highest_salary(dept_id INT) RETURNS NUMERIC AS $$
-- Declare a variable to store the highest salary
DECLARE
    max_salary NUMERIC;
BEGIN
    -- Retrieve the maximum salary from the Employees table for the specified department and store it in max_salary
    SELECT MAX(salary) INTO max_salary FROM Employees WHERE department_id = dept_id;
    -- Return the highest salary
    RETURN max_salary;
END;
-- Specify the language used in the function as PL/pgSQL
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• Finds the highest salary in a department.
• Real-World Application:
• Used in payroll and HR analytics.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function to find the second highest salary in a given department.
• Write a PostgreSQL function that finds the average salary of all employees in a department.
• Write a PostgreSQL function that returns the name of the employee earning the highest salary in a department.
• Write a PostgreSQL function that returns the highest salary but only if it is greater than $50,000.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Function to return the Current Date and Time.
Next PostgreSQL Exercise: Function to Get the Number of Days Between Two Dates.