DML(학생,교수,강의,수강신청,과목)

-- 테이블 삭제는 생성의 역순!
DROP TABLE ENROLL_TBL;
DROP TABLE LECTURE_TBL;
DROP TABLE STUDENT_TBL;
DROP TABLE COURSE_TBL;
DROP TABLE PROFESSOR_TBL;

-- 테이블 생성
-- 1. PROFESSOR_TBL 테이블
CREATE TABLE PROFESSOR_TBL (
    P_NO    VARCHAR2(5 BYTE)  NOT NULL  -- 기본키
  , P_NAME  VARCHAR2(30 BYTE) NULL
  , P_MAJOR VARCHAR2(30 BYTE) NULL
  , CONSTRAINT PK_PROF PRIMARY KEY(P_NO)
);

-- 2. COURSE_TBL 테이블
CREATE TABLE COURSE_TBL (
    C_NO   VARCHAR2(5 BYTE)  NOT NULL  -- 기본키
  , C_NAME VARCHAR2(30 BYTE) NULL
  , C_UNIT NUMBER(1)         NULL
  , CONSTRAINT PK_COURSE PRIMARY KEY(C_NO)
);

-- 3. STUDENT_TBL 테이블
CREATE TABLE STUDENT_TBL (
    S_NO       VARCHAR2(5 BYTE)   NOT NULL  -- 기본키
  , S_NAME     VARCHAR2(100 BYTE) NULL
  , S_ADDRESS  VARCHAR2(100 BYTE) NULL
  , S_GRADE_NO NUMBER(1)          NULL
  , P_NO       VARCHAR2(5 BYTE)   NOT NULL  -- PROFESSOR_TBL 테이블의 P_NO 칼럼을 참조하는 외래키
  , CONSTRAINT PK_STUDENT PRIMARY KEY(S_NO)
  , CONSTRAINT FK_PROF_STUDENT FOREIGN KEY(P_NO) REFERENCES PROFESSOR_TBL(P_NO) ON DELETE CASCADE
);

-- 4. LECTURE_TBL 테이블
CREATE TABLE LECTURE_TBL (
    L_NO       NUMBER             NOT NULL
  , P_NO       VARCHAR2(5 BYTE)   NULL  -- PROFESSOR_TBL 테이블의 P_NO 칼럼을 참조하는 외래키
  , C_NO       VARCHAR2(5 BYTE)   NULL  -- COURSE_TBL 테이블의 C_NO 칼럼을 참조하는 외래키
  , L_NAME     VARCHAR2(100 BYTE) NULL
  , L_LOCATION VARCHAR2(100 BYTE) NULL
  , CONSTRAINT PK_LEC PRIMARY KEY(L_NO)
  , CONSTRAINT FK_PROF_LEC   FOREIGN KEY(P_NO) REFERENCES PROFESSOR_TBL(P_NO) ON DELETE SET NULL
  , CONSTRAINT FK_COURCE_LEC FOREIGN KEY(C_NO) REFERENCES COURSE_TBL(C_NO)    ON DELETE SET NULL
);

-- 5. ENROLL_TBL 테이블
CREATE TABLE ENROLL_TBL (
    E_NO   NUMBER           NOT NULL  -- 기본키
  , S_NO   VARCHAR2(5 BYTE) NOT NULL  -- STUDENT_TBL 테이블의 S_NO 칼럼을 참조하는 외래키
  , L_NO   NUMBER           NOT NULL  -- LECTURE_TBL 테이블의 L_NO 칼럼을 참조하는 외래키
  , E_DATE DATE   NULL
  , CONSTRAINT PK_ENROLL PRIMARY KEY(E_NO)
  , CONSTRAINT FK_STUDENT_ENROLL FOREIGN KEY(S_NO) REFERENCES STUDENT_TBL(S_NO) ON DELETE CASCADE
  , CONSTRAINT FK_LEC_ENROLL     FOREIGN KEY(L_NO) REFERENCES LECTURE_TBL(L_NO) ON DELETE CASCADE
);


-- 데이터 삽입

