귤귤의 대모험

선택적 조인

Lateral View를 활용한 튜닝

Lateral View와 아우터조인의 개념에 대해서는 이미 언급이 되었고

이번에는Lateral View 를 이용한 튜닝에 대하여 알아보기로 한다.

먼저 개발자들에게 받는 질문 상위 10개 중에 항상 들어있는 질문이 있다.

"선택적으로 조인하는 기능이 오라클에 있습니까?"

필자는 항상 다음과 같이 답변한다.

"있습니다."

아래 모델을 보자.

이모델을 보면 고객유형(subtype) 에 따라서 개인기본으로 조인할지 사업자기본으로 조인할지 결정이 되는것이다.

물론 연락처기본은 고객기본과 항상 1:1 이다.

이런 모델에서 대부분의 개발자는 아래와 같은 SQL 을 작성한다.

SELECT

A.고객번호, A.고객유형,

B.취미코드, B.종교코드,

C.사업규모코드, C.종업원수,

D.대표핸드폰번호

FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D

WHERE A.고객번호 = B.고객번호 (+)

AND A.고객번호 = C.고객번호(+)

AND A.고객번호 = D.고객번호

AND A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함.

언뜻 보기에 위의 SQL 은 아무 문제가 없어보인다.

하지만 과연 그런가?

아래 Trace 결과를 보자

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)

1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)

1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)

1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)

1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)

1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)

1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)

0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=3 pr=0 pw=0 time=33 us)

0 INDEX UNIQUE SCAN PK_사업자기본 (cr=3 pr=0 pw=0 time=29 us)

1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)

1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)

개인고객임에도 불구하고 사업자기본 테이블 및 인덱스에 3블럭(cr =3)씩 read 한것을 볼수 있다.

위의 SQL 은 항상 고객번호 인덱스로 개인기본과 사업자 기본을 뒤진후에 연락처기본과 조인하는 구조이다.

다시말하면 개인고객인경우도 사업자기본 테이블을 access 하고 사업자고객인 경우도 개인기본 테이블을 access 한다는 뜻이다.

아래처럼 ANSI SQL 을 사용하여 SQL 을 수정하면 오라클은 Lateral View 로 변환하여 비효율적인 access 를 방지한다.

고객유형에 따라서 개인일 경우 개인 기본 테이블만 access하고 고객유형이 사업자일 경우는 사업자기본 테이블만 access 한다.

SELECT

A.고객번호, A.고객유형,

B.취미코드, B.종교코드,

C.사업규모코드, C.종업원수,

D.대표핸드폰번호

FROM 고객기본 A

left outer join 개인기본 B

on (A.고객번호 = B.고객번호 and A.고객유형 = '1') --> 고객유형이 개인 일경우만 조인됨

left outer join 사업자기본 C

on (A.고객번호 = C.고객번호 and A.고객유형 = '2') --> 고객유형이 사업자 일경우만 조인됨

join 연락처기본 D

on (A.고객번호 = D.고객번호) --> 무조건 조인한다.

WHERE A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함

이것을 Trace 에서 차이를 비교해보면 read 한 블럭수가 차이난다.

즉 개인고객이면 사업자기본 테이블을 읽은 블럭수가 0 이고 사업자고객이면 개인기본 테이블을 읽은 블럭수가 0 이라는 뜻이다.

아래의 Trace 결과를 보자.

Rows Row Source Operation

------- ---------------------------------------------------

0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)

1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)

1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)

1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)

1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)

1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)

1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)

0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=0 pr=0 pw=0 time=33 us)

0 INDEX UNIQUE SCAN PK_사업자기본 (cr=0 pr=0 pw=0 time=29 us)

1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)

1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)

자주 엑세스 하는 뷰를 만들때도 위와 같은 SQL 로 만들어야 할것이다.

ANSI SQL 을 사용할수 없는 구조라면 아래처럼 DECODE 함수를 활용하면 위와 같은 효과를 얻을수 있다.

SELECT

A.고객번호, A.고객유형,

B.취미코드, B.종교코드,

C.사업규모코드, C.종업원수,

D.대표핸드폰번호

FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D

WHERE DECODE(A.고객유형, '1',A.고객번호) = B.고객번호(+) --> 고객유형이 개인일경우만 조인됨

AND DECODE(A.고객유형, '2',A.고객번호) = C.고객번호(+) --> 고객유형이 사업자 일경우만 조인됨

AND A.고객번호 = D.고객번호

AND A.고객번호 = :V_고객번호;

부등호 조인

이번 장은 기본 개념 부분에서 다룬 조인에 대한 심화학습이다

1. 부등호 조인에 대한 복습

2. 조인을 이용해 해결하는 방법과 서브쿼리로 해결하는 방법을 제시한다

   두가지 해법의 비교를 통해 언제 조인과 서브쿼리가 같은 결과를 낼 수 있는지를 알아보기 바란다

3. 경우에 따라 선택적으로 조인을 하는 문제를 다뤄본다

참고로 부등호 조인은 Cartesian Product와 많은 관련이 있으므로 기본적인 의미를 알아야만 이해하기가 쉽다

Cartesian Product WHERE절에 조인조건 없이 조인하는 경우 테이블 ROW 개수만큼 데이터가 복제되는 현상이다.

부등호 조인은 조인을 할 경우 BETWEEN, LIKE, 부등호 같은 연산자 조인이 필요할 때 사용한다

부등호 조인이 일어나기전 Cartesian Product로 조인이 일어난다 그리고 where절의 조건문에 의해 행마다 조건에 부합하는 행들이 출력되는 것이다

(이것은 부등호조인만이 아니라 테이블을 조인할 때에는

select문 순서에 의해 모든 테이블이 Cartesian Product이 일어나는 것을 알수 있다)

이 때 부등호 조인은 한 개의 테이블의 컬럼 값이 다른 테이블 두 개의 컬럼에 수치상으로 포함될 때 두 테이블은 하나의 테이블로 합쳐지고 그 합쳐질 때

두 개를 부등호 조인으로 행마다 조건절을 주어 거기에 해당하는 행만 출력하는 조건이다

- 부등호조인 단점

cartesian product로 join을 하면서 부등호 조건을 동시에 비교하기 때문에

칼럼이 많을 수록 연산속도가 저하

100행 연산 0.38 / 1000행 연산 16.80

27 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 이전사원급여합계

FROM TEMP

28, 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼에 해당 부서별 사원과 이전 사원 급여액 합계를 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (PARTITION BY DEPT_CODE

                                       ORDER BY SALARY

                                       ROWS 1 PRECEDING ) AS 부서별이전사원급여합계

FROM TEMP

29 사번, 이름, 부서번호, 급여를 출력하고 분석함수를 사용한 컬럼 3개를 생성하여 첫번째는 사원들의 급여의 총액

두 번째는 사원들의 누적급여총액 그리고 또 다른 하나의 세 번째는 두 번째를 역순으로 표시하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

          ,SUM(SALARY) OVER (ORDER BY SALARY

                                       ROWS BETWEEN UNBOUNDED PRECEDING

                                       AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

         ,SUM(SALARY) OVER (ORDER BY SALARY

                                      ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

         ,SUM(SALARY) OVER (ORDER BY SALARY DESC

                                      ROWS BETWEEN CURRENT ROW

                                      AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

30 29번 결과 값을 참고하여 사원이 아닌 부서별로 사원들의 총액, 부서별 사원들의 누적급여액 등을 구하여라

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS BETWEEN UNBOUNDED PRECEDING

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원급여합계

               ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY

                                                            ROWS UNBOUNDED PRECEDING ) AS 부서사원누적급여합계

                ,SUM(SALARY) OVER ( PARTITION BY DEPT_CODE

                                                            ORDER BY SALARY DESC

                                                            ROWS BETWEEN CURRENT ROW

                                                            AND UNBOUNDED FOLLOWING ) AS 부서사원누적역순급여합계

FROM TEMP

31 일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균 금액을 구하라 (SALE_HIST)

31번 문제는 PARTITION, GROUP, RANGE 활용 문제이다

SELECT SALE_DATE, SALE_SITE, SUM(SALE_AMT),

                 ROUND(AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_SITE

                                                                                              ORDER BY SALE_DATE

                                                                                              RANGE INTERVAL '2' DAY PRECEDING)) AS 이동평균

FROM SALE_HIST

GROUP BY SALE_DATE, SALE_SITE

32 각 ROW의 판매액, 동일일자/ 동일품목의 최대판매액, 최대판매액사업장, 해당 사업장 최소판매액, 최소판매액 사업장을 구하여라

SELECT SALE_DATE, SALE_ITEM, SALE_AMT, SALE_SITE

          ,FIRST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                    ORDER BY SALE_AMT DESC

                                                    ROWS BETWEEN UNBOUNDED PRECEDING

                                                    AND UNBOUNDED FOLLOWING ) AS 최고판매액

         ,FIRST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                   ORDER BY SALE_AMT DESC

                                                   ROWS BETWEEN UNBOUNDED PRECEDING

                                                   AND UNBOUNDED FOLLOWING ) AS 최고판매사업장

        ,LAST_VALUE(SALE_AMT) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매액

        ,LAST_VALUE(SALE_SITE) OVER( PARTITION BY SALE_DATE, SALE_ITEM

                                                 ORDER BY SALE_AMT DESC

                                                 ROWS BETWEEN UNBOUNDED PRECEDING

                                                 AND UNBOUNDED FOLLOWING ) AS 최저판매사업장

