MySQL) JOIN연습

# 모든 직원의 이름과 소속 부서명을 출력

  1) 내부조인 

select e.name , d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id;

   2) LEFT JOIN :  소속부서 없는 직원 이름도 출력  

select  name , dept_name
from employees e
left join departments d on e.dept_id = d.dept_id;

  3) RIGHT JOIN :  직원 없는 부서도 출력 

select name , dept_name
from employees e
right join departments d on e.dept_id = d.dept_id;

# 급여가 5000 이상인 직원의 이름과 부서명 출력

select e.name, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id
where e.salary >= 5000;

#  부서명이 '디자인과'인 직원들의 이름과 급여 출력

select e.name, e.salary
from employees e
inner join departments d on e.dept_id = d.dept_id
where d.dept_name = '디자인과';

#  프로젝트에 참여하고 있는 직원의 이름과 프로젝트명 출력

select e.name, p.project_name
from employees e
inner join projects p on e.emp_id = p.emp_id;

#   부서명과 해당 부서에 속한 직원 수 출력 (group by, count) 

select  d.dept_name , count(e.emp_id)
from departments d
inner join employees e on d.dept_id = e.dept_id 
group by d.dept_name;

 

#  모든 프로젝트에 대한 프로젝트명과 담당 직원의 이름 출력

select p.project_name, e.name
from projects p
inner join employees e on p.emp_id = e.emp_id;

#  부서별 평균 급여 출력 (group by, avg) 

select d.dept_name, avg(e.salary)
from employees e
inner join departments d on e.dept_id = d.dept_id
group by d.dept_name;

#  급여가 가장 높은 직원의 이름과 부서명 출력 

  1) order by, limit 1 이용

select e.name, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id
order by e.salary desc limit 1;

  2) 서브쿼리, max 함수 이용 

select e.name, d.dept_name    
from employees e
inner join departments d on e.dept_id = d.dept_id
where e.salary = (
select max(salary)
    from employees
);

#  모든 직원의 이름과, 프로젝트명이 있을 경우 함께 출력

select *
from employees e
left join projects p on e.emp_id = p.emp_id;

#  프로젝트에 참여하지 않은 직원의 이름 출력

select e.name
from employees e
left join projects p on e.emp_id = p.emp_id
where project_name is null;

#  부서가 없는 직원도 포함하여 이름과 부서명 출력

select e.name, d.dept_name
from employees e
left join departments d on e.dept_id = d.dept_id;

#  부서별로 직원이 없는 부서도 포함하여 부서명과 직원 수 출력 (group by, count)

select d.dept_name, count(e.emp_id)  /*직원 수를 세어줘야하니까 */
from departments d
left join employees e on d.dept_id=e.dept_id
group  by d.dept_name;

#  모든 부서와 그 부서의 평균 급여를 출력하되, 직원이 없는 부서도 출력 (group by, avg)

select d.dept_name, avg(e.salary)
from departments d
left join employees e on d.dept_id = e.dept_id
group by d.dept_name;

#  모든 프로젝트와 해당 직원의 이름을 출력하되, 직원 정보가 없더라도 출력 (right join)

select p.project_name, e.name
from employees e
right join projects p on e.emp_id = p.emp_id;

#  모든 부서와 그 부서에 속한 직원 이름을 출력하되, 직원이 없어도 부서는 출력

select d.dept_name, e.name
from employees e
right join departments d on e.dept_id = d.dept_id;

#  각 프로젝트의 이름, 담당 직원 이름, 소속 부서명 출력 ( 테이블 3개 join)

select p.project_name, e.name, d.dept_name
from employees e
inner join projects p on e.emp_id = p.emp_id
inner join departments d on e.dept_id = d.dept_id;

#  모든 부서에 대해 평균 급여가 6000 이상인 부서명만 출력 (left조인, group by having 이용) 

select d.dept_name, avg(e.salary)
from departments d
inner join employees e on d.dept_id = e.dept_id
group by d.dept_name
having avg(e.salary) >= 6000;

#  프로젝트가 없는 부서명 출력 (테이블 3개 join-left 조인) (hint : where project_id IS NULL) 

select *
from departments d
left join projects p on p.emp_id = e.emp_id
left join employees e on e.dept_id = d.dept_id
where p.project_is is null;

#  프로젝트 수가 가장 많은 직원의 이름을 출력 (내부조인, group by, order by, limit 1) 

select e.name, count(e.name) as project_count
from employees e
inner join projects p on e.emp_id = p.emp_id
group by e.name
order by project_count desc limit 1;  /* 이름으로 묶고 이름으로 카운트*/

 

select e.name
from employees e
inner join projects p on e.emp_id = p.emp_id
group by e.name
order by count(project_id ) desc limit 1;