drop table Project
drop table Employee
Create table Project (project_id int, employee_id int)
Create table Employee (employee_id int, name varchar(20), experience_years int)
insert into Project (project_id, employee_id) values ('1', '1')
insert into Project (project_id, employee_id) values ('1', '2')
insert into Project (project_id, employee_id) values ('1', '3')
insert into Project (project_id, employee_id) values ('2', '1')
insert into Project (project_id, employee_id) values ('2', '4')
insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '1')
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2')
select * from Project
select * from Employee
1075. Project Employees I
Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.
select project_id,round(cast(AVG(e.experience_years) as float),2) as average_years
from Project p join Employee e
on p.employee_id = e.employee_id
group by p.project_id
1076. Project Employees II
Write an SQL query that reports all the projects that have the most employees.
CTE
with cte as(
select project_id,count(employee_id) as people
from Project
group by project_id
),cte2 as
(select project_id,RANK()over(order by people desc) as rnk from cte
)select project_id from cte2 where rnk=1
Regular:
select project_id
from Project
group by project_id
having count(employee_id)=
(
select top 1 count(distinct employee_id)
from Project
group by project_id
)
1077. Project Employees III
Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.
CTE
with cte as(
select p.project_id,e.employee_id,RANK() over(partition by p.project_id order by e.experience_years desc) as rnk
from Project p join Employee e
on p.employee_id=e.employee_id
)select project_id,employee_id from cte where rnk=1