use hr_join;
/* **************************************************************************
서브쿼리(Sub Query)
- 쿼리안에서 select 쿼리를 사용하는 것.
- 메인 쿼리 - 서브쿼리
서브쿼리가 사용되는 구
- select절, from절, where절, having절
서브쿼리의 종류
- 어느 구절에 사용되었는지에 따른 구분
- 스칼라 서브쿼리 - select 절에 사용. 반드시 서브쿼리 결과가 1행 1열(값 하나-스칼라) 0행이 조회되면 null을 반환
- 인라인 뷰 - from 절에 사용되어 테이블의 역할을 한다.
서브쿼리 조회결과 행수에 따른 구분
- 단일행 서브쿼리 - 서브쿼리의 조회결과 행이 한행인 것.
- 다중행 서브쿼리 - 서브쿼리의 조회결과 행이 여러행인 것.
동작 방식에 따른 구분
- 비상관(비연관) 서브쿼리 - 서브쿼리에 메인쿼리의 컬럼이 사용되지 않는다.
메인쿼리에 사용할 값을 서브쿼리가 제공하는 역할을 한다.
- 상관(연관) 서브쿼리 - 서브쿼리에서 메인쿼리의 컬럼을 사용한다.
메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 주로 사용한다.
- 서브쿼리는 반드시 ( ) 로 묶어줘야 한다.
************************************************************************** */
-- 직원_ID(emp.emp_id)가 120번인 직원과 같은 업무(emp.job_id)를 하는 직원의 id(emp_id),이름(emp.emp_name), 업무(emp.job_id), 급여(emp.salary) 조회
select emp_id, emp_name, job_id, salary
from emp
where job_id = 'ST_MAN';
select job_id from emp where emp_id = 120;
select emp_id, emp_name, job_id, salary
from emp
where job_id = (select job_id from emp where emp_id = 120);
-- 직원_id(emp.emp_id)가 115번인 직원과 같은 업무(emp.job_id)를 하고 같은 부서(emp.dept_id)에 속한 직원들을 조회하시오.
-- pair 방식 서브쿼리. 두개 이상 컬럼 비교해도 된다.
select *
from emp
where (job_id, dept_id) = (select job_id, dept_id from emp where emp_id = 115);
select * from emp
where (job_id, dept_id) = ('PU_MAN', 30);
-- 직원들 중 급여(emp.salary)가 전체 직원의 평균 급여보다 적은 직원들의 id(emp.emp_id), 이름(emp.emp_name), 급여(emp.salary)를 조회.
select avg(salary) from emp;
select emp_id, emp_name, salary from emp
where salary < (select avg(salary) from emp)
order by 3 desc;
-- 부서직원들의 평균이 전체 직원의 평균(emp.salary) 이상인 부서의 이름(dept.dept_name), 평균 급여(emp.salary) 조회.
-- 평균급여는 소숫점 2자리까지 나오고 통화표시($)와 단위 구분자 출력
select d.dept_name,
concat('$', format(a.avg, 2)) as '평균급여'
from (
select dept_id,
avg(salary) as 'avg'
from emp
group by dept_id
having avg(salary) > (select avg(salary) from emp)
order by 2 -- 오름차순
) a left join dept d on a.dept_id = d.dept_id;
-- TODO: 직원의 ID(emp.emp_id)가 145인 직원보다 많은 연봉을 받는 직원들의 이름(emp.emp_name)과 급여(emp.salary) 조회.급여가 큰 순서대로 조회
select emp_name, salary
from emp
where salary > (select salary
from emp
where emp_id = 145);
select salary from emp where emp_id = 145;
-- TODO: 직원의 ID(emp.emp_id)가 150인 직원과 업무(emp.job_id)와 상사(emp.mgr_id)가 같은 직원들의
-- id(emp.emp_id), 이름(emp.emp_name), 업무(emp.job_id), 상사(emp.mgr_id) 를 조회
select emp_id, emp_name, job_id
from emp
where (job_id, mgr_id) in (select job_id, mgr_id
from emp
where emp_id = 150);
-- TODO : EMP 테이블에서 직원 이름이(emp.emp_name)이 'John'인 직원들 중에서
-- 급여(emp.salary)가 가장 높은 직원의 salary(emp.salary)보다 많이 받는 직원들의 id(emp.emp_id), 이름(emp.emp_name), 급여(emp.salary)를 조회.
select emp_id, emp_name, salary
from emp
where salary > (select max(salary)
from emp
where emp_name = 'John');
select salary from emp where emp_name = 'John';
-- TODO: 급여(emp.salary)가장 많이 받는 직원이 속한 부서의 이름(dept.dept_name), 위치(dept.loc)를 조회.
select dept_name, loc
from dept
where dept_id in (select dept_id
from emp
where salary = (select max(salary) from emp));
-- join 활용
select *
from dept d left join emp e on d.dept_id = e.dept_id
where salary = (select max(salary) from emp);
-- TODO: 급여(emp.salary)를 제일 많이 받는 직원들의 이름(emp.emp_name), 부서명(dept.dept_name), 급여(emp.salary) 조회.
select e.emp_name, d.dept_name, e.salary
from emp e join dept d on e.dept_id = d.dept_id
where salary = (select max(salary) from emp);
-- TODO: 30번 부서(emp.dept_id) 의 평균 급여(emp.salary)보다 급여가 많은 직원들의 모든 정보를 조회.
select *
from emp
where (salary) > all(select avg(salary)
from emp
where dept_id = 30)
order by salary;
select avg(salary) from emp where dept_id = 30;
-- TODO: 전체 직원들 중 담당 업무 ID(emp.job_id) 가 'ST_CLERK'인 직원들의 평균 급여보다 적은 급여를 받는 직원들의 모든 정보를 조회.
-- 단 업무 ID가 'ST_CLERK'이 아닌 직원들만 조회.
select *
from emp
where salary < (select avg(salary)
from emp
where job_id = 'ST_CLERK')
and (job_id != 'ST_CLERK' or job_id is null)
order by salary desc;
select avg(salary) from emp where job_id = 'ST_CLERK';
-- TODO: EMP 테이블에서 업무(emp.job_id)가 'IT_PROG' 인 직원들의 평균 급여보다 더 많은 급여를 받는
-- 직원들의 id(emp.emp_id), 이름(emp.emp_name), 급여(emp.salary)를 급여 내림차순으로 조회.
select emp_id, emp_name, salary
from emp
where salary > (select avg(salary)
from emp
where job_id = 'IT_PROG')
order by 3 desc;
select avg(salary) from emp where job_id = 'IT_PROG';
-- TODO: 전체 직원들 중 'IT' 부서(dept.dept_name)의 직원중 가장 많은 급여를 받는 직원보다 더 많이 받는
-- 직원의 ID(emp.emp_id), 이름(emp.emp_name), 입사일(emp.hire_date), 부서 ID(emp.dept_id), 급여(emp.salary) 조회
-- 입사일은 "yyyy년 mm월 dd일" 형식으로 출력
-- join 활용
select emp_id, emp_name, date_format(hire_date, '%Y년 %m월 %d일'), dept_id, salary
from emp
where salary > (select max(e.salary)
from emp e join dept d on e.dept_id = d.dept_id
where dept_name = 'IT');
select salary from emp join dept on emp.dept_id = dept.dept_id where dept_name = 'IT';
select emp_id, emp_name, date_format(hire_date, '%Y년 %m월 %d일'), dept_id, salary
from emp
where salary > (select max(e.salary)
from emp e
where dept_id = (select dept_id
from dept
where dept_name = 'IT'));
/* ----------------------------------------------
다중행 서브쿼리
- 서브쿼리의 조회 결과가 여러행인 경우
- where절 에서의 연산자
- in : 단일 행 서브쿼리의 = 과 동일한 의미
- 비교연산자 any : 조회된 값들 중 하나만 참이면 참 (where 컬럼 > any(서브쿼리) )
- 비교연산자 all : 조회된 값들 모두와 참이면 참 (where 컬럼 > all(서브쿼리) )
------------------------------------------------*/
-- 'Alexander' 란 이름(emp.emp_name)을 가진 관리자(emp.mgr_id)의
-- 부하 직원들의 ID(emp_id), 이름(emp_name), 업무(job_id), 입사년도(hire_date-년도만출력), 급여(salary)를 조회
select emp_id, emp_name from emp where emp_name = 'Alexander';
select emp_id, emp_name, job_id, year(hire_date), salary, mgr_id
from emp
where mgr_id in (select emp_id
from emp
where emp_name = 'Alexander');
-- 직원 ID(emp.emp_id)가 101, 102, 103 인 직원들 보다 급여(emp.salary)를 많이 받는 직원의 모든 정보를 조회.
select emp_id, salary from emp where emp_id in (101, 102, 103);
select *
from emp
where salary > all (select salary
from emp
where emp_id in (101, 102, 103));
select *
from emp
where salary > (select max(salary)
from emp
where emp_id in (101, 102, 103));
-- 직원 ID(emp.emp_id)가 101, 102, 103 인 직원들 중 급여가 가장 적은 직원보다 급여를 많이 받는 직원의 모든 정보를 조회.
select salary from emp where emp_id in (101, 102, 103);
select * from emp
where salary > any (select salary
from emp
where emp_id in (101, 102, 103));
-- TODO : 부서 위치(dept.loc) 가 'New York'인 부서에 소속된 직원의 ID(emp.emp_id), 이름(emp.emp_name), 부서_id(emp.dept_id) 를 sub query를 이용해 조회.
select * from dept where loc = 'New York';
select emp_id,
emp_name,
dept_id
from emp
where dept_id in (select dept_id
from dept
where loc = 'New York');
-- join 활용
select *
from emp e join dept d on e.dept_id = d.dept_id
where d.loc = 'New York';
-- TODO : 최대 급여(job.max_salary)가 6000이하인 업무를 담당하는 직원(emp)의 모든 정보를 sub query를 이용해 조회.
select *
from emp e
where job_id in (select job_id
from job
where max_salary <= 6000);
-- TODO: 전체 직원들중 부서_ID(emp.dept_id)가 20인 부서의 모든 직원들 보다 급여(emp.salary)를 많이 받는 직원들의 정보를 sub query를 이용해 조회.
select *
from emp
where salary > all(select salary
from emp
where emp.dept_id = 20);
-- max 활용
select * from emp
where salary > (select max(salary)
from emp
where emp.dept_id = 20);
-- TODO: 부서별 급여의 평균중 가장 적은 부서의 평균 급여보다 보다 많이 받는 직원들의 이름, 급여, 업무를 서브쿼리를 이용해 조회
select emp_name,
salary,
job_id
from emp
where salary > any(select avg(salary)
from emp
group by dept_id)
order by 2;
select dept_id, avg(salary) from emp group by dept_id;
-- TODO: 업무 id(job_id)가 'SA_REP' 인 직원들중 가장 많은 급여를 받는 직원보다
-- 많은 급여를 받는 직원들의 이름(emp_name), 급여(salary), 업무(job_id) 를 subquery를 이용해 조회.
select emp_name,
salary,
job_id
from emp
where salary > all (select salary
from emp
where job_id = 'SA_REP')
order by 2;