1075. Project Employees I

테이블 생성 코드

Create table If Not Exists Project (project_id int, employee_id int)

Create table If Not Exists Employee (employee_id int, name varchar(10), experience_years int)

Truncate table Project

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')

Truncate table Employee

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 project_id, ROUND(AVG(experience_years),2) AS average_years

FROM Project P

JOIN Employee E ON P.employee_id = E.employee_id

GROUP BY P.project_id

 

 

 

 해설

 

1. Project 테이블과 Employee 테이블을 join 한다.

 

2. experience_years의 평균을 구하고 소수점 둘째자리까지 반올림하고 average_years로 이름을 바꿔서 출력한다.

 

3. 그룹바이에 P.project_id를 넣어서 묶어준다.