210525 oracle - SET, SUBQUERY

1. set 연산자를 이해하고 기술할 수 있다.
2. subquery를 기술할 수 있다.

============================================================================
SET 연산자는 두 개 이상의 질의 결과를 하나의 결과로 통합하며 SET 연산자를 포함하는 질의를
복합질의 라고 한다.  --> 질의는 쿼리를 말함

종류는 다음과 같다.
  - UNION : 두 질의 중 어느 것 하나에 의해서라도 선택된 모든 구분(DISTINCT)행을 결과로 한다.
                양쪽 질의의 의해서 선택된 행을 모두 포함한다. 
                중복 값은 한 번만 첫 번째 칼럼을 기준으로 ASC정렬

  - UNION ALL : 중복 행을 포함하여 두 질의 중 어느 것 하나에 의해서 선택된 모든 행을 결과로

  - INTERSECT : 두 질의에 공통으로 선택된 모든 구분(DISTINCT)행을 결과로 한다.
                     첫 번째 선택 문장에서 두 번째 선택 문장과 일치하는 최소한 하나의 행을 리턴

  - MINUS : 첫 번째 SELECT 문에 선택되고 두 번째 SELECT 문에서 선택되지 않은 
                모든 구분(DISTINCT)행을 결과로 한다.
                두 번째 선택 문장에서는 선택되지 않은 첫 번째 선택 문장에 의해 선택된 모든 유일 행

특징
  - 모든 SET 연산자는 같은 우선순위를 가진다. SQL문장이 여러 개의 SET 연산자를 포함한다면
    DB는 명시적으로 다른 순서를 명시하는 괄호가 없을 경우 왼쪽(위)에서 오른쪽(아래)로 실행

  - 사용하는 질의의 평가 순서를 명시적으로 표현하기 위해서는 괄호를 사용한다.

[형식]
SELECT 
FROM
WHERE
GROUP BY
HAVING

    SET 연산자

SELECT 
FROM
WHERE
GROUP BY
HAVING
ORDER BY

============================================================================
UNION
  - 두 개의 SELECT 문장에서 컬럼의 개수가 맞아야 한다. 컬럼의 순서도 맞아야 하는 듯?

  - 컬럼 이름은 같지 않아도 된다.

  - 중복 값은 하나만 리턴된다.

  - WHERE 절에서 UNION을 사용하는 질의는 
    SELECT 목록에 있는 것과 똑같은 수와 데이터 타입의 열을 가져야 한다.

============================================================================
Subquery
정의, 유형
  - Single row Subquery, Multi row Subquery
  - Multi column Subquery
  - Scalar Subquery
  - Correlated Subquery
  - With 절과 Subquery

Scalar -> 프로그램에서 자료형을 의마한다. 단일 값을 의미한다. 정수, 문자, 문자열 등

[형식]
SELECT
FROM
WHERE ( SELECT
            FROM
            WHERE )

    밖에가 주 쿼리, OUTER 쿼리, 기본 질의, 외부 질의
    괄호 안이 서브 쿼리, INNER 쿼리, 부 쿼리

  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 문에 사용


ex) 'JONES'보다 더 많은 월급을 받는 사원의 이름과 월급을 출력해보자
SELECT SAL
FROM EMP
WHERE ENAME = 'JONES';  --> 2975

SELECT ENAME, SAL
FROM EMP
WHERE SAL > 2975;  --> 2명 

이 작업을 한번에 한다.
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ( SELECT SAL
                     FROM EMP
                     WHERE ENAME = 'JONES' );

 

============================================================================

다중 행 서브쿼리 : Multi row Subquery
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 연산자와 함께 사용된다. 

 

============================================================================

다중 열 서브쿼리 : Multi column Subquery
서브쿼리의 결과값이 두 개 이상의 컬럼을 반환하는 서브쿼리로 혼합WHERE를 작성한다.
중복된 WHERE 조건을 하나의 WHERE 절로 결합시킨다.
  - NON-PAIRWISE 비교 : 

  - PAIRWISE 비교 : 

 

============================================================================

