SQL 이론&실습 (2)

SQL 이론&실습 230522~230524

(이어서)5. 조인

6. 데이터 조작어 DML

7. 트랜잭션 제어 TCL

8. 데이터 정의어 DDL

9. 오라클 데이터베이스

 

5. 조인

여러 테이블을 하나의 테이블처럼 사용하는 방법

교차 조인 = 크로스 조인 = 모든 행의 조합

등가 조인 = 내부 조인 = 단순 조인

=> 조인에는 최소 테이블 개수-1개 만큼의 조건이 필요함

비등가 조인 = 등가조인 이외의 방식 BETWEEN A AND B

자체 조인 = 같은 테이블끼리 조인

외부 조인 = 왼쪽/오른쪽 LEFT/RIGHT OUTER JOIN

기준이 되는 테이블의 행은 모두 다 나와야 함 & 기준이 되지 않는 테이블은 교집합만 붙음

 

SQL-99 문법으로 조인

NATURAL JOIN / JOIN USING / JOIN ON / LEFT/RIGHT JOIN ON

--조인: 여러 테이블을 하나의 테이블처럼 사용하는 방법
--행 14개
SELECT * FROM EMP;
--행 4개
SELECT * FROM DEPT;
--교차 조인 = 크로스 조인 = 모든 행의 조합 = 14 * 4 = 56
--조인 조건이 없어서 생긴 문제
SELECT * FROM EMP, DEPT;
SELECT * FROM EMP, DEPT ORDER BY EMP.EMPNO ASC, EMP.ENAME ASC, DEPT.DEPTNO ASC;
--등가조인 = 내부조인 = 단순조인
--같다는 조건으로 조인
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
--테이블 별칭 사용
SELECT * FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO;
--열 이름을 구체화하여 작성할 것!
--테이블 이름 명시
SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM,
       B.DEPTNO, B.DNAME, B.LOC
    FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO;
--테이블이 가지고 있는 열이 명확할 때는 문제 없음
SELECT ENAME, DNAME FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO;
--공통되는 열에서는 오류 발생 => 반드시 테이블 출처 명시
SELECT ENAME, B.DEPTNO, DNAME FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO;
--조건 추가
--조인에는 최소 테이블 개수 - 1 개 만큼의 조건이 필요함
--조인을 할 때 사용하고 있는 테이블 2개 => 조건이 하나가 필요함
--조인을 할 때 사용하고 있는 테이블 3개 => 조건이 두 개가 필요함
--예) A, B, C => A + B = 조건 1개 필요, A + B + C => 조건 1개 필요 
SELECT * FROM EMP A, DEPT B 
    WHERE A.DEPTNO = B.DEPTNO
      AND A.SAL >= 3000;
--비등가조인 = 등가조인 이외의 방식
--BETWEEN A AND B 문장으로 조인
SELECT * FROM EMP;
SELECT * FROM SALGRADE;
--조인
SELECT * FROM EMP A, SALGRADE B WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;
--자체 조인 = 같은 테이블 끼리 하는 조인
SELECT * FROM EMP;
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME 
    FROM EMP A, EMP B WHERE A.MGR = B.EMPNO;
--외부조인
--기준이 되는 테이블의 행은 모두 다 나와야 함 & 기준이 되지 않는 테이블은 교집합만 붙음
--LEFT OUTER JOIN
--왼쪽에 있는 테이블이 기준
--조건절에서 오른쪽에 있는 테이블 이름.열이름 뒤에 (+)
--직속상관 + 직속상관이 없는 사람도 나옴
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME 
    FROM EMP A, EMP B WHERE A.MGR = B.EMPNO (+);
--RIGHT OUTER JOIN
--오른쪽에 있는 테이블이 기준
--조건절에서 왼쪽에 있는 테이블 이름.열이름 뒤에 (+)
--부하직원 + 부하직원이 없는 사람도 나옴
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME 
    FROM EMP A, EMP B WHERE A.MGR (+) = B.EMPNO;
--SQL-99문법으로 조인
--등가조인과 결과는 같음
--1) NATURAL JOIN
-- 같다는 조건 X & 공통된 열 이름의 테이블 이름 X
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM,
       DEPTNO, B.DNAME, B.LOC
    FROM EMP A NATURAL JOIN DEPT B;
--2) JOIN USING
-- 같다는 조건 => USING 공통된 열 명시 & 공통된 열 이름의 테이블 이름 X
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM,
       DEPTNO, B.DNAME, B.LOC
    FROM EMP A JOIN DEPT B USING (DEPTNO);
--3) JOIN ON
-- 같다는 조건 O & 공통된 열 이름의 테이블 이름 O => 우리가 배운 문법과 제일 유사
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM,
       B.DEPTNO, B.DNAME, B.LOC
    FROM EMP A JOIN DEPT B ON (A.DEPTNO = B.DEPTNO);
--외부조인
--LEFT OUTER JOIN ON
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME 
    FROM EMP A LEFT OUTER JOIN EMP B ON (A.MGR = B.EMPNO);
--RIGHT OUTER JOIN ON
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME 
    FROM EMP A RIGHT OUTER JOIN EMP B ON (A.MGR = B.EMPNO);
--FULL OUTER JOIN ON
SELECT A.EMPNO, A.ENAME, A.MGR AS MGR_EMPNO, B.ENAME AS MGR_ENAME 
    FROM EMP A FULL OUTER JOIN EMP B ON (A.MGR = B.EMPNO);

 

서브쿼리

WHERE 절 뒤에서

• 단일 행 서브쿼리 = 결과가 하나 = 행이 1개
• 다중 행 서브쿼리 ; IN, ANY, SOME, ALL, EXISTS(서브쿼리에 결과가 있으면 TRUE 없으면 FALE)
• 다중 열 서브쿼리

 

