1) Employee with highest salary in the department
Query 1
Select EmployeeKey, FirstName, LastName, Emp.DepartmentName, Salary from dbo.Employee Emp
INNER JOIN
(Select DepartmentName, Max(Salary) as MaxSalary from dbo.EMployee
Group BY DepartmentName ) MaxSalEMp
ON EMp.DepartmentName = MaxSalEmp.DepartmentName
AND Emp.Salary = MaxSalEmp.MaxSalary
Query 2
Select EmployeeKey, FirstName, LastName, DepartmentName, Salary from
(Select EmployeeKey, FirstName, LastName, DepartmentName, Salary ,
rank() OVER (Partition by DepartmentName Order BY Salary desc) as MaxSal
from dbo.Employee ) as Emp
Where Emp.MaxSal = 1
Watch YouTube video with explanation here.
2) Employees having greater than average salary of the department
Select EmployeeKey, FirstName, LastName, Emp.DepartmentName, Salary from dbo.Employee Emp
INNER JOIN
(Select DepartmentName, Avg(Salary) as AvgSalary from dbo.EMployee
Group BY DepartmentName ) AvgEmpSal
ON Emp.DepartmentName = AvgEmpSal.DepartmentName
AND Emp.Salary > AvgEMpSal.AvgSalary
Watch YouTube video with explanation here.
3) Employees having more than average salary of the department but less than the overall average
Select EmployeeKey, FirstName, LastName, Emp.DepartmentName, Salary from dbo.Employee Emp
INNER JOIN
(Select DepartmentName, Avg(Salary) as AvgSalary from dbo.EMployee
Group BY DepartmentName ) AvgEmpSal
ON Emp.DepartmentName = AvgEmpSal.DepartmentName
AND Emp.Salary > AvgEMpSal.AvgSalary
WHERE Emp.Salary < (Select Avg(Salary) from dbo.EMployee)
Watch YouTube video with explanation here.
4) Employees having less than average salary of the department but greater than the average salary of any other department
Select EmployeeKey, FirstName, LastName, Emp.DepartmentName, Salary from dbo.Employee Emp
INNER JOIN
(Select DepartmentName, Avg(Salary) as AvgSalary from dbo.EMployee
Group BY DepartmentName ) AvgEmpSal
ON Emp.DepartmentName = AvgEmpSal.DepartmentName
AND Emp.Salary < AvgEMpSal.AvgSalary
WHERE Emp.Salary > ANY (Select Avg(Salary) from dbo.EMployee Group By DepartmentName)
Watch YouTube video with explanation here.
5) Employees with salary greater than their manager's salary
Query 1 - Fetch Employee's information
Select EmployeeKey, FirstName, LastName, DepartmentName, Salary from dbo.Employee emp
WHERE salary > (Select salary from dbo.EMployee where employeekey = emp.managerkey)
Query 2 - Fetch Employee's as well as Manager's information.
Select emp.EmployeeKey, FirstName, LastName, DepartmentName, Salary, MgrName, MgrSal from dbo.Employee emp
JOIN
(Select EmployeeKey, FirstName + ' ' + LastName as MgrName, Salary as MgrSal from dbo.Employee ) Mgr
ON emp.ManagerKey = Mgr.EmployeeKey
AND emp.Salary > Mgr.MgrSal
Watch YouTube video with explanation here.
6) Employees incremented salary if they have completed 2 years with the organization
Select EmployeeKey, FirstName, LastName, DepartmentName, Salary, Salary*1.15 as IncrSalary from dbo.Employee emp
WHERE DATEDIFF(YEAR,HireDate,'2020-12-31') > 2
Watch YouTube video with explanation here.