DataBase 수업 기록(8.5) DML예시

-- 테이블 삭제
DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;

-- DEPARTMENT 테이블 생성
CREATE TABLE DEPARTMENT(
    DEPT_NO   NUMBER            NOT NULL,
    DEPT_NAME VARCHAR2(15 BYTE) NOT NULL,
    LOCATION  VARCHAR2(15 BYTE) NOT NULL
);

-- EMPLOYEE 테이블 생성
CREATE TABLE EMPLOYEE(
    EMP_NO    NUMBER            NOT NULL,
    NAME      VARCHAR2(20 BYTE) NOT NULL,
    DEPART    NUMBER            NULL,
    POSITION  VARCHAR2(20 BYTE) NULL,
    GENDER    CHAR(2)           NULL,
    HIRE_DATE DATE              NULL, 
    SALARY    NUMBER            NULL
);

-- 기본키
ALTER TABLE DEPARTMENT 
    ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY(DEPT_NO);
ALTER TABLE EMPLOYEE 
    ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY(EMP_NO);

-- 외래키
ALTER TABLE EMPLOYEE 
    ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENT FOREIGN KEY(DEPART) 
        REFERENCES DEPARTMENT(DEPT_NO)
            ON DELETE SET NULL;

-- 부서 테이블에 행(Row) 삽입
INSERT INTO DEPARTMENT
    (DEPT_NO, DEPT_NAME, LOCATION)
VALUES
    (1, '영업부', '대구');
INSERT INTO DEPARTMENT
    (DEPT_NO, DEPT_NAME, LOCATION)
VALUES
    (2, '인사부', '서울');
INSERT INTO DEPARTMENT
    (DEPT_NO, DEPT_NAME, LOCATION)
VALUES
    (3, '총무부', '대구');
INSERT INTO DEPARTMENT
    (DEPT_NO, DEPT_NAME, LOCATION)
VALUES
    (4, '기획부', '서울');

-- 작업의 완료
COMMIT;


-- 사원 테이블에 행(Row) 삽입
-- 자식 테이블(관계에서 FK를 가진 테이블)은 참조 무결성에 위배되지 않는 데이터만 삽입 가능
INSERT INTO 
    EMPLOYEE
VALUES
    (1001, '구창민', 1, '과장', 'M', '95/05/01', 5000000);
INSERT INTO
    EMPLOYEE
VALUES
    (1002, '김민서', 1, '사원', 'F', '17/09/01', 2000000);
INSERT INTO
    EMPLOYEE
VALUES
    (1003, '이은영', 2, '부장', NULL, '90-09-01', 5500000);
INSERT INTO
    EMPLOYEE
VALUES
    (1004, '한성일', 2, '과장', 'M', '93-04-01', 5000000);

COMMIT;




-- 테이블 수정
-- 1. 부서번호가 1인 부서의 지역을 '인천'으로 수정
UPDATE DEPARTMENT
   SET LOCATION = '인천'
 WHERE DEPT_NO = 1;

-- 2. 부서번호가 3인 부서명을 '전략부', 지역을 '부산'으로 수정
UPDATE DEPARTMENT
   SET DEPT_NAME = '전략부'
     , LOCATION = '부산'
 WHERE DEPT_NO = 3;

/*
    부서  -  사원
    1     -  1
    2     -  1
    3     -  2
    4     -  2
    5     -  3
*/

-- 3. 부서번호가 3인 부서의 부서번호를 6으로 수정
-- DEPARTMENT의 부서번호를 EMPLOYEE가 참조중이므로 수정이 안 됨
-- 해결책
-- 1. 외래키 일시중지
-- 2. 수정
-- 3. 외래키 재시작

ALTER TABLE EMPLOYEE
    DISABLE CONSTRAINT FK_EMPLOYEE_DEPARTMENT;  -- 외래키 중지

UPDATE EMPLOYEE
   SET DEPART = 6
 WHERE DEPART = 3;

UPDATE DEPARTMENT
   SET DEPT_NO = 6
 WHERE DEPT_NO = 3;

ALTER TABLE EMPLOYEE
    ENABLE CONSTRAINT FK_EMPLOYEE_DEPARTMENT;  -- 외래키 시작

-- 4. 부서번호 1인 사원들의 월급을 100000 인상
UPDATE EMPLOYEE
   SET SALARY = SALARY + 100000
 WHERE DEPART = 1;

