sqlbeginner10 minutes

Write a SQL Function to Calculate the Average Salary from Employee Table

Create a SQL function that calculates the average salary of all employees in a given department using a simple SELECT and AVG aggregate function.

Challenge prompt

Write a SQL function named `GetAverageSalary` that accepts a department ID as input and returns the average salary of all employees in that department. Assume you have an `Employees` table with columns `EmployeeID`, `DepartmentID`, and `Salary`. Your function should handle the case where there are no employees in the department by returning NULL.

Guidance

• Use the AVG() aggregate function to calculate the average salary.
• Filter rows by the given DepartmentID in the WHERE clause.
• Return the result from within the function.

Hints

• Recall that AVG function ignores NULL values automatically.
• Make sure your function uses correct syntax for creating scalar functions in your SQL dialect.

Starter code

CREATE FUNCTION GetAverageSalary(@DeptID INT)
RETURNS FLOAT
AS
BEGIN
    -- Write your query here
    RETURN NULL
END

Expected output

If Employees table has salaries (50000, 70000, 60000) in department 2, then GetAverageSalary(2) should return 60000.

Core concepts

SQL functionsAggregate functionsFiltering with WHERE clause

Challenge a Friend

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