FROM절 뒤에서

• 인라인뷰 = SQL 문 실행 결과를 마치 테이블처럼 사용

 

SELECT절에서

• 스칼라서브쿼리 = 열 처럼 사용

--서브쿼리
--WHERE 절 뒤에서 사용하는 서브퀴리
--단일행 서브쿼리 = 결과가 하나 = 행이 1개
SELECT SAL FROM EMP WHERE ENAME = 'JONES';
SELECT * FROM EMP WHERE SAL > 2975;
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');
--날짜 데이터와 함께 사용
SELECT * FROM EMP WHERE ENAME = 'SCOTT';
SELECT HIREDATE FROM EMP WHERE ENAME = 'SCOTT';
--SCOTT 보다 일찍 입사한 직원 = SCOTT 입사일보다 과거 날짜 = 작은 값
SELECT * FROM EMP WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'SCOTT');
--SCOTT 보다 늦게 입사한 직원 = SCOTT 입사일보다 미래 날짜 = 큰 값
SELECT * FROM EMP WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'SCOTT');
--다중행 함수와 함께 사용 = 결과가 하나
--전체 직원들의 평균 급여
SELECT AVG(SAL) FROM EMP;
--전체 직원들의 평균 급여보다 많이 받는 직원
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
--다중행 서브쿼리
SELECT DISTINCT DEPTNO FROM EMP;
--IN 연산자 = 같다는 OR 조건 여러 개 = 합집합
SELECT * FROM EMP WHERE DEPTNO IN (10,20,30);
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP);
--ANY / SOME = 여러 결과 중에 하나라도 만족하면 TRUE
-- = 조건 => 같다는 OR 조건 여러 개 = 합집합
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;
SELECT * FROM EMP WHERE SAL = ANY (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
SELECT * FROM EMP WHERE SAL = SOME (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
--작다는 (<) 조건 = OR 조건 여러 개
--800 < 1100 < 2975 < 3000
--최대값보다 작은 급여 가진 직원
SELECT SAL FROM EMP WHERE DEPTNO = 20;
SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 20);
SELECT * FROM EMP WHERE SAL < SOME (SELECT SAL FROM EMP WHERE DEPTNO = 20);
--단일행 서브쿼리로 재현 = 결과 동일
SELECT * FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);
--크다는 (>) 조건 = OR 조건 여러 개
--최소값보다 큰 급여 가진 직원
SELECT * FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 20);
SELECT * FROM EMP WHERE SAL > SOME (SELECT SAL FROM EMP WHERE DEPTNO = 20);
--단일행 서브쿼리로 재현 = 결과 동일
SELECT * FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20);
--ALL = 여러 결과 중에서 모두 만족해야 TRUE
-- = 조건 => 같다는 AND 조건 여러 개 이므로 사용 불가, 예) 한 사람이 하나의 급여를 가지고 있음
--부서별로 최대 급여 3개 값이 한 사람의 급여와 모두 같다는 조건을 성립할 수 없음
--작다는 (<) 조건 = AND 조건 여러 개
--950 < 1250 < 1500 < 1600 < 2850
--최소값보다 작은 급여 가진 직원
SELECT SAL FROM EMP WHERE DEPTNO = 30;
SELECT * FROM EMP WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);
--단일행 서브쿼리로 재현 = 결과 동일
SELECT * FROM EMP WHERE SAL < (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30);
--크다는 (>) 조건 = AND 조건 여러 개
--최대값보다 큰 급여 가진 직원
SELECT * FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);
--단일행 서브쿼리로 재현 = 결과 동일
SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);
--
--EXISTS 연산자 - 서브쿼리에 결과가 있으면 TRUE 없으면 FALSE
SELECT DNAME FROM DEPT WHERE DEPTNO = 10; --결과가 하나 존재하기 때문에 TRUE
--EXISTS 연산자에서 TRUE 로 사용이 되면 앞에 SQL 문이 정상적으로 실행이 됨
SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO = 10);
SELECT * FROM EMP;
SELECT DNAME FROM DEPT WHERE DEPTNO = 100; --결과가 존재하지 않기 때문에 FALSE
--EXISTS 연산자에서 FALSE 로 사용이 되면 앞에 SQL 문이 정상적으로 실행이 되지 않음 = 데이터 출력 없음
SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO = 100);
--다중열 서브쿼리 = 열이 여러 개 = 복수열 서브쿼리
--실무에서 꽤 많이 사용하므로 꼭 기억!
SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO; 
SELECT * FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO);
SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO; 
SELECT * FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);
--FROM 절 뒤에 사용하는 서브쿼리
--인라인 뷰 = SQL 문 실행 결과를 마치 테이블처럼 사용
SELECT * FROM EMP WHERE DEPTNO = 30;
SELECT * FROM DEPT;
SELECT E30.EMPNO, E30.ENAME, D.DEPTNO, D.DNAME 
    FROM (SELECT * FROM EMP WHERE DEPTNO = 30) E30, 
         (SELECT * FROM DEPT) D
    WHERE E30.DEPTNO = D.DEPTNO;
--인라인뷰에 사용하는 SQL 문이 길어진다면 WITH 절 사용하면 됨
--가독성을 높이기 위한 목적
--WITH 절에서 미리 내용을 파악할 수 있음 => SELECT 문을 읽으면 도움이 됨
WITH 
    E30 AS (SELECT * FROM EMP WHERE DEPTNO = 30), 
    D AS (SELECT * FROM DEPT)
SELECT E30.EMPNO, E30.ENAME, D.DEPTNO, D.DNAME 
    FROM E30, D
    WHERE E30.DEPTNO = D.DEPTNO;
