- Create a database called YourName_Company.
- Run the LewsCompany script to populate your database with tables and data
- Create a scalar UDF that calculates a AVG salary for a department.
- Parameter(s): @DepartmentName ie ‘Accounting’
- Returns: AvgSalary for department
- Now write a test script that will return all employees whose salary is BELOW avg for their department. This will require use of the CROSS APPLY function.
- In your script add a comment that answers this question. How would the result change if you used OUTER APPLY.
If OBJECT_ID('Avg_salary', 'FN') is not null
DROP Function Avg_Salary
-- create scalar function
Create Function Avg_salary
DECLARE @average decimal(7,2)
SELECT @average = AVG(salary)
FROM employee e JOIN DEPARTMENT d on e.deptno = d.deptno
WHERE d.name = @departmentName
-- Use the scalar function in Select clause
-- Test the UDF
SELECT d.name 'Department', dbo.avg_salary(d.name)'Avg Salary',
FROM dbo.EMPLOYEE e JOIN dbo.DEPARTMENT d ON e.deptno = d.deptno
WHERE salary < dbo.avg_salary(d.name)
Order by D.name
This appears to be a simple scalar User Defined Function returning the AVG salary for a department. This scalar value is used in a test script to return only those employees with salary BELOW avg for their department. So, the test script uses the UDF in a WHERE clause testing salaries for each employee against the AVG salary fora department. The test doesn't use the CROSS APPLY or OUTER APPLY.