Correlated Subquery
[형식]
SELECT
FROM TABLE1 MAIN
WHERE COLUMN1 OPERATOR ( SELECT
                                          FROM TABLE2
                                          WHERE expr1 = main.expr2 )

  - 메인 쿼리의 한 row에 대해서 서브쿼리가 한 번씩 실행된다

  - 테이블에서 행을 먼저 읽어서 각 행의 값을 관련된 데이터와 비교한다.

  - 기본 질의에서 각 후보행에 대해 서브쿼리가 다른 결과를 반환해야 하는 경우 사용된다.

  - 서브쿼리에서 메인쿼리의 컬럼명을 사용할 수 있으나, 
    메인쿼리에서는 서브쿼리의 컬렴명을 사용할 수 없다.

실행
  1. 후보행을 메인쿼리에서 가져온다
  2. 후보행의 값을 사용해서 서브쿼리를 실행한다
  3. 서브쿼리의 결과 값을 사용하여 후보행의 조건을 확인한다.
  4. 후보행이 남지 않을 때까지 반복한다.

FROM 절에 서브쿼리 = INLINE VIEW
[형식]
SELECT
FROM ( SELECT ,,, )
WHERE 조건식;

============================================================================
TOP-N 쿼리
ex)
SELECT ROWNUM, ENAME, SAL
FROM EMP
WHERE ROWNUM < 4
ORDER BY SAL DESC;

SELECT ROWNUM, ENAME, SAL
FROM ( SELECT *
           FROM EMP
           ORDER BY SAL DESC )
WHERE ROWNUM < 4;

============================================================================
스칼라 서브쿼리 : Scalar Subquery
  - 하나의 행에서 하나의 열 값만 반환하는 서브쿼리를 스칼라 서브쿼리라고 한다.

  - SELECT, INSERT 문의 VALUES 목록, DECODE 및 CASE의 조건 및 표현식 부분,
    GROUP BY 를 제외한 SELECT 의 모든 절, UPDATE 문의 SET 절 및 WHERE 절에서 연산자 목록

  - 서브쿼리가 0개 행을 반환하면 스칼라 서브쿼리의 값은 NULL이다.

  - 서브쿼리가 2개 이상일 때는 행을 반환하는 오류가 발생한다.

============================================================================
Exists 연산자 : 서브쿼리의 결과 집합에 행이 있는지 유무를 검사

WITH절
  - 질의를 쉽게 만든다.

  - 반복 실행 시에 한 번만 실행된다.

============================================================================
계층형 쿼리
  - 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 = CHILD KEY
    CONNECT BY PRIOR CHILD KEY = PARENT KEY

============================================================================
Q1) 데이터 생성을 해보자

CREATE TABLE EMP_HISTORY( NAME, TITLE, DEPTID )
AS
SELECT ENAME, JOB, DEPTNO
FROM EMP;

DESC EMP_HISTORY;


Q2) EMP 테이블과 EMP_HISTORY의 테이블의 내용을 UNION 연산을 실행

SELECT ENAME, JOB, DEPTNO 
FROM EMP
UNION
SELECT *
FROM EMP_HISTORY;

같은결과 = FULL JOIN으로 변경

SELECT ENAME, JOB, DEPTNO 
FROM EMP E FULL JOIN EMP_HISTORY A
ON E.ENAME = A.NAME;

어제 만든 TEST_EMP로도 해봄

SELECT ENAME, JOB, DEPTNO 
FROM EMP
UNION
SELECT ENAME, JOB, DEPTNO
FROM TEST_EMP;


Q3) EMP 테이블과 EMP_HISTORY의 테이블의 내용을 UNION ALL 연산을 실행

SELECT ENAME, JOB, DEPTNO 
FROM EMP
UNION ALL
SELECT *
FROM EMP_HISTORY;


Q4) TEST_EMP, EMP를 이용해서 INTERSECT 해보자

SELECT *
FROM TEST_EMP
INTERSECT
SELECT *
FROM EMP;


Q5) EMP 테이블과 EMP_HISTORY의 테이블의 내용을 MINUS 연산을 실행

SELECT ENAME, JOB, DEPTNO 
FROM EMP
MINUS
SELECT *
FROM EMP_HISTORY;