--SELECT 절 뒤에서 사용하는 서브쿼리 = 스칼라 서브쿼리 = 열 처럼 사용
SELECT DNAME FROM EMP A, DEPT WHERE A.DEPTNO = DEPT.DEPTNO;
SELECT GRADE FROM EMP A, SALGRADE WHERE A.SAL BETWEEN LOSAL AND HISAL;
SELECT EMPNO, ENAME, JOB, SAL,
       (SELECT GRADE FROM SALGRADE WHERE A.SAL BETWEEN LOSAL AND HISAL) AS GRADE,
       DEPTNO,
       (SELECT DNAME FROM DEPT WHERE A.DEPTNO = DEPT.DEPTNO) AS DNAME
       FROM EMP A;
--

 

6. 데이터 조작어 DML

CREATE TABLE

DROP TABLE

INSERT INTO 테이블이름 (열1,열2,,,,) VALUES (값1, 값2,,,,)

INSERT 오류 발생의 경우

1) 열 개수와 값 개수 불일치

2) 데이터 타입의 불일치

3) 데이터 입력 범위 벗어남

 

UPDATE 변경할 테이블

SET 변경할 열 1 = 변경할 값 1, 변경할 열 2 = 변경할 값 2

 WHERE 조건

--데이터 조작어 DML, Data Manipulation Language
--데이터 수정하는 SQL 문장
--TCL 을 함께 사용해야 함(신중하게) - 최종변경 / 취소
--데이터 정의어 DDL, Data Definition Language + 자동 COMMIT
--원본을 복사한 실습용 테이블 만들기
CREATE TABLE DEPT_COPY
    AS SELECT * FROM DEPT;
SELECT * FROM DEPT_COPY;
--테이블 삭제
DROP TABLE DEPT_COPY;
SELECT * FROM DEPT_COPY;
--삭제 후 다시 테이블 만들기
CREATE TABLE DEPT_COPY
    AS SELECT * FROM DEPT;
SELECT * FROM DEPT_COPY;
--데이터 추가 INSERT
--INSERT INTO 테이블 이름 (열이름) VALUES (값)
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (50, 'DA', 'BUSAN');
SELECT * FROM DEPT_COPY;
ROLLBACK;
--전체 열에 추가하는 경우는 열 생략 가능
INSERT INTO DEPT_COPY VALUES (50, 'DA', 'BUSAN');
SELECT * FROM DEPT_COPY;
--데이터 타입 확인
DESC DEPT_COPY;
--INSERT 문의 오류 발생의 경우
--1) 열 개수와 값 개수의 불일치
SELECT * FROM DEPT_COPY;
--열 3개 값 2개 생긴 오류 = 3번째 값을 같이 넣어주시면 해결
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (60, 'DS');
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (60, 'DS', 'SEOUL');
SELECT * FROM DEPT_COPY;
ROLLBACK;
SELECT * FROM DEPT_COPY;
INSERT INTO DEPT_COPY (DEPTNO, DNAME) VALUES (60, 'DS');
SELECT * FROM DEPT_COPY;
--2) 데이터 타입의 불일치
--숫자를 넣어야 하는 열에 문자를 넣었기 때문에 발생한 오류
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES ('70번', 'DE', 'SUWON');
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (70, 'DE', 'SUWON');
SELECT * FROM DEPT_COPY;
--3) 데이터 입력 범위 벗어남
--2자리 숫자에 3자리 숫자를 넣어서 발생한 오류
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (100, 'DD', 'INCHEON');
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (80, 'DD', 'INCHEON');
SELECT * FROM DEPT_COPY;
--NULL 데이터 추가
SELECT * FROM DEPT_COPY;
ROLLBACK;
--명시적 입력
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (50, 'DA', NULL);
SELECT * FROM DEPT_COPY;
--암시적 입력
INSERT INTO DEPT_COPY (DEPTNO, DNAME) VALUES (60, 'DS');
SELECT * FROM DEPT_COPY;
--BLANK 데이터 추가
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC) VALUES (70, 'DE', '');
SELECT * FROM DEPT_COPY;
--개발자와 협력할 때는 비어있는 값이라는 의미를 확실히 전달하기 위해서는 NULL 용어 사용 권장
--날짜 데이터 INSERT
--실습 테이블 - EMP 테이블이 가지고 있는 구조만 복사 = 열만 복사 = 행은 복사하지 않음
CREATE TABLE EMP_COPY
    AS SELECT * FROM EMP WHERE 1 != 1;
