✨ 집합(SET) 연산자를 이해하고 기술 할 수 있다.
✨ SUBQUERY를 기술할 수 있다.
집합(SET)연산
SET 연산자는 두 개 이상의 질의 결과를 하나의 결과로 통합하며,
SET 연산자를 포함하는 질의를 복합 질의라고한다. → 여기서 질의는 쿼리를 말함.
UNION
두 질의 중 어느 것 하나에 의해서라도 선택된 모든 구분 (DISTINCT) 행을 결과로 한다.
양쪽 질의에 의해서 선택된 행을 모두 포함한다.
중복 값은 한번만 리턴하며, 첫번째 컬럼을 기준으로 ASC 정렬한다.
UNION ALL
중복 행을 포함하여 두 질의 중 어느 것 하나에 의해서 선택된 모든 행을 결과로한다.
DISTINCT 와 같이 사용 불가.
INTERSECT
두 질의에 공통으로 선택된 모든 구분(DISTINCT) 행을 결과로 한다.
첫 번째 선택 문장에서 두번째 선택 문장과 일치하는 최소한 하나의 행을 리턴한다.
MINUS
첫 째 SELECT문에 선택되고, 둘 째 SELECT문에서 선택되지 않은 모든 구분(DISTINCT)행을 결과로한다.
(두번째 선택 문장에서는 선택되지 않는 첫번째 선택 문장에 의해 선택된 모든 유일한 행이다)
👉 모든 SET 연산자는 같은 우선순위를 가진다.
SQL 문장이 여러 개 SET 연산자를 포함한다면 데이터 베이스는 명시적으로
다른 순서를 명시하는 괄호가 없을 경우 왼쪽( 위)에서 오른쪽 (아래)로 실행을 한다.
사용하는 질의의 평가 순서를 명시적으로 표현하기 위해서는 괄호를 사용한다.
[형식]
SELECT : FROM WHERE GROUP BY HAVING
SET 연산자
SELECT FROM WHERE GROUP BY HAVING
ORDER BY
□ UNION
컬럼의 개수가 두 개의 SELECT 문장에 일치 하여야 한다.
컬럼의 이름은 같지 않아도 된다.
중복 값은 하나만 리턴 된다.
WHERE 절에서 UNION을 사용하는 질의를 SELECT 목록에 있는 것과 똑같은 수와 데이터 타입의 열을 가져야한다.
Q1) 데이터 생성을 해보자.
CREATE TABLE EMP_HISTORY(NAME, TITLE, DEPTID)
AS
SELECT ENAME, JOB, DEPTNO
FROM EMP;
DESC EMP_HISTORY
SQL> CREATE TABLE EMP_HISTORY(NAME, TITLE, DEPTID)
2 AS
3 SELECT ENAME, JOB, DEPTNO
4 FROM EMP;
Table created.
SQL> DESC EMP_HISTORY
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
TITLE VARCHAR2(9)
DEPTID NUMBER(2)
SQL>
Q2) EMP 테이블과 EMP_HISTORY 테이블의 내용을 UNION 연산을 구현하자.
SELECT ENAME, JOB, DEPTNO
FROM EMP
UNION
SELECT NAME, TITLE, DEPTID
FROM EMP_HISTORY;
SQL> SELECT ENAME, JOB, DEPTNO
2 FROM EMP
3 UNION
4 SELECT NAME, TITLE, DEPTID
5 FROM EMP_HISTORY;
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.
□ UNION ALL
Q3) EMP 테이블과 EMP_HISTORY 테이블의 내용을 UNION ALL연산을 구현하자.
SELECT ENAME, JOB, DEPTNO
FROM EMP
UNION ALL
SELECT NAME, TITLE, DEPTID
FROM EMP_HISTORY;
SQL> SELECT ENAME, JOB, DEPTNO
2 FROM EMP
3 UNION ALL
4 SELECT NAME, TITLE, DEPTID
5 FROM EMP_HISTORY;
ENAME JOB DEPTNO
-------------------- ------------------ ----------
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
JONES MANAGER 20
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
KING PRESIDENT 10
TURNER SALESMAN 30
JAMES CLERK 30
FORD ANALYST 20
ENAME JOB DEPTNO
-------------------- ------------------ ----------
MILLER CLERK 10
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
JONES MANAGER 20
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
KING PRESIDENT 10
TURNER SALESMAN 30
JAMES CLERK 30
ENAME JOB DEPTNO
-------------------- ------------------ ----------
FORD ANALYST 20
MILLER CLERK 10
24 rows selected.
SQL>
□ INTERSECT
Q4) TEST_EMP, EMP 이용해서 INTERSECT 해보자. #교집합
SELECT ENAME, JOB, DEPTNO
FROM TEST_EMP
INTERSECT
SELECT ENAME, JOB, DEPTNO
FROM EMP;
SQL> SELECT ENAME, JOB, DEPTNO
2 FROM EMP
3 INTERSECT
4 SELECT ENAME, JOB, DEPTNO
5 FROM TEST_EMP;
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.
# 위치 바꿔도 동일
SELECT ENAME, JOB, DEPTNO
FROM EMP
INTERSECT
SELECT ENAME, JOB, DEPTNO
FROM TEST_EMP;
□ MINUS
Q5) EMP 테이블과 EMP_HISTORY 테이블의 내용을 MINUS연산을 구현하자. #차집합
SELECT ENAME, JOB, DEPTNO
FROM TEST_EMP
MINUS
SELECT NAME, TITLE, DEPTID
FROM EMP;
SQL> SELECT ENAME, JOB, DEPTNO
2 FROM TEST_EMP
3 MINUS
4 SELECT ENAME, JOB, DEPTNO
5 FROM EMP;
ENAME JOB DEPTNO
-------------------- ------------------ ------
111 30
222 30
333 20
444 20
777
Q6) EMP, DEPT에서 부서번호, 부서이름, 입사일을 UNION 연산자를 이용해서 출력해보자.
SELECT DEPTNO,TO_CHAR(NULL),HIREDATE
FROM EMP
UNION
SELECT DEPTNO, DNAME, TO_DATE(NULL)
FROM DEPT
ORDER BY 1;
SQL> SELECT DEPTNO, TO_CHAR(NULL), HIREDATE
2 FROM EMP
3 UNION
4 SELECT DEPTNO, DNAME, TO_DATE(NULL)
5 FROM DEPT
6 ORDER BY 1;
DEPTNO TO_CHAR(NULL) HIREDATE
---------- ---------------------------- --------
10 ACCOUNTING
10 81/06/09
10 81/11/17
10 82/01/23
20 RESEARCH
20 80/12/17
20 81/04/02
20 81/12/03
30 SALES
30 81/02/20
30 81/02/22
DEPTNO TO_CHAR(NULL) HIREDATE
---------- ---------------------------- --------
30 81/05/01
30 81/09/08
30 81/09/28
30 81/12/03
40 OPERATIONS
16 rows selected.
SQL>
SUBQUERY
SINGLE ROW SUBQUERY, MULTI ROW SUBQUERY
MULTI COLUMN SUBQUERY
SCALAR SUBQUERY
CORRELATED SUBQUERY
WITH 절과 SUBQUERY
※ SCALAR ? 프로그램에서 자료형 의미한다. 단일 값을 의미한다. 정수, 문자, 문자열 등
Q1) 'JONES' 보다 더 많은 월급을 받는 사원의 이름과 월급을 출력해보자.
(1) 먼저 이름이 'JONES' 인 사원의 월급을 구한다.
SELECT SAL
FROM EMP
WHERE ENAME = 'JONES';
SQL> SELECT SAL
2 FROM EMP
3 WHERE ENAME = 'JONES';
SAL
----------
2975
SQL>
(2) 구한 월급 이상을 대상으로 사원의 이름과 직업을 리턴한다.
SELECT ENAME, SAL
FROM EMP
WHERE SAL > 2975;
SQL> SELECT ENAME, SAL
2 FROM EMP
3 WHERE SAL > 2975;
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
✔ 서브쿼리
: 괄호로 묶여저 있는것 먼저 실행 한다.
OUTER QUERY / INNER QUERY
(3) 결과
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'JONES');
SQL> SELECT ENAME, SAL
2 FROM EMP
3 WHERE SAL > (SELECT SAL
4 FROM EMP
5 WHERE ENAME = 'JONES');
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
SQL>
[형식]
SELECT ----------> 기본질의
FROM
WHERE (SELECT
FROM
WHERE) ------> 서브쿼리
1. 서브쿼리는 일반적으로 기본질의 실행 전에 한번 실행된다.
2. 서브쿼리의 결과는 기본질의(외부질의, 주 쿼리, 메인쿼리리) 사용된다.
3. 서브쿼리는 ( ) 로 묶여있다.
4. 비교조건의 오른쪽에 서브쿼리를 명시한다.
5. 일반적인 서브쿼리(TOP-N 분석 제외) 에서는 ORDER BY 절을 사용하지 않는다.
6. 단일행 연산자 ( >, <, >=, <=, ==, !=), 복수행 연산자 (IN, ANY, ALL)을 사용한다.
7. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, UPDATE, INSERT, DELETE문 에서 올 수 있다.
Q2) 7566 사원보다 더 많은 월급을 받는 사원의 이름을 출력해보자.
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE EMPNO = 7566);
SQL> SELECT ENAME, SAL
2 FROM EMP
3 WHERE SAL > (SELECT SAL
4 FROM EMP
5 WHERE EMPNO = 7566);
ENAME SAL
-------------------- ----------
KING 5000
FORD 3000
SQL>
Q3) 급여의 평균보다 적은 사원의 사원번호, 이름, 급여 부서번호 출력하자
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL < (SELECT AVG(SAL)
FROM EMP);
SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
2 FROM EMP
3 WHERE SAL < (SELECT AVG(SAL)
4 FROM EMP);
EMPNO ENAME JOB SAL DEPTNO
---------- -------------------- ------------------ ---------- ----------
7369 SMITH CLERK 800 20
7499 ALLEN SALESMAN 1600 30
7521 WARD SALESMAN 1250 30
7654 MARTIN SALESMAN 1250 30
7844 TURNER SALESMAN 1500 30
7900 JAMES CLERK 950 30
7934 MILLER CLERK 1300 10
7 rows selected.
Q4) 사원의 번호가 7521인 사원의 직업과 같고
급여가 7934 사원보다 많은 사원의 사원번호, 이름, 직업, 입사일자 급여를 조회하자.
SELECT EMPNO, ENAME, JOB, HIREDATE, SAL
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE EMPNO = 7934)
AND
JOB = (SELECT JOB
FROM EMP
WHERE EMPNO = 7521);
SQL> SELECT EMPNO, ENAME, JOB, HIREDATE, SAL
2 FROM EMP
3 WHERE SAL > (SELECT SAL
4 FROM EMP
5 WHERE EMPNO = 7934)
6 AND
7 JOB = (SELECT JOB
8 FROM EMP
9 WHERE EMPNO = 7521);
EMPNO ENAME JOB HIREDATE SAL
---------- -------------------- ------------------ -------- ----------
7499 ALLEN SALESMAN 81/02/20 1600
7844 TURNER SALESMAN 81/09/08 1500
SQL>
Q5) 사원 테이블에서 사원의 급여가 20번 부서의 최소 급여보다 많은 부서 리턴
SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > (SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 20);
SQL> SELECT DEPTNO, MIN(SAL)
2 FROM EMP
3 GROUP BY DEPTNO
4 HAVING MIN(SAL) = (SELECT MIN(SAL)
5 FROM EMP
6 WHERE DEPTNO = 20);
DEPTNO MIN(SAL)
---------- ----------
20 800
Q6) 사원 테이블에서 직업중에서 가장 작은 평균 급여를 받는 직업을 조회하자.
SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = (SELECT MIN(AVG(SAL))
FROM EMP
GROUP BY JOB);
SQL> SELECT JOB, AVG(SAL)
2 FROM EMP
3 GROUP BY JOB
4 HAVING AVG(SAL) = (SELECT MIN(AVG(SAL))
5 FROM EMP
6 GROUP BY JOB);
JOB AVG(SAL)
------------------ ----------
CLERK 1016.66667
SQL>
Q7) 직업이 SALESMAN 인 최소 한명 이상의 사원보다 급여를 많이받는 사원의 이름, 급여, 직업을 출력하자.
SELECT ENAME, SAL, JOB
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
# 멀티 ROW 연산자, SALESMAN 값이 많음
ORA-01427: single-row subquery returns more than one row
SQL> SELECT ENAME, SAL, JOB
2 FROM EMP
3 WHERE SAL > (SELECT SAL
4 FROM EMP
5 WHERE JOB = 'SALESMAN');
WHERE SAL > (SELECT SAL
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
✔ 다중행 서브쿼리
IN : 목록에 있는 임의의 값과 동일하다. DATA OR , DATA OR, ,
ANY : 값을 반환하는 각가의 값과 개별 비교한다.
< ANY : 최대 값보다 적음
> ANY : 최소 값 보다 크다
= ANY : IN 연산자와 동일
ALL : 서브쿼리에 의해 반환되는 모든값과 비교한다.
< ALL : 최소 값보다 작음
> ALL : 최대 값 보다 클때
NOT : IN, ANY, ALL 연산자와 함께 사용된다.
# Q7과 동일적용 해보자.
SELECT ENAME, SAL, JOB
FROM EMP
WHERE SAL >ANY (SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
SQL> SELECT ENAME, SAL, JOB
2 FROM EMP
3 WHERE SAL > ANY (SELECT SAL
4 FROM EMP
5 WHERE JOB = 'SALESMAN');
ENAME SAL JOB
-------------------- ---------- ------------------
KING 5000 PRESIDENT
FORD 3000 ANALYST
JONES 2975 MANAGER
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
ALLEN 1600 SALESMAN
TURNER 1500 SALESMAN
MILLER 1300 CLERK
8 rows selected.
SELECT ENAME, SAL, JOB
FROM EMP
WHERE JOB!= 'SALESMAN'
AND SAL > ANY (SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
SQL> SELECT ENAME, SAL, JOB
2 FROM EMP
3 WHERE JOB! = 'SALESMAN'
4 AND SAL > ANY (SELECT SAL
5 FROM EMP
6 WHERE JOB = 'SALESMAN');
ENAME SAL JOB
-------------------- ---------- ------------------
KING 5000 PRESIDENT
FORD 3000 ANALYST
JONES 2975 MANAGER
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
MILLER 1300 CLERK
6 rows selected.
SQL>
Q8) 직업이 SALESMAN 인 모든 사원보다 급여를 많이 받는 사원의 이름, 봉급, 직업, 입사일, 부서번호 출력
SELECT ENAME, SAL, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE SAL > ALL (SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
SQL> SELECT ENAME, SAL, JOB, HIREDATE, DEPTNO
2 FROM EMP
3 WHERE SAL > ALL(SELECT SAL
4 FROM EMP
5 WHERE JOB = 'SALESMAN');
ENAME SAL JOB HIREDATE DEPTNO
-------------------- ---------- ------------------ -------- ----------
CLARK 2450 MANAGER 81/06/09 10
BLAKE 2850 MANAGER 81/05/01 30
JONES 2975 MANAGER 81/04/02 20
FORD 3000 ANALYST 81/12/03 20
KING 5000 PRESIDENT 81/11/17 10
SQL>
SELECT ENAME, SAL, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE JOB!= 'SALESMAN'
AND SAL > ALL (SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN');
SQL> SELECT ENAME, SAL, JOB, HIREDATE, DEPTNO
2 FROM EMP
3 WHERE JOB!= 'SALESMAN'
4 AND SAL > ALL (SELECT SAL
5 FROM EMP
6 WHERE JOB = 'SALESMAN');
ENAME SAL JOB HIREDATE DEPTNO
-------------------- ---------- ------------------ -------- ----------
CLARK 2450 MANAGER 81/06/09 10
BLAKE 2850 MANAGER 81/05/01 30
JONES 2975 MANAGER 81/04/02 20
FORD 3000 ANALYST 81/12/03 20
KING 5000 PRESIDENT 81/11/17 10
□ MULTI COLUMN SUBQUERY (다중열서브쿼리)
: 서브쿼리의 결과 값이 두 개 이상의 컬럼을 반환하는 서브쿼리로 혼합 WHERE 를 작성한다.
(중복된 WHERE 조건을 하나의 WHERE 절로 결합시킨다.)
Q9) FORD, BLAKE 와 관리자 및 부서가 같은 사원의 정보를 출력해보자.
- NON-PAIRWISE 비교
SELECT ENAME, MGR, DEPTNO
FROM EMP
WHERE MGR IN (SELECT MGR
FROM EMP
WHERE ENAME IN ('FORD','BLAKE'))
AND
DEPTNO IN (SELECT DEPTNO
FROM EMP
WHERE ENAME IN ('FORD','BLAKE'))
AND
ENAME NOT IN ('FORD','BLAKE');
SQL> SELECT ENAME, MGR, DEPTNO
2 FROM EMP
3 WHERE MGR IN (SELECT MGR
4 FROM EMP
5 WHERE ENAME IN ('FORD','BLAKE'))
6 AND
7 DEPTNO IN (SELECT DEPTNO
8 FROM EMP
9 WHERE ENAME IN ('FORD','BLAKE'))
10 AND
11 ENAME NOT IN ('FORD','BLAKE');
ENAME MGR DEPTNO
-------------------- ----- ------
JONES 7839 20
Q10) FORD, BLAKE 와 관리자 및 부서가 같은 사원의 정보를 출력해보자.
- PAIRWISE 비교
SELECT ENAME, MGR, DEPTNO
FROM EMP
WHERE (MGR, DEPTNO) IN (SELECT MGR, DEPTNO
FROM EMP
WHERE ENAME IN ('FORD', 'BLAKE'))
AND
ENAME NOT IN ('FORD','BLAKE');
SQL> SELECT ENAME, MGR, DEPTNO
2 FROM EMP
3 WHERE (MGR,DEPTNO) IN (SELECT MGR, DEPTNO
4 FROM EMP
5 WHERE ENAME IN ('FORD','BLAKE'))
6 AND
7 ENAME NOT IN ('FORD','BLAKE');
no rows selected
=============================
SELECT ENAME, MGR, DEPTNO
FROM EMP
WHERE MGR IN (FORD_EMPNO, BLAKE_EMPNO) AND DEPTNO IN (30,20);
SELECT ENAME, MGR, DEPTNO
FROM EMP
WHERE (MGR, DEPTNO) IN ((FORD_EMPNO,30), (BLAKE_EMPNO,20));
= WHERE (MGR, DEPTNO) IN ((7839,30), (7566,20));
==============================
□ CORRELATED SUBQUERY
SELECT
FROM TABLE1 MAIN
WHERE COLUMN1 OPERATOR (SELECT
FROM TABLE2
WHERE EXPR1 = MAIN.EXPR2);
- 메인 쿼리의 한 ROW에 대해서 서브쿼리가 한번 씩 실행된다.
- 테이블에서 행을 먼저 읽어서 각 행의 값을 관련된 데이터와 비교한다.
- 기본 질의에서 각 후보행에 대해 서브쿼리가 다른 결과를 반환해야 하는 경우 사용된다.
- 서브 쿼리에서 메인 쿼리의 컬럼명을 사용할 수 있으나, 메인쿼리에서는 서브쿼리의 컬럼명을 사용할 수 없다.
실행
후보행을 메인쿼리에서 가저온다 → 후보행의 값을 사용해서 서브쿼리를 실행한다.
→ 서브쿼리의 결과 값을 사용하여 후보행의 조건을 확인한다.→ 후보행이 남지 않을 떄 까지 반복한다.
Q11)
사원의 속한 부서의 평균 급여보다 많은 급여를 받는 사원의 이름, 급여, 부서번호, 입사일 직업을 출력하자.
(서브쿼리에서 메인 쿼리의 컬럼명을 사용할 수 있으나)
E. DEPTNO 는 메인쿼리의 EMP E로 지정된 테이블의 부서번호 값을 참조한다.
SELECT ENAME, SAL, DEPTNO, HIREDATE, JOB
FROM EMP E
WHERE SAL > (SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = E.DEPTNO);
SQL> SELECT ENAME, SAL, DEPTNO, HIREDATE, JOB
2 FROM EMP E
3 WHERE SAL > (SELECT AVG(SAL)
4 FROM EMP
5 WHERE DEPTNO = E.DEPTNO);
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>
□ 인라인 뷰 (INLINE VIEW)
: 원하는 테이블이 많을 경우 간추려서 사용할려고 이용한다. (속도빠르게)
FROM 절에 오는 서브쿼리
SELECT
FROM (SELECT ....)
WHERE 조건식 ;
Q12)
사원의 속한 부서의 평균 급여보다 많은 급여를 받는 사원의 이름, 급여, 부서번호, 입사일 직업을 출력하자.
SELECT E.ENAME, E.SAL, E.DEPTNO, E.HIREDATE, D.AVGSAL
FROM EMP E, (SELECT DEPTNO, AVG(SAL) AVGSAL
FROM EMP
GROUP BY DEPTNO) D
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL > D.AVGSAL;
SQL> SELECT E.ENAME, E.SAL, E.DEPTNO, E.HIREDATE, D.AVGSAL
2 FROM EMP E, (SELECT DEPTNO, AVG(SAL) AVGSAL
3 FROM EMP
4 GROUP BY DEPTNO) D
5 WHERE E.DEPTNO = D.DEPTNO
6 AND E.SAL > D.AVGSAL;
ENAME SAL DEPTNO HIREDATE AVGSAL
-------------------- ----- ------ -------- ------
ALLEN 1600 30 81/02/20 1567
JONES 2975 20 81/04/02 2258
BLAKE 2850 30 81/05/01 1567
KING 5000 10 81/11/17 2917
FORD 3000 20 81/12/03 2258
SQL>
□ TOP-N QUERY
Q13) 사원테이블에서 사원 중 급여를 많이받는 상위 3명의 이름, 급여를 조회해보자.
SELECT ROWNUM, ENAME, SAL
FROM EMP
WHERE ROWNUM <4
ORDER BY SAL DESC;
SQL> SELECT ROWNUM, ENAME, SAL
2 FROM EMP
3 WHERE ROWNUM < 4
4 ORDER BY SAL DESC;
ROWNUM ENAME SAL
------ -------------------- -----
2 ALLEN 1600
3 WARD 1250
1 SMITH 800
SQL>
SELECT ROWNUM, ENAME, SAL
FROM (SELECT*
FROM EMP
ORDER BY SAL DESC)
WHERE ROWNUM <4;
SQL> SELECT ROWNUM, ENAME, SAL
2 FROM (SELECT*
3 FROM EMP
4 ORDER BY SAL DESC)
5 WHERE ROWNUM <4;
ROWNUM ENAME SAL
------ -------------------- -----
1 KING 5000
2 FORD 3000
3 JONES 2975
SQL>
Q14) 4등에서 7등까지 출력해보자.
SELECT ROWNUM, ENAME, SAL
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC)
WHERE ROWNUM < 8
MINUS
SELECT ROWNUM, ENAME, SAL
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC)
WHERE ROWNUM <5;
-> 다시해볼것!
□ 스칼라 서브 쿼리
- 하나의 행 에서 하나의 열 값만 반환 하는 서브쿼리를 스칼라 서브쿼리 라고한다.
- SELECT, INSERT 문의 VALUES목록, DECODE 및 CASE 의 조건 및 표현식 부분, GROUP BY 를 제외한
SELECT 의 모든 절, UPDATE문의 SET절 및 WHERE 절에서 연산자 목록
- 서브 쿼리가 0개행을 반환하면 스칼라 서브쿼리의 값은 NULL이다.
- 서브쿼리가 2개 이상일 때는 행을 반환하는 오류가 발생한다.
Q15) EMP 테이블에서 사원번호, 이름, 봉급, 부서번호, 본인 부서의 평균 급여를 조회하자.
SELECT EMPNO, ENAME, SAL, DEPTNO,
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO) AS ASAL
FROM EMP E;
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO,
2 (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO) AS ASAL
3 FROM EMP E;
EMPNO ENAME SAL DEPTNO ASAL
----- -------------------- ----- ------ -----
7369 SMITH 800 20 2258
7499 ALLEN 1600 30 1567
7521 WARD 1250 30 1567
7566 JONES 2975 20 2258
7654 MARTIN 1250 30 1567
7698 BLAKE 2850 30 1567
7782 CLARK 2450 10 2917
7839 KING 5000 10 2917
7844 TURNER 1500 30 1567
7900 JAMES 950 30 1567
7902 FORD 3000 20 2258
EMPNO ENAME SAL DEPTNO ASAL
----- -------------------- ----- ------ -----
7934 MILLER 1300 10 2917
12 rows selected.
Q16) 사원번호, 이름, 해당 부서의 지역이 DALLAS 이면 TOP으로 BOSTON이면 BRENCH로 표시하자.
SELECT EMPNO, ENAME,
(CASE WHEN EMP.DEPTNO =
(SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS') THEN 'TOP'
WHEN EMP.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'BOSTON') THEN 'BRENCH'
ELSE 'NON-SPECIFIED' END ) AS LOCATION
FROM EMP;
SQL> SELECT EMPNO, ENAME,
2 (CASE WHEN EMP.DEPTNO =
3 (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS') THEN 'TOP'
4 WHEN EMP.DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'BOSTON') THEN 'BRENCH'
5 ELSE 'NON-SPECIFIED' END) AS LOCATION
6 FROM EMP;
EMPNO ENAME LOCATION
----- -------------------- --------------------------
7369 SMITH TOP
7499 ALLEN NON-SPECIFIED
7521 WARD NON-SPECIFIED
7566 JONES TOP
7654 MARTIN NON-SPECIFIED
7698 BLAKE NON-SPECIFIED
7782 CLARK NON-SPECIFIED
7839 KING NON-SPECIFIED
7844 TURNER NON-SPECIFIED
7900 JAMES NON-SPECIFIED
7902 FORD TOP
EMPNO ENAME LOCATION
----- -------------------- --------------------------
7934 MILLER NON-SPECIFIED
12 rows selected.
SQL>
Q17) ORDER BY 절에 서브쿼리 사용한 경우
EMP테이블에 사원번호, 사원이름, 부서번호, 입사일을 출력하되 본인 부서명이 큰값이 나오도록 정렬하자.
SELECT EMPNO, ENAME, DEPTNO, HIREDATE
FROM EMP E
ORDER BY (SELECT DNAME
FROM DEPT
WHERE DEPTNO = E.DEPTNO)DESC;
SQL> SELECT EMPNO, ENAME, DEPTNO, HIREDATE
2 FROM EMP E
3 ORDER BY (SELECT DNAME
4 FROM DEPT
5 WHERE DEPTNO = E.DEPTNO) DESC;
EMPNO ENAME DEPTNO HIREDATE
----- -------------------- ------ --------
7698 BLAKE 30 81/05/01
7900 JAMES 30 81/12/03
7844 TURNER 30 81/09/08
7654 MARTIN 30 81/09/28
7521 WARD 30 81/02/22
7499 ALLEN 30 81/02/20
7902 FORD 20 81/12/03
7566 JONES 20 81/04/02
7369 SMITH 20 80/12/17
7934 MILLER 10 82/01/23
7839 KING 10 81/11/17
EMPNO ENAME DEPTNO HIREDATE
----- -------------------- ------ --------
7782 CLARK 10 81/06/09
12 rows selected.
SQL>
□ EXISTS 연산자
: 서브쿼리의 결과 집합에 행이 있는지 유무를 검사.
Q18) 사원테이블에서 부하직원을 가지고 있는 사원의 사원번호, 이름, 직업, 입사일자를 리턴하자.
SELECT EMPNO, ENAME, JOB, HIREDATE
FROM EMP E
WHERE EXISTS (SELECT 1
FROM EMP
WHERE E.EMPNO = MGR)
ORDER BY EMPNO;
SQL> SELECT EMPNO, ENAME, JOB, HIREDATE
2 FROM EMP E
3 WHERE EXISTS (SELECT 1
4 FROM EMP
5 WHERE E.EMPNO = MGR)
6 ORDER BY EMPNO;
EMPNO ENAME JOB HIREDATE
----- -------------------- ------------------ --------
7566 JONES MANAGER 81/04/02
7698 BLAKE MANAGER 81/05/01
7782 CLARK MANAGER 81/06/09
7839 KING PRESIDENT 81/11/17
7902 FORD ANALYST 81/12/03
SQL>
□ WITH절
: 질의를 쉽게 만든다. 반복실행시에 한번만 실행된다.
Q19) WITH절을 이용해서 전체 부서의 총 급여보다 총급여가 많은 부서의 부서이름, 총 급여를 조회하자.
= 전체 부서의 평균급여보다 부서별 총 급여가 많은 부서 이름, 총 급여
WITH
DEPT_COSTS AS(SELECT DNAME, SUM(SAL) AS DEPT_TOTAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME
),
AVG_COST AS(
SELECT SUM(DEPT_TOTAL)/ COUNT(*)AS DEPT_AVG
FROM DEPT_COSTS
)
SELECT *
FROM DEPT_COSTS
WHERE DEPT_TOTAL > (SELECT DEPT_AVG
FROM AVG_COST)
ORDER BY DNAME;
SQL> WITH
2 DEPT_COSTS AS(SELECT DNAME, SUM(SAL) AS DEPT_TOTAL
3 FROM EMP E, DEPT D
4 WHERE E.DEPTNO = D.DEPTNO
5 GROUP BY D.DNAME
6 ),
7 AVG_COST AS(
8 SELECT SUM(DEPT_TOTAL)/ COUNT(*)AS DEPT_AVG
9 FROM DEPT_COSTS
10 )
11 SELECT *
12 FROM DEPT_COSTS
13 WHERE DEPT_TOTAL > (SELECT DEPT_AVG
14 FROM AVG_COST)
15 ORDER BY DNAME;
DNAME DEPT_TOTAL
---------------------------- ----------
ACCOUNTING 8750
SALES 9400
SQL>
😎 알아두기
: 인라인뷰는 1회용 VS 뷰는 객체
□ 계층형쿼리
- START WITH 절을 이용한 시작지점
- CONNECT BY 절을 이용한 트리진행 방향 설정 / 조건절을 이용해서 데이터 제거
- LEVEL 의사열의 활용
- PRIOR 키워드
- WHERE 조건절 활용해서 데이터 제거
[형식]
SELECT [LEVEL] COLUMN1, COLUMN2 , , ,
FROM TABLE1 MAIN
WHERE
[START WITH CONDITION] → 시작할 조건 명시, COLUMN1 = VALUE
[CONNECT BY PRIOR CONDITION];
CONNECT BY PRIOR PARENT KEY = CHLD KEY
CONNECT BY PRIOR PARENT KEY = PARENT KEY
Q20) SCOTT 계정의 상관관계
SELECT ENAME || ' REPORTS TO ' || PRIOR ENAME "WALK"
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR;
SQL> SELECT ENAME || ' REPORTS TO ' || PRIOR ENAME "WALK"
2 FROM EMP
3 START WITH ENAME = 'KING'
4 CONNECT BY PRIOR EMPNO = MGR;
WALK
--------------------------------------------------------------------
KING REPORTS TO
JONES REPORTS TO KING
FORD REPORTS TO JONES
SMITH REPORTS TO FORD
BLAKE REPORTS TO KING
ALLEN REPORTS TO BLAKE
WARD REPORTS TO BLAKE
MARTIN REPORTS TO BLAKE
TURNER REPORTS TO BLAKE
JAMES REPORTS TO BLAKE
CLARK REPORTS TO KING
WALK
--------------------------------------------------------------------
MILLER REPORTS TO CLARK
12 rows selected.
Q21) SMITH 기준으로 BOTTON UP 방향으로 매니저 정보를 출력해보자.
SELECT ENAME, JOB, MGR
FROM EMP
START WITH ENAME = 'SMITH'
CONNECT BY PRIOR MGR = EMPNO;
SQL> SELECT ENAME, JOB, MGR
2 FROM EMP
3 START WITH ENAME = 'SMITH'
4 CONNECT BY PRIOR MGR = EMPNO;
ENAME JOB MGR
-------------------- ------------------ -----
SMITH CLERK 7902
FORD ANALYST 7566
JONES MANAGER 7839
KING PRESIDENT
SQL>
Q22) 트리모양으로 계층 확인
SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
SQL> SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR;
ORG_CHART LEVEL EMPNO MGR DEPTNO
-------------------- ------- ------- ------- -------
KING 1 7839 10
JONES 2 7566 7839 20
FORD 3 7902 7566 20
SMITH 4 7369 7902 20
BLAKE 2 7698 7839 30
ALLEN 3 7499 7698 30
WARD 3 7521 7698 30
MARTIN 3 7654 7698 30
TURNER 3 7844 7698 30
JAMES 3 7900 7698 30
CLARK 2 7782 7839 10
ORG_CHART LEVEL EMPNO MGR DEPTNO
-------------------- ------- ------- ------- -------
MILLER 3 7934 7782 10
12 rows selected.
Q23) SMITH를 삭제하자
SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE ENAME != 'SMITH'
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
SQL> SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
2 FROM EMP
3 WHERE ENAME != 'SMITH'
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;
DEPTNO EMPNO ENAME JOB SAL
------- ------- -------------------- ------------------ -------
10 7839 KING PRESIDENT 5000
20 7566 JONES MANAGER 2975
20 7902 FORD ANALYST 3000
30 7698 BLAKE MANAGER 2850
30 7499 ALLEN SALESMAN 1600
30 7521 WARD SALESMAN 1250
30 7654 MARTIN SALESMAN 1250
30 7844 TURNER SALESMAN 1500
30 7900 JAMES CLERK 950
10 7782 CLARK MANAGER 2450
10 7934 MILLER CLERK 1300
11 rows selected.
SQL>
Q24) BRANCH 삭제하기
SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'JONES'
SQL> SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'JONES'
5 ;
DEPTNO EMPNO ENAME JOB SAL
------- ------- -------------------- ------------------ -------
10 7839 KING PRESIDENT 5000
30 7698 BLAKE MANAGER 2850
30 7499 ALLEN SALESMAN 1600
30 7521 WARD SALESMAN 1250
30 7654 MARTIN SALESMAN 1250
30 7844 TURNER SALESMAN 1500
30 7900 JAMES CLERK 950
10 7782 CLARK MANAGER 2450
10 7934 MILLER CLERK 1300
9 rows selected.
Q25) 계층형 쿼리 정렬
SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER BY ENAME;
SQL> SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR
5 ORDER BY ENAME;
ORG_CHART LEVEL EMPNO MGR DEPTNO
-------------------- ------- ------- ------- -------
ALLEN 3 7499 7698 30
BLAKE 2 7698 7839 30
CLARK 2 7782 7839 10
FORD 3 7902 7566 20
JAMES 3 7900 7698 30
JONES 2 7566 7839 20
KING 1 7839 10
MARTIN 3 7654 7698 30
MILLER 3 7934 7782 10
SMITH 4 7369 7902 20
TURNER 3 7844 7698 30
ORG_CHART LEVEL EMPNO MGR DEPTNO
-------------------- ------- ------- ------- -------
WARD 3 7521 7698 30
12 rows selected.
SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;
SQL> SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
2 FROM EMP
3 START WITH MGR IS NULL
4 CONNECT BY PRIOR EMPNO = MGR
5 ORDER SIBLINGS BY ENAME;
ORG_CHART LEVEL EMPNO MGR DEPTNO
-------------------- ------- ------- ------- -------
KING 1 7839 10
BLAKE 2 7698 7839 30
ALLEN 3 7499 7698 30
JAMES 3 7900 7698 30
MARTIN 3 7654 7698 30
TURNER 3 7844 7698 30
WARD 3 7521 7698 30
CLARK 2 7782 7839 10
MILLER 3 7934 7782 10
JONES 2 7566 7839 20
FORD 3 7902 7566 20
ORG_CHART LEVEL EMPNO MGR DEPTNO
-------------------- ------- ------- ------- -------
SMITH 4 7369 7902 20
12 rows selected.
Q26) 계층형 연결 경로 확인
SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB,
SYS_CONNECT_BY_PATH(ENAME, '-')
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
SQL> SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB,
2 SYS_CONNECT_BY_PATH(ENAME, '-')
3 FROM EMP
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;
ORG_CHART JOB
-------------------- ------------------
SYS_CONNECT_BY_PATH(ENAME,'-')
--------------------------------------------------------------------------------
KING PRESIDENT
-KING
JONES MANAGER
-KING-JONES
FORD ANALYST
-KING-JONES-FORD
ORG_CHART JOB
-------------------- ------------------
SYS_CONNECT_BY_PATH(ENAME,'-')
--------------------------------------------------------------------------------
SMITH CLERK
-KING-JONES-FORD-SMITH
BLAKE MANAGER
-KING-BLAKE
ALLEN SALESMAN
-KING-BLAKE-ALLEN
ORG_CHART JOB
-------------------- ------------------
SYS_CONNECT_BY_PATH(ENAME,'-')
--------------------------------------------------------------------------------
WARD SALESMAN
-KING-BLAKE-WARD
MARTIN SALESMAN
-KING-BLAKE-MARTIN
TURNER SALESMAN
-KING-BLAKE-TURNER
ORG_CHART JOB
-------------------- ------------------
SYS_CONNECT_BY_PATH(ENAME,'-')
--------------------------------------------------------------------------------
JAMES CLERK
-KING-BLAKE-JAMES
CLARK MANAGER
-KING-CLARK
MILLER CLERK
-KING-CLARK-MILLER
12 rows selected.
Q27) 계층형 쿼리에서 해당하는 로우가 자식노드가 있는지 없는지 여부를 체크
: 자식노드가 있을 경우 0 , 자식노드가 없을 경우 1
SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB,
CONNECT_BY_ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
SQL> SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB,
2 CONNECT_BY_ISLEAF
3 FROM EMP
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;
ORG_CHART JOB CONNECT_BY_ISLEAF
-------------------- ------------------ -----------------
KING PRESIDENT 0
JONES MANAGER 0
FORD ANALYST 0
SMITH CLERK 1
BLAKE MANAGER 0
ALLEN SALESMAN 1
WARD SALESMAN 1
MARTIN SALESMAN 1
TURNER SALESMAN 1
JAMES CLERK 1
CLARK MANAGER 0
ORG_CHART JOB CONNECT_BY_ISLEAF
-------------------- ------------------ -----------------
MILLER CLERK 1
12 rows selected.
Q28) LEAF만 보여주기
WHERE CONNECT_BY_ISLEAF = 1 : ROW가 자식노드를 가지고 있지 않을때 LEAF NODE이다.
WHERE CONNECT_BY_ISLEAF = 0 : ROW가 자식노드를 가지고 있지 않을 때 LEAF NODE가 아니다.
SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB
FROM EMP
WHERE CONNECT_BY_ISLEAF = 1
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
SQL> SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB
2 FROM EMP
3 WHERE CONNECT_BY_ISLEAF = 1
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;
ORG_CHART JOB
-------------------- ------------------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
JAMES CLERK
MILLER CLERK
7 rows selected.
SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB
FROM EMP
WHERE CONNECT_BY_ISLEAF = 0
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
SQL> SELECT LPAD(' ', 4*LEVEL-4)||ENAME ORG_CHART, JOB
2 FROM EMP
3 WHERE CONNECT_BY_ISLEAF = 0
4 START WITH MGR IS NULL
5 CONNECT BY PRIOR EMPNO = MGR;
ORG_CHART JOB
-------------------- ------------------
KING PRESIDENT
JONES MANAGER
FORD ANALYST
BLAKE MANAGER
CLARK MANAGER
'｜Playdata_study > SQL.Oracle' 카테고리의 다른 글
| 210527_DB5(SQL) / 트랜잭션 (0) | 2021.05.27 |
|---|---|
| 210526_DB4 (SQL) / INSERT, UPDATE, DELETE, MARGE (0) | 2021.05.26 |
| 210524 _DB3 (SQL) / 분석함수, JOIN (0) | 2021.05.24 |
| 210521 _DB3 (SQL) (0) | 2021.05.22 |
| 210518_DB2 (SQL) (0) | 2021.05.19 |
댓글