1. 다중행 (Multiple-Row) 서브 쿼리
- 하나 이상의 행을 return하는 서브쿼리를 다중행 서브쿼리라고 한다.
- 복수 행 연산자(IN, ANY, ALL)를 사용한다.
1) IN(목록) : 목록에 있는 값에 해당되는 것이 있으면 TRUE
2) ANY : 서브쿼리에서 반환된 각각의 값과 비교하여 하나라도 TRUE이면 TRUE (=ANY는 IN과 동일)
3) ALL : 서브쿼리에서 반환된 모든 값과 비교했을 때, 모두 TRUE이어야 TRUE
* NOT 연산자는 IN, ANY, ALL 연산자와 함께 사용될 수 있다.
2. 다중열 (Multiple-Column) 서브 쿼리
- 2개 이상의 칼럼(열)을 반환하는 쿼리를 말한다.
- 보통 복수의 칼럼(열)을 가져오기 위해서는 복수의 WHERE절을 사용한다.
Q1. 30번 부서번호의 사원 커미션(comm)과 같은 부서이고, 동일한 월급을 받는 사원의 정보를 출력하자.
(Non-pairwise 서브쿼리)
EMPNO SAL DEPTNO
---------- ---------- ----------
7499 1600 30
7654 1250 30
7521 1250 30
7844 1500 30SQL> select empno, sal, deptno from emp
2 where (sal, deptno) in (select sal, deptno from emp where deptno = 30 and comm is not null);
3. 상호연관 (Correlated) 서브 쿼리
- 메인(바깥쪽) 쿼리의 한 row에 대해서 서브쿼리가 한번씩 실행
- 테이블에서 행을 먼저 읽어서 각 행의 값을 관련된 데이터와 비교하는 방법
- 기본 질의에서 고려된 각 후보 행에 대해 서브 쿼리가 다른 결과를 반환하는 경우에 사용
- 각 행의 값에 따라 응답 달라지는 다중 질의에 응답할 때 상호연관 서브쿼리를 사용
- 서브쿼리에서 메인 쿼리의 칼럼명을 사용할 수 있으나, 메인 쿼리에서는 서브쿼리의 칼럼명을 사용할 수 없다.
- 상호 연관 서브 쿼리 실행
A. 후보 행을 가져온다 (메인 쿼리에서 인출한다)
B. 후보 행의 값을 사용하여 서브쿼리를 실행한다.
C. 서브 쿼리의 결과 값을 사용하여 후보 행의 조건을 확인한다.
D. 후보 행이 남지 않을 때가지 반복한다.
SELECT COLUMN1
FROM TABLE 1 MAIN
WHERE COLUMN1 OPERATOR (SELECT COLUMN1 FROM TABLE 2 WHERE EXPR1 = MAIN.EXPR2);
Q2. 사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원의 이름, 급여, 부서번호, 입사일, 직업을 출력하자.
ENAME SAL DEPTNO HIREDATE JOB
-------------------- ---------- ---------- -------- ------------------
ALLEN 1600 30 81/02/20 SALESMAN
JONES 2975 20 81/04/02 MANAGER
BLAKE 2850 30 81/05/01 MANAGER
KING 5000 10 81/11/17 PRESIDENT
FORD 3000 20 81/12/03 ANALYST
SQL> SELECT ENAME, SAL, DEPTNO, HIREDATE, JOB
2 FROM EMP E
3 WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO);
// 아래의 오답 풀이
SQL> SELECT ENAME, SAL, DEPTNO, HIREDATE, JOB FROM EMP
2 WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30);
ENAME SAL DEPTNO HIREDATE JOB
-------------------- ---------- ---------- -------- ------------------
ALLEN 1600 30 81/02/20 SALESMAN
JONES 2975 20 81/04/02 MANAGER
BLAKE 2850 30 81/05/01 MANAGER
CLARK 2450 10 81/06/09 MANAGER
KING 5000 10 81/11/17 PRESIDENT
FORD 3000 20 81/12/03 ANALYST
4. FROM절 상의 서브쿼리(INLINE VIEW)
- FROM절에 있는 서브쿼리는 인라인 뷰(INLINE VIEW)라고도 한다.
SELECT E.ENAME, D.MyMGR
FROM EMP E, (SELECT MGR AS MyMGR FROM EMP) D;
(=FROM EMP AS E, (SELECT MGR AS MyMGR FROM EMP) AS D;)
Q3. 직업이 MANAGER인 사원의 이름과 직업, 부서이름, 부서위치를 출력해보자.
ENAME JOB DNAME
-------------------- ------------------ ----------------------------
LOC
--------------------------
CLARK MANAGER ACCOUNTING
NEW YORK
JONES MANAGER RESEARCH
DALLAS
BLAKE MANAGER SALES
CHICAGOSQL> SELECT E.ENAME, E.JOB, D.DNAME, D.LOC
2 FROM (SELECT ENAME, JOB, DEPTNO FROM EMP
3 WHERE JOB = 'MANAGER') E, DEPT D
4 WHERE E.DEPTNO = D.DEPTNO;
Q4. 사원테이블에서 급여를 받는 상위 3명의 이름, 급여를 출력하자.
* TOP_N 서브쿼리 : 칼럼의 데이터를 ROWNUM을 이용해서 정렬과 함께 순위를 매긴다.
// 생각: ROWNUM을 순서대로 다시 바꾸고 싶다.
SQL> SELECT ROWNUM, ENAME, SAL
2 FROM EMP
3 ORDER BY SAL DESC;
ROWNUM ENAME SAL
---------- -------------------- ----------
8 KING 5000
11 FORD 3000
4 JONES 2975
6 BLAKE 2850
7 CLARK 2450
2 ALLEN 1600
9 TURNER 1500
12 MILLER 1300
5 MARTIN 1250
3 WARD 1250
10 JAMES 950
ROWNUM ENAME SAL
---------- -------------------- ----------
1 SMITH 800SQL> SELECT ROWNUM, ENAME, SAL // 2번
2 FROM (SELECT * FROM EMP ORDER BY SAL DESC) // 1번
3 WHERE ROWNUM < 4; // 3번
ROWNUM ENAME SAL
---------- -------------------- ----------
1 KING 5000
2 FORD 3000
3 JONES 2975
Q5. 사원테이블에서 급여를 받는 이름, 급여를 조회하자. 단, 4~7의 순위에 속하는 사원만 출력하자.
==========================================================================
SQL> SELECT ROWNUM, ENAME, SAL FROM EMP;
ROWNUM ENAME SAL
---------- -------------------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250
4 JONES 2975
5 MARTIN 1250
6 BLAKE 2850
7 CLARK 2450
8 KING 5000
9 TURNER 1500
10 JAMES 950
11 FORD 3000
ROWNUM ENAME SAL
---------- -------------------- ----------
12 MILLER 1300
12 rows selected.
==========================================================================
SQL> SELECT ROWNUM, ENAME, SAL
2 FROM (SELECT * FROM EMP ORDER BY SAL DESC); // 연봉에 따라서 내림차순 재정렬
ROWNUM ENAME SAL
---------- -------------------- ----------
1 KING 5000
2 FORD 3000
3 JONES 2975
4 BLAKE 2850
5 CLARK 2450
6 ALLEN 1600
7 TURNER 1500
8 MILLER 1300
9 MARTIN 1250
10 WARD 1250
11 JAMES 950
ROWNUM ENAME SAL
---------- -------------------- ----------
12 SMITH 800
12 rows selected.
==========================================================================
SQL> SELECT ROWNUM, ENAME, SAL
2 FROM (SELECT * FROM EMP ORDER BY SAL DESC)
3 WHERE ROWNUM >= 4 AND ROWNUM <=7;
============================================================================
SUB_QUERY EXAM
1. 'SMITH'보다 월급을 많이 받는 사원들의 이름과 월급을 출력하라.
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
ENAME SAL
-------------------- ----------
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
JAMES 950
FORD 3000
MILLER 1300
11 rows selected.
2. 10번 부서의 사원들과 같은 월급을 받는 사원들의 이름, 월급, 부서번호를 출력하라
SQL> SELECT ENAME, SAL, DEPTNO
2 FROM EMP
3 WHERE (SAL, DEPTNO) IN (SELECT SAL, DEPTNO FROM EMP WHERE DEPTNO = 10 AND SAL = EMP.SAL);
ENAME SAL DEPTNO
-------------------- ---------- ----------
CLARK 2450 10
KING 5000 10
MILLER 1300 10
3. 'BLAKE'와 같은 부서에 있는 사원들의 이름과 입사일을 뽑는데, 'BLAKE'를 제외하고 출력하자.
SQL> SELECT ENAME, HIREDATE FROM EMP
2 WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'BLAKE');
3 AND ENAME != 'BLAKE';
ENAME HIREDATE
-------------------- --------
ALLEN 81/02/20
WARD 81/02/22
MARTIN 81/09/28
TURNER 81/09/08
JAMES 81/12/03
5 rows selected.
4. 평균급여보다 많은 급여를 받는 사원들의 사원번호, 이름, 월급을 출력하되, 월급이 높은 사람 순으로 출력하라.
SQL> SELECT EMPNO, ENAME, SAL FROM EMP
2 WHERE SAL > (SELECT AVG(SAL) FROM EMP)
3 ORDER BY SAL DESC;
EMPNO ENAME SAL
---------- -------------------- ----------
7839 KING 5000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
5. 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고 있는 사원의 사원번호와 이름을 출력하라.
SQL> SELECT EMPNO, ENAME FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%');
EMPNO ENAME
---------- --------------------
7902 FORD
7566 JONES
7369 SMITH
7900 JAMES
7844 TURNER
7698 BLAKE
7654 MARTIN
7521 WARD
7499 ALLEN
9 rows selected.
6. 30번 부서에 있는 사원들 내에서 가장 많은 월급을 받는 사원보다 많은 월급을 받는 사원들의 이름, 부서번호, 월급을 출력하라 (단, ALL 또는 ANY 연사자를 사용할 것)
SQL> SELECT ENAME, DEPTNO, SAL FROM EMP
2 WHERE SAL > ALL(SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);
ENAME DEPTNO SAL
-------------------- ---------- ----------
JONES 20 2975
KING 10 5000
FORD 20 3000
7. 'DALLAS'에서 근무하고 있는 사원과 같은 부서에서 일하는 사원의 이름, 부서번호, 직업을 출력하라
SQL> SELECT E.ENAME, E.DEPTNO, E.JOB, D.LOC
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = (SELECT DEPTNO FROM DEPT D WHERE LOC = 'DALLAS');
// DEPT 테이블과 EMP 테이블 모두 DEPTNO라는 칼럼이 있어서, 구분해야 함.
ENAME DEPTNO JOB LOC
-------------------- ---------- ------------------ --------------------------
SMITH 20 CLERK NEW YORK
SMITH 20 CLERK DALLAS
SMITH 20 CLERK CHICAGO
SMITH 20 CLERK BOSTON
JONES 20 MANAGER NEW YORK
JONES 20 MANAGER DALLAS
JONES 20 MANAGER CHICAGO
JONES 20 MANAGER BOSTON
FORD 20 ANALYST NEW YORK
FORD 20 ANALYST DALLAS
FORD 20 ANALYST CHICAGO
ENAME DEPTNO JOB LOC
-------------------- ---------- ------------------ --------------------------
FORD 20 ANALYST BOSTON
12 rows selected.
============================================================================
SQL> SELECT ENAME, DEPTNO, JOB
2 FROM EMP
3 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');
ENAME DEPTNO JOB
-------------------- ---------- ------------------
SMITH 20 CLERK
JONES 20 MANAGER
FORD 20 ANALYST
8. SALES 부서에서 일하는 사원들의 부서번호, 이름, 직업을 출력하라
SQL> SELECT DEPTNO, ENAME, JOB FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');
DEPTNO ENAME JOB
---------- -------------------- ------------------
30 JAMES CLERK
30 TURNER SALESMAN
30 BLAKE MANAGER
30 MARTIN SALESMAN
30 WARD SALESMAN
30 ALLEN SALESMAN
6 rows selected.
9. 'KING'에게 보고하는 모든 사원의 이름과 급여를 출력하라
SQL> SELECT ENAME, MGR, EMPNO FROM EMP;
ENAME MGR EMPNO
-------------------- ---------- ----------
SMITH 7902 7369
ALLEN 7698 7499
WARD 7698 7521
JONES 7839 7566
MARTIN 7698 7654
BLAKE 7839 7698
CLARK 7839 7782
KING 7839
TURNER 7698 7844
JAMES 7698 7900
FORD 7566 7902
ENAME MGR EMPNO
-------------------- ---------- ----------
MILLER 7782 7934
12 rows selected.
===========================================================
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE MGR = 7839;
ENAME SAL
-------------------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
===========================================================
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING');
ENAME SAL
-------------------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
10. 자신의 급여가 평균 급여보다 많고, 이름에 'S'가 들어가는 사원과 동일한 부서에서 근무하는 모든 사원의 사원번호, 이름, 급여를 출력하라
SQL> SELECT EMPNO, ENAME, SAL FROM EMP
2 WHERE (SAL > (SELECT AVG(SAL) FROM EMP) AND DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%S%'));
EMPNO ENAME SAL
---------- -------------------- ----------
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
11. 커미션을 받는 사원과 부서번호, 월급이 같은 사원의 이름, 월급, 부서번호를 출력하라
SQL> SELECT ENAME, SAL, DEPTNO FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO
3 FROM EMP WHERE COMM IS NOT NULL)
4 AND SAL IN (SELECT SAL FROM EMP WHERE COMM IS NOT NULL);
ENAME SAL DEPTNO
-------------------- ---------- ----------
TURNER 1500 30
WARD 1250 30
MARTIN 1250 30
ALLEN 1600 30
==============================================================
SQL> SELECT ENAME, SAL, DEPTNO FROM EMP
2 WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL FROM EMP WHERE COMM IS NOT NULL);
ENAME SAL DEPTNO
-------------------- ---------- ----------
ALLEN 1600 30
MARTIN 1250 30
WARD 1250 30
TURNER 1500 30
12. 30번 부서 사원들과 월급과 커미션이 같지 않은 사원들의 이름, 월급, 커미션을 출력하라
SQL> SELECT ENAME, SAL, COMM FROM EMP
2 WHERE SAL NOT IN (SELECT SAL FROM EMP WHERE DEPTNO = 30) AND
3 COMM NOT IN (SELECT NVL(COMM,0) FROM EMP WHERE DEPTNO = 30);
13. 사원번호, 이름, 월급, 그리고 월급 누적액을 출력하라
SQL> SELECT E.EMPNO, E.ENAME, E.SAL, SUM(D.SAL)
2 FROM EMP E, (SELECT EMPNO, SAL FROM EMP) D
3 WHERE E.EMPNO >= D.EMPNO
4 GROUP BY E.EMPNO, E.ENAME, E.SAL
5 ORDER BY 4; ## 4 = SUM(D.SAL)
EMPNO ENAME SAL SUM(D.SAL)
---------- -------------------- ---------- ----------
7369 SMITH 800 800
7499 ALLEN 1600 2400
7521 WARD 1250 3650
7566 JONES 2975 6625
7654 MARTIN 1250 7875
7698 BLAKE 2850 10725
7782 CLARK 2450 13175
7839 KING 5000 18175
7844 TURNER 1500 19675
7900 JAMES 950 20625
7902 FORD 3000 23625
EMPNO ENAME SAL SUM(D.SAL)
---------- -------------------- ---------- ----------
7934 MILLER 1300 24925
12 rows selected.
14. 7369번 업무와 같고, 급여가 7876번 사원보다 사원의 이름, 직업을 출력하자
SQL> SELECT EMPNO, ENAME, SAL FROM EMP
2 WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7369)
3 AND SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7876);
no rows selected
============================================================================
SET 연산자
| 1. UNION | 두 질의 중 어느 것 하나에 의해서라도 선택된 모든 구분 (DISTINCT) 행을 결과로 한다. |
| 2. UNION ALL | 중복 행을 포함하여 두 질의 중 어느 것 하나에 의해서 선택된 모든 행을 결과로 한다. |
| 3. INTERSECT | 두 질의에 공통으로 선택된 모든 구분(DISTINCT)행을 결과로 한다. |
| 4. MINUS | 첫째 SELECT문에 선택되고, 둘째 SELECT문에서 선택되지 않은 모든 구분(DISTINCT)행을 결과로 한다. |
SET 연산자 특징
| 1. UNION ALL을 제외한 다른 연산자를 사용할 경우에는 중복행이 자동으로 제거된다. |
| 2. 첫째 질의의 열 이름이 결과에 표시된다. |
| 3. UNION ALL을 제외한 다른 연산자의 출력은 기본적으로 오름차순으로 정렬된다. |
| 4. ORDER BY 절에서 사용된 열 이름이나 별칭은 첫 번째 SELECT 목록에 있어야 한다. |
| 5. SET 연산자는 서브쿼리에서도 사용할 수 있다. |
| 6. SELECT 문장은 왼쪽(위)에서 오른쪽(아래)로 실행된다. |
| 7. 괄호를 사용하여 연산자의 우선순위를 바꿀 수 있다. |
Q1. 사원의 이름, 직업, 부서번호를 출력하되 두 테이블의 중복데이터를 제거한 후 병합한다.
UNION : WHERE 절에서 UNION을 사용하는 질의는 SELECT 목록에 있는 것과 똑같은 수와 데이터 타입의 열을 가져야
한다. [ 칼럼명은 달라도 상관없다 ]
UNION = EMP + EMP_RES = 중복데이터 제거 후 리턴
SQL> SELECT ENAME, JOB, DEPTNO FROM EMP
2 UNION SELECT ENAME, JOB, DEPTNO FROM EMP_RES;
ENAME JOB DEPTNO
-------------------- ------------------ ----------
ALLEN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
FORD ANALYST 20
JAMES CLERK 30
JONES MANAGER 20
KING PRESIDENT 10
MARTIN SALESMAN 30
MILLER CLERK 10
SMITH CLERK 20
TURNER SALESMAN 30
ENAME JOB DEPTNO
-------------------- ------------------ ----------
WARD SALESMAN 30
12 rows selected.
Q2. UNION ALL : UNION과 달리 중복되는 행은 제거되지 않으며, 결과는 디폴트로 정렬되지 않는다.
DISTINCT 키워드를 사용할 수 없다.
Q3. MINUS 집합 연산자 :
- 두 번째 질의에 의해 리턴되는 행을 제외하고 첫 번째 질의에 의해서만 리턴되는 행을 조회하기 위해 사용한다.
- WHERE 절에 있는 모든 열은 MINUS 연산자 질의에 의한 SELECT 절에 있어야 한다!
'학습 과정 공유 및 후기 > SQLD' 카테고리의 다른 글
| [ SQLD ] 데이터 모델링의 이해 (2) - 데이터 모델의 성능 (0) | 2020.05.28 |
|---|---|
| [ SQLD ] Oracle SQL (9) - JOIN (INNER, OUTER) (0) | 2020.05.28 |
| [ SQLD ] Oracle SQL (6) - 서브쿼리 (0) | 2020.05.21 |
| [ SQLD ] Oracle SQL (5) - 형변환 함수 (0) | 2020.05.20 |
| [ SQLD ] Oracle SQL (4) - 숫자함수, 날짜 함수 (0) | 2020.05.20 |
