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