FROM SALE_HIST;

33 SALE_HIST의 자료를 이용하여‘01’ 사업장‘ PENCIL’ 품목의 일자별 누적 판매금액을 구하여라

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER ( ORDER BY SALE_DATE

                                          RANGE INTERVAL '5' DAY PRECEDING) AS 누적합계

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01'

34 품목별/일자별 과거 판매매액을 모두 이용하는 이동평균값을 구하라

SELECT SALE_ITEM , SALE_DATE, SUM(SALE_AMT)

          ,AVG(SUM(SALE_AMT)) OVER( PARTITION BY SALE_ITEM

                                                 ORDER BY SALE_DATE

                                                 ROWS UNBOUNDED PRECEDING ) AS 품목일자별이동평균

FROM SALE_HIST

GROUP BY SALE_ITEM , SALE_DATE

35 SALE_HIST의 자료를 이용하여 “01”사업장 PENCIL 품목에 대해 일자별 매출액과 전일 매출, 당일과 전일의 매출액 차이를 구하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT

          , LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출

          , SALE_AMT - LAG(SALE_AMT,1) OVER ( ORDER BY SALE_DATE) AS 전일매출차액

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01’

21 TEMP의 자료에서 ROWNUM을 채취해 값이 5와 10 사이에 있는 행의 ROWNUM, 사번, 이름을 조회하시오

SELECT NUM, EMP_ID, EMP_NAME

FROM ( SELECT ROWNUM NUM, EMP_ID, EMP_NAME FROM TEMP)

WHERE NUM > 4 AND NUM <11

22

INSERT INTO TDEPT ( DEPT_CODE, DEPT_NAME, PARENT_DEPT, USE_YN, AREA, BOSS_ID ) VALUE

(‘000000’, ‘사장실’, ‘ ’, ‘Y’, ‘서울’, ‘ ’)

위의 구문을 COMMIT 하고

기존 테이블에 최상위 부서로 지정되어있는 경영지원 부서를 사장실로 변경한다

UPDATE TDEPT

SET PARENT_DEPT = '000000'

WHERE DEPT_CODE=PARENT_DEPT;

23 사장실을 기준으로 계층구조 전개를 하여 출력하시오 (LPAD를 활용)

SELECT LPAD(DEPT_NAME, LEVEL*12,' ')AS 부서이름

,DEPT_CODE

,PARENT_DEPT

FROM TDEPT

START WITH DEPT_CODE='000000'

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT

24 CA0001의 하위 부서를 모두 제거 하고 계층구조 전개를 하여 출력하시오 (LPAD를 활용)

SELECT LPAD(DEPT_NAME, LEVEL*12,' ')AS 부서이름, DEPT_CODE, PARENT_DEPT

FROM TDEPT

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT

AND DEPT_CODE <> ‘CA0001’

START WITH DEPT_CODE='000000'

CONNECT 와 조건절의 순서를 어디에 배치하는가에 따라 그 느낌도 구조도 달라진다

"CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT" 

->  왼쪽의 구문은 CONNECT가 일어나고 그다음

"AND DEPT_CODE <> ‘CA0001’" 

->  CONNECT에 대한 조건절을 하나 더 추가하는 느낌으로 CONNECT가 생성이되고 나서 조건절에 대한 계층에 의미를 부여한다 그래서 CA0001에 해당하는 하위부서 까지 모두 제거된다

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

SELECT LPAD(DEPT_NAME, LEVEL*12,' ')AS 부서이름, DEPT_CODE, PARENT_DEPT

FROM TDEPT

WHERE DEPT_CODE <> ‘CA0001’

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT

START WITH DEPT_CODE='000000'

테이블의 조건절로 사용되며 테이블에서 먼저 CA0001을 제거한 후

WHERE DEPT_CODE <> ‘CA0001’ 

->  CONNECT를 실시하게 된다 그래서 CA0001만 없어지고

CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT 

->  CA0001의 하위부서는 그대로 남겨지게된다

25 어느 한 사원이 영업2(CD0001)부서에 근무하고 있다 자기의 상위 부서를 출력하시오

(계층구조 전개로출력)

SELECT LEVEL, LPAD(DEPT_NAME, LEVEL*12,' ') AS 부서이름 ,DEPT_CODE ,PARENT_DEPT

FROM TDEPT

START WITH DEPT_CODE = 'CD0001'

CONNECT BY PRIOR PARENT_DEPT=DEPT_CODE

26 부서별로 부서장(BOSS_ID)과 성명(EMP_ID)를 부서의 전개에 의해 도출된 결과와 같은 형식으로 출력하시오

SELECT A.LEV, A.EMP_NAME, B.BOSS, B.DEPT_CODE, B.DEPT_NAME

FROM TEMP A,

         (SELECT LEVEL LEV, LPAD(BOSS_ID, LEVEL*12,' ') BOSS

                    ,DEPT_CODE, DEPT_NAME

          FROM TDEPT

          START WITH DEPT_CODE = '000000'

          CONNECT BY PRIOR DEPT_CODE= PARENT_DEPT

          ) B

WHERE A.EMP_ID(+)=B.BOSS

SALE_HIST의 자료를 이용하여 사업장의 상품 판매금액과 아이템순으로 일자별 판매누적금액을 출력하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_DATE ORDER BY SALE_ITEM

                                          ROWS UNBOUNDED PRECEDING) AS 누적합계

FROM SALE_HIST

SALE_HIST의 자료를 이용하여 사업장의 상품 판매금액과 일자순으로 아이템별 판매누적금액을 출력하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_ITEM ORDER BY SALE_DATE

                                           ROWS UNBOUNDED PRECEDING) AS 누적합계

FROM SALE_HIST

SALE_HIST의 자료를 이용하여 사업장의 상품 판매금액과 일자와 아이템별 판매누적금액을 출력하시오

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_ITEM, SALE_DATE ORDER BY SALE_AMT

                                           ROWS UNBOUNDED PRECEDING) AS 누적합계

FROM SALE_HIST

SALE_HIST의 자료를 이용하여 ‘01 사업장‘ ’PENCIL’ 품목의 아이템별 판매금액과 누적 판매금액을 구하여라

SELECT SALE_DATE, SALE_SITE, SALE_ITEM, SALE_AMT,

          SUM(SALE_AMT) OVER (PARTITION BY SALE_ITEM ORDER BY SALE_DATE

                                           RANGE INTERVAL '5' DAY PRECEDING) AS 누적합계

FROM SALE_HIST

WHERE SALE_ITEM = 'PENCIL'

AND SALE_SITE = '01’

일자별 사업장별 매출액과 사업장별 매출액의 3일 이동평균 금액을 구하라 (SALE_HIST)