-- 1. PROFESSOR_TBL 테이블 데이터 입력
INSERT INTO PROFESSOR_TBL(P_NO, P_NAME, P_MAJOR) VALUES ('P0001', '제임스', '컴퓨터');
INSERT INTO PROFESSOR_TBL(P_NO, P_NAME, P_MAJOR) VALUES ('P0002', '앨리스', '컴퓨터');
INSERT INTO PROFESSOR_TBL(P_NO, P_NAME, P_MAJOR) VALUES ('P0003', '스미스', '컴퓨터');

-- 2. COURSE_TBL 테이블 데이터 입력
INSERT INTO COURSE_TBL(C_NO, C_NAME, C_UNIT) VALUES ('CE001', '자바', 3);
INSERT INTO COURSE_TBL(C_NO, C_NAME, C_UNIT) VALUES ('CE002', '데이터베이스', 3);
INSERT INTO COURSE_TBL(C_NO, C_NAME, C_UNIT) VALUES ('CE003', '서버프로그램', 3);

-- 3. STUDENT_TBL 테이블 데이터 입력
INSERT INTO STUDENT_TBL(S_NO, S_NAME, S_ADDRESS, S_GRADE_NO, P_NO) VALUES ('21001', '고길동', '서울', 3, 'P0001');
INSERT INTO STUDENT_TBL(S_NO, S_NAME, S_ADDRESS, S_GRADE_NO, P_NO) VALUES ('21002', '최무배', '경기', 3, 'P0002');
INSERT INTO STUDENT_TBL(S_NO, S_NAME, S_ADDRESS, S_GRADE_NO, P_NO) VALUES ('22001', '강우림', '인천', 2, 'P0003');
INSERT INTO STUDENT_TBL(S_NO, S_NAME, S_ADDRESS, S_GRADE_NO, P_NO) VALUES ('22002', '지수아', '제주', 2, 'P0001');
INSERT INTO STUDENT_TBL(S_NO, S_NAME, S_ADDRESS, S_GRADE_NO, P_NO) VALUES ('23001', '김예림', '대구', 1, 'P0002');
INSERT INTO STUDENT_TBL(S_NO, S_NAME, S_ADDRESS, S_GRADE_NO, P_NO) VALUES ('23002', '나태진', '광주', 1, 'P0003');

-- 4. 강의번호 시퀀스 + LECTURE_TBL 테이블 데이터 입력
DROP SEQUENCE LEC_SEQ;
CREATE SEQUENCE LEC_SEQ ORDER;
INSERT INTO LECTURE_TBL(L_NO, P_NO, C_NO, L_NAME, L_LOCATION) VALUES (LEC_SEQ.NEXTVAL, 'P0001', 'CE001', '자바완전정복', '101호');
INSERT INTO LECTURE_TBL(L_NO, P_NO, C_NO, L_NAME, L_LOCATION) VALUES (LEC_SEQ.NEXTVAL, 'P0002', 'CE002', '관계형데이터베이스이론', '102호');
INSERT INTO LECTURE_TBL(L_NO, P_NO, C_NO, L_NAME, L_LOCATION) VALUES (LEC_SEQ.NEXTVAL, 'P0003', 'CE003', '스프링프레임워크개요', '103호');

-- 5. 수강신청번호 시퀀스 + ENROLL_TBL 테이블 데이터 입력
DROP SEQUENCE ENROLL_SEQ;
CREATE SEQUENCE ENROLL_SEQ ORDER;
INSERT INTO ENROLL_TBL(E_NO, S_NO, L_NO, E_DATE) VALUES (ENROLL_SEQ.NEXTVAL, '21001', 1, '23-02-25');
INSERT INTO ENROLL_TBL(E_NO, S_NO, L_NO, E_DATE) VALUES (ENROLL_SEQ.NEXTVAL, '21001', 2, '23-02-25');
INSERT INTO ENROLL_TBL(E_NO, S_NO, L_NO, E_DATE) VALUES (ENROLL_SEQ.NEXTVAL, '21001', 3, '23-02-25');

-- 6. 변경된 내용을 DB에 반영
COMMIT;