SQL Server : Some common queries on Employee and Department table

Schema of Employee and Department table are as follows :

Create Department :

Create table Department (DepartmentId integer not null primary key identity, DepartmentName varchar(50));


Create Employee :

Create table Employee(
EmployeeId integer not null identity primary key
,FirstName varchar(50)
,LastName varchar(50)
,DepartmentId integer
);

---------------Some common queries on above tables-----------------

--Department wise employees name and other details 
select d.DepartmentName, e.FirstName,e.LastName,e.Salary
from Employee e left join Department d on e.departmentId=d.DepartmentId

-- Find out no. of Employees in Each Deaprtment.
---------------------------------------------------------
select d.DepartmentName, count(e.Employeeid)
from Employee e
left join Department d on e.departmentId=d.DepartmentId
group by d.departmentID,d.departmentName
 --------------------------------------------------------
-- find out departments where no.of employee more than 3
select d.DepartmentName, count(e.Employeeid)
from Employee e
left join Department d on e.departmentId=d.DepartmentId
where d.departmentname is not null
group by d.departmentID,d.departmentName having  count(e.employeeid)>3

-----------------------------------------------------
-- Find out name of employee,salary and departmentName, who has max salary in their department .
--i.e. departmentname and name of employee who has max salary in their department.

SELECT DepartmentName, firstName,LastName, Salary, Salaryrank from
(select d.DepartmentName, e.firstName,e.LastName, e.Salary, row_number() over (partition by e.departmentID order by salary desc)  as Salaryrank
from Employee e
left join Department d on e.departmentId=d.DepartmentId) as result
where result.Salaryrank=1
-- Result will contains department name ,name of employee who has highest salary in their department, salary