SELECT SALE_DATE, SALE_SITE, SUM(SALE_AMT),

          ROUND(AVG(SUM(SALE_AMT)) OVER (PARTITION BY SALE_SITE ORDER BY SALE_DATE

                                                            RANGE INTERVAL '2' DAY PRECEDING) AS 이동평균

FROM SALE_HIST

GROUP BY SALE_DATE, SALE_SITE

EMP테이블에서 직속상관 MGR, 자기이름 ENAME, 자기급여 SAL 출력하여라 단, 같은 MGR별 직속부하들 중 최고 급여를 받는 사람들의 정보를 출력할 것

SELECT MGR, ENAME, SAL

FROM (SELECT MGR, ENAME, SAL,

                    MAX(SAL) OVER (PARTITION BY MGR) AS IV_MAX_SAL

           FROM EMP)

WHERE SAL = IV_MAX_SAL;

A_TB의 자료를 이용하여 매월의 A_OUT과 분기별 누계를 구하시오

SELECT A_MON, A_OUT,

          SUM(A_OUT) OVER (PARTITION BY TO_CHAR(TO_DATE(A_MON,'YYYYMM'),'Q')

                                      ORDER BY TO_DATE(A_MON,'YYYYMM')

                                      RANGE INTERVAL '2' MONTH PRECEDING) AS C1

FROM A_TB

TEST02의 20010901부터 20010911까지의 자료를 이용해 해당 일자 보다 작거나 같은 날의 환율을 순서대로 6개까기지 보여주는 QUERY를 만들어 보자

SELECT CDATE, AMT, CRATE

          , LAG(CRATE,1) OVER (ORDER BY CDATE) AS "-1 DAY"

          , LAG(CRATE,2) OVER (ORDER BY CDATE) AS "-2 DAY"

          , LAG(CRATE,3) OVER (ORDER BY CDATE) AS "-3 DAY"

          , LAG(CRATE,4) OVER (ORDER BY CDATE) AS "-4 DAY"

          , LAG(CRATE,5) OVER (ORDER BY CDATE) AS "-5 DAY"

          , LAG(CRATE,6) OVER (ORDER BY CDATE) AS "-6 DAY"

FROM TEST02

WHERE CDATE BETWEEN '20010901' AND '20010911’

TEST02의 20010901부터 20010911까지의 자료를 이용해 해당 일자 보다 크거나 같은 날의 환율을 순서대로 6개까기지 보여주는 QUERY를 만들어 보자

SELECT CDATE, AMT, CRATE

, LEAD(CRATE,1) OVER (ORDER BY CDATE) AS "+1 DAY"

, LEAD(CRATE,2) OVER (ORDER BY CDATE) AS "+2 DAY"

, LEAD(CRATE,3) OVER (ORDER BY CDATE) AS "+3 DAY"

, LEAD(CRATE,4) OVER (ORDER BY CDATE) AS "+4 DAY"

, LEAD(CRATE,5) OVER (ORDER BY CDATE) AS "+5 DAY"

, LEAD(CRATE,6) OVER (ORDER BY CDATE) AS "+6 DAY"

FROM TEST02

WHERE CDATE BETWEEN '20010901' AND '20010911'

TEST02 테이블을 이용해서 20010905부터 20010910까지 데이터를 출력하되 널값은 제외하고 출력하시오

현재일자의 금액에 전일 환율을 곱한 환산금액을 구하여 현재 일자, 현재 금액, 전일 환율, 환산금액을 출력하여라

SELECT C_DATE, C_AMT, 금일환율, 전일환율, 환산금액

FROM (SELECT CDATE C_DATE, AMT C_AMT, CRATE 금일환율

                    ,LAG(CRATE,1) OVER(ORDER BY CDATE) AS 전일환율

                    ,AMT*LAG(CRATE,1) OVER(ORDER BY CDATE) AS 환산금액

          FROM TEST02

          WHERE CDATE BETWEEN '20010904' AND '20010910'

          )

WHERE 환산금액 IS NOT NULL

TEMP의 EMP_ID를 이용해 자신의 사번과 자신보다 한 단계 빠른 사번을 가진 직원의 사번과 성명을 보여주는 QUERY를 만들어라 ( LAG(), MIN(), MAX() 함수 활용을 생각해보자 )

SELECT EMP_ID, EMP_NAME

          ,LAG(EMP_ID,1) OVER (ORDER BY EMP_ID) AS 이전사원사번

          ,LAG(EMP_NAME,1) OVER (ORDER BY EMP_ID) AS 이전사원이름

FROM TEMP

,MIN(EMP_ID) OVER (ORDER BY EMP_ID

                             ROWS BETWEEN 1 PRECEDING

                                       AND 1 PRECEDING) AS 전사원사번

,MAX(EMP_ID) OVER (ORDER BY EMP_ID

                              ROWS BETWEEN UNBOUNDED PRECEDING

                                        AND 1 PRECEDING) AS 이전사원사번

TEMP의 자료를 이용하여 자신의 사번, 성명, SALARY를 읽고, 자신을 포함한 이후 사번 중 SALARY가 가장 큰 금액을 함께 보여라

SELECT EMP_ID, EMP_NAME, SALARY

          ,MAX(SALARY) OVER (ORDER BY EMP_ID

                                       ROWS BETWEEN CURRENT ROW

                                                 AND UNBOUNDED FOLLOWING ) AS 이후최고금액

FROM TEMP

연습문제

11 오늘이 몇일인지 출력하시오

SELECT TO_CHAR(SYSDATE,‘DD’) FROM DUAL

12 오늘이 무슨요일인지 출력하시오

SELECT TO_CHAR(SYSDATE,‘DAY’) FROM DUAL

13 위의 11번과 12을 합쳐서 출력하시오 결과 예제 -> 03목요일

SELECT TO_CHAR(SYSDATE,‘DD’) || TO_CHAR(SYSDATE,‘DAY’) FROM DUAL

14 요일마다 LOGIN 암호를 자동으로 바꿔주는 프로그램을 만든다고 가정하자 월요일엔 해당일자에 01을 붙여서 4자리를 만들고 화요일엔 11, 수요일엔 21, 목요일엔 31, 금요일엔 41, 토요일엔 51, 일요일엔 61, 붙여서 만들고자한다

SELECT TO_CHAR(SYSDATE,'DD') || DECODE(TO_CHAR(SYSDATE,'DAY'), 'MONDAY', 11, 'TUESDAY', 12,

                              'WEDNESDAY', 13, 'THURSDAY', 14, 'FRIDAY', 15, 'SATURDAY', 16, 'SUNDAY', 17)

FROM DUAL

15 LECTURE 테이블에서 LEC_TIME과 LEC_POINT를 비교해보자 비교한 결과를 이용해 LEC_TIME이 크면 ’실험과목‘ LEC_POINT가 크면 ’기타과목‘, 둘이 같으면 ’일반과목’으로 값을 돌려받고자 한다.

SELECT DECODE(SIGN(LEC_TIME-LEC_POINT), 0, ‘일반과목’, 1, ‘실험과목’, -1, ‘기타과목’) FROM LECTURE

16 강의 시간과 학점이 같거나 강의시간이 학점보다 작으면 ‘일반과목’을 돌려받고 강의시간이 학점보다 큰경우만‘실험과목’을 돌려받고자 한다 두가지 방법 SIGN, LEAST를 사용하자

SELECT LEC_ID, DECODE(LEAST(LEC_TIME,LEC_POINT), LEC_TIME, '일반과목', '실험과목') FROM LECTURE

SELECT LEC_ID, DECODE(SIGN(LEC_TIME-LEC_POINT), 0, '일반과목', -1, '일반과목', 1, '실험과목') FROM LECTURE

17 LECTURE 테이블의 자료중 LEC_TIME과 LEC_POINT가 같다라는 조건을 만족하고 LEC_TIME=3 인 자료는 옆에 ‘중요과목’이라는 비고를 붙이고자한다

SELECT LEC_ID, DECODE(LEC_TIME, LEC_POINT, DECODE(LEC_TIME,3, '중요과목')) FROM LECTURE

18. TEMP의 행을 3개씩 묶어 하나의 번호를 부여하고, 해당 ROWNUM을 3으로 나눈 나머지가 1이면 사번 아니면 NULL, 또 한 번은 1이면 성명 아니면 NULL 이렇게 순차적으로 사번, 성명을 출력하고 이어서 나머지가 2, 0인 경우도 같은 조건으로 출력하여라

SELECT CEIL(ROWNUM/3) C0

,DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL) C1

,DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL) C2

,DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL) C3

,DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL) C4

,DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL) C5

,DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL) C6

FROM TEMP

19 위 예문의 결과를 이용하여 한 행에 사번 성명을 3명씩 보여주는 SQL을 작성하자 (열을 행으로 바꾸는 문제)

SELECT CEIL(ROWNUM/3) C0

, MAX(DECODE(MOD(ROWNUM,3), 1, EMP_ID, NULL)) C1

, MAX(DECODE(MOD(ROWNUM,3), 1, EMP_NAME, NULL)) C2

, MAX(DECODE(MOD(ROWNUM,3), 2, EMP_ID, NULL)) C3

, MAX(DECODE(MOD(ROWNUM,3), 2, EMP_NAME, NULL)) C4

, MAX(DECODE(MOD(ROWNUM,3), 0, EMP_ID, NULL)) C5

, MAX(DECODE(MOD(ROWNUM,3), 0, EMP_NAME, NULL)) C6

FROM TEMP

GROUP BY CEIL(ROWNUM/3)

20 사원 테이블에서 사원을 골라 해당 사원의 연봉이 동일한 직급을 가진 사원의 평균 연봉보다 많은 사원을 출력하라

SELECT A.SALARY, A.EMP_ID, A.EMP_NAME ,B.AVG_SAL

FROM TEMP A ,(SELECT LEV, AVG(SALARY) AVG_SAL

                                  FROM TEMP

                                  GROUP BY LEV ) B

WHERE A.LEV = B.LEV

AND A.SALARY > B.AVG_SAL

1. TEMP와 TCOM에 존재하는 사번의 교집합을 구한후, TEMP에서 TCOM에 존재하는 사번을 제외시킨 차집합을 구하고, 두결과의 합집합을 구해보자

(SELECT EMP_ID FROM TEMP INTERSECT SELECT EMP_ID FROM TCOM) UNION

(SELECT EMP_ID FROM TEMP MINUS SELECT EMP_ID FROM TCOM);

2. TEMP 테이블과 TCOM의 행의 개수를 카테시안 조인으로 구하여라