SELECT * FROM EMP_COPY;
--팀장 2명 추가
INSERT INTO EMP_COPY (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
    VALUES (5555, '장원영', 'MANAGER', 7839, '2023/05/22', 4000, 0, 10);
SELECT * FROM EMP_COPY;
INSERT INTO EMP_COPY (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
    VALUES (7777, '차은우', 'MANAGER', 7839, '2023-05-22', 4000, 0, 10);
SELECT * FROM EMP_COPY;
--날짜 형식을 다르게 = 일/월/연
INSERT INTO EMP_COPY (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
    VALUES (8888, '김민종', 'MANAGER', 7839, '22/05/2023', 4000, 0, 10);
SELECT * FROM EMP_COPY;
--에러 수정 = TO_DATE 함수로 일/월/연 날짜 형식을 알려줘야 함
INSERT INTO EMP_COPY (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
    VALUES (8888, '김민종', 'MANAGER', 7839, TO_DATE('22/05/2023', 'DD/MM/YYYY'), 4000, 0, 10);
SELECT * FROM EMP_COPY;
--현재 날짜로 입력
INSERT INTO EMP_COPY (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
    VALUES (9999, '배성욱', 'ANALYST', 5555, SYSDATE, 3000, NULL, 10);
SELECT * FROM EMP_COPY;
--서브쿼리로 추가
SELECT * FROM EMP_COPY;
SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO 
    FROM EMP A, SALGRADE B 
    WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
      AND B.GRADE = 2;
INSERT INTO EMP_COPY (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    SELECT A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO 
    FROM EMP A, SALGRADE B 
    WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
      AND B.GRADE = 2;
SELECT * FROM EMP_COPY;
--데이터 변경 UPDATE
SELECT * FROM DEPT_COPY;
DROP TABLE DEPT_COPY;
CREATE TABLE DEPT_COPY
    AS SELECT * FROM DEPT;
SELECT * FROM DEPT_COPY;
--전체 = 일괄적으로 변경
UPDATE DEPT_COPY
    SET LOC = 'JEJU';
SELECT * FROM DEPT_COPY;
--취소
ROLLBACK;
SELECT * FROM DEPT_COPY;
--30번 부서 이름과 위치 변경
SELECT * FROM DEPT_COPY WHERE DEPTNO = 30;
SELECT DNAME, LOC FROM DEPT_COPY WHERE DEPTNO = 30;
UPDATE DEPT_COPY
    SET DNAME = 'DA',
        LOC = 'SEOUL'
    WHERE DEPTNO = 30;
SELECT * FROM DEPT_COPY;
SELECT * FROM DEPT_COPY WHERE DEPTNO = 30;
--수당 변경
SELECT * FROM EMP_COPY WHERE EMPNO = 9999;
UPDATE EMP_COPY
    SET COMM = 1000
    WHERE EMPNO = 9999;
SELECT * FROM EMP_COPY WHERE EMPNO = 9999;
--서브쿼리 이용하여 변경
SELECT * FROM DEPT_COPY WHERE DEPTNO = 30;
SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 30;
--DA SEOUL => SALES	CHICAGO 변경
UPDATE DEPT_COPY
    SET (DNAME, LOC) = (SELECT DNAME, LOC FROM DEPT WHERE DEPTNO = 30)
    WHERE DEPTNO = 30;
SELECT * FROM DEPT_COPY WHERE DEPTNO = 30;
--서브쿼리를 조건절에 사용
SELECT * FROM DEPT_COPY;
SELECT DEPTNO FROM DEPT_COPY WHERE DNAME = 'RESEARCH';
--DALLAS => BUSAN 변경
UPDATE DEPT_COPY
    SET LOC = 'BUSAN'
    WHERE DEPTNO = (SELECT DEPTNO FROM DEPT_COPY WHERE DNAME = 'RESEARCH');
SELECT * FROM DEPT_COPY;
--데이터 삭제 DELETE
SELECT * FROM EMP_COPY;
DROP TABLE EMP_COPY;
CREATE TABLE EMP_COPY
    AS SELECT * FROM EMP; 
SELECT * FROM EMP_COPY;
--데이터 일부 삭제
SELECT * FROM EMP_COPY WHERE JOB = 'CLERK';
DELETE EMP_COPY WHERE JOB = 'CLERK';
SELECT * FROM EMP_COPY WHERE JOB = 'CLERK';
--서브쿼리를 이용하여 데이터 일부 삭제
SELECT * FROM EMP_COPY;
SELECT * FROM SALGRADE;
SELECT A.EMPNO FROM EMP_COPY A, SALGRADE B 
    WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
      AND B.GRADE = 2;
SELECT * FROM EMP_COPY WHERE EMPNO IN (SELECT A.EMPNO FROM EMP_COPY A, SALGRADE B 
                                            WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
                                              AND B.GRADE = 2);
DELETE EMP_COPY WHERE EMPNO IN (SELECT A.EMPNO FROM EMP_COPY A, SALGRADE B 
                                            WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
                                              AND B.GRADE = 2);
SELECT * FROM EMP_COPY WHERE EMPNO IN (SELECT A.EMPNO FROM EMP_COPY A, SALGRADE B 
                                            WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
                                              AND B.GRADE = 2);
--테이블의 행 모두 삭제 = 데이터 전체 삭제
SELECT * FROM EMP_COPY;
DELETE EMP_COPY;
SELECT * FROM EMP_COPY;
--테이블 행 + 열 = 테이블 삭제
SELECT * FROM DEPT_COPY;
DROP TABLE DEPT_COPY;
SELECT * FROM DEPT_COPY;
--

 

7. 트랜잭션 제어 TCL

트랜잭션 : 더 이상 분할할 수 없는 최소 수행 단위, 데이터 유실을 막으려면 둘 다 수행이 되거나 되지 않아야 하므로 트랜잭션으로 구성되어야 함

COMMIT 최종 변경

ROLLBACK 취소. 수정한 내용 되돌리고 싶을 경우

 

세션 : 어떤 활동을 위한 시간이나 기간, 한 계정이 여러 세션을 사용할 수 있음, 한 세션 안에서 하나 이상의 트랜잭션이 있음

읽기 일관성 : 데이터베이스는 여러 사람이 동시에 접근, 데이터 변경 중인 세션 말고는 원래대로 보여줌

 

수정 중인 데이터 접근을 막는 LOCK

조작 중인 데이터는 트랜잭션이 완료되기 전까지 다른 세션에서 조작할 수 없는 상태가 됨 = 데이터 잠기는 것

조작 중인 데이터를 다른 세션은 조작 할 수 없도록 접근을 보류시키는 것

--트랜잭션 제어 TCL, Transaction Control Language
--COMMIT: 최종 변경
--ROLLBACK: 취소
--실습 테이블 생성
CREATE TABLE DEPT_COPY
    AS SELECT * FROM DEPT;
SELECT * FROM DEPT_COPY;
--3 가지 DML 실행 후 취소
INSERT INTO DEPT_COPY VALUES (50, 'DA', 'SEOUL');
SELECT * FROM DEPT_COPY;
UPDATE DEPT_COPY
    SET LOC = 'SUWON'
    WHERE DEPTNO = 50;
SELECT * FROM DEPT_COPY;
DELETE DEPT_COPY WHERE DNAME = 'ACCOUNTING';
SELECT * FROM DEPT_COPY;
ROLLBACK;
SELECT * FROM DEPT_COPY;
--3 가지 DML 실행 후 최종 변경
SELECT * FROM DEPT_COPY;
INSERT INTO DEPT_COPY VALUES (50, 'DA', 'SEOUL');
SELECT * FROM DEPT_COPY;
UPDATE DEPT_COPY
    SET LOC = 'SUWON'
    WHERE DEPTNO = 50;
SELECT * FROM DEPT_COPY;
DELETE DEPT_COPY WHERE DNAME = 'ACCOUNTING';
SELECT * FROM DEPT_COPY;
COMMIT;
SELECT * FROM DEPT_COPY;
--읽기 일관성
--편집은 sqldeveloper 에서 하고 붙여넣기를 sqlplus 에 할 것
--sqldeveloper, sqlplus 각자 실행
SELECT * FROM DEPT_COPY;
--현재 양쪽에 보여지는 결과는 동일함
--sqldeveloper 에서 데이터 삭제
DELETE DEPT_COPY WHERE DEPTNO = 50;
--sqldeveloper, sqlplus 각자 실행
SELECT * FROM DEPT_COPY;
--sqldeveloper 에서 최종 변경
COMMIT;
--sqldeveloper, sqlplus 각자 실행
SELECT * FROM DEPT_COPY;
--수정 중인 데이터 접근을 막는 LOCK
--sqldeveloper, sqlplus 각자 실행
SELECT * FROM DEPT_COPY;
--sqldeveloper 에서 데이터 변경
--sqldeveloper, sqlplus 각자 실행
UPDATE DEPT_COPY 
    SET DNAME = 'DA'
    WHERE DEPTNO = 30;
--sqldeveloper 에서 최종 변경
COMMIT;

 

8. 데이터 정의어 DDL

DML과 달리, 자동으로 COMMIT 수행됨

ROLLBACK을 통한 실행 취소가 불가능하기 때문에 사용할 때 주의를 기울여야 함

 

CREATE

ALTER

RENAME

TRUCATE

DROP

--데이터 정의어 DDL, Data Definition Language = 자동 COMMIT
--테이블 생성
--1) 열 이름과 자료형을 정의하면서 테이블 생성
SELECT * FROM EMP_COPY;
DROP TABLE EMP_COPY;
SELECT * FROM EMP_COPY;
--EMP_COPY 테이블 만들기
SELECT * FROM EMP;
CREATE TABLE EMP_COPY(
    EMPNO    NUMBER(4),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2)
);
--2) 전체 행과 열을 복사하여 테이블 생성 = 열 구조 + 데이터
SELECT * FROM DEPT_COPY;
DROP TABLE DEPT_COPY;
CREATE TABLE DEPT_COPY
    AS SELECT * FROM DEPT;
SELECT * FROM DEPT_COPY;
--3) 일부 행과 열을 복사하여 테이블 생성 = 열 구조 + 일부 데이터
CREATE TABLE DEPT_COPY_30
    AS SELECT * FROM DEPT WHERE DEPTNO = 30;
SELECT * FROM DEPT_COPY_30;
--4) 열을 복사하여 테이블 생성 = 열 구조 = 행이 없음
SELECT A.EMPNO, A.ENAME, A.HIREDATE, A.SAL, B.DEPTNO, B.DNAME 
    FROM EMP A, DEPT B 
    WHERE A.DEPTNO = B.DEPTNO;
SELECT A.EMPNO, A.ENAME, A.HIREDATE, A.SAL, B.DEPTNO, B.DNAME 
    FROM EMP A, DEPT B 
    WHERE 1 != 1;
CREATE TABLE EMP_DEPT
    AS SELECT A.EMPNO, A.ENAME, A.HIREDATE, A.SAL, B.DEPTNO, B.DNAME 
    FROM EMP A, DEPT B 
    WHERE 1 != 1;
SELECT * FROM EMP_DEPT;
--테이블 변경
CREATE TABLE EMP_ALTER
    AS SELECT * FROM EMP;
SELECT * FROM EMP_ALTER;
--1) 열 추가
ALTER TABLE EMP_ALTER
    ADD PHONE VARCHAR2(15);
