Sequence
: 테이블이나 데이터와는 전혀 상관 없음
: 여러 테이블이 공유 가능
▶ CREATE
: CREATE SEQUENCE SEQUENCE_NAME (SEQUENCE);
▶ START WITH
: 시작 번호
▶ **INCREMENT BY
: 번호가 증가하는 크기
▶ MAX VALUE
: 마지막 번호
▶ CYCLE
: 마지막 번호다음 다시 시작 번호로 돌아감
▶ NEXTVAL / CURRVAL
: SEQUENCE_NAME.NEXTVAL - 시퀀스 다음 번호
: SWQUENCE_NAME.CURRVAL - 현재까지 발생된 숫자 / NEXTVAL이 1번 이상 수행 후 사용 가능
▶ DROP
: DROP SEQUENCE SEQUENCE_NAME
CREATE SEQUENCE SEQUENCE_NAME
STRAT WITH NUMBER
INCREMENT BY NUMBER
MAXVALUE NUMBER
CYCLE;QUIZ
BOOK TABLE 생성
: 책 관리 번호, 책 이름, 저자, 출판사, 도서유통번호(ISBN), 정가
: CHECK, NOT NULL, PRIMARY KEY 각각 하나씩 적용
CREATE TABLE BOOK ( BNO NUMBER(6) PRIMARY KEY
, BTITLE VARCHAR2(150) NOT NULL
, BAUTHOR VARCHAR2(150)
, BPUBLISHER VARCHAR2(90)
, BISBN NUMBER(13)
, BPRICE NUMBER(6) CHECK (BPRICE > 0));JOIN
: 2개 이상의 테이블을 연결하여 DATA를 SELECT 하는 기법
→ 한 테이블 내에서 지속적으로 반복되는 구간을 분리, 새로운 테이블을 작성
: 같은 이름의 COLUMN을 가진 테이블 속성을 사용하는 경우 각 속성의 이름 앞에 테이블 이름을 작성
→ 테이블 이름이 긴 경우 ALIAS를 사용
▶ FORIEGN KEY
: 하나의 테이블에서 다른 테이블을 연결하는 속성(ATTRIBUTE, COLUMN)
: 대부분 다른 테이블의 PK(PRIMARY KEY)
→ ex) EMP의 DEPTNO(FOREIGN KEY, FK)는 DEPT의 PK
▶ EQUI JOIN
: 동등한 조건으로 두 개 이상의 테이블을 조회하는 것
→ 동등 비교 연산자(=)를 사용한 JOIN문
: 가장 일반적이며, 많이 사용하는 JOIN문
SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DNAME, DEPT.LOC
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO ORDER BY EMP.DEPTNO;출력 결과
ENAME DEPTNO DNAME LOC ---------- ---------- -------------- ------------- CLARK 10 ACCOUNTING NEW YORK KING 10 ACCOUNTING NEW YORK MILLER 10 ACCOUNTING NEW YORK JONES 20 RESEARCH DALLAS FORD 20 RESEARCH DALLAS ADAMS 20 RESEARCH DALLAS SMITH 20 RESEARCH DALLAS SCOTT 20 RESEARCH DALLAS WARD 30 SALES CHICAGO TURNER 30 SALES CHICAGO ALLEN 30 SALES CHICAGO JAMES 30 SALES CHICAGO BLAKE 30 SALES CHICAGO MARTIN 30 SALES CHICAGO
▶ NON EQUI JOIN
: 비교 연산자(= 제외)를 사용한 JOIN문
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;출력 결과
ENAME JOB SAL GRADE ---------- --------- ---------- ---------- SMITH CLERK 800 1 JAMES CLERK 950 1 ADAMS CLERK 1100 1 WARD SALESMAN 1250 2 MARTIN SALESMAN 1250 2 MILLER CLERK 1300 2 TURNER SALESMAN 1500 3 ALLEN SALESMAN 1600 3 CLARK MANAGER 2450 4 BLAKE MANAGER 2850 4 JONES MANAGER 2975 4 SCOTT ANALYST 3000 4 FORD ANALYST 3000 4 KING PRESIDENT 5000 5
QUIZ
: 상기 NON EQUI JOIN QUERY를 BETWEEN AND로 전환
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;출력 결과
ENAME JOB SAL GRADE ---------- --------- ---------- ---------- SMITH CLERK 800 1 JAMES CLERK 950 1 ADAMS CLERK 1100 1 WARD SALESMAN 1250 2 MARTIN SALESMAN 1250 2 MILLER CLERK 1300 2 TURNER SALESMAN 1500 3 ALLEN SALESMAN 1600 3 CLARK MANAGER 2450 4 BLAKE MANAGER 2850 4 JONES MANAGER 2975 4 SCOTT ANALYST 3000 4 FORD ANALYST 3000 4 KING PRESIDENT 5000 5
▷ 3개 이상의 JOIN
: 모든 TABLE은 다른 TABLE과 하나 이상의 연관 관계 필요
SELECT ENAME, JOB, SAL, GRADE, DNAME
FROM EMP, SALGRADE, DEPT
WHERE SAL BETWEEN LOSAL AND HISAL
AND EMP.DEPTNO = DEPT.DEPTNO;출력 결과
ENAME JOB SAL GRADE DNAME ---------- --------- ---------- ---------- -------------- KING PRESIDENT 5000 5 ACCOUNTING FORD ANALYST 3000 4 RESEARCH SCOTT ANALYST 3000 4 RESEARCH JONES MANAGER 2975 4 RESEARCH BLAKE MANAGER 2850 4 SALES CLARK MANAGER 2450 4 ACCOUNTING ALLEN SALESMAN 1600 3 SALES TURNER SALESMAN 1500 3 SALES MILLER CLERK 1300 2 ACCOUNTING WARD SALESMAN 1250 2 SALES MARTIN SALESMAN 1250 2 SALES ADAMS CLERK 1100 1 RESEARCH JAMES CLERK 950 1 SALES SMITH CLERK 800 1 RESEARCH
▶ CROSS JOIN : CARTESIAN PRODUCT(카티션 프로덕트)
: FULL OUTER JOIN 과 유사
: 2개 이상의 테이블에서 가능한 모든 경우의 수를 조회
SELECT * FROM EMP, DEPT;출력 결과
ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- SMITH CLERK 20 ACCOUNTING ALLEN SALESMAN 30 ACCOUNTING WARD SALESMAN 30 ACCOUNTING JONES MANAGER 20 ACCOUNTING MARTIN SALESMAN 30 ACCOUNTING BLAKE MANAGER 30 ACCOUNTING CLARK MANAGER 10 ACCOUNTING SCOTT ANALYST 20 ACCOUNTING KING PRESIDENT 10 ACCOUNTING TURNER SALESMAN 30 ACCOUNTING ADAMS CLERK 20 ACCOUNTING JAMES CLERK 30 ACCOUNTING FORD ANALYST 20 ACCOUNTING MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ALLEN SALESMAN 30 RESEARCH WARD SALESMAN 30 RESEARCH JONES MANAGER 20 RESEARCH MARTIN SALESMAN 30 RESEARCH BLAKE MANAGER 30 RESEARCH CLARK MANAGER 10 RESEARCH SCOTT ANALYST 20 RESEARCH KING PRESIDENT 10 RESEARCH TURNER SALESMAN 30 RESEARCH ADAMS CLERK 20 RESEARCH JAMES CLERK 30 RESEARCH FORD ANALYST 20 RESEARCH MILLER CLERK 10 RESEARCH SMITH CLERK 20 SALES ALLEN SALESMAN 30 SALES WARD SALESMAN 30 SALES JONES MANAGER 20 SALES MARTIN SALESMAN 30 SALES BLAKE MANAGER 30 SALES CLARK MANAGER 10 SALES SCOTT ANALYST 20 SALES KING PRESIDENT 10 SALES TURNER SALESMAN 30 SALES ADAMS CLERK 20 SALES JAMES CLERK 30 SALES FORD ANALYST 20 SALES MILLER CLERK 10 SALES SMITH CLERK 20 OPERATIONS ALLEN SALESMAN 30 OPERATIONS WARD SALESMAN 30 OPERATIONS JONES MANAGER 20 OPERATIONS MARTIN SALESMAN 30 OPERATIONS BLAKE MANAGER 30 OPERATIONS CLARK MANAGER 10 OPERATIONS SCOTT ANALYST 20 OPERATIONS KING PRESIDENT 10 OPERATIONS TURNER SALESMAN 30 OPERATIONS ADAMS CLERK 20 OPERATIONS JAMES CLERK 30 OPERATIONS FORD ANALYST 20 OPERATIONS MILLER CLERK 10 OPERATIONS
▶ SELF JOIN
: 하나의 테이블을 2번 이상 사용해 JOIN
SELECT E1.ENAME "결재 상신자", E2.ENAME "결재자"
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;출력 결과
결재 상신자 결재자 ---------- ---------- SCOTT JONES FORD JONES ALLEN BLAKE WARD BLAKE MARTIN BLAKE TURNER BLAKE JAMES BLAKE MILLER CLARK ADAMS SCOTT JONES KING BLAKE KING CLARK KING SMITH FORD
QUIZ
: 상기 SELF JOIN QUERYDP DEPT 테이블의 DNAME을 함께 출력
: 단, 결재 상신자 부서와 결재자 부서 두 번에 걸쳐 출력
SELECT E1.ENAME "결재 상신자", D1.DNAME "결재 상신자 부서", E2.ENAME "결재자", D2.DNAME "결재자 부서"
FROM EMP E1, EMP E2, DEPT D1, DEPT D2
WHERE E1.MGR = E2.EMPNO
AND E1.DEPTNO = D1.DEPTNO
AND E2.DEPTNO = D2.DEPTNO;출력 결과
결재 상신자 결재 상신자 부서 결재자 결재자 부서 ---------- -------------- ---------- -------------- MILLER ACCOUNTING CLARK ACCOUNTING JONES RESEARCH KING ACCOUNTING BLAKE SALES KING ACCOUNTING CLARK ACCOUNTING KING ACCOUNTING SCOTT RESEARCH JONES RESEARCH FORD RESEARCH JONES RESEARCH ADAMS RESEARCH SCOTT RESEARCH SMITH RESEARCH FORD RESEARCH ALLEN SALES BLAKE SALES WARD SALES BLAKE SALES MARTIN SALES BLAKE SALES TURNER SALES BLAKE SALES JAMES SALES BLAKE SALES
▶ INNER JOIN
: EQUI JOIN / NON EQUI JOIN
: FROM TABLE_NAME1 INNER JOIN TABLE_NAME2 ON 조건문;
SELECT ENAME, JOB, DEPT.DEPTNO, DNAME
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
SELECT ENAME, SAL, GRADE
FROM EMP INNER JOIN SALGRADE
ON EMP.SAL >= SALGRADE.LOSAL
AND EMP.SAL <= SALGRADE.HISAL;출력 결과
ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- SMITH CLERK 20 RESEARCH ALLEN SALESMAN 30 SALES WARD SALESMAN 30 SALES JONES MANAGER 20 RESEARCH MARTIN SALESMAN 30 SALES BLAKE MANAGER 30 SALES CLARK MANAGER 10 ACCOUNTING SCOTT ANALYST 20 RESEARCH KING PRESIDENT 10 ACCOUNTING TURNER SALESMAN 30 SALES ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES FORD ANALYST 20 RESEARCH MILLER CLERK 10 ACCOUNTINGENAME SAL GRADE ---------- ---------- ---------- SMITH 800 1 JAMES 950 1 ADAMS 1100 1 WARD 1250 2 MARTIN 1250 2 MILLER 1300 2 TURNER 1500 3 ALLEN 1600 3 CLARK 2450 4 BLAKE 2850 4 JONES 2975 4 SCOTT 3000 4 FORD 3000 4 KING 5000 5
▷ 3개 이상의 테이블 INNER JOIN
SELECT ENAME, JOB, DNAME, GRADE
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
INNER JOIN SALGRADE ON EMP.SAL BETWEEN LOSAL AND HISAL;출력 결과
ENAME JOB DNAME GRADE ---------- --------- -------------- ---------- KING PRESIDENT ACCOUNTING 5 FORD ANALYST RESEARCH 4 SCOTT ANALYST RESEARCH 4 JONES MANAGER RESEARCH 4 BLAKE MANAGER SALES 4 CLARK MANAGER ACCOUNTING 4 ALLEN SALESMAN SALES 3 TURNER SALESMAN SALES 3 MILLER CLERK ACCOUNTING 2 WARD SALESMAN SALES 2 MARTIN SALESMAN SALES 2 ADAMS CLERK RESEARCH 1 JAMES CLERK SALES 1 SMITH CLERK RESEARCH 1
▶ OUTER JOIN
: 합치는 SQL
▷ LEFT OUTER JOIN
: 두 개의 테이블 중 좌측의 테이블을 중심으로 JOIN을 수행
→ 반드시 좌측은 다 출력 (모든 COLUMN 출력이 아니라 모든 DATA가 한 번 이상 출력)
→ 우측은 JOIN에 해당하는 내용만 출력
SELECT DNAME, LOC, ENAME, EMP.DEPTNO
FROM DEPT LEFT OUTER JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO;출력 결과
DNAME LOC ENAME DEPTNO -------------- ------------- ---------- ---------- RESEARCH DALLAS SMITH 20 SALES CHICAGO ALLEN 30 SALES CHICAGO WARD 30 RESEARCH DALLAS JONES 20 SALES CHICAGO MARTIN 30 SALES CHICAGO BLAKE 30 ACCOUNTING NEW YORK CLARK 10 RESEARCH DALLAS SCOTT 20 ACCOUNTING NEW YORK KING 10 SALES CHICAGO TURNER 30 RESEARCH DALLAS ADAMS 20 DNAME LOC ENAME DEPTNO -------------- ------------- ---------- ---------- SALES CHICAGO JAMES 30 RESEARCH DALLAS FORD 20 ACCOUNTING NEW YORK MILLER 10 OPERATIONS BOSTON
▷ RIGHT OUTER JOIN
: 두 개의 테이블 중 우측의 테이블을 중심으로 JOIN을 수행
: 반드시 우측은 다 출력 (모든 COLUMN 출력이 아니라 모든 DATA가 한 번 이상 출력)
→ 좌측은 JOIN에 해당하는 내용만 출력
SELECT DNAME, LOC, ENAME, EMP.DEPTNO
FROM EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO;출력 결과
DNAME LOC ENAME DEPTNO -------------- ------------- ---------- ---------- RESEARCH DALLAS SMITH 20 SALES CHICAGO ALLEN 30 SALES CHICAGO WARD 30 RESEARCH DALLAS JONES 20 SALES CHICAGO MARTIN 30 SALES CHICAGO BLAKE 30 ACCOUNTING NEW YORK CLARK 10 RESEARCH DALLAS SCOTT 20 ACCOUNTING NEW YORK KING 10 SALES CHICAGO TURNER 30 RESEARCH DALLAS ADAMS 20 SALES CHICAGO JAMES 30 RESEARCH DALLAS FORD 20 ACCOUNTING NEW YORK MILLER 10 OPERATIONS BOSTON
▷ FULL OUTER JOIN
: 두 개의 테이블의 모든 DATA를 반드시 출력 (JOIN 된 DATA / JOIN이 되지 않은 DATA)
SELECT 고객.*, 제품.*
FROM 고객 FULL OUTER JOIN 제품 ON 고객.고객아이디 = 제품.제품명;출력 결과
고객아이디 고객이름 나이 등급 직업 적립금 제품번 제품명 재고량 단가 제조업 -------------------- ---------- ---------- ---------- -------------------- ---------- --- -------------------- ---------- ---------- --- p01 그냥만두 5000 4500 c01 p02 매운쫄면 2500 5500 c03 p03 쿵떡파이 3600 2600 c02 p04 맛난초콜렛 1250 2500 c02 p05 얼큰라면 2200 1200 c01 p06 통통우동 1000 1550 c03 p07 달콤비스켓 1650 1500 c02 pear 채광주 31 silver 회사원 500 apple 정소화 20 gold 학생 1000 carrot 고명석 28 gold 교사 4500 melon 성원용 35 gold 회사원 5000 peach 오형준 silver 의사 300 banana 김선우 25 vip 간호사 2500 orange 김용욱 22 silver 학생 0
▶ NATURAL JOIN
: 두 테이블간 연결 관계(RELATION) 필요
: 두 테이블간 연결 관계가 있는 COLUMN의 이름 동일
→ DBMS 자체적으로 JOIN
→ JOIN을 통한 관계 명시 없이도 JOIN 가능
SELECT EMPNO, ENAME, DEPTNO, DNAME, LOC
FROM EMP NATURAL JOIN DEPT;출력 결과
EMPNO ENAME DEPTNO DNAME LOC ---------- ---------- ---------- -------------- ------------- 7369 SMITH 20 RESEARCH DALLAS 7499 ALLEN 30 SALES CHICAGO 7521 WARD 30 SALES CHICAGO 7566 JONES 20 RESEARCH DALLAS 7654 MARTIN 30 SALES CHICAGO 7698 BLAKE 30 SALES CHICAGO 7782 CLARK 10 ACCOUNTING NEW YORK 7788 SCOTT 20 RESEARCH DALLAS 7839 KING 10 ACCOUNTING NEW YORK 7844 TURNER 30 SALES CHICAGO 7876 ADAMS 20 RESEARCH DALLAS 7900 JAMES 30 SALES CHICAGO 7902 FORD 20 RESEARCH DALLAS 7934 MILLER 10 ACCOUNTING NEW YORK