SELECT COUNT(*) FROM TEMP, TCOM

3. TEMP에서 사번, 성명, 부서코드, 부서명을 출력하시오

SELECT A.EMP_ID, A.EMP_NAME, A.DEPT_CODE, B.DEPT_NAME

FROM TEMP A, TDEPT B

WHERE B.DEPT_CODE = A.DEPT_CODE;

4. TEMP에 존재하는 직원들 중 과장직급을 가질만한 나이에 포함되는 사람이 누구인지 출력하시오

( EMP_LEVE, TEMP )

SELECT B.EMP_ID,B.BIRTH_DATE FROM EMP_LEVEL A, TEMP B

WHERE B.BIRTH_DATE BETWEEN ADD_MONTHS(SYSDATE,-1* TO_AGE*12)

                                                           AND ADD_MONTHS(SYSDATE,-1* FROM_AGE*12)

                                                           AND B.LEV='대리';

5. TEMP와 EMP_LEVE를 이용해 과장 직급의 연봉 상한/하한 범위내에 있는 직원 사번 성명 직급 SALARY를 출력

SELECT A.EMP_ID, A.EMP_NAME, A.LEV, A.SALARY

FROM TEMP A, EMP_LEVEL B

WHERE A.SALARY BETWEEN FROM_SAL AND TO_SAL

AND B.LEV='과장';

6. 사번, 이름 SALARY, 연봉 상한 금액을 보고자 한다. TEMP와 EMP_LEVEL을 조인하여 결과를 보여주되 연봉의 상하한이 등록되어있지않은 수습사원은 사번, 이름 SALARY까지만 출력하는 QUERY를 구성해보자

SELECT A.EMP_ID, A.EMP_NAME, A.SALARY, B.TO_SAL

FROM TEMP A, EMP_LEVEL B

WHERE B.LEV(+)=A.LEV;

7. TEMP의 자료를 이용해 NON-EQUI JOIN이면서 SELF JOIN이고 QUTER조인인 QUREY를 하나 만들어 보자

사번, 성명, 생일, 자신보다 생일이 빠른 사람의 수를 읽어와 자신보다 생일이 빠른 사람의 수로 정렬하여 출력

SELECT A.EMP_ID, A.EMP_NAME,A.BIRTH_DATE, COUNT(B.BIRTH_DATE)

FROM TEMP A, TEMP B

WHERE B.BIRTH_DATE(+)<A.BIRTH_DATE

GROUP BY A.EMP_ID, A.EMP_NAME, A.BIRTH_DATE

ORDER BY COUNT(B.BIRTH_DATE)

8. TEMP에 속한 수습사원만 순번을 붙여 출력하시오. 단 5번까지만 출력하시오

SELECT ROWNUM, EMP_ID, EMP_NAME

FROM TEMP

WHERE EMP_ID > 0 AND LEV=’수습‘ AND ROWNUM <= 5

9. TEMP 테이블의 자료를 이용하여 SELECT 결과를 3개행씩 묶어 하나의 번호를 부여하는 SQL을 만들어보자

SELECT ROWNUM, CEIL(ROWNUM/3), EMP_ID, EMP_NAME FROM TEMP WHERE EMP_ID>0

10. 강의 ID와 주당 강의 시간과 학점이 같으면 일반으로 표시하고 아니면 특별로 출력하시오

SELECT LEC_ID, DECODE(LEC_TIME, LEC_POINT,’일반‘,’특별’) FROM LECTURE

DECODE의 기본적인 기능은 프로그래밍 언어의 IF 문을 SQL 문장으로 사용하기 위하여 만들어진 오라클이다

FROM 절만 빼고 어디서나 사용할 수 있고 IF문의 전형적인 형태를 분류하여 DECODE문으로 나누어 사용하자

WINDOW 함수

WINDOW 함수는 AVG, COUNT, SUM, MAX, MIN만 사용 가능