SELECT * FROM EMP_ALTER;
--2) 열 이름 변경 = RENAME 변경 전 이름 TO 변경 후 이름
ALTER TABLE EMP_ALTER
    RENAME COLUMN PHONE TO CP;
SELECT * FROM EMP_ALTER;
--3) 열의 데이터 타입 변경
DESC EMP_ALTER;
ALTER TABLE EMP_ALTER
    MODIFY EMPNO NUMBER(5);
DESC EMP_ALTER;
--4) 열 삭제
ALTER TABLE EMP_ALTER
    DROP COLUMN CP;
SELECT * FROM EMP_ALTER;
--테이블 이름 변경
RENAME EMP_ALTER TO EMP_ALTER_AFTER;
DESC EMP_ALTER;
SELECT * FROM EMP_ALTER;
DESC EMP_ALTER_AFTER;
SELECT * FROM EMP_ALTER_AFTER;
--데이터 삭제 = 테이블 비우기 = 열 구조는 남아 있고 행이 모두 삭제
TRUNCATE TABLE EMP_ALTER_AFTER;
SELECT * FROM EMP_ALTER_AFTER;
--테이블 삭제
DROP TABLE EMP_ALTER_AFTER;
SELECT * FROM EMP_ALTER_AFTER;
--

 

 

9. 오라클 데이터베이스 테이블

