귤귤의 대모험

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';

Spring Boot의 특징

• 임베디드 톰캣(Embed Tomcat), 제티, 언더토우를 사용하여 독립 실행이 가능한 스프링 애플리케이션 개발
• 통합 스타터를 제공하여 메이븐/그레이들 구성 간소화
• 번거로운 XML 설정을 요구하지 않음
• JAR을 사용하여 자바 옵션만으로도 배포가능
• 애플리케이션의 모니터링과 관리를 위한 스프링 액츄에이터(Spring Actuator)제공

Stater 내부의 의존성 확인 방법

1.  Spring Boot Reference Guide를 참고하여 확인할 수 있다 (링크를 참조하자)https://docs.spring.io/spring-boot/docs/current-SNAPSHOT/reference/htmlsingle/#using-boot-starter

stater 내부의 의존성을 확인하는 이유는 다음과 같습니다

• 특정 스타터를 사용하려하는데 의존 관계가 궁금할 때
• 의존관계를 확인하지 않고 Spring-boot-starter를 추가했는데 의존관계 설정이 궁금할 때 

Spring-boot-starter는 다음 여섯 가지 의존성을 제공한다

1. spring-boot : 스프링 부트에서 기본 제공하는 의존성
2. spring-boot-autoconfigure : 스프링 부트의 자동 환경 구성에 필요한 의존성
3. spring-boot-starter-logging : 각종 로그를 사용하는데 필요한 의존성
4. javax.annotation-api : 소프트웨어의 결함을 탐지하는 어노테이션을 지원하는 의존성
5. spring-core : 스프링 코어를 사용하는데 필요한 의존성
6. snakeyaml : yaml을 사용하는 데 필요한 의존성

애플리케이션 스타터

• spring-boot-starter : 스프링 부트 코어, auto-configuration, logging yaml 제공
• spring-boot-starter-aop : 관전 지향 프로그래밍을 위한 스타터
• spring-boot-starter-batch : 스프링 배치를 사용하는데 필요한 스타터
• spring-boot-starter-data-jpa : 스프링 데이터 JPA와 하이버네이트를 사용하는데 필요한 스타터
• spring-boot-starter-data-redis : 메모리 저장방식의 저장소인 레디스와 자바에서 쉽게 레디스를 사용하게끔 도와주는 제디스 설정 자동화 스타터
• spring-boot-starter-data-rest : 스프링 데이터 저장소 방식에 맞춘 REST API를 제공하는데 사용하는 스타터
• spring-boot-starter-thymeleaf : 타임리프 템플릿 엔진을 사용하는 데 필요한 스타터
• spring-boot-starter-jdbc : 톰캣 JDBC 커넥션풀에 사용하는 스타터
• spring-boot-starter-security : 각종 보안에 사용하는 스프링 시큐리티 스타터
• spring-boot-starter-oauth2 : OAuth2 인증에 사용하는 스타터
• spring-boot-starter-validation : 자바 빈 검증(Java Bean Vaildation)에 사용하는 스타터
• spring-boot-starter-web : 웹을 만드는 데 사용하는 스타터(스프링MVC, REST형, 임베디드 톰캣, 기타 라이브러리 포함)

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)

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

GROUP BY 절 특징

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT절에 집계함수를 사용.
• 집계함수의 통계정보는 NULL 값을 가진 행을 제외하고 수행한다.
• GROUP BY 절에서는 ALIAS 명을 사용할 수 없다.
• 집계함수는 WHERE 절에 올 수 없다.
• 집계함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다.
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시 할 수있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력.

HAVING 절 특징

• HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있다
• HAVING 절과 GROUP 절의 순서를 바꾸어서 수행하더라도 문법 에러는 없다. 하지만 논리적으로 순서를 지키는 것을 권고
• HAVING 절은 SELECT 절에서 사용되지 않은 칼럼이나 집계함수가 아니더라도 소그룹 집계함수를 이용한 조건을 표시을 가능

데이터의 효율적인 자원 활용 (WHERE 절과 HAVING 절의 관계)

• GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산하는 방법
• GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링하는 방법
• 위의 두 가지 방법 중 WHERE 절에서 조건절을 적용하여 GROUP BY의 계산 대상을 줄이는 것이 효율적인 자원 사용이다