SELECT *
FROM TEST_EMP
MINUS
SELECT *
FROM EMP;


Q6) EMP, DEPT에서 부서번호, 부서이름, 입사일을 UNION 연산자를 이용해서 출력해보자

SELECT DEPTNO, TO_CHAR(NULL), HIREDATE
FROM EMP
UNION
SELECT DEPTNO, DNAME, TO_DATE(NULL)
FROM DEPT
ORDER BY 1;

같은 표현이지만 다른데서 타입을 확인하기 위해 명시적으로 표현하려고 TO CHAR TO DATE를 씀

SELECT DEPTNO, NULL, HIREDATE
FROM EMP
UNION
SELECT DEPTNO, DNAME, NULL
FROM DEPT
ORDER BY 1;


Q7) 7566 사원보다 더 많은 월급을 받는 사원의 이름과 월급을 출력해보자

SELECT ENAME , SAL
FROM EMP
WHERE SAL > ( SELECT SAL
                     FROM EMP
                     WHERE EMPNO = 7566 );


Q8) 급여의 평균보다 적은 사원의 사원번호, 이름, 직업, 급여, 부서번호를 출력하자

SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL < ( SELECT AVG(SAL)
                     FROM EMP );


Q9) 사원의 번호가 7521인 사원의 직업과 같고 급여가 7934 사원보다 많은 사원의
      사원번호, 이름, 직업, 입사일자, 급여를 조회하자

SELECT EMPNO, ENAME, JOB, HIREDATE, SAL
FROM EMP
WHERE JOB = ( SELECT JOB
                     FROM EMP
                     WHERE EMPNO = 7521 )
AND SAL > ( SELECT SAL
                  FROM EMP
                  WHERE EMPNO = 7934 );


Q10) 사원테이블에서 사원의 급여가 20번 부서의 최소 급여보다 많은 부서를 리턴해보자

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > ( SELECT MIN(SAL)
                             FROM EMP
                             WHERE DEPTNO = 20 );


Q11) 사원테이블에서 직업 중에서 가장 작은 평균 급여를 받는 직업을 조회하자

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING AVG(SAL) = ( SELECT MIN(AVG(SAL))
                              FROM EMP
                              GROUP BY JOB);


Q12) 직업이 SALESMAN인 최소 한 명 이상의 사원보다 급여를 많이 받는 사원의 
       이름, 급여, 직업을 출력하자

SELECT ENAME, SAL, JOB
FROM EMP
WHERE JOB != 'SALESMAN'
AND SAL > ANY ( SELECT SAL
                        FROM EMP
                        WHERE JOB = 'SALESMAN' );


Q13) 직업이 SALESMAN인 모든 사원보다 급여를 많이 받는 사원의
       이름, 봉급, 직업, 입사일, 부서번호를 출력하자

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

결과 같음

SELECT ENAME, SAL, JOB, HIREDATE, DEPTNO
FROM EMP
WHERE SAL > ( SELECT MAX(SAL)
                     FROM EMP
                     WHERE JOB = 'SALESMAN' );


Q14) FORD, BLAKE와 관리자 및 부서가 같은 사원의 정보를 출력해보자

SELECT *
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');


Q15) FORD, BLAKE와 관리자 및 부서가 같은 사원의 정보를 출력해보자
       PAIRWISE 비교

SELECT *
FROM EMP
WHERE (MGR, DEPTNO) IN ( SELECT MGR, DEPTNO
                                     FROM EMP
                                     WHERE ENAME IN ('FORD', 'BLAKE') )
AND ENAME NOT IN ('FORD', 'BLAKE');    --> 안되는게 정상


Q16) 사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원의
       이름, 급여, 부서번호, 입사일, 직업을 출력하자

SELECT ENAME, SAL, DEPTNO, HIREDATE, JOB
FROM EMP E
WHERE SAL > ( SELECT AVG(SAL)
                     FROM EMP
                     WHERE DEPTNO = E.DEPTNO );


Q17) 사원이 속한 부서의 평균 급여보다 많은 급여를 받는 사원의
       이름, 급여, 부서번호, 입사일, 직업을 출력하자

