SQL Question 21

Problem

  • 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.

Code

Use Stanton_Company
Go
If OBJECT_ID('Avg_salary', 'FN') is not null
DROP Function Avg_Salary
GO
-- create scalar function
Create Function Avg_salary
(
@DepartmentName varchar(40)
)
RETURNS decimal(7,2)
AS
BEGIN
DECLARE @average decimal(7,2)
SELECT @average = AVG(salary)
FROM employee e JOIN DEPARTMENT d on e.deptno = d.deptno
WHERE d.name = @departmentName
RETURN @average
END
-- Use the scalar function in Select clause
-- Test the UDF
SELECT d.name 'Department', dbo.avg_salary(d.name)'Avg Salary',
e.name,e.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

Results

Question 21 Result

Question 21 Result

Notes

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.

No Comments Yet.

Leave a comment