윈도우 함수( ) OVER (

             PARTITION BY 절                              PARTITION BY 절에 명시된 그룹을 좀 더 세부적으로 그룹핑 가능

             ORDER BY 절 [ASC|DESC]             

             ROWS | RANGE

             BETWEEN UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW

             AND UNBOUNDED FOLLOWING | n FOLLOWING | CURRENT ROW

OVER - 쿼리 RESULT SET을 이용해 동작하는 함수라는 구분

PARTITION BY - RESULT SET을 VALUE EXPRESS 에 지정된 값에 근거하여 분활하는 역할 수행

( ) - 안에 기술된 ORDER BY는 RESULT SET에 순위를 부여할 때 정렬기준을 표시하는 부분

ORDER BY – 각 PARTITION 내에서 DATA 각 어떤 값을 기준으로 정렬될 것인가를 지정

[NULLIS FIRST | NULLS LAST] - NULL이 포함된 ROW가 순서상 제일 앞이나 제일 뒤에 위치할 것인지 지정

ROWS | RANGE - 자료의 물리적 순서를 이용(ROWS)할 것인지 논리적 순서(RANGE)를 이용할 것인지 결정

BETWEEN ~ AND ~ - 자료의 범위를 결정한다.

- UNBOUNDED PRECEDING PARTITION의 첫 번째 로우에서 윈도우가 시작한다.

- UNBOUNDED FOLLOWING PARTITION의 마지막 로우에서 윈도우가 시작한다

- CURRENT ROW 현재 ROW를 시작 값

- ROWS 1 PRECEDING 1행 이전 행을 말한다

- ROWS 2 FOLLOWING 2행 이후 행을 말한다

- RANGE는 INTERVAL을 꼭 붙여서 숫자 ‘ ’ 안에 표시해야 한다

- RANGE INTERVAL ‘2’ DAY PRECEDING 3일 이동 합계

윈도우함수의 종류

FIRST_VALUE() - 윈도우 함수의 정렬이 끝이나고 제일 앞에 위치하는 ROW의 값들을 읽어올 때 사용.

LAST_VALUE() - 윈도우 함수의 정렬이 끝이나고 제일 뒤에 위치하는 ROW의 값들을 읽어올 때 사용.

RATIO_TO_REPORT - 함수가 전체대비 해당 ROW의 값이 차지하는 비율을 구해주고 있다.

LAG(컬럼, 원하는 행) - 해당 행의 앞에 몇 번째 행을 참조할 것인지 지정 가능

LEAD(컬럼, 원하는 행) - 해당 행의 뒤에 몇 번째 행을 참조할 것인지 지정 가능

분석 함수

RANK() OVER( [PARTITION BY <VALUE EXPRESS>]

                              ORDER BY <VALUE EXPRESS> [ASC | DESC]

                              [NULLIS FIRST | NULLS LAST]

                            )

활용 예문

SELECT 사원이름, 사원번호, 평균급여, 부서번호 같은 등수 7등이 2명 있다면

RANK() OVER (PARTITION BY 부서번호 DENS_RANK()는 8등이 되며

                              ORDER BY 평균급여 ASC) AS 순위 RANK()는 9등이 된다

FROM 사원테이블

GROUP BY DEPT_CODE, EMP_ID, EMP_NAME;

OVER – 해당함수가 QUERY의 RESULT SET을 이용하여 동작하는 함수라는 표시

( ) – 안에 기술된 ORDER BY는 RESULT SET에 순위를 부여할 때 정렬기준을 표시 해주는 부분

ORDER BY – 각 PARTITION 내에서 DATA 각 어떤 값을 기준으로 정렬될 것인가를 지정

[NULLIS FIRST | NULLS LAST] – NULL이 포함된 ROW가 순서상 제일 앞이나 제일 뒤에 위치할 것인지 지정

PARTITION BY의 기능

전체 SELECT 문에서 GROUP BY ROLLUP으로 특정 칼럼을 ID, 부서번호를 부서별의 소계와 ID별 소계가 출력이된다

그렇게 출력된 소계들을 다시 순위를 줄수 있는데

그 방법은 RANK() OVER (PARTITION BY 에 GROUPING 으로 ID 와 부서번호를 각 각 묶어주면 소계를 따로 묶어서 출력할 수 있다

또 한, SELECT 문에서는 모든 데이터가 RESULT SET이 일어나고 그 값으로 PARTITION으로 분류한다

MAX(점수), AVG(점수)등을 이용하고 OVER (PARTITION BY 이름)으로 묶는다면 개인별 최고 점수가 출력된다

쉽게말해 구분마다 GROUP BY 하여 컬럼에 값을 담는다라고 생각하면된다

ROLLUP과 CUBE 설명은 다음에 다시 하겟다

CUME_DIST - 관련 DATSET안에서 해당 ROW의 위치를 0에서 1까지의 값으로 표시 해준다.

PERCENT_RANK - 순위가 0에서 1까지의 값으로 지정되지만 소수점도 같이 지정된다

NTILE(N) - N이라는 숫자를 지정하면 그 숫자만큼 순위를 지정할 수 있다

                      예로 10명의 사원에서 N을 2로 지정했다면 1 다섯개와 2 다섯 개가 지정된다

ROW_NUMBER() - PARTITION 내의 ROW들에 순서대로 UNIQUE한 일련번호를 부여한다

학습 목표

– 시퀀스 활용과 DDL 활용방법을 숙지한다

단 서브쿼리의 숙지를 위해 서브쿼리만 사용하도록하자

그리고 DCL의 숙지를 위해 테이블을 추가 수정 삭제 시 오류 문으로 인해 잘못된 정보를 저장하지

않도록 테이블 추가 삭제 수정시 SELECT문을 활용하여 사전 데이터를 확인하여 작업하자

그리고 ROLLBACK과 COMMIT을 활용하여 잘못된 정보 저장을 방지하자

DDL 문제 -

테이블을 구조와 정보를 복사하는 방법

1. EMP 테이블과 같은 EMP_TEMP 테이블 생성하시오

CREATE TABLE EMP_TEMP AS SELECT * FROM EMP;

2. DEPT 테이블과 같은 DEPT_TEMP 테이블을 생성하시오

CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT;

3. SALGRADE 테이블과 같은 SALGRADE_TEMP 테이블을 생성하시오

CREATE TABLE SALGRADE_TEMP AS SELECT * FROM SALGRADE;

4. 교육생의 TRAINEES 테이블을 만드시오

IDX(숫자 4자리이상), NAME(문자형 5자리이상), TEL(문자형 13자리이상),

CLASS (문자형 10자리이상) ROOM(숫자 3자리이상), INDATE(날짜형),

GENDER(남자 또는 여자 둘 중 하나 지정)

아이디 넘버를 PRIMARY KEY로 설정하고 이름은 필수입력값으로 지정한다

제약조건은 필히 ALTER 사용

ALTER TABLE TRAINEES ADD CONSTRAINT TRAINEES_CK_GENDER CHECK (GENDER IN('남','여'));

5. 교육생을 추가하시오 ( 교육생 정보는 아래와 같다 )

일반방법으로 추가 한다  1, '이순신', '02-3333-5555', '자바개발자반', 401 2019-06-05, 남

INSERT INTO COUNTRIES(IDX, NAME, TEL, CLASS, ROOM, INDATE)

VALUES(1,'이순신', '02-3333-5555', '자바개발자반', 401 2019-06-05, 남);

두 번째는 ( 아이디 번호를 작성할 때 MAX 활용법과 시퀀스 두 가지 모두 사용하자 )(dual 활용방법도 생각할 수 있다)

2, '유관순', '010-9999-0231', '빅데이터반', 402, SYSDATE, 여

INSERT INTO TRAINEES(IDX, NAME, TEL, CLASS, ROOM, INDATE)

VALUES( (SELECT NVL(MAX(REGION_ID)+1,0)

              FROM TRAINEES), 

              '유관순', '010-9999-0231', '빅데이터반', 402, SYSDATE, 여);

CREATE SEQUENCE seq_trainees_idx

START WITH 2

INCREMENT BY 1

INSERT INTO TRAINEES VALUES(SEQ_TRAINEES_IDX.NEXTVAL,

                                                                    '유관순', '010-9999-0231', '빅데이터반', 402, SYSDATE, '여자')

6. 사내 확장으로 부서가 증가했다. ORACL, SQL, JAVA, JSP 각 순서대로 부서번호를 50, 60, 70, 80번을 부여하고 지역 또 한, BUSAN, ILSAN, INCHON, BUNDANG 추가하시오

INSERT INTO CHAP10HW_DEPT (DEPTNO, DNAME, LOC) VALUES (50, 'ORACLE', 'BUSAN');

INSERT INTO CHAP10HW_DEPT (DEPTNO, DNAME, LOC) VALUES (60, 'SQL', 'ILSAN');

INSERT INTO CHAP10HW_DEPT (DEPTNO, DNAME, LOC) VALUES (70, 'SELECT', 'INCHEON');

INSERT INTO CHAP10HW_DEPT (DEPTNO, DNAME, LOC) VALUES (80, 'DML', 'BUNDANG');

테이블 구조만 복사하는 방법

7. EMP 테이블의 구조만 같은 RETIRE 테이블을 새로 생성하시오

CREATE TABLE RETIRE AS SELECT * FROM EMP WHERE 1 <> 1;

8. EMP_TEMP 부서별 최대 급여를 받는 사원의 모든 정보를 RETIRE 테이블로 이동하시오

(EMP_TEMP 테이블을 호출하여 칼럼을 클릭하면 해당 칼럼 이름을 출력할 수 있다 활용할 것)

INSERT INTO RETIRE (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SELECT * FROM EMP_TEMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)

                                                                        FROM EMP_TEMP

                                                                        GROUP BY DEPTNO)

9. 30번 부서가 매출 부진으로 인해 부서이동을 실시한다 30번 내에서 급여등급 2인 사원만 20번 부서로 이동하라

UPDATE EMP_TEMP SET DEPTNO = 20

WHERE ENAME IN ( SELECT ENAME

                           FROM EMP_TEMP E, SALGRADE S

                           WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

                           AND DEPTNO = 30

                           AND S.GRADE = 2)

10. EMP_TEMP테이블에 8명의 신입사원을 추가하여라 (아래는 신입사원의 정보이다)

7201, 'TEST_USER1', 'MANAGER', 7788, 2016-01-02, 4500, NULL, 50

7202, 'TEST_USER2', 'CLERK', 7201, 2016-02-21 1800, NULL, 50

7203, 'TEST_USER3', 'ANALYST', 7201, 2016-04-11, 3400, NULL, 60

7204, 'TEST_USER4', 'SALESMAN', 7201, 2016-05-31, 2700, 300, 60

7205, 'TEST_USER5', 'CLERK', 7201, 2016-07-20, 2600, NULL, 70

7206, 'TEST_USER6', 'CLERK', 7201, 2016-09-08, 2600, NULL, 70

7207, 'TEST_USER7', 'LECTURER', 7201, 2016-10-28, 2300, NULL, 80

7208, 'TEST_USER8', 'STUDENT', 7201, 2018-03-09, 1200, NULL, 80

11. DEPT_TEMP 40번 부서기준으로 30번 부서와 통합하려한다 ( 이름과 지역을 동일시하자 )

UPDATE DEPT_TEMP SET (DNAME, LOC, deptno) = (SELECT DNAME, LOC, deptno

                                                                     FROM DEPT_TEMP

                                                                     WHERE DEPTNO = 30)

WHERE DEPTNO = 40;

그리고 EMP_TEMP 테이블과 DEPT_TEMP테이블의 변경된 정보를 확인하여라

12. EMP_TEMP에 속한 사원 중 50번 부서에서 근무하는 사원들의 평균 급여보다 많은 급여를 받고 있는 사원들을70번 부서로 옮기는 SQL문을 작성하세요

UPDATE EMP_TEMP SET DEPTNO = 70

WHERE SAL > (SELECT AVG(SAL)

                     FROM EMP

                     WHERE DEPTNO = 50);

13. EMP_TEMP에 속한 사원중, 60번 부서의 사원중에 입사일이 가장 빠른 사원보다

늦게 입사한 사원의 급여를 10% 인상하고 80번 부서로 옮기는 SQL문을 작성하시오

UPDATE EMP_TEMP SET SAL = SAL*1.1, DEPTNO = 80

WHERE HIREDATE > (SELECT MIN(HIREDATE)

                             FROM EMP_TEMP

                             WHERE DEPTNO = 60);

14. EMP_TEMP에 속한 사원 중, 급여 등급이 5인 사원을 삭제하는 SQL문을 작성하세요

DELETE FROM EMP_TEMP

WHERE EMPNO IN (SELECT E.EMPNO

                           FROM EMP_TEMP E, SALGRADE_TEMP S

                           WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

                           AND S.GRADE = 5);

15. EMP_TEMP에서 부서별 제일 오래 근무한 사원을 찾으세요.

SELECT ename FROM EMP_TEMP

WHERE (hiredate, deptno) IN (SELECT min(hiredate), deptno

                                        FROM EMP_TEMP

                                         GROUP BY deptno)

16. 각 부서의 급여 최소가 900이상 최대가 10000이하인 부서의 사원들 중 1500이상의 급여를 받는

사원들의 평균 급여액을 출력하라

SELECT avg(sal) FROM emp

WHERE sal >= 1500

GROUP BY deptno

HAVING min(sal) >= 900 AND max(sal) <= 10000;

17. 급여 등급이 4등급인 사원들의 사원번호, 이름, 급여, 근무 부서이름, 근무지역을 가져온다.

SELECT e.empno

FROM emp e, detp d, salgrade s

WHERE e.deptno = d.deptno AND E.SAL BETWEEN S.LOSAL AND S.HISAL

AND S.GRADE = 4

18. SMITH 사원의 직속상관과 동일한 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.

e1 : SMITH의 정보

e2 : SMITH의 직속상관 정보

e3 : 직속상관과 동일한 직무를 가지고 있는 사원들의 정보

SELECT empno, ename, job FROM EMP

WHERE job = (SELECT job

                    FROM EMP

                     WHERE empno = (SELECT mgr

                                               FROM EMP

                                               WHERE ename='SMITH')

                     )

SELECT e3.empno, e3.ename, e3.job FROM EMP e1, EMP e2, EMP e3

WHERE e1.mgr = e2.empno AND e2.job = e3.job

AND e1.ename = 'SMITH';

CREATE TABLE PLAYER (
       PLAYER_ID                CHAR(7)             NOT NULL,
       PLAYER_NAME        VARCHAR2(20)  NOT NULL,
       TEAM_ID                   CHAR(3)             NOT NULL,
       E_PLAYER_NAME   VARCHAR2(40),
       NICKNAME               VARCHAR2(30),
       JOIN_YYYY               CHAR(4),  
       POSITION                  VARCHAR2(10),
       BACK_NO                 NUMBER(2),
       NATION                     VARCHAR2(20),
       BIRTH_DATE            DATE,
       SOLAR                      CHAR(1),
       HEIGHT                    NUMBER(3),

      CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),
      CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
    
);

