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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics