Lecture 16 - SQL(6)

DDL(Data Definition Language)
    테이블 - 
    뷰 - select문의 별명
    시퀀스 - 고유번호 생성기
    인덱스 - 목차
    시노님 - 객체의 별칭
TCL(Transaction Control Language)
    * Transaction - lock / 읽기 일관성
    - commit (all or nothing)
    - rollback
    - savepoint
DCL(Data Control Language)
    * create user 사용자아이디 identified by 암호;
    - grant to
    - revoke from

권한
    시스템 - 관리자
    객체 - 소유자

truncate는 rollback이 안됨!!

속도는 truncate가 빠르나 rollback이 안되니 rollback이 필요한 자료일 경우 delete를 애용할 것

제약조건( Constraints)
    not null - 필수 입력 컬럼
    unique key - 중복제거
    primary key - not null + 중복제거
    foreign key - 참조
    check - 데이터 검사

필요없는 테이블, 뷰, Synonym을 지워준다

위와 같이 4개의 테이블만 남겨둔다

Not Null

에러 메세지가 떳다는 것은 Not null 제약조건이 해당되었단 뜻이다.

dept_n1을 sys_c0010315로 서버가 임의로 제약조건의 이름을 정의해버렸다

제약조건 dept_n2_deptno_nn 이름 지정

Unique

컬럼 단위 제약조건

deptno가 unique라 그외 dname, loc은 중복입력이 되지만 unique는 중복이 되지 않아 에러가 난다

null은 중복검사를 하지 않는다!!

테이블 단위 제약조건

Primary Key - 주키 : 사원번호 같이 고유값을 가진것에 사용

하나는 들어가는데 중복되거나 null은 들어갈 수 없다

테이블 단위 제한조건 넣는 법 (Unique와 동일)

Foreign Key - 참조키, 외래키 : 참조할게 없으면 안됨, 유니크나 프라이머리 키의 것을 참조해서 만든다고 생각하면 쉽다

자식이 있는데 부모를 없앨 수 없으니 에러 발생

위와 같은 이유로 purge도 안된다.

ex) 106p

Check 제약 조건

sal이 0보다 큰 경우여야 하므로 -5000은 조건에 위배됨

테이블 수준에서 정의함

제약조건의 관리

제약조건의 추가

primary key로 수정

foreign key로 수정

제약조건의 삭제

제약조건 비활성화

제약조건 활성화

111p 다음 ERD를 보고 테이블을 작성하시오. 단, 제약조건은 primary key, foreign key만 사용하시오.

 

집합 연산자

Union

Union All

Intersect

ex) 10번 부서와 30 부서의 직책을 중복없이 출력 => Union

Minus

집합 연산자 사용 지침

null

0

138p
1. 다음 연산자 중 출력결과가 정렬되지 않는 집합 연산자는? b
    a. Union
    b. Union All
    c. Intersect
    d. Minus
2. 다음 연산자 중 쿼리의 작성 순서가 결과에 영향을 미치는 집합 연산자는? d
    a. Union
    b. Union All
    c. Intersect
    d. Minus
3. 집합 연산자를 사용 할 때 주의해야 할 사항이 아닌 것은? c
    a. 각각의 쿼리에서 컬럼의 개수는 일치해야 한다.
    b. 각각의 쿼리에서 컬럼의 데이터 타입은 일치해야 한다.
    c. 각각의 쿼리에서 컬럼명은 일치해야 한다.

Rollup과 Cube

Rollup

급여의 합

급여의 평균

Cube

Union으로 cube를 사용한 위와 똑같이 구성함

Grouping 함수 - 어떤 컬럼이 그룹화 되었나 확인할 때 사용

Grouping Sets

union all 을 이요하여 위와 같이 똑같이 구성함

147p
1. Rollup (a, b)에 의해서 만들어지는 그룹을 기술하시오
2.Cube (a, b)에 의해서 만들어지는 그룹을 기술하시오
3. Rollup (a, (b, c, d), e)에 의해서 만들어지는 그룹을 기술하시오
4. Cube ((a, b), (c, d), e)에 의해서 만들어지는 그룹을 기술하시오
5. 사원 테이블에서 업무 및 부서코드별 평균 급여, 업무별 평균 급여, 전체 평균 급여를 출력하는 SQL 문장을 작성하시오

서브쿼리 2

페어와이즈

With - 진짜 쿼리에 대한 별명

155p
1. 다음 join 문장을 상관관계 서브쿼리로 변환하시오

2. 사원 테이블에서 같은 업무를 수행하는 직원들 중, 해당 업무별 평균 급여보다 많은 급여를 받는 직원의 사원명, 업무, 급여를 검색하시오(상관관계 서브쿼리로 작성할 것)
3. 사원 테이블에서 같은 부서에 근무하는 직원들 중, 해당 부서별 최대 급여를 받는 직원의 사원명, 부서번호, 급여를 검색하시오(상관관계 서브쿼리로 작성할 것)
[4-6] 다음과 같은 Master 테이블과 Temp 테이블을 작성하시오

Master 테이블에는 데이터의 원본이며, Temp 테이블은 Master 테이블에 변경할 데이터가 저장되는 테이블이다. Temp 테이블을 기반으로 Master 테이블을 변경하는 규칙은 다음과 같다.
- MASTER.ID = TEMP.ID 이면 MASTER 테이블에서 해당 행을 변경
- MASTER.ID에 TEMP.ID가 없는 경우는 MASTER 테이블에서 해당 행을 추가
- TEMP.NAME이 NULL인 경우는 MASTER 테이블에서 해당 행을 삭제
4. TEMP 테이블을 기반으로 MASTER 테이블의 해당 행을 변경하시오. 변경 후의 MASTER 테이블은 다음과 같아야 한다.

5. TEMP 테이블을 기반으로 MASTER 테이블의 해당 행을 추가하시오. 변경 후의 MASTER 테이블은 다음과 같아야 한다.

6. TEMP 테이블을 기반으로 MASTER 테이블의 해당 행을 삭제하시오. 변경 후의 MASTER 테이블은 다음과 같아야 한다

계층형 쿼리

트리 구조

계층형 쿼리의 작성 방법

ex) 사원 테이블에서 사원명이 'KING'인 직원부터 하향식 전개하는 방법은 다음과 같다.

Level

Level 추가

트리 구조의 노드(Node) 및 브랜치(Branch) 제거

Node 제거

Branch 제거

162p
1. 계층형 쿼리를 이용하여 'JONES'의 부하직원을 모두 출력하시오
2. 계층형 쿼리를 이용하여 'ADAMS'의 상관을 모두 출력하시오
3. 계층형 쿼리를 이용하여 'KING'의 부하직원을 출력하시오. 단, 'BLAKE'와 'BLAKE'의 부하직원들은 제외 할 것

Bulky(대량) Insert
    insert 서브쿼리
     - 다중 insert
        * 데이터 테이블이 존재
        무조건 insert
        조건 insert all
        조건 insert first

이런것을 무조건 인서트라고 한다

조건 insert all - if가 두개라고 생각하면 쉽다

조건 insert first - if, else if, else와 같다고 생각하면 쉽다

ex) empno, ename, sal, hiredate를 가진 
emp80 : 80/01/01 ~ 80/12/31
emp81 : 81/01/01 ~ 81/12/31 
emp82 : 82/01/01 ~ 82/12/31 
emp87 : 87/01/01 ~ 87/12/31
insert 문

강사님이 쓰신거