UNIQUE 조건

데이터의 유일성을 보장(중복되는 데이터가 존재할 수 없다)되고, 자동으로 인덱스가 생성된다..

-- deptno 컬럼에 UNIQUE 제약조건 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_uk_deptno UNIQUE (deptno);

-- 제약 조건의 삭제

SQL> ALTER TABLE emp2 DROP CONSTRAINT emp2_uk_deptno;

CHECK 조건

컬럼의 값을 어떤 특정 범위로 제한할 수 있다.

-- comm 컬럼에 1에서 100까지의 값만을 가질수 있는 체크조건 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_ck_comm CHECK (comm >= 1 AND comm <= 100);

-- 제약 조건의 삭제

SQL> ALTER TABLE emp2 DROP CONSTRAINT emp2_ck_comm;

-- 10000,20000,30000,40000,50000의 값만을 가질수 있는 체크조건 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_ck_comm CHECK comm IN (10000,20000,30000,40000,50000);

DEFAULT(컬럼 기본값) 지정

데이터를 입력하지 않아도 지정된 값이 기본으로 입력 된다.

-- hiredate 컬럼에 값을 입력하지 않아도 오늘 날짜가 입력된다.

SQL> CREATE TABLE emp4(

... (컬럼생략) ...,

hiredate DATE DEFAULT SYSDATE );

PRIMARY KEY 지정

- 기본키는 UNIQUE 와 NOT NULL의 결합과 같다.

- 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다. 이를 참조 무결성이라 한다.

- UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며, 그 이름은 기본 키 제약 조건의 이름과 같다.

- INDEX: 검색 키로서 검색 속도를 향상 시킨다.(UNIQUE,PRIMARY KEY 생성시 자동적으로 생긴다.)

-- PRIMARY KEY 생성 예제

SQL> CREATE TABLE emp5( empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY );

-- ALTER TABLE 명령어로 PRIMARY KEY 생성 예제

ALTER TABLE emp2 ADD CONSTRAINT emp2_pk_empno PRIMARY KEY (empno) ;

FOREIGN KEY(외래 키)지정

- 기본키를 참조하는 컬럼 또는 컬럼들의 집합이다.

- 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터형과 일치해야 한다. 이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없다.

- 외래키에 의해 참조되고 있는 기본 키는 삭제 할 수 없다.

- ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제 될 때 같이 삭제 된다.

-- emp 테이블의 deptno 컬럼이 dept 테이블에 deptno 컬럼을 참조하는 외래키 생성

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno);

시퀀스란?

• 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.
• 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
• 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.
• 메모리에 Cache되었을 때 시퀀스값의 액세스 효율이 증가한다.
• 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

시퀀스 생성 Syntax

CREATE SEQUENCE sequence_name

• [START WITH n] : 시퀀스의 시작 값을 지정한다. n을 1로 지정하면 1부처 순차적으로 시퀀스번호가 증가한다
• [INCREMENT BY n] : 시퀀스의 증가 값을 지정한다. n을 2로 하면 2씩 증가한다. START WITH를 1로, INCREMENT BY를 2로 설정하면 1, 3, 5, 7,.. 이렇게 시퀀스 번호가 증가한다.
• [MAXVALUE n | NOMAXVALUE] : 시퀀스 최대값 | 최대값 없음
• [MINVALUE n | NOMINVALUE] : 시퀀스 최소값 | 최소값 없음
• [CYCLE | NOCYCLE] : 최대값 도달시 순환 여부
• [CACHE | NOCACHE] : CACHE 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache에 상주시킨다.

1. 증감 숫자가 최대 값에 도달 하면 생성을 중단 할 지 ( NOCYCLE )
2. 처음 부터 다시 생성 할지 ( CYCLE )
3. 메모리에 시퀀스 값을 미리 할당 ( CACHE )
4. 혹은 할당 하지 않음 ( NOCACHE )

시퀀스 수정

START WITH 값을 제외하고 ALTER SEQUENCE 명령어로 수정 할 수 있다.

시퀀스 수정 Syntax

ALTER SEQUENCE sequence_name

[INCREMENT BY n]

[MAXVALUE n | NOMAXVALUE]

[MINVALUE n | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE | NOCACHE]

시퀀스의 사용예

8000부터 시작하는 empno를 자동 증가 시퀀스를 만들어 보자

CREATE SEQUENCE seq_empno

START WITH 8000

INCREMENT BY 1

CACHE 20;

시퀀스가 정상적으로 생성되었는지 조회해 보자

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스를 이용해서 데이터를 등록해 보자

INSERT 시 시퀀스 사용

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

              VALUES(seq_empno.NEXTVAL,'TIGER', 'MANAGER', 7839, SYSDATE, 3000, null, 20);

-- SELECT 절에서 시퀀스 사용

CREATE TABLE emp2

AS

SELECT seq_empno.NEXTVAL as empno, ename,

job, mgr, hiredate, sal, comm, deptno

FROM emp;

-- 시퀀스가 정상적으로 반영되었는지 조회해 보자

SELECT * FROM emp;

시퀀스 수정

-- seq_empno 시퀀스의 증과값과 Cache 사이즈를 변경하는 예이다.

ALTER SEQUENCE seq_empno

INCREMENT BY 10

CACHE 40;

-- 시퀀스가 정상적으로 변경되었는지 조회해 보자

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.NEXTVAL FROM DUAL;

SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스 삭제

DROP SEQUENCE 명령으로 시퀀스를 삭제 할 수 있다.

-- seq_empno 시퀀스를 삭제한다.

DROP SEQUENCE seq_empno;

인덱스

인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.

• 자동 인덱스 : 프라이머리 키 또는 UINQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스.
• 수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스

인덱스를 생성하는 것이 좋은 컬럼

• WHERE절이나 join조건 안에서 자주 사용되는 컬럼
• null 값이 많이 포함되어 있는 컬럼
• WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들 다음과 같은 경우에는 인덱스 생성이 불 필요 하다.
• (테이블이 작을 때, 테이블이 자주 갱신될 때)

오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있다.

B-tree 인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 낸다.

이 알고리즘 원리는 주어진 값을 리스트의 중간점에 있는 값과 비교한다.

만약 그 값이 더 크면 리스트의 아래쪽 반을 버린다. 만약 그 값이 더 작다면 위쪽 반을 버린다.

하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복한다.

비트맵 인덱스

• 비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 사용하는 것이 좋다.(ex 남,여의 값을 가지는 성별 컬럼)
• 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킨다.
• 테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있다.
• SQL> CREATE BITMAP INDEX emp_deptno_indx ON emp(deptno);

UNIQUE 인덱스

• UNIQUE 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있다.
• 프라이머리키 와 UNIQUE 제약 조건시 생성되는 인덱스는 UNIQUE 인덱스이다.
• SQL> CREATE UNIQUE INDEX emp_ename_indx ON emp(ename);

NON-UNIQUE 인덱스

• NON-UNIQUE 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있다.
• SQL> CREATE INDEX dept_dname_indx ON dept(dname);

결합 인덱스

• 복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개 이다
• SQL> CREATE UNIQUE INDEX emp_empno_ename_indx ON emp(empno, ename);

인덱스의 삭제

• 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않는다.
• 인덱스를 삭제하려면 인덱스의 소유자 이거나 DROP ANY INDEX권한을 가지고 있어야 한다.
• 인덱스는 ALTER를 할 수 없다.
• SQL> DROP INDEX emp_empno_ename_indx;

1. 인덱스 컬럼을 변형하여 비교할 때

• BAD - WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
• NOT BAD - WHERE HIREDATE = TO_DATE('19980518')

