sqlbeginner10 minutes

Create a Function to Calculate Average Salary by Department

Write an SQL function that calculates the average salary for a given department from an employee table.

Challenge prompt

You have an Employees table with columns: EmployeeID, Department, and Salary. Write an SQL function named CalculateAvgSalary that takes a department name as input and returns the average salary of all employees in that department.

Guidance

• Use the CREATE FUNCTION syntax supported by your SQL dialect.
• Ensure the function accepts a department name as a parameter.
• Use an aggregation query with AVG() to calculate the average salary.
• Return the calculated average salary from the function.

Hints

• You can use a SELECT statement inside the function to compute the average salary.
• Use the WHERE clause to filter employees by the input department.
• Remember to specify the return type for your function (e.g., FLOAT or DECIMAL).

Starter code

CREATE FUNCTION CalculateAvgSalary(dept_name VARCHAR) RETURNS FLOAT AS $$
BEGIN
  -- Your code here
END;
$$ LANGUAGE plpgsql;

Expected output

If you call CalculateAvgSalary('Sales'), it should return the average salary of all employees in the Sales department.

Core concepts

CREATE FUNCTIONAVG aggregate functionWHERE clause

Challenge a Friend

Send this duel to someone else and see if they can solve it.