[ Oracle DB ] Subquery

 서브쿼리 

1. ( )안에 작성

2. 외부쿼리(=메인쿼리)와 비교할 때 알맞은 연산자를 사용(단일행/복수행, 단일컬럼/복수 컬럼)

3. 연관성이있는 쿼리(correlated subquery) 또는 없는 쿼리

4. group by, order by 절을 제외하고 모든 절에 사용 가능

---

 복수행이 리턴될 때 

• < any : 서브쿼리 리턴값 중 최대값
• > any : 서브쿼리 리턴 값 중 최소화
• < all : 서브쿼리값 중 최소값
• > all : 서브 쿼리 리턴값 중 최대값

---

 문제1. 부서명이 'SALES'인 사원의 사번, 이름을 출력해라.

유형1 .  join 이용

select e.empno,e.ename 
from emp e join dept d on e.deptno = d.deptno
where d.dname='SALES' ;

 

 

유형 2. subquery이용

select empno,ename 
from emp
where deptno = (select deptno fROM dept where dname='SALES'); 

하지만 = 비교연산자는 1:1이어야 되므로 에러 발생이 있을 수 있다.

따라서 =대신 in을 사용한다.

select empno,ename 
from emp
where deptno in (select deptno fROM dept where dname='SALES'); 

---

 문제2. 사번이 '7844'인 사원의 job과 동일한 job인 사원의 사번, 이름, job을 출력해라. 

select empno, ename, job from emp
where job = (select job from emp where empno='7844');

 

 

---

 문제 3. 사번이 '7521'인 사원의 job과 동일하고 '7900'인 사번의 급여보다 많은 급여를 받는 사원의 사번, 이름, job, 급여를 출력 

select empno, ename, job, sal from emp
where job = (select job from emp where empno='7521') 
and sal>(select sal from emp where empno='7900');

 

 

---

 문제 4. 가장 적은 급여를 받는 사원의 사번, 이름, 급여를 출력 

select empno, ename, sal from emp 
where sal = (select min(sal) from emp);

 

 

---

 문제 5. 부서별 최소 급여 중에서 30번 부서의 최소급여보다는 큰 최소급여인 부서의 부서번호, 최소 급여를 출력 

유형1.

select deptno, min(sal) from emp 
group by deptno
having min(sal) > (select min(sal) from emp where deptno=30) ;

유형 2.

서브쿼리 리턴값 중 최소값을 출력하고자 할 때 > any를 사용한다.

select deptno, min(sal) from emp 
group by deptno
having min(sal) >any (select min(sal) from emp where deptno=30) ;

 

 

---

 문제 6. job이 'CLERK'인 사원의 2명 이상 있는 부서의 부서명만 출력 

유형 1. join 이용

select d.dname from emp e join dept d on e.deptno = d.deptno where e.job='CLERK'
group by d.dname having count(*) >= 2;

유형 2. sub query 이용

select dname from dept where deptno 
 = (select deptno from emp where job='CLERK' group by deptno having count(*) >= 2);

 

 

유형 3. correlated subquery 이용

( ) 안의 쿼리문이 끝날때까지 계속 돌고, 끝나면 해당되는 값을 출력해준다.

correlated subquery는 처리속도가 다른 것에 비해 떨어지는 단점이 있으나, 사용해야 되는 경우도 있기 때문에 알아둬야 한다.

select dname from dept d
where 2<=(select count(*)from emp e where job='CLERK' and d.deptno=e.deptno);

---

 문제 6-1. job이 'CLERK'인 사원이 한명이라도 있는 부서의 부서명만 출력해라. 

유형 1.

select dname from dept d
where 1<=(select count(*)from emp e where job='CLERK' and d.deptno=e.deptno);

유형 2.

데이터가 있기만 하면 되기 때문에 exists를 사용할 수 있다.

그러면 count( )함수를 사용하지 않아도 값을 출력할 수 있다.

select dname from dept d
where exists (select *from emp e where job='CLERK' and d.deptno=e.deptno);

 

 

---

 문제 6-2. job이 'CLERK'인 사원이 한명도 없는 부서의 부서명만 출력해라. 

유형 1. sub query 이용

select dname from dept where deptno 
 not in (select deptno from emp where job='CLERK' group by deptno having count(*) >= 1);

유형 2. correlated subquery 이용

 select dname from dept d
where not exists (select *from emp e where job='CLERK' and d.deptno=e.deptno);

 

 

---

 문제 7. 각 부서별로 최소 급여를 받는 사원의 부서번호, 부서명, 사번 이름 급여를 출력해라. 

유형 1. sub query 이용

select d.deptno, d.dname, e.empno, e.ename, e.sal from emp e join dept d on e.deptno = d.deptno 
where (e.deptno,e.sal) in (select deptno,min(sal) from emp group by deptno);

유형 2. correlated subquery 이용

select deptno, (select dname from dept where emp.deptno=deptno), empno, ename, sal from emp 
where (deptno,sal) in (select deptno,min(sal) from emp group by deptno);

 

 

---

 문제 8. 직속상사가 없는 사원을 포함해서 모든 사원의 부하직원 사번, 부하직원명, 직속상사사번, 직속상사명을 출력해라. 

유형 1. select절에서 correlated subquery 이용

select empno, ename, mgr,(select ename from emp where e.mgr = empno) as 직속상사명
from emp e;

 

 

유형 2. 인라인 뷰 (from절에 쓰는 서브 쿼리 = inline view)

select e.empno, e.ename, e.mgr, i.ename as 직속상사명 
from emp e left join (select ename,empno from emp) i
on e.mgr = i.empno;

 

 

유형 3. self join 

select e1.empno, e1.ename,e1.mgr,e2.ename
from emp e1 left join emp e2 on e1.mgr = e2.empno;

 

 

---

 문제 9. 평균 급여보다 많거나 같고, 최대급여보다는 적은 급여를 받는 사원의 사번, 이름, 급여를 출력해라. 

유형 1. select절에서 correlated subquery 이용

select empno, ename, sal 
from emp where sal >= (select avg(sal)from emp) and sal < (select max(sal)from emp);

유형 2. inline view 이용

select empno, ename, sal from emp e join (select avg(sal) as a, max(sal) as b from emp) i 
on e.sal >= i.a and e.sal < i.b;

 

 

---

 문제 10. 월급이 높은순으로 사번, 이름, 월급을 상위 5명만 출력해라. 

select empno,ename, sal 
from (select * from emp order by sal desc) where rownum <= 5;

 

 

---

 문제 11. CTAS기법으로 emp 테이블을 testemp로 복사하고, 모든 사원의 월급을 자기 부서의 평균월급으로 update하라.

update testemp t
set sal=(select trunc(avg(sal))
         from testemp
         where t.deptno=deptno);