비교하는 인덱스 컬럼의 형이나 값을 변경하면 발생합니다.이 경우에는 비교값을 변경해 주어야 인덱스를 사용하게 됩니다.

인덱스를 HIREDATE로 했을 때 인덱스를 타기위해서는 INDEX를 생성한것에 변형을 주어서는 안된다.

위 처럼 결과도 빠르게 나온다 그러나 중요한 점이 있다 결과가 같지 않을 수도 있다는 것이다

날짜 타입은 날짜와 시분초의 정보도 가지고 있다. 따라서 TO_DATE(‘19980518’)라는 말은 정확히 1998년5월18일 0시0분0초라는뜻이다.

그래서 우리가 원하는 1998년5월18일자와는 차이가 있다.

따라서 1998년5월18일 0시0분1초 ~ 23시59분59초까지의 데이터는 나오지 않게되는것이다.

이것은 튜닝할 때 유의할 점이다. 결과를 같게 유지해야하는것이다. 이 상황을 알고있다면 방법은 간단하다.

아래아 같이 고치면 빠른시간에 원하는 결과를 얻을 수 있을 것이다.

• VERY GOOD
• WHERE HIREDATE BETWEEN TO_DATE('19980518'||'00:00:00','YYYYMMDD HH24:MI:SS') AND TO_DATE('19980518'||'23:59:59','YYYYMMDD HH24:MI:SS')

• BAD - WHERE SALARY + 1000 > 100000;
• GOOD - WHERE SALARY > 100000 - 1000;

함수의한 변형이 아닌 간단한 연산에의한 변형의 경우도 마찬가지이다.

2. 비교 대상의 형이 달라서 내부적으로 형변환을 하는 경우

• BAD - WHERE EMP_ID = 200383;
• GOOD - WHERE EMP_ID = ‘200383’;

EMP_ID가 varchar라고 할 경우에 비교값이 숫자인 경우에 DB에서 자동으로 이를 숫자로 변경하고 비교하게 됩니다.

이 경우에 인덱스 컬럼에 변형이 일어났기 때문에 인덱스를 사용하지 못하게 됩니다.

3. NULL을 비교하는 경우

• BAD - WHERE JOB IS NULL;

일반적으로 Oracle을 기준으로 NULL은 인덱스 대상이 아니라고 합니다.

따라서, 이를 해결하기 위해서는 NULL을 쓰지 말고 다른 정해진 값을 이용해서 비교해야 합니다. 

따라서 위와 같은 경우 반드시 index를 타려거든

job컬럼을 NOT NULL로 설정하고 NULL대신 특정값 (예를 들면 : ‘NOT ASSIGN’ ) 으로 설정하고 

QUERY를 아래와 같이 수정한다면 인덱스를 탈수 있을 것이다.

• GOOD 
• SELECT LAST_NAME,FIRST_NAME FROM EMPLOYEES WHERE JOB = ‘NOT ASSIGN’;

4. 부정형 조건인 경우

• BAD - WHERE JOB NOT IN ( 'INSTRUCTOR', 'STAFF');

부정형 역시 인덱스를 사용하지 못하는 대표적인 조건 쿼리 입니다.

일반적인 INDEX가 =이나 <, > , BETWEEN조건에 만 인덱스를 탈수 있고 부정형으로 비교했을때는 인덱스를 탈수 없기때문이다

아닌 놈을 찾으려면 전체를 뒤지는 수 밖에요. 이를 피하기 위한 근본적인 DB 모델링이 중요합니다.

1. '정남일' 선수가 소속된 팀의 선수들에 대한 정보를 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

FROM PLAYER

WHERE TEAM_ID = (SELECT TEAM_ID

                                                                        FROM PLAYER

                                                                        WHERE PLAYER_NAME = '정남일')

2. 선수들 중에서 키가 평균 이하인 선수들의 정보를 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

FROM PLAYER

WHERE HEIGHT <= (SELECT AVG(HEIGHT)

                                                                        FROM PLAYER) ORDER BY PLAYER_NAME;

3. 각 부서별 급여 평균보다 더 많이 받는 사원의 사원번호, 이름, 급여를 출력하세요.

SELECT empno,ename,sal

FROM EMP

WHERE sal > all (SELECT AVG(sal)

                                  FROM EMP

                                  GROUP BY deptno)

4. CHICAGO 지역에 근무하는 사원들 중 BLAKE이 직속상관인 사원들의 사원번호, 이름, 직무를 출력하세요.

SELECT e.empno, e.ename, e.job

FROM EMP e, DEPT d

WHERE e.DEPTNO = d.DEPTNO

AND e.mgr = (SELECT empno

                            FROM EMP

                            WHERE ename = 'BLAKE')

                            AND d.loc = 'CHICAGO';

5. 각 부서별로 최고급여를 받는 직원의 Dept_id, ID, Last_name, Salary를 출력하시오.

SQL> SELECT dept_id, id, last_name, salary

FROM s_emp

WHERE (dept_id,salary) IN (SELECT dept_id, max(salary)

                                                          FROM s_emp

                                                          GROUP BY dept_id);

6. '20120501' 부터 '20120502' 사이에 경기가 있는 경기장을 출력하시오

SELECT STADIUM_ID ID, STADIUM_NAME 경기장명

FROM STADIUM A

WHERE EXISTS (SELECT 1

                                   FROM SCHEDULE X

                                   WHERE X.STADIUM_ID = A.STADIUM_ID

                                   AND X.SCHE_DATE BETWEEN '20120501' AND '20120502')

7. 소속팀별 키가 가장 작은 사람들의 정보를 출력하시오

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER

WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT)

                                                                 FROM PLAYER

                                                                 GROUP BY TEAM_ID)

ORDER BY TEAM_ID, PLAYER_NAME;

8. 각 부서별로 최고급여를 받는 직원의 Dept_id, ID, Last_name, Salary를 출력하시오.

SELECT dept_id, id, last_name, salary

FROM s_emp

WHERE (dept_id,salary) IN (SELECT dept_id, max(salary)

                                                           FROM s_emp

                                                           GROUP BY dept_id);

9. K-리그 선수들 중에서 포지션이 미드필더(MF)인 선수들의 소속팀명 및 선수 정보를 출력하시오

SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명, P.BACK_NO 백넘버

FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO

               FROM PLAYER

               WHERE POSITION = 'MF') P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID ORDER BY 선수명;

10. K리그 선수 중 가장 키가 큰 선수 5명을 출력하시오

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT

                FROM PLAYER

                WHERE HEIGHT IS NOT NULL

                ORDER BY HEIGHT DESC)

WHERE ROWNUM <= 5;

11. 최근 입사한 5명의 사원을 출력하시오

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM (SELECT *

               FROM EMP

               ORDER BY HIREDATE DESC)

WHERE ROWNUM <= 5;

12. 사원의 급여를 내림차순으로 모든 정보를 출력하시오

SELECT * FROM EMP ORDER BY SAL DESC

13. 사원이름과 급여를 최고 높은 순으로 정렬하여 출력하고 순위를 표시하는 컬럼을 생성하여 출력하시오

SELECT ROWNUM RNUM, ENAME, SAL

FROM (SELECT * FROM EMP ORDER BY SAL DESC)

14. 급여를 많이 받는 6 ~ 10번째 사원을 출력

SELECT RNUM, ENAME, SAL

FROM (SELECT ROWNUM RNUM, ENAME, SAL             -- FROM 절의 INLINE VIEW ROWNUM에 대해 별칭 부여

                FROM (SELECT *

                                  FROM EMP

                                  ORDER BY SAL DESC))

WHERE RNUM BETWEEN 6 AND 10;

15. 삼성블루윙즈팀(K02)의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 출력하시오

SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID

GROUP BY P.TEAM_ID, T.TEAM_NAME

HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT)

                                                        FROM PLAYER WHERE TEAM_ID ='K02')

16. 사원이 속해있는 부서번호와 부서이름을 출력하시오, 단, 중복 값은 허용하지 않는다

위의 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우

추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 체크해야 한다.

두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 DISTINCT로 중복 제거를 한다.

SELECT DISTINCT d.deptno, d.dname

FROM dept d, emp e

WHERE d.deptno = e.deptno;

-- EXISTS를 사용하는 Subquery로 변경

-- 추출하고자 하는 테이블인 dept d을 FROM절에 놓고 emp테이블은 체크만 하기위해 EXISTS절에 위치시켰으며

이로 인해 수행속도가 대폭 감소하게 된다.

SELECT d.deptno, d.dname

FROM dept d

WHERE EXISTS (SELECT 1

                                  FROM emp e

                                  WHERE e.deptno = d.deptno);

테이블은 아래에 링크에 파일첨부가 되어있다https://tantangerine.tistory.com/17?category=379409

비교 연산자 & 논리연산자 활용 & SQL 연산자

1. 소속팀이 삼성블루윙즈팀인 선수들을 출력 하시오

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE TEAM_ID = 'K02'

