w3resource

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.