--객체
--오라클 데이터베이스 테이블
--1) 사용자 테이블
--2) 데이터 사전
--scott 계정에서 보기
SELECT * FROM DICTIONARY;
SELECT * FROM DICT;
--SCOTT 계정이 소유하고 있는 테이블
SELECT * FROM USER_TABLES;
--모든 사용자가 소유하고 있는 테이블
SELECT * FROM ALL_TABLES;
--관리자 계정이 있어야 볼 수 있는 내용
SELECT * FROM DBA_TABLES;
--분명히 존재하는데도 불구하고 SCOTT 계정에서 보면 존재하지 않는다고 뜨는 이유
--보안의 문제: 존재 여부를 알려주지 않아야 안전
--존재하지만 권한이 없다고 알려주면 테이블이 존재한다는 정보를 알려주는 꼴
--관리자 계정으로 접속해서 다시 확인하기
SELECT * FROM DBA_TABLES;
--관리자 계정으로 사용자 정보 확인
SELECT * FROM DBA_USERS WHERE USERNAME = 'SCOTT';
--다시 scott 계정으로 돌아오기
--인덱스
--DB 에 있는 데이터를 더 빠르게 찾도록 도와주는 객체
--인덱스가 없으면 전체를 다 찾음
--인덱스 정보 확인
SELECT * FROM USER_INDEXES;
--인덱스 열 정보 확인
SELECT * FROM USER_IND_COLUMNS;
--인덱스 생성
CREATE INDEX IDX_EMP_SAL ON EMP(SAL);
--방금 생성한 인덱스 열 정보 확인
SELECT * FROM USER_IND_COLUMNS;
--인덱스 삭제
DROP INDEX IDX_EMP_SAL;
--인덱스 삭제되었는지 확인
SELECT * FROM USER_IND_COLUMNS;
--뷰 - 테이블처럼 사용할 수 있는 객체
--사용 목적: 1) 편리성, 2) 보안성
--뷰 생성 권한이 필요
--관리자 계정으로 권한 부여
--명령 프롬프트에서 실행
sqlplus system/oracle
--권한 부여 = GRANT 권한 TO 계정
GRANT CREATE VIEW TO SCOTT;
--SCOTT 계정으로 실행하기
SELECT * FROM EMP WHERE DEPTNO = 10;
CREATE VIEW VW_EMP10
    AS (SELECT * FROM EMP WHERE DEPTNO = 10);
--뷰 생성 확인
SELECT * FROM USER_VIEWS;
--SQLPLUS 에서 다시 확인
sqlplus scott/tiger
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
--우리가 만든 뷰 사용
--SELECT * FROM EMP WHERE DEPTNO = 10; => VW_EMP10 로 대체하여 사용
SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM VW_EMP10;
--뷰 삭제
DROP VIEW VW_EMP10;
--삭제가 되었는지 확인
SELECT * FROM VW_EMP10;
SELECT * FROM USER_VIEWS;
--ROWNUM = 테이블의 열이 아닌 가상의 열
SELECT ROWNUM, A.* FROM EMP A;
--급여가 높은 순으로 정렬
SELECT ROWNUM, A.* FROM EMP A ORDER BY SAL DESC;
--급여가 높은 순으로 정렬해서 ROWNUM 붙이려면 뷰를 사용해야 함
SELECT * FROM EMP ORDER BY SAL DESC;
--인라인뷰
SELECT ROWNUM, A.* FROM (SELECT * FROM EMP ORDER BY SAL DESC) A;
--WITH 절로 변환
WITH A AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, A.* FROM A;
--급여가 높은 상위 5개 보기
--인라인뷰
SELECT ROWNUM, A.* 
    FROM (SELECT * FROM EMP ORDER BY SAL DESC) A
    WHERE ROWNUM <= 5;
--WITH 절로 변환
WITH A AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, A.* FROM A WHERE ROWNUM <= 5;
--
--시퀀스 = 일련번호 생성하는 객체 = 규칙을 설정
--DEPT 테이블의 열 구조만 복사해서 테이블 생성
DROP TABLE DEPT_COPY;
CREATE TABLE DEPT_COPY
    AS SELECT * FROM DEPT WHERE 1 != 1;
SELECT * FROM DEPT_COPY;
--시퀀스 생성
CREATE SEQUENCE SEQ_DEPT
    INCREMENT BY 10
    START WITH 0
    MAXVALUE 90
    MINVALUE 0
    NOCYCLE
    CACHE 2;
--시퀀스 확인
SELECT * FROM USER_SEQUENCES;
--시퀀스 이용해서 데이터 추가 => 9번 반복
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC)
    VALUES (SEQ_DEPT.NEXTVAL, 'DA', 'SEOUL');
SELECT * FROM DEPT_COPY;
--마지막으로 한번 더 해보면 이미 최대값에 도달했기 때문 추가 불가
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC)
    VALUES (SEQ_DEPT.NEXTVAL, 'DA', 'SEOUL');
--시퀀스 사용 방법
SELECT SEQ_DEPT.CURRVAL FROM DUAL;
SELECT SEQ_DEPT.NEXTVAL FROM DUAL;
--시퀀스 변경
ALTER SEQUENCE SEQ_DEPT
    INCREMENT BY 3
    MAXVALUE 99
    CYCLE;
--데이터 입력 상태 먼저 확인
SELECT * FROM DEPT_COPY;
--3번 실행 = 최대값 99에 도달
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC)
    VALUES (SEQ_DEPT.NEXTVAL, 'DA', 'SEOUL');
SELECT * FROM DEPT_COPY;
--3번 더 실행 = CYCLE 적용 = 최대값에 도달해도 최소값으로 다시 돌아감
INSERT INTO DEPT_COPY (DEPTNO, DNAME, LOC)
    VALUES (SEQ_DEPT.NEXTVAL, 'DA', 'SEOUL');
