1월 17일 국비교육

#1

Q1. EMP 테이블을 사용해서 사원번호, 사원이름, 직업, 연봉, 부서번호를 가진 MYTEST02 TABLE을 만들자.

SQL> CREATE TABLE MYTEST02

  2  AS

  3  SELECT EMPNO, ENAME, JOB, (SAL*12 + NVL(COMM, 0)) YB FROM EMP;

Q2. MYTEST02 테이블의 사원번호에게 식별키를 지정하자.

ALTER TABLE MYTEST02 ADD CONSTRAINTS PK_MT PRIMARY KEY(EMPNO);

Q3. 사원이름을 NOT NULL 추가하자.

 ALTER TABLE MYTEST02 MODIFY ENAME NOT NULL;

Q4. DEPT -> MYDEPT로 생성한다.

SQL> CREATE TABLE MYDEPT

  2  AS

  3  SELECT * FROM DEPT;

Q5. MYDEPT에 DEPTNO에 식별키를 지정한다.

ALTER TABLE MYDEPT ADD CONSTRAINTS PK_MD PRIMARY KEY(DEPTNO);

Q6. MYTEST02 EMPNO와 MYDEPT의 DEPTNO를 참조시키자.

ALTER TABLE MYTEST02 ADD CONSTRAINTS FK_MT FOREIGN KEY(DEPTNO) REFERENCES MYDEPT(DEPTNO);

#2

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME

  2  FROM USER_CONSTRAINTS

  3  WHERE TABLE_NAME IN ('MYTEST02', 'MYDEPT');

#3

INSERT * FROM MYTEST;

INSERT INTO TABLE명 VALUES(,,,);

UPDATE TABLE명 SET COL = 변경값,,, WHERE,,,

DELETE FROM TABLE명 WHERE,,,

Q1. 부서번호가 30인 사원들의 직업을 MANAGER로 지정하고 커미션을 봉급의 20%로 지정하자.

SQL> UPDATE MYTEST SET JOB = 'MANAGER', COMM = SAL * 0.2

  2  WHERE DEPTNO = 30;

#4

#5

Q1. MYTEST의 부서번호 20번을 모두 삭제하자.

DELETE

FROM MYTEST

WHERE DEPTNO = 20;

Q2. MYTEST의 MANAGER를 찾아 모두 삭제하자.

DELETE 

FROM MYTEST

WHERE JOB = 'MANAGER';

Q3. EMP 테이블의 모든 자료를 MYTEST로 입력해보자.

INSERT INTO MYTEST SELECT * FROM EMP;

Q4. JONES의 월급보다 많이 받는 사원의 월급을 0으로 변경해보자.

SQL> UPDATE MYTEST

  2  SET SAL = 0

  3  WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');

Q5. 30번 부서의 평균 월급보다 적게 받는 사원의 봉급을 7000으로 변경하자.

SQL> UPDATE MYTEST

  2  SET SAL = 7000

  3  WHERE SAL < (SELECT AVG(SAL) FROM MYTEST WHERE DEPTNO = 30);

Q6. MYTEST의 DEPTNO 컬럼에 EMP 테이블의 DEPTNO값을 입력하자.

INSERT INTO MYTEST(DEPTNO) SELECT DEPTNO FROM EMP;

#6

뷰

1) 정의

- 다른 테이블이나 뷰에 포함된 맞춤 표현

- DESC VIEWS; 로 확인할 것

- 가상테이블이므로 메모리는 없음

- 하나 또는 하나 이상의 테이블 / 뷰에 포함된 데이터 부분 집합을 나타내는 논리적인 객체 -> 선택적인 정보 제공 가능

- 자체적으로 데이터를 포함하지 않는다(참조일 뿐)

- 베이스 테이블 : 뷰를 통해 보여지는 데이터를 포함하고 있는 실제 테이블

2) 명령어

ⓐ VIEW 생성 & 생성된 VIEW 조회

CREATE VIEW MY01

AS

SELECT * FROM EMP;

&

SQL> SELECT VIEW_NAME, VIEW_TYPE

  2  FROM USER_VIEWS;

ⓑ MYTEST 테이블을 EMP테이블로 이름 변경

 RENAME MYTEST TO EMP;

ⓒ VIEW 테이블의 이름을 MY01에서 MYVIEW로 변경

RENAME MY01 TO MYVIEW;

ⓒ - 1 사본 테이블에 컬럼을 추가하자.(ABC)

ALTER TABLE MYVIEW ADD(ABC NUMBER(2));

-> 오류발생(ORA-00942: 테이블 또는 뷰가 존재하지 않습니다)

ⓓ VIEW는 DML, DDL이 필요가 없음

ⓔ CREATE OR REPLACE

지정한 이름의 뷰가 없으면 새로 생성, 동일 이름이 있으면 OVERWRITE(덮어 쓰기)

ⓕ MYVIEW (DEPT 참조) 생성 & EMP 참조로 덮어쓰기

SQL> CREATE OR REPLACE VIEW MYVIEW

  2  AS

  3  SELECT * FROM DEPT;

&

SQL> CREATE OR REPLACE VIEW MYVIEW

  2  AS

  3  SELECT * FROM EMP;

