SQL 서브쿼리

<서브쿼리>  

:  select 문 안에 기술된 select 문

​
- 서브쿼리가 먼저 실행된 다음 메인 쿼리가 실행
- 서브쿼리의 분류
  * 위치에 따라
      --select 다음 : scalar 서브 쿼리
      --from 다음 : inline view
      --where 다음 : 서브쿼리
  * 유형에 따라
      -- 단일행 : 하나의 행을 리턴
      -- 다중행 : 여러 개의 행을 리턴, 다중행 연상자 in, all, any, exists 를 사용
      -- 다중열
      

 

1) 단일행 서브쿼리 : where 절의 조건에 사용.
  -- SMITH의 관리자 이름을 출력
  select mgr from emp where ename='SMITH'; -- 의 실행결과 mgr 번호 7902를 얻은 다음
  select ename from emp where empno=7902;  -- 7902에 해당하는 사원의 이름을 출력.
  --> 한문장으로 합치면
  select ename from emp where empno=(select mgr from emp where ename='SMITH');
-- 셀프조인으로 할 수 있잖아.
  -- 소량의 데이터를 처리할 때는 취향
  -- 대량의 데이터를 처리할 때는 조인이 유리
  -- 일부의 데이터를 처리할 때는 서브쿼리가 유리

 

 

 

 

-- ex>
-- 1 SMITH 보다 급여가 높은 사원들의 이름과 급여
select ename, sal from emp where sal > (select sal from emp where ename='SMITH'); 

 

-- 2 평균급여보다 많은 급여를 받느 사원들의 이름과 급여를 출력
select ename, sal from emp where sal > (select avg(sal) from emp); 

 

-- 3 전체 평균 급여보다 높은 평균 급여를 받는 부서의 부서번호와 평균급여를 출력
select deptno, avg(sal) from emp group by deptno having avg(sal) > (select avg(sal) from emp);

 

 

-- 연습문제.
 -- 0. JONES의 부서명을 출력
 select dname from dept where deptno = (select deptno from emp where ename='JONES');
 
 -- 1. 인사테이블에서 9월 22일 입사자보다 높은 급여를 받는 직원 출력
 select * from insa where basicpay > ( select basicpay from insa where to_char(ibsadate,'mm/dd') = '09/22');
 
 -- 2. 총무부의 평균 급여보다 많은 급여를 받는 직원들의 이름, 부서명, 급여 출력
 select name, buseo, basicpay from insa where basicpay > (select avg(basicpay) from insa where buseo='총무부');
 
 -- 3. 총무부 직원들보다 더 많은 급여를 받는 직원 정보
  select * from insa where basicpay > (select max(basicpay) from insa where buseo='총무부');
 
 -- 4. 직원 전체 평균 급여보다 많은 급여를 받는 부서별 직원의 수를 인원수대로 정렬
 select count(*) from insa where basicpay > (select avg(basicpay) from insa) group by buseo order by count(*);
 
 -- 5. '홍길동' 직원과 같은 부서의 직원 정보(홍길동 제외)
 select * from insa where buseo = (select buseo from insa where name='홍길동') and name != '홍길동';
 
 -- 6. emp에서 부서번호가 10인 사원 중에서 최대 급여를 받는 사원과 동일한 급여를 받는 사람의 사번과 이름, 급여를 출력
 select ename, sal from emp where sal = (select max(sal) from emp where deptno=10);
 
 -- 7. KING에게 보고하는 모든 사원의 이름과 급여를 출력
 select ename, sal from emp where mgr = (select empno from emp where ename='KING');
 
 -- 8. emp테이블에서 최고 급여자의 이름과 급여를 출력하시오
 select ename, sal from emp where sal = (select max(sal) from emp);
 
 -- 9. 부서 10에서 부서 30에서 comm을 받는 사원과 같은 업무를 맡고 있는 사원의 부서명, 이름, 직책, 급여등급을 출력
 select dname, ename, job, grade from dept d, emp e, salgrade s
 where d.deptno = e.DEPTNO and sal between losal and hisal and d.deptno=10
 and job = (select distinct(job) from emp where deptno=30 and comm>0);
 
 -- 10. 최고급여자의 이름과 부서명을 출력하시오
 select ename, dname from emp e, dept d where e.deptno = d.deptno and sal = (select max(sal) from emp);
 
 -- 11. 30번 부서의 최저 급여보다 높은 급여를 받는 사원을 출력
 select * from emp where sal > (select min(sal) from emp where deptno=30);
 
 -- 12. emp에서 BLAKE와 같은 부서에서 근무하는 사원의 이름과 입사일자를 출력
 select ename, hiredate from emp where deptno = (select deptno from emp where ename='BLAKE');
 
 -- 13. 20번 부서의 평균급여보다 높은 급여를 받는 사원의 부서명, 이름, 급여를 출력
 select dname, ename, sal from emp e, dept d where e.deptno = d.deptno
 and sal > (select avg(sal) from emp where deptno=20);
 
 -- 14. 20번 부서의 평균급여보다 높은 급여를 받는 사원들을 대상으로 부서명, 평균급여를 부서명 오름차순으로 출력
 select dname, avg(sal) from emp e, dept d where e.deptno=d.deptno
 and sal > (select avg(sal) from emp where deptno=20) group by dname order by dname;
 
 -- 15. 20번 부서의 평균급여보다 높은 급여를 받는 사원들이 2명이상 있는 부서의 부서명, 평균급여를 부서명 오름차순으로 출력
 select dname, avg(sal) from emp e, dept d where e.deptno=d.deptno
 and sal>(select avg(sal) from emp where deptno=20) group by dname having count(*)>=2 order by dname;
 
 -- 16. ALLEN과 같은 입사년도에 입사한 사원들의 부서위치, 사원명, 이름, 급여를 이름, 급여 내림차순으로 출력
 select loc, ename, dname, sal from emp e, dept d where e.deptno=d.deptno
 and hiredate = (select hiredate from emp where ename='ALLEN') order by ename desc, sal desc;