SELECT * FROM DEPT_COPY;
--시퀀스 삭제
DROP SEQUENCE SEQ_DEPT;
--삭제가 되었는지 확인
SELECT * FROM USER_SEQUENCES;
--시노님 = 공식적인 별칭 = 일회성이 아님 = 시노님 객체 삭제하기 전까지 계속해서 사용 가능
--SELECT 열 이름 AS 별칭 ~ => 일회성 별칭과 다름
--시노님 생성 권한이 필요
--관리자 계정으로 권한 부여
--명령 프롬프트에서 실행
sqlplus system/oracle
--권한 부여 = GRANT 권한 TO 계정
GRANT CREATE SYNONYM TO SCOTT;
GRANT CREATE PUBLIC SYNONYM TO SCOTT;
--SCOTT 계정으로 실행하기
CREATE SYNONYM E FOR EMP;
--시노님 사용
--EMP 대신에 E 라는 테이블 이름으로 사용 가능
SELECT * FROM EMP;
SELECT * FROM E;
--시노님 삭제
DROP SYNONYM E;
--삭제가 되었는지 확인
SELECT * FROM E;
--원래 이름 EMP 는 사용 가능
SELECT * FROM EMP;
--
--제약 조건
--1) NOT NULL = 빈값 허용하지 않음 = 빈값 불가
CREATE TABLE TABLE_NOTNULL(
    LOG_ID VARCHAR2(15) NOT NULL,
    LOG_PW VARCHAR2(15) NOT NULL,
    CP VARCHAR(15)
);
SELECT * FROM TABLE_NOTNULL;
DESC TABLE_NOTNULL;
--널값 추가 => 널값 불가 제약조건으로 인해 추가 불가
INSERT INTO TABLE_NOTNULL (LOG_ID, LOG_PW, CP)
    VALUES ('TEST_ID_1', NULL, '010-1234-5678');
INSERT INTO TABLE_NOTNULL (LOG_ID, CP)
    VALUES ('TEST_ID_1', '010-1234-5678');
--전화번호에 널값 추가 => 제약조건이 없기 때문에 입력 가능
INSERT INTO TABLE_NOTNULL (LOG_ID, LOG_PW)
    VALUES ('TEST_ID_1', 'TEST_PW_1');
SELECT * FROM TABLE_NOTNULL;
--널값으로 변경 => 널값 불가 제약조건으로 인해 변경 불가
UPDATE TABLE_NOTNULL
    SET LOG_PW = NULL
    WHERE LOG_ID = 'TEST_ID_1';
--제약 조건 확인
--P = PRIMARY KEY
--R = REFERENCE
--C = CHECK / NOT NULL
--U = UNIQUE
SELECT * FROM USER_CONSTRAINTS;
--제약조건 이름 지정
CREATE TABLE TABLE_NOTNULL1(
    LOG_ID VARCHAR2(15) CONSTRAINT TBNN1_LOGID_NN NOT NULL,
    LOG_PW VARCHAR2(15) CONSTRAINT TBNN1_LOGPW_NN NOT NULL,
    CP VARCHAR(15)
);
--제약 조건 확인
SELECT * FROM USER_CONSTRAINTS;
--제약 조건 변경(추가) => 이미 널값이 있기 때문에 변경 안됨
ALTER TABLE TABLE_NOTNULL
    MODIFY (CP NOT NULL);
--전화 번호 널값을 다른 값으로 변경
SELECT * FROM TABLE_NOTNULL;
UPDATE TABLE_NOTNULL
    SET CP = '010-1234-5678'
    WHERE LOG_ID = 'TEST_ID_1';
SELECT * FROM TABLE_NOTNULL;
--제약 조건 변경(추가) => 널값이 없기 때문에 변경 가능
ALTER TABLE TABLE_NOTNULL
    MODIFY (CP NOT NULL);
--혹시 TABLE_NOTNULL1 테이블에 전화번호 제약조건을 만들었을 경우에는 지우고 실습
SELECT * FROM USER_CONSTRAINTS;
ALTER TABLE TABLE_NOTNULL1 DROP CONSTRAINT SYS_C0011057;
--제약 조건 변경(추가)
ALTER TABLE TABLE_NOTNULL1
    MODIFY (CP CONSTRAINT TBNN1_CP_NN NOT NULL);
--제약 조건 확인
SELECT * FROM USER_CONSTRAINTS;
DESC TABLE_NOTNULL;
DESC TABLE_NOTNULL1;
--제약 조건 이름 변경
ALTER TABLE TABLE_NOTNULL1
    RENAME CONSTRAINT TBNN1_CP_NN TO TBNN1_CP_NN_AFTER;
--제약 조건 확인
SELECT * FROM USER_CONSTRAINTS;
--제약 조건 삭제
ALTER TABLE TABLE_NOTNULL1
    DROP CONSTRAINT TBNN1_CP_NN_AFTER;
--제약 조건 확인
SELECT * FROM USER_CONSTRAINTS;
--
--UNIQUE = 중복 불가
--이름 지정하여 제약조건 만들기
CREATE TABLE TB_UNQ(
    LOG_ID VARCHAR2(15) CONSTRAINT TBUNQ_LOGID_UNQ UNIQUE,
    LOG_PW VARCHAR2(15) CONSTRAINT TBUNQ_LOGPW_UNQ UNIQUE,
    CP     VARCHAR2(15)
);
--제약 조건 확인
SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME LIKE 'TBUNQ%';
--데이터 추가
INSERT INTO TB_UNQ (LOG_ID, LOG_PW, CP)
    VALUES ('TEST_ID_1', 'TEST_PW_1', '010-1234-5678');
--한번 더 실행 => ID 와 PW 는 중복 불가라는 제약조건으로 인해 추가 불가
INSERT INTO TB_UNQ (LOG_ID, LOG_PW, CP)
    VALUES ('TEST_ID_1', 'TEST_PW_1', '010-1234-5678');
