SQL_05_문제풀이(JOIN)

-- TODO 직원 id(emp.emp_id)가 200번대(200 ~ 299)인 직원들의  
-- 직원_ID(emp.emp_id), 이름(emp.emp_name), 급여(emp.salary), 소속부서이름(dept.dept_name), 부서위치(dept.loc)를 조회. 직원_ID의 내림차순으로 정렬.
select e.emp_id,
	   e.emp_name,
       e.salary,
       d.dept_name,
       d.loc
from   emp e join dept d on e.dept_id = d.dept_id 
where e.emp_id between 200 and 299
order by 1 desc;

-- TODO 업무(emp.job_id)가 'FI_ACCOUNT'인 직원의 ID(emp.emp_id), 이름(emp.emp_name),
-- 업무(emp.job_id), 소속부서이름(dept.dept_name), 부서위치(dept.loc)를 조회. 직원_ID의 내림차순으로 정렬.
select e.emp_id,
	   e.emp_name,
       e.job_id,
       d.dept_name,
       d.loc
from emp e join dept d on e.dept_id = d.dept_id
		   join job j on e.job_id = j.job_id
where e.job_id = 'FI_ACCOUNT'
order by e.emp_id desc;		

-- TODO 커미션을(emp.comm_pct) 받는 직원들의 직원_ID(emp.emp_id), 이름(emp.emp_name),
-- 급여(emp.salary), 커미션비율(emp.comm_pct), 소속부서이름(dept.dept_name), 부서위치(dept.loc)를 조회. 직원_ID의 내림차순으로 정렬.
select e.emp_id,
	   e.emp_name,
       e.salary,
       e.comm_pct,
       d.dept_name,
       d.loc
from emp e join dept d on e.dept_id = d.dept_id
where e.comm_pct is not null
order by e.emp_id desc;	

-- TODO 'New York'에 위치한(dept.loc) 부서의 부서_ID(dept.dept_id), 부서이름(dept.dept_name), 위치(dept.loc), 
-- 그 부서에 소속된 직원_ID(emp.emp_id), 직원 이름(emp.emp_name), 업무(emp.job_id)를 조회. 
select d.dept_id,
       d.dept_name,
       d.loc,
       e.emp_id,
       e.emp_name,
       e.job_id
from dept d join emp e on d.dept_id = e.dept_id
where d.loc = 'New York';	

-- TODO 직원_ID(emp.emp_id), 이름(emp.emp_name), 업무_ID(emp.job_id), 업무명(job.job_title) 를 조회.
select e.emp_id,
	   e.emp_name,
       e.job_id,
       j.job_title
from emp e join job j on e.job_id = j.job_id
order by 1;
      
-- TODO: 직원 ID 가 200 인 직원의 직원_ID(emp.emp_id), 이름(emp.emp_name), 
-- 급여(emp.salary), 담당업무명(job.job_title), 소속부서이름(dept.dept_name)을 조회              
select e.emp_id,
	   e.emp_name,
       e.salary,
       j.job_title,
       d.dept_name
from emp e join dept d on e.dept_id = d.dept_id
		   join job j on e.job_id = j.job_id
where e.emp_id = 200;		

-- TODO: 'Shipping' 부서의 부서명(dept.dept_name), 위치(dept.loc), 
-- 소속 직원의 이름(emp.emp_name), 업무명(job.job_title)을 조회. 직원이름 내림차순으로 정렬
select d.dept_name,
       d.loc,
       e.emp_name,
       j.job_title
from emp e join dept d on e.dept_id = d.dept_id
		   join job j on e.job_id = j.job_id
where d.dept_name = 'Shipping'
order by e.emp_name desc;	

-- TODO: 'San Francisco' 에 근무(dept.loc)하는 직원의 id(emp.emp_id), 
-- 이름(emp.emp_name), 입사일(emp.hire_date)를 조회 입사일은 'yyyy년 mm월 dd일' 형식으로 출력
select e.emp_id,
	   e.emp_name,
       date_format(e.hire_date, '%Y년 %m월 %d일')
from emp e join dept d on e.dept_id = d.dept_id
order by emp_id;


-- TODO 부서별 급여(salary)의 평균을 조회. 부서이름(dept.dept_name)과 급여평균을 출력. 급여 평균이 높은 순서로 정렬. 
select d.dept_name,
	   avg(e.salary)
from emp e join salary_grade s on e.salary between s.low_sal and s.high_sal
		   join dept d on e.dept_id = d.dept_id
group by d.dept_name;

-- TODO 직원의 ID(emp.emp_id), 이름(emp.emp_name), 업무명(job.job_title), 
-- 급여(emp.salary), 급여등급(salary_grade.grade), 소속부서명(dept.dept_name)을 조회. 등급 내림차순으로 정렬
select e.emp_id,
	   e.emp_name,
       j.job_title,
       e.salary,
       concat(s.grade,'등급') grade,
       d.dept_name
from emp e join job j on e.job_id = j.job_id
		   join dept d on e.dept_id = d.dept_id
           join salary_grade s on e.salary between s.low_sal and s.high_sal
order by 5 desc;

-- TODO salary 등급(salary_grade.grade)이 1인 직원들이 부서별로 몇명있는지 조회. 직원수가 많은 부서 순서대로 정렬.
select dept_name,
	   count(*)
from emp e join salary_grade s on e.salary between s.low_sal and s.high_sal
		   join dept d on e.dept_id = d.dept_id
where s.grade = 1
group by dept_name
order by 1 desc;