SELECT 문장 실행 순서

1. 발췌대상 테이블을 참조한다 (FROM)
2. 발췌대상 데이터가 아닌 것은 제거한다. (WHERE)
3. 행들을 소그룹화 한다 (GROUP BY)
4. 그룹핑된 값의 조건에 맞는 것만을 출력한다 (HAVING)
5. 데이터 값을 출력/계산한다 (SELECT)
6. 데이터를 정렬한다 (ORDER BY)

• 집계함수

- COUNT(표현식) : 표현식의 NULL 값인 것을 제외한 행의 수를 출력한다

- COUNT(*) : NULL값을 포함한 행의 수를 출력한다

– SUM(DISTINCT | ALL) : 표현식의 NULL 값인 것을 제외한 합계

– AVG(DISTINCT | ALL) : 표현식의 NULL 값인 것을 제외한 평균값

– MAX(DISTINCT | ALL | 표현식) : 최대값

– MIN(DISTINCT | ALL | 표현식) : 최소값

– STDDEV(DISTINCT | ALL) : 표준편차

– VARIANCE(DISTINCT | ALL) : 분산

• 공집합이란 NULL과는 다르게 조건에 맞는 데이터가 단한건도 없는 경우를 말한다
• 일반적으로 NULL과는 다른 데이터 값이다 

공집합은 어떠한 특정 테이블에 존재하지 않는 컬럼의 데이터 값을 출력할 때 발생한다

SELECT EMPNO FROM EMP WHERE ENAME ='KIM';

위의 쿼리문으로 공집합이 발생한다면 여기서 NVL 함수를 써도 결과는 변하지 않는다.

NVL 함수는 NULL 값을 변경할수 있는 함수이며, 데이터가 없는 공집합은 대상이 되지 않는다

그래서 다른 방법을 생각해야한다

집계함수는 결과가 공집합인 경우에도 NULL을 출력할 수 있다

그러므로 먼저 집계함수를 사용하여 NULL값으로 만든 다음 NVL함수를 사용해서 원하는 형태로 출력한다

SELECT NVL(MAX(EMPNO), '1111') EMPNO FROM EMP WHERE ENAME='KIM'

출력값 : 1111

• 조건함수

• DECODE (칼럼 또는 수식, 조건1, 값1, 조건2, 값2, 조건3, 값3,…….., 디폴트 값)

–IF –THEN –ELSE 문장과 같은 처리

–칼럼 또는 수식의 값이 조건1에 해당하면 값1을 리턴하고, 조건2에 해당하면 값2를 리턴한다.

–조건에 맞는 값이 없으면 디폴트 값을 리턴한다.

• NULL 함수

• NVL함수

– NVL( expr1, expr2 ) expr1 : NULL 값을 포함하는 값이나 표현식, expr2 : NULL 대신 사용할 값

# SELECT first_name, titile, salary * NVL(commission_pct,0)/100 COMM from s_emp;

- NULLIF( expr1, expr2 ) expr1 : 어떤 표현식에 해당하는 범주 expr2: expr1의 표현식과 같은 값을 비교할 표현식

#SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) FROM EMP

#SELECT ENAME, EMPNO, MGR,

                      CASE MGR WHEN 7698

                                             THEN NULL

                                             ELSE MGR

                                             END AS MGR2 FROM EMP;

• 숫자함수

• ROUND (칼럼명or 숫자값, n) : 숫자값을소수점 n의 위치까지 반올림한다

. – ROUND(45.925, 2)=>45.93

• TRUNC (칼럼명or 숫자값, n) : 숫자값을소수점 n의 위치 아래까지 잘라낸다.

– TRUNC(45.925, 2)=>45.92

• FLOOR (칼럼명or 숫자값) : 숫자값보다 같거나 작은 최대의 정수를 리턴한다.

– FLOOR(45.925)=>45

• CEIL (칼럼명or 숫자값) : 숫자값보다 같거나 큰 최소의 정수를 리턴한다.

– CEIL(45.925)=>46

• MOD (칼럼명or 숫자값, 칼럼명or 숫자값) : 첫 번째 인수를 두 번째 인수로 나눈 나머지를 리턴한다.