--전화번호는 제약조건이 없기 때문에 중복 입력 가능
INSERT INTO TB_UNQ (LOG_ID, LOG_PW, CP)
    VALUES ('TEST_ID_2', 'TEST_PW_2', '010-1234-5678');
SELECT * FROM TB_UNQ;
--널값 추가 => UNIQUE 는 중복 불가이지 널값 불가 아님 = 널값 입력 가능
INSERT INTO TB_UNQ (LOG_ID, LOG_PW, CP)
    VALUES (NULL, 'TEST_PW_3', '010-1234-5678');
SELECT * FROM TB_UNQ;
--널값은 중복 대상 아님 & UNIQUE 는 중복 불가이지 널값 불가 아님 = 널값 입력 가능
INSERT INTO TB_UNQ (LOG_PW, CP)
    VALUES ('TEST_PW_4', '010-1234-5678');
SELECT * FROM TB_UNQ;
--데이터 변경 => 중복 불가 제약 조건으로 인해 중복 데이터 변경 불가
UPDATE TB_UNQ
    SET LOG_ID = 'TEST_ID_1'
    WHERE LOG_PW = 'TEST_PW_3';
--중복 불가 제약 조건이 있기 때문에 중복 데이터가 아닌 값으로 변경
UPDATE TB_UNQ
    SET LOG_ID = 'TEST_ID_3'
    WHERE LOG_PW = 'TEST_PW_3';
SELECT * FROM TB_UNQ;
--기본키 = PRIMARY KEY = NOT NULL + UNIQUE
CREATE TABLE TB_PK(
    LOG_ID VARCHAR2(15) CONSTRAINT TBPK_LOGID_PK PRIMARY KEY,
    LOG_PW VARCHAR2(15) CONSTRAINT TBPK_LOGPW_NN NOT NULL,
    CP VARCHAR2(15)
);
--데이터 추가
INSERT INTO TB_PK (LOG_ID, LOG_PW, CP)
    VALUES ('TEST_ID_1', 'TEST_PW_1', '010-1234-5678');
SELECT * FROM TB_PK;
--한번 더 실행 = 중복 데이터 입력 안됨 => ID 는 PK = 중복 불가 + 널값 불가
INSERT INTO TB_PK (LOG_ID, LOG_PW, CP)
    VALUES ('TEST_ID_1', 'TEST_PW_1', '010-1234-5678');
--PW 는 널값 불가이기 때문에 중복 데이터는 입력 가능
INSERT INTO TB_PK (LOG_ID, LOG_PW, CP)
    VALUES ('TEST_ID_2', 'TEST_PW_1', '010-1234-5678');
SELECT * FROM TB_PK;
--널값 추가 = 널값 입력 안됨 => ID 는 PK = 중복 불가 + 널값 불가
INSERT INTO TB_PK (LOG_ID, LOG_PW, CP)
    VALUES (NULL, 'TEST_PW_1', '010-1234-5678');
--외래키 = FOREIGN KEY = 참조키 = 다른 테이블의 기본키를 참조
--EMP, DEPT 테이블이 가지고 있는 제약조건 확인
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('EMP', 'DEPT');
--EMP 테이블에 DEPT 테이블에 없는 부서 번호를 입력
SELECT * FROM EMP;
SELECT * FROM DEPT;
--참조할 수 있는 값이 없기 때문에 입력 안됨 = 부서 테이블에 90번 부서 데이터가 없음
--부서 테이블에 90번 데이터 먼저 입력한 다음에 사원 정보 입력해야 정상적으로 추가 가능
INSERT INTO EMP
    VALUES (9999, 'QUEEN', 'VICE', 7839, '2023-05-23', 5000, NULL, 90);
--테이블 생성
CREATE TABLE DEPT_FK(
    DEPTNO NUMBER(2) CONSTRAINT DEPTFK_DEPTNO_FK PRIMARY KEY,
    DNAME  VARCHAR2(15)
);
CREATE TABLE EMP_FK(
    EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10),
    DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO)
);
--사원 추가 = 참조할 수 있는 값이 없기 때문에 추가 불가
--참조할 수 있는 값을 먼저 만들어야 함 = 부서 추가
INSERT INTO EMP_FK
    VALUES (9999, 'QUEEN', 10);
SELECT * FROM DEPT_FK;
--부서 추가
INSERT INTO DEPT_FK 
    VALUES (10, 'DA');
SELECT * FROM DEPT_FK;
--다시 사원 추가 = 참조할 수 있는 값이 있기 때문에 추가 가능
INSERT INTO EMP_FK
    VALUES (9999, 'QUEEN', 10);
--외래키가 있을 때 데이터 입력 순서
--1) 외래키 생성
--2) 참조할 부서 데이터 입력 (예, 부서 10번)
--3) 직원 데이터 입력 가능 (예, 직원 QUEEN)
--
--부서 삭제
SELECT * FROM DEPT_FK;
SELECT * FROM EMP_FK;
--10번 부서를 참조하고 있는 직원이 있기 때문에 삭제 불가
--사원을 먼저 삭제
DELETE DEPT_FK WHERE DEPTNO = 10;
--사원 삭제
DELETE EMP_FK WHERE EMPNO = 9999;
SELECT * FROM EMP_FK;
--다시 부서 삭제 => 더이상 10번 부서번호를 참조하고 있는 사원이 없기 때문 
DELETE DEPT_FK WHERE DEPTNO = 10;
SELECT * FROM DEPT_FK;
--외래키가 있을 때 데이터 삭제 순서
--1) 참조하고 있는 데이터 = 10번 부서 번호를 참조하고 있는 직원 데이터 삭제
--2) 부서 데이터 삭제
--