ⓖ VIEW 생성구문 + SubQuery

CREATE OR REPLACE VIEW MYVIEW(사원의 이름, 사원의 번호, 근무지역)

AS

SELECT ENAME, EMPNO, LOC

FROM EMP JOIN DEPT USING(DEPTNO);

3) 제약조건

ⓐ 뷰의 원래 목적은 아니지만 뷰를 통한 DML 작업은 가능함

ⓑ DML 작업 결과는 베이스 테이블의 데이터 적용

ⓒ 뷰 생성 시 DML 작업은 제한 될 수 있음

ⓓ 

ⓔ WITH CHECK OPTION - 조건에 따라 INSERT/UPDATE 작업 제한(DELETE는 제한 없음) - 중요

ⓕ - 1 MYTEST 생성 뒤 원본에 기본키를 생성. 

ⓕ - 2 MYTEST_VIEW라는 VIEW 생성

ⓕ - 3 MYTEST_VIEW와 MYTEST의 CONSTRAINT 확인

ⓕ - 4 MYTEST_VIEW에 DATA INPUT

ⓕ - 1    

SQL> CREATE TABLE MYTEST

  2  AS

  3  SELECT * FROM EMP;

ⓕ - 2 

ALTER TABLE MYTEST ADD CONSTRAINT B1 PRIMARY KEY(EMPNO);

ⓕ - 3 

SQL> CREATE VIEW MYTEST_VIEW

  2  AS

  3  SELECT * FROM MYTEST;

SQL> SELECT CONSTRAINT_TYPE, CONSTRAINT_NAME

  2  FROM USER_CONSTRAINTS

  3  WHERE TABLE_NAME IN ('MYTEST_VIEW', 'MYTEST');

ⓕ - 4

INSERT INTO MYTEST_VIEW(ENAME) VALUES('111');

-> 오류발생 (ORA-01400: NULL을 ("BIGDATA"."MYTEST"."EMPNO") 안에 삽입할 수 없습니다)

-> 제약조건은 원본 테이블에서 찾으려고 함  

ⓕ - 5

INSERT INTO MYVIEW(사원이름) VALUES('아잉');

-> 오류발생 (ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다)

ⓕ - 6

SQL> CREATE OR REPLACE VIEW MYVIEW02

  2  AS

  3  SELECT ENAME, EMPNO, LOC

  4  FROM EMP JOIN DEPT USING(DEPTNO);

 INSERT INTO MYVIEW VALUES('ELE', 10, 'ENO');

-> 오류 발생( ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다)

이유 : DEPTNO를 기준으로 JOIN해주므로 READ ONLY가 됨. JOIN걸어버리면 READ ONLY!

3) InlIne View 개념

View Table도 Join이 가능하다.

ⓐ 예문

SQL> SELECT A.ENAME, A.SAL

  2  FROM EMP JOIN (SELECT * FROM EMP) A USING(EMPNO);

-> 빨간색 부분이 A라는 일회용 뷰가 되는 걸로 생각하면 쉬움 

SQL> SELECT EMP.ENAME 사원의이름, MGR.ENAME 매니저이름

  2  FROM EMP JOIN (SELECT * FROM EMP) MGR USING(EMPNO);

Q1. 사원의 이름과 월급을 출력하되 누적월급을 출력하자.

SQL> SELECT E.EMPNO, E.ENAME, E.SAL, SUM(D.SAL)

  2  FROM EMP E, (SELECT EMPNO, SAL FROM EMP) D

  3  WHERE E.EMPNO >= D.EMPNO

  4   GROUP BY E.EMPNO, E.ENAME, E.SAL

  5  ORDER BY SUM(D.SAL);

SQL> SELECT E.ENAME, M.ENAME

  2  FROM EMP E, (SELECT ENAME FROM EMP) M;

-> (SELECT ENAME FROM EMP)는 뷰로서 원하는 것만 가져오는 기능을 한다.

이 문장은

SELECT E.ENAME, M.ENAME

FROM EMP E, EMP M; 과 같음

4) 인라인 뷰 활용 : Top N 분석 구문

- 순번을 활용하려면 ROWNUM이 할당되기 전에 미리 정렬을 해야 함

- 미리 정렬된 결과를 가지고 있도록 하기 위해 인라인 뷰를 활용

SELECT * | SELECT_LIST

FROM (SELECT SELECT_LIST

    FROM    TABLE_NAME

    ...

    ORDER BY 기준 컬럼)

WHERE ROWNUM <= (또는 < ) N;

SELECT ROWNUM, ENAME, SAL

FROM EMP

ORDER BY 3;

Q1. EMP 테이블에서 위에서 3명만 출력하라

SELECT ROWNUM, ENAME, SAL 

FROM EMP

WHERE ROWNUM =<3;

Q2. EMP 테이블에서 월급의 상위 TOP 3만 출력하라.

SQL> SELECT K.ENAME, K.SAL

  2  FROM (SELECT ROWNUM AS NO, ENAME, SAL FROM EMP

  3  ORDER BY SAL DESC) K

  4  WHERE ROWNUM <=3;