23강 오라클 SQL 서브 쿼리(2)

-- 1. 서브 쿼리에서의 그룹 함수 사용
-- 2. 다중 행 서브 쿼리

-- 03. 다중행 서브 쿼리의 사용
-- 10번 부서에 근무하는 사원의 이름과 10번 부서의 부서명을 출력
select e.ename, d.dname from emp e, dept d
where e.deptno = d.deptno and d.deptno=10;

select e.ename, d.dname from emp e,
-- 2. 메인 쿼리문 실행 : emp 테이블과 dept 테이블의 조인 실행.
(select deptno, dname from dept where deptno = 10)d
-- 1. 서브 쿼리문 실행 : 부서번호가 10인 deptno 출력. 별칭 d지정
where e.deptno = d.deptno;
-- 2-1. 메인 쿼리문 실행 : e.deptno와 d.deptno가 같은거.

-- 03. 서브쿼리에서의 그룹함수의 사용
-- 평균 급여를 구하는 쿼리문을 서브 쿼리로 사용하여 평균 급여보다
-- 더 많은 급여를 받는 사원을 검색하는 문장은 다음과 같습니다.

-- 1. 급여 평균구하기
select avg(sal) from emp;
-- 2. 급여 최대 값 구하기.
select max(sal) from emp;
-- 3. 부서별 급여 최대값 구하기
select max(sal) from emp group by deptno;

select ename, sal from emp 
where sal > (select avg(sal) from emp);

-- 04. 다중 행 서브쿼리
-- 반환되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리입니다.
-- 다중행 서브 쿼리는 반드시 다중 행 연산자와 함께 사용해야 한다.

-- IN : 메인 쿼리의 비교조건('=' 연산자로 비교할 경우) 서브 쿼리의 결과 중에서 하나라도 일치하면 참입니다.
-- ANY, SOME : 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상이 일치하면 참입니다.
-- ALL : 메인 쿼리의 비교 조건이 서브 쿼리의 결과와 모든 값이 일치히면 참입니다.
-- EXIST : 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참입니다.

-- 결과가 2개 이상 구해지는 쿼리문을 서브 쿼리로 기술할 경우에는 다중 행 연산와 함께 사용해야 한다.
-- 주어진 문제가 3000 이상 받는 사원이 소속된 부서(10번, 20번)와 동일한 부서에서 근무하는 사원이기에
-- 서브 쿼리의 결과 중에서 하나라도 일치하면 참인 결과를 구하는 IN 연산자와 함께 사용되어야 합니다.

-- 서브 쿼리문 실행, distinct가 없을 경우 결과값에 중복 값이 같이나옴.
-- 주어진 조건문이 10과 20인 경우에서 가져오는 경우이기 때문에
-- 정확한 결과를 출력하기 위해 중복되는 값을 제거함.
select deptno from emp where sal>=3000;

select ename, sal, deptno from emp 
where deptno in
-- 10인 경우에 대해서 가져오고 20인 경우에 대해서 가져온다.
(select distinct deptno 
from emp where sal >= 3000);
-- 1. 서브쿼리 : 급여가 3000 이상이고 중복되는 값을 제거(distinct)한다.
-- 2. 메인쿼리 : 서브 쿼리의 결과 중 하나라도 일치하는 것을 출력한다.

-- <탄탄히 다지기>
-- 7. 부서별로 가장 급여를 많이 받는 사원의 정보(사원 번호, 사원 이름, 급여,
-- 부서번호)를 출력하시오.(IN연산자 이용)
select empno, ename, sal, deptno from emp
where sal in (select max(sal) from emp group by deptno);

-- 8. 직급(JOB)이 MANAGER인 사람의 속한 부서의 부서 번호와 부서명과
-- 지역을 출력하시오.
select deptno, dname, loc from dept where deptno in
(select deptno from emp where job='MANAGER');