2. 포지션이 미드필더인 선수들을 출력하시오

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE POSITION = 'MF'

3. 키가 170이상인 선수들을 출력하시오

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE HEIGHT >= 170

4. 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들을 출력하시오

SELECT * FROM PLAYER WHERE TEAM_ID IN ('K02','K07');

TEAM_ID ='K02' or K07'

5. 소속이 삼성블루윙즈이거나 전남드래곤즈이며,

포지션이 미드필더이면서 현재 키가 170이상 180이하인 선수들을 출력하시오

* SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07'

AND POSITION = 'MF' AND HEIGHT >= 170 AND HEIGHT <= 180;

* SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM PLAYER WHERE (TEAM_ID = 'K02' OR TEAM_ID = 'K07')

AND POSITION = 'MF' AND HEIGHT >= 170 AND HEIGHT <= 180 ( BETWEEN ~AND ~ 사용 가능 )

6. 소속이 삼성블루윙즈이거나 전남드래곤즈이며,

포지션이 미드필더가 아니면서 현재 키가 170이상 180이하인 선수들을 출력하

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM

PLAYER WHERE TEAM_ID = 'K02' AND POSITION <> 'MF' AND HEIGHT BETWEEN 170 AND 180;

7. JOB이 MANGER이면서 20번 부서에 속하거나

JOB이 CLERK이면서 30번 부서에 속하는 사원의 정보를 출력하여라

///SELECT ENAME, JOB, DEPTNO FROM EMP WHERE JOB IN ('MANAGER','CLERK') AND DEPTNO IN (20,30);

SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (JOB, DEPTNO) IN (('MANAGER',20),('CLERK',30));

8, 부서 번호가 20번이면서 급여가 1000이하 받는 사원과 전체 사원 중 급여 4000이상을 받는 사원을 출력하시오

SELECT * FROM emp WHERE (deptno= 20 AND sal< 1000) OR sal> 4000;

9. 부서번호가 20이번 이면서 급여가 1000이하이거나 4000이상인 사원을 출력 하시오

SELECT * FROM emp WHERE deptno= 20 AND (sal<= 1000 OR sal>= 4000);

칼럼 별칭 : (Alias) 12 * (salary + 100) AS ANNUAL_SALARY 12 * (salary + 100) “Annual Salary”

리터럴 문자 : 임의의 문자열 값을 의미

SQL> SELECT ‘Korea Fighting’ FROM emp; (14 번 출력)

SQL> SELECT ‘Korea Fighting’ FROM dual; (1 번 출력)

산술연산자 : (),*,/,+,-의 우선순위를 가진다. 새로운 의미 부여한 것이므로 적절한 ALIAS를 부여하는 것이 좋다

합성연산자 : 컬럼과 컬럼, 또는 컬럼과 다른 값을 연결, 합성연산의 결과로 새로운 문자값을 생성한다

#SQL> SELECT ename||ename AS FULLNAME FROM emp;

DISTINCT : SELECT 절에 나열된 값들 중 중복 값 제거 옵션 (중복 행 표시, 중복 행 제거, 다중 컬럼에 대한 중복 행 제거)

SELECT DISTINCT deptno FROM emp;

 SELECT DISTINCT job, deptno FROM emp; 두 예문은 결과 값에 대해 생각하기

연산자

- 비교연산자 - 문자형 타입은 인용부호(작은따옴표, 큰따옴표)로 비교처, 숫자형은 제외

SELECT ename, job, salFROM empWHERE job = ‘CLERK’ AND sal>= 1000;

SQL 비교 연산자

- BETWEEN ~ AND ~

SELECT * FROM employee WHERE salary BETWEEN 1000 AND 1500;

- IN (list) : 리스트에 있는 값 중에서 어느 하나라도 일치하면 된다

SELECT ename, deptno FROM emp WHERE deptno IN (10,20);

SELECT ename, deptno FROM emp WHERE deptno = 10 OR deptno = 20;

- LIKE : WHERE절에 사용한다

‘%‘ - 0개 이상의 어떤 문자를 의미한다, ‘_’ - 1개 단일 문자를 의미한다

- IS NULL : NULL을 포함한 산술 표현식 결과는 NULL이 된다 그리고

비교 연산자로 통해 비교할 수 없고 만약 비교한다면 FALSE의 값을 반환한다

논리연산자 우선순위 ( )괄호, NOT, AND, OR 순서대로 처리

- AND : 앞 뒤 모두 참이여야 참이된다

- OR : 둘 중 하나만 참이라도 참이다

- NOT : 뒤에 오는 조건에 반대되는 결과를 되돌려준다.

ORDER BY – ASC 올림차수(DEFAULT), DESC 내림차순

밑의 문제들은 SQL전문가 가이드 실습용 테이블을 활용하여 문제를 풀었다

GROUP BY, HAVING BY, WHERE 절 기본 개념은 아래의 페이지를 참조하자 

https://tantangerine.tistory.com/15

답은 흰색으로 처리하였습니다

1. 포지션별 인원수, 최대키, 최소키, 평균키를 출력하시오 이때 소수점 2번째 자리까지 구하시오

SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상,

MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키

FROM PLAYER GROUP BY POSITION

2. K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180 센티미터 이상인 정보만 출력하시오

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키

FROM PLAYER GROUP BY POSITION HAVING AVG(HEIGHT) >= 180;

3. K-리그의 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수를 출력하시오.

단, 가장 효율적인 자원 활용을 생각하시오

SELECT TEAM_ID 팀ID, COUNT(*) 인원수

FROM PLAYER

WHERE TEAM_ID IN ('K09', 'K02')

GROUP BY TEAM_ID;

SELECT TEAM_ID 팀ID, COUNT(*) 인원수

FROM PLAYER

GROUP BY TEAM_ID

HAVING TEAM_ID IN ('K09', 'K02');

4. 포지션별 평균 키만 출력하는데, 최대키가 190cm 이상인 선수를 포함하고 있는 포지션의 정보만 출력한다.

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키

FROM PLAYER GROUP BY POSITION HAVING MAX(HEIGHT) >= 190;

< 서브쿼리 >

ㅇ 서브쿼리의 개념

ㅇ 서브쿼리의 위치에 따른 명칭

• SELECT문에 있는 서브쿼리 : 스칼라 서브쿼리
• FROM절에 있는 서브쿼리 : 인라인 뷰
• WHERE절에 있는 서브쿼리 : 서브쿼리

@인라인 뷰  임시적으로 생성되는 동적인 뷰이기 때문에 해당 정보가 저장되지않는다

ROWNUM과 INLINE VIEW의 특성을 이용하여 페이징 처리 등의 작업을 수행할 수 있다.

ㅇ 서브쿼리의 반환 값에 따른 서브쿼리 종류

- 단일 행 서브쿼리(Single-Row Subquery) 

서브 쿼리의 실행 결과가 항상 1건 이하인 것을 의미한다

단일 행 비교 연산자에는 =, <, <=, >, >=, <>이 있다

- 다중 행 서브쿼리(Multiple-Row Subquery)

    서브쿼리의 결과가 여러 행일 때를 의미한다

다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS가 있다

- IN(서브쿼리)

    서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미

- 비교연산자 ALL(서브쿼리) 

    메인쿼리는 서브쿼리 결과 값의 모든 값을 만족해야하므로 비교연산자 <,>올 때 다른값 반환한다

서브쿼리의 값이 1,2,3 일 때 비교연산자 > 일 때 서브쿼리는 3의 값으로 비교 된다

- 비교연산자 ANY(서브쿼리)

    메인쿼리는 서브쿼리의 결과 값들 중 어떤 값이라도 만족하면 되므로 비교연산자 <,>올 때 다른값 반환한다

서브쿼리의 값이 1,2,3 일 때 비교연산자 > 일 때 서브쿼리는 1의 값으로 비교 된다

- EXISTS (서브쿼리)

    EXISTS는 존재하는지 여부를 확인하여 정보를 조회하는 경우사용된다, 즉 필요한 정보는

메인쿼리에 정보가 존재하지만 필요한 조건에 해당하는 다른 테이블에 있을 때 사용한다

그래서 항상 연관 서브 쿼리로 사용

- 다중 컬럼 서브쿼리(Multi-Column Subquery) :

서브 쿼리의 실행결과로 여러 칼럼을 반환한다

메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야한다

메인 쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다

다중 행 비교 연산과 같은 의미를 가지고 있으며 동시 비교가 가능하다

- HAVING 절 서브쿼리

 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다.

ㅇ 스칼라 서브쿼리(Scala Subquery)

- SELECT문에서 사용하는 서브쿼리로 1행만 반환

ㅇ 상호연관 서브쿼리(Correlated Subquery)

- 메인쿼리의 값을 서브쿼리가 사용하고, 서브쿼리의 값을 받아서 메인쿼리가 계산하는 구조의 쿼리