Write sql query to find the employees who are making more than 80% of average salary

There is a table as below

Emp (EmpID, Salary)

Write query to find the employees who are making more than 80% of average salary. Discuss the query plan for written query.

Solution:


IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
DROP TABLE #Employee

CREATE TABLE #Employee (EmpID int , Salary int)

INSERT INTO #Employee VALUES(1,3000)
INSERT INTO #Employee VALUES(2,4500)
INSERT INTO #Employee VALUES(3,5000)
INSERT INTO #Employee VALUES(4,6500)
INSERT INTO #Employee VALUES(5,4500)
INSERT INTO #Employee VALUES(6,2000)
INSERT INTO #Employee VALUES(7,1000)


SELECT *
FROM #Employee
WHERE Salary > (SELECT AVG(Salary)*.8 as [80%Salary] FROM #Employee)


This is the estimated execution plan:

|--Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[#Employee].[Salary]>CONVERT_IMPLICIT(numeric(10,0),[Expr1008],0)*(0.8)))
|--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016]/CONVERT_IMPLICIT(int,[Expr1015],0) END))
| |--Stream Aggregate(DEFINE:([Expr1015]=COUNT_BIG([tempdb].[dbo].[#Employee].[Salary]), [Expr1016]=SUM([tempdb].[dbo].[#Employee].[Salary])))
| |--Table Scan(OBJECT:([tempdb].[dbo].[#Employee]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#Employee]))