-- 5. 직급이 '과장'인 사원들의 월급을 10% 인상
UPDATE EMPLOYEE
   SET SALARY = SALARY * 1.1
 WHERE POSITION = '과장';



-- 테이블 삭제

-- 1. 부서번호가 4인 부서를 삭제
-- 부서번호가 4인 행(ROW)을 참조하는 사원이 없으므로 정상 삭제
DELETE
  FROM DEPARTMENT
 WHERE DEPT_NO = 4;

-- 2. 부서번호가 1인 부서를 삭제
-- 외래키 옵션으로 ON DELETE SET NULL 처리를 하였기 때문에
-- 부서번호가 1인 행(ROW)의 소속부서가 NULL 값으로 함께 변경
DELETE
  FROM DEPARTMENT
 WHERE DEPT_NO = 1;

 

DROP TABLE LECTURE;
DROP TABLE ENROLL;
DROP TABLE STUDENT;
DROP TABLE PROFESSOR;
DROP TABLE COURSE;

-- 1. 학번 테이블
CREATE TABLE STUDENT(
    STU_NO      NUMBER(5)          NOT NULL,
    STU_NAME    VARCHAR2(20 BYTE)  NULL,
    STU_ADDS    VARCHAR2(100 BYTE) NULL,
    STU_LEV     NUMBER(1)          NULL,
    PRO_NO      NUMBER(5)          NOT NULL
);

-- 2. 교수 테이블
CREATE TABLE PROFESSOR(
    PRO_NO    NUMBER(5)         NOT NULL,
    PRO_NAME  VARCHAR2(20 BYTE) NULL,
    PRO_MAJOR VARCHAR2(25 BYTE) NULL
);

-- 3. 과목 테이블
CREATE TABLE COURSE(
    COU_NO    NUMBER(5)         NOT NULL,
    COU_NAME  VARCHAR2(20 BYTE) NULL,
    COU_SCORE NUMBER(3,2)       NULL
);

-- 4. 수강신청 테이블
CREATE TABLE ENROLL(
    ENR_NO NUMBER(5) NOT NULL,
    STU_NO NUMBER(5) NOT NULL,
    COU_NO NUMBER(2) NOT NULL,
    ENR_DATE DATE    NULL
);

-- 5. 강의 테이블
CREATE TABLE LECTURE(
    LEC_NO   NUMBER(5)         NOT NULL,
    PRO_NO   NUMBER(5)         NOT NULL,
    ENR_NO   NUMBER(5)         NOT NULL,
    LEC_NAME VARCHAR2(25 BYTE) NULL,
    LEC_ROOM VARCHAR2(15 BYTE) NULL
);

-- 기본키 지정
ALTER TABLE STUDENT   ADD CONSTRAINT PK_STUDENT   PRIMARY KEY(STU_NO);
ALTER TABLE PROFESSOR ADD CONSTRAINT PK_PROFESSOR PRIMARY KEY(PRO_NO);
ALTER TABLE COURSE    ADD CONSTRAINT PK_COURSE    PRIMARY KEY(COU_NO);
ALTER TABLE ENROLL    ADD CONSTRAINT PK_ENROLL    PRIMARY KEY(ENR_NO);
ALTER TABLE LECTURE   ADD CONSTRAINT PK_LECTURE   PRIMARY KEY(LEC_NO);

-- 외래키 지정
ALTER TABLE ENROLL ADD CONSTRAINT FK_ENROLL_STUDENT FOREIGN KEY(STU_NO)
    REFERENCES STUDENT(STU_NO)
        ON DELETE CASCADE;
ALTER TABLE ENROLL ADD CONSTRAINT FK_ENROLL_COURSE FOREIGN KEY(COU_NO)
    REFERENCES COURSE(COU_NO)
        ON DELETE CASCADE;
ALTER TABLE LECTURE ADD CONSTRAINT FK_LECTURE_ENROLL FOREIGN KEY(ENR_NO)
    REFERENCES ENROLL(ENR_NO)
        ON DELETE SET NULL;
ALTER TABLE LECTURE ADD CONSTRAINT FK_LECTURE_PROFESSOR FOREIGN KEY(PRO_NO)
    REFERENCES PROFESSOR(PRO_NO)
        ON DELETE SET NULL;
ALTER TABLE STUDENT ADD CONSTRAINT FK_STUDENT_PROFESSOR FOREIGN KEY(PRO_NO)
    REFERENCES PROFESSOR(PRO_NO)
        ON DELETE SET NULL;