[Link] the number of employees hired each year.
select year(DateHired) as hyear ,count(EmployeeID) as noofemployee
from Employee
group by year(DateHired);
[Link] the average salary of employees in the Engineering department.
SELECT AVG([Link]) AS average_salary
FROM Employee e
JOIN Department d ON [Link] = [Link]
where [Link]='Engineering';
[Link] the number of employees in each job title.
select JobTitle,count(*) as noofemployee
from Employee
group by JobTitle;
[Link] departments with more than 3 employees.
select DepartmentID
from Employee
group by DepartmentID
having count(DepartmentID)>3;
[Link] the total number of projects handled by each project manager.
select count([Link])as noofprojects
from Employee e join Project p
on [Link]=[Link]
group by [Link];
[Link] departments where the average salary is more than 80,000.
select DepartmentID
from employee
group by DepartmentID
having avg(Salary)>80000;
[Link] the number of employees without a project in each department.
select DepartmentID,count(EmployeeID) as noofemployees
from Employee
where ProjectID is null
group by DepartmentID;
[Link] the total salary for all employees in the Marketing department.
select sum(Salary) as totalsal
from Employee e join Department d
on [Link]=[Link]
where DepartmentName='Marketing';
[Link] the earliest hire date for each department.
select DepartmentID ,min(DateHired)as earlyhiredate
from Employee
group by DepartmentID;
[Link] the average salary for employees hired after 2020.
select avg(Salary) as avgsalary
from Employee
where DateHired>'2020';
[Link] the total number of employees with the same job title.
select JobTitle ,count(EmployeeID) as noofemployeeswithsametitle
from Employee
group by JobTitle;
[Link] the number of projects each department is working on.
select count(ProjectId) as noofprojects
from Employee
group by DepartmentID;
[Link] the maximum salary in each project.
select max(Salary)as maxsalary
from Employee
group by ProjectID;
[Link] departments where the total salary exceeds 300,000.
select DepartmentID
from Employee
group by DepartmentID
having sum(Salary)>300000;
[Link] the average number of years employees have worked in each department.
SELECT DepartmentID, AVG(DATEDIFF(YEAR, DateHired, GETDATE())) AS
average_years_worked
FROM Employee
GROUP BY DepartmentID;
[Link] the names of employees along with their department names.
select [Link],[Link],[Link]
from Employee e join Department d
on [Link]=[Link];
[Link] the projects that employees from the Sales department are working on.
select [Link]
from Employee e join Department d
on [Link]=[Link]
join Project p on [Link]=[Link]
where [Link]='Sales';
[Link] employees and their respective project names (if any).
select [Link] ,[Link],[Link]
from Employee e join Project p
on [Link]=[Link];
[Link] employees who work in the same department as "Priya Patel".
SELECT FirstName,LastName
FROM Employee
WHERE DepartmentID = (
SELECT DepartmentID
FROM Employee
WHERE FirstName = 'Priya' and LastName='Patel'
)
AND Firstname!='Priya' and LastName!= 'Patel';
[Link] the names of managers for each department.
SELECT [Link], [Link] AS managerfname,[Link] as Managerlname
FROM Employee e
JOIN Department d ON [Link] = [Link]
WHERE [Link] = [Link];
[Link] employees who are working on "Project A".
select [Link],[Link],[Link]
from Employee e join Project p
on [Link]=[Link]
where ProjectName='Project A';
[Link] employees and the names of their projects, if they are assigned one.
SELECT [Link], [Link]
FROM Employee e
LEFT JOIN Project p ON [Link] = [Link];
[Link] employees who have been hired for longer than their department manager.
SELECT [Link], [Link], [Link] AS manager_name, [Link] AS
manager_hire_date
FROM Employee e
JOIN Employee m ON [Link] = [Link]
WHERE [Link] In ('SalesManager','FinanceManager','HRManager')
AND [Link] < [Link];
[Link] all departments that have at least one employee working on "Project B".
SELECT DISTINCT [Link]
FROM Employee e
JOIN Project p ON [Link] = [Link]
JOIN Department d ON [Link] = [Link]
WHERE [Link] = 'Project B';
[Link] employees who are working on projects managed by "Sandeep Garg".
SELECT [Link],[Link],[Link]
FROM Employee e
JOIN Project p ON [Link] = [Link]
JOIN Employee m ON [Link] = [Link]
WHERE [Link] = 'Sandeep' and [Link]='Garg';
[Link] employees whose project manager earns more than 100,000.
SELECT [Link], [Link], [Link], [Link] AS manager_name, [Link]
AS manager_salary
FROM Employee e
JOIN Project p ON [Link] = [Link]
JOIN Employee m ON [Link] = [Link]
WHERE [Link] > 100000;
[Link] employees who do not work on any project.
SELECT [Link]
FROM Employee e
LEFT JOIN Project p ON [Link] = [Link]
WHERE [Link] IS NULL;
