[ORACLE] 서브쿼리(SUBQUERY)

 

1. SUBQUERY(서브쿼리)

서브쿼리(SUBQUERY)는 SELECT, INSERT, UPDATE, DELETE 문 또는 다른 SUBQUERY 안에 들어가는 SELECT 구문이며 중첩된 SELECT문 이라는 뜻으로 NESTED SELECT라고도 합니다.

 

사용규칙

1. 모든 서브쿼리는 괄호로 싸여진다.

2. 서브쿼리 안에 서브쿼리가 들어갈 수 있다.

3. 서브쿼리의 SELECT문에서는 하나의 컬럼값, 또는 이를 응용한 하나의 표현식만 사용할 수 있다.

4. 서브쿼리에서 COMPUTE(ROLLUP, CUBE), SELECT INTO(원본 그대로 생성/복사)는 사용할 수 없다.

5. 주 쿼리의 테이블 컬럼은 모두 서브쿼리에서 참조하여 서용할 수 있다.

6. 서브쿼리에서 SELECT 하지 않은 컬럼은 주 쿼리에서 사용할 수 없다.

 

 

1-1. SINGLE ROW SUBQUERY

서브쿼리의 결과가 1개의 ROW를 가짐.

연산자 : >, =, >=, .....

 

--Q) JONES 보다 더 많은 월급을 받는 사람의 이름과 월급을 출력하자.

SELECT ENAME, SAL FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');

 

 

1-2. MULTI ROW SUBQUERY

서브쿼리의 결과가 여러 개의 ROW를 가짐.

연산자 : IN, NOT IN, ANY, ALL,...

 

--Q) 부하직원이 없는 사원의 사원번호와 이름을 출력하자.

SELECT EMPNO, ENAME FROM EMP
WHERE EMPNO NOT IN (SELECT NVL(MGR, 0) FROM EMP);

 

 

1-3. MULTI COLUMN SUMBQUERY

WHERE절에서 여러 개의 COLUMN값 비교

 

--Q) 직업이 "SALESMAN"인 사원과 같은 부서에서 근무하고 같은 월급을 받는 사람들의 이름, 월급, 부서번호를 출력하자.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE JOB='SALESMAN');

 

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB='SALESMAN')
AND SAL IN (SELECT SAL FROM EMP WHERE JOB='SALESMAN');

 

 

 

1-4. INLINE VIEW

FROM 절에서의 서브쿼리(가상 테이블)

 

--Q) 자기 부서의 평균 월급보다 더 많은 월급을 받는 사원들의 이름, 월급, 부서번호, 부서별 평균월급을 출력하자.

SELECT E.ENAME, E.SAL, MYRES.DEPTNO, MYRES.MYAVG
FROM EMP E,
(SELECT DEPTNO, AVG(SAL) AS MYAVG FROM EMP GROUP BY DEPTNO) MYRES
WHERE E.DEPTNO = MYRES.DEPTNO AND E.SAL > MYRES.MYAVG;

 

 

---

--01. 부서번호가 10번인 사원들과 같은 월급을 받는 사원의 이름과 월급을 출력하자.

SELECT ENAME, SAL FROM EMP
WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO='10');

 

 

 

 

--02. 직업이 'CLERK'인 사원과 같은 부서에서 근무하는 사원의 이름과 월급, 부서번호를 출력하자.

SELECT ENAME, SAL, DEPTNO FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB='CLERK');

 

 

 

--03. 'CHICAGO'에서 근무하는 사원들과 같은 부서에서 근무하는 사원의 이름과 월급을 출력하자.

SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO =
(SELECT DEPTNO
FROM DEPT
WHERE LOC = 'CHICAGO');

 

 

 

--04. 부하직원이 있는 사원의 사원번호와 이름을 출력하자. (자기 자신이 다른 사원의 관리자인 사원)

SELECT EMPNO, ENAME
FROM EMP
WHERE EMPNO IN
(SELECT MGR
FROM EMP);

 

 

 

--05. 부하직원이 없는 사원의 사원번호와 이름을 출력하자.

SELECT EMPNO, ENAME
FROM EMP
WHERE EMPNO NOT IN
(SELECT NVL(MGR,0) FROM EMP);

 

 

 

--06. 'KING'에게 보고하는 사원의 이름과 월급을 출력하자. (관리자가 'KING'인 사원)

SELECT ENAME, SAL
FROM EMP
WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING');

 

 

 

--07. 20번 부서의 사원 중 가장 많은 월급을 받는 사원보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하자.

SELECT ENAME, SAL
FROM EMP
WHERE SAL >
(SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 20);

 

 

 

--08. 직업이 'SALESMAN' 인 사원중 가장 큰 월급을 받는 사원보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하자.

--단, MAX함수를 사용하지 말자.(ANY, ALL 연산자)

SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL
(SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');

 

 

--09. 'SMITH'보다 월급을 많이 받는 사원들의 이름과 월급을 출력하자.

SELECT ENAME, SAL
FROM EMP
WHERE SAL >
(SELECT SAL
FROM EMP
WHERE ENAME='SMITH');

 

 

 

--10. 10번 부서의 사원들과 같은 월급을 받는 사원들의 이름, 월급, 부서번호를 출력하자.

SELECT ENAME, SAL, EMPNO
FROM EMP
WHERE SAL IN
(SELECT SAL
FROM EMP
WHERE DEPTNO = 10);

 

 

 

--11. 'BLAKE'가 근무하는 부서의 위치(LOC)를 출력하자.

SELECT LOC
FROM DEPT
WHERE DEPTNO =
(SELECT DEPTNO
FROM EMP
WHERE ENAME = 'BLAKE');

 

 

 

--12. 총 사원의 평균월급보다 더 많은 월급을 받는 사원들의 사원번호, 이름, 월급을 출력하되,

월급이 높은 사람 순으로 출력하자.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > 
(SELECT AVG(SAL)
FROM EMP)
ORDER BY SAL DESC;

 

 

 

--13. 이름에 'T'를 포함하고 있는 사원들의 이름을 출력하자.

SELECT ENAME
FROM EMP
WHERE INSTR(ENAME,'T',1,1) > 0;

 

SELECT ENAME
FROM EMP
WHERE ENAME LIKE '%T%';

 

 

 

--14. 20번 부서에 있는 사원들 중 가장 많은 월급을 받는 사원보다

많은 월급을 받는 사원들의 이름, 부서번호, 월급을 출력하자.

SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL>
(SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 20);

 

 

 

 

--15. 'DALLAS'에서 근무하고 있는 사원과 같은 부서에서 일하는 사원의 이름, 부서번호, 직업을 출력하자.

SELECT ENAME, DEPTNO, JOB
FROM EMP
WHERE DEPTNO =
(SELECT DEPTNO
FROM DEPT
WHERE LOC='DALLAS');

 

 

 

--16. 이름에 'S'가 들어가는 사원과 동일한 부서에서 근무하는 사원 중,

자신의 급여가 평균 급여보다 많은 사원들의 사원번호, 이름, 급여를 출력하자.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM EMP
WHERE INSTR(ENAME,'S',1,1) > 0);

 

 

 

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN
(SELECT DEPTNO
FROM EMP
WHERE ENAME LIKE '%S%');

 

 

 

 

--17. 사원번호가 7369 인 사원과 같은 직업이고, 월급이 사원번호 7876인 사원보다 많은 사원의 이름과 직업을 출력하자.

SELECT ENAME, JOB
FROM EMP
WHERE JOB =
(SELECT JOB
FROM EMP
WHERE EMPNO = 7369)
AND SAL > 
(SELECT SAL
FROM EMP
WHERE EMPNO = 7876);