SELECT E.ENAME, E.SAL, E.DEPTNO, E.HIREDATE, E.JOB, D.AVGSAL
FROM EMP E, ( SELECT DEPTNO, AVG(SAL) AVGSAL
                    FROM EMP E
                    GROUP BY DEPTNO ) D
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL > D.AVGSAL;


Q18) 사원테이블에서 사원 중 급여를 많이 받는 상위 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 < 4;

다른 풀이
SELECT ENAME, SAL
FROM ( SELECT ROWNUM, ENAME, SAL
           FROM ( SELECT *
                      FROM EMP
                      ORDER BY SAL DESC )
                      WHERE ROWNUM < 8
                      ORDER BY 1 DESC )
WHERE ROWNUM < 5;


Q19) EMP 테이블에서 사원번호, 이름, 부서번호, 본인 부서의 평균 급여를 조회하자
SELECT EMPNO, ENAME, SAL, DEPTNO, ( SELECT AVG(SAL)
                                                     FROM EMP
                                                     WHERE DEPTNO = E.DEPTNO) AS ASAL
FROM EMP E;

SELECT EMPNO, ENAME, SAL, DEPTNO, AVG(SAL)
FROM EMP JOIN DEPT USING (DEPTNO)
GROUP BY DEPTNO;




Q20) 사원번호, 이름, 해당부서의 지역이 DALLAS이면 TOP으로 BOSTON이면 BRENCH로 표시하자

SELECT EMPNO, ENAME, ( CASE WHEN DEPTNO = ( SELECT DEPTNO
                                                                     FROM DEPT
                                                                     WHERE LOC = 'DALLAS' ) THEN 'TOP'
                                          WHEN DEPTNO = ( SELECT DEPTNO
                                                                     FROM DEPT
                                                                     WHERE LOC = 'BOSTON' ) THEN 'BRENCH' 
                                          ELSE '없음' END ) AS LOCATION
FROM EMP;


Q21) 사원테이블에서 사원번호 이름 부서번호 입사일을 출력하되 
       본인 부서의 부서명이 큰 값이 나오도록 정렬
SELECT EMPNO, ENAME, DEPTNO, HIREDATE
FROM EMP E
ORDER BY ( SELECT DNAME
                FROM DEPT
                WHERE DEPTNO = E.DEPTNO) DESC;


Q22) 사원테이블에서 부하직원을 가지고 있는 사원의 사원번호, 이름, 직업, 입사일자를 조회하자.

SELECT EMPNO, ENAME, JOB, HIREDATE
FROM EMP E
WHERE EXISTS ( SELECT 1 
                      FROM EMP
                      WHERE E.EMPNO = MGR )
ORDER BY EMPNO;


Q23) WITH 절을 이용해서 전체 부서의 총 급여보다 총 급여가 많은 부서의 
      부서이름, 총 급여를 조회하자
      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;


Q24) 

SELECT ENAME || '  reports to  ' || PRIOR ename "walk"
FROM EMP
START WITH ENAME = 'KING'
CONNECT BY PRIOR EMPNO = MGR; 


Q25) SMITH 기준으로 BOTTOM UP 방향으로 매니저 정보를 출력해보자

SELECT ENAME, JOB, EMPNO, MGR
FROM EMP
START WITH ENAME = 'SMITH'
CONNECT BY PRIOR MGR = EMPNO;


Q26) 

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, LEVEL, EMPNO, MGR, DEPTNO
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR; 


Q27) 

SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE ENAME != 'SMITH'
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR; 


Q28) Branch 삭제 하기

SELECT DEPTNO, EMPNO, ENAME, JOB, SAL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'JONES';


Q29) 

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;

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;


Q30) 누구 - 누구 -

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, JOB,
SYS_CONNECT_BY_PATH(ENAME, '--') "11"
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;


Q31) 자식이 있다 없다

SELECT LPAD(' ', 4 * LEVEL-4) || ENAME ORG_CHART, JOB,
CONNECT_BY_ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;


Q32) LEAF만 보여주기

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;