– MOD(45, 2)=>1

• TO_CHAR (날짜 값, 형식) : 날짜 값을 형식에 맞춰 문자 스트링으로만들어 준다.

• TO_CHAR (숫자 값, 형식) : 숫자 값을 문자 스트링으로만들어 준다.

• TO_NUMBER (문자 스트링, 형식) : 문자 스트링을주어진 형식대로 해석하여 숫자 값으로 만들어 준다.

문자함수

LOWER (칼럼명or 스트링) : 알파벳 문자를 소문자로 바꿔준다.

–LOWER(‘Oracle DBMS’)=>oracle dbms

UPPER (칼럼명or 스트링) : 알파벳 문자를 대문자로 바꿔준다.

–UPPER(‘Oracle DBMS’)=>ORACLE DBMS

INITCAP (칼럼명or 스트링) : 알파벳 단어의 첫 글자는 대문자로, 나머지는 소문자로 바꿔준다.

SUBSTR (칼럼명or 스트링, m, n) : m의 위치에서 n 길이만큼 문자열을 잘라 리턴한다.

- SUBSTR(‘Oracle DBMS’, 2,4)=>racl

LENGTH (칼럼명or 스트링) : 문자열의 길이를 리턴한다.

– LENGTH(‘Oracle DBMS’)=>11

INSTR (칼럼명or 스트링, 문자) : 문자열 내에서 문자의 위치 값을 리턴한다.

– INSTR(‘Oracle DBMS’, ‘a’)=>3

LPAD (칼럼명or 스트링, m, 문자) : 문자열 전체 길이가 m이 되도록 문자열의 왼쪽에 문자를 채워준다.

– LPAD(‘Oracle DBMS’, 13, ‘x’)=>xxOracleDBMS

RPAD (칼럼명or 스트링, m, 문자) : 문자열 전체 길이가 m이 되도록 문자열의 오른쪽에 문자를 채워준다.

– RPAD(‘Oracle DBMS’, 13, ‘x’)=>Oracle DBMSxx

TO_CHAR (날짜 값, 형식)

- 날짜 값을 형식에 맞춰 문자 스트링으로만들어 준다.

TO_CHAR (숫자 값, 형식)

- 숫자 값을 문자 스트링으로만들어 준다.

TO_DATE (문자 스트링, 형식)

- 문자 스트링을주어진 형식대로 해석하여 날짜 값으로 만들어 준다.

TO_NUMBER (문자 스트링, 형식)

- 문자 스트링을주어진 형식대로 해석하여 숫자 값으로 만들어 준다.

import React from "react";

import { HashRouter, Route, Switch, Redirect} from "react-router-dom";

// components

import Layout from "./RouteMenunComponents/Layout/Layout";

// pages

import Error from "../pages/error";

import LoginContainer from "../pages/login/LoginContainer";

const App = (props) => {

  // global

  const { isAuthenticated } = props

  return (

    <HashRouter>

      <Switch>

        <Route 

          exact 

          path="/" 

          render = {() => <Redirect to="/app/dashboard" />} 

        />

        <Route

          exact

          path="/app"

          render={() => <Redirect to="/app/dashboard" />}

        />

        <PrivateRoute path="/app" component={Layout} />

        <PublicRoute path="/login" component={LoginContainer} />

        <Route component={Error} />

      </Switch>

    </HashRouter>

  );

•   ========================================================================

PrivateRoute, PublicRoute는 권한을 가지고 있는 변수 값이 true false를 판별하여 그에 따른 값을 치환한다

  function PrivateRoute({ component, ...rest }) {

    return (

      <Route

        {...rest}

        render={props =>

          isAuthenticated ? (

            React.createElement(component, props)

          ) : (

            <Redirect

              to={{

                pathname: "/login",

                state: {

                  from: props.location,

                  explain: '로그인 인증 실패'

                },

              }}

            />

          )

        }

      />

    );

  }

•   ========================================================================

 function PublicRoute({ component, ...rest }) {

    return (

      <Route

        {...rest}

        render={props =>

          isAuthenticated ? (

            <Redirect

              to={{

                pathname: "/",

              }}

            />

          ) : (

            React.createElement(component, props)

          )

        }

      />

    );

  }

}

export default App;