오라클 연습용 테이블!

  /*

  DROP SEQUENCE EMP_SQ;

  DROP SEQUENCE CUS_SQ;

  DROP TABLE SALARY_GRADE;

  DROP TABLE CUSTOMER;

  DROP TABLE EMPLOYEE;

  DROP TABLE DEPT;

  */

CREATE SEQUENCE EMP_SQ

    START WITH 1

    INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 30000;

CREATE SEQUENCE CUS_SQ

    START WITH 1

    INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 30000;

CREATE TABLE DEPT(

    DEP_NO NUMBER(2),

    DEP_NAME VARCHAR2(10) NOT NULL UNIQUE,

    LOC VARCHAR2(10) NOT NULL,

    PRIMARY KEY(DEP_NO)

);

INSERT INTO DEPT(DEP_NO,DEP_NAME,LOC) VALUES(10,'총무부','서울');

INSERT INTO DEPT(DEP_NO,DEP_NAME,LOC) VALUES(20,'영업부','부산');

INSERT INTO DEPT(DEP_NO,DEP_NAME,LOC) VALUES(30,'전산부','대전');

INSERT INTO DEPT(DEP_NO,DEP_NAME,LOC) VALUES(40,'자재부','광주');

SELECT * FROM DEPT;

COMMIT;

CREATE TABLE EMPLOYEE(

    EMP_NO NUMBER(3),

    EMP_NAME VARCHAR2(10) NOT NULL,

    DEP_NO NUMBER(2),

    JIKUP VARCHAR2(10) NOT NULL,

    SALARY NUMBER(9) DEFAULT 0,

    HIRE_DATE DATE DEFAULT SYSDATE,

    JUMIN_NUM CHAR(13) NOT NULL UNIQUE,

    PHONE_NUM VARCHAR(15) NOT NULL,

    MGR_EMP_NO NUMBER(3),

    PRIMARY KEY(EMP_NO),

    FOREIGN KEY(DEP_NO) REFERENCES DEPT(DEP_NO) ON DELETE CASCADE,

    CONSTRAINT EMPLOYEE_MGR_EMP_NO_FK FOREIGN KEY(MGR_EMP_NO)

            REFERENCES EMPLOYEE(EMP_NO) ON DELETE CASCADE

);

ALTER TABLE EMPLOYEE DISABLE CONSTRAINT EMPLOYEE_MGR_EMP_NO_FK;

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '홍길동', 10, '사장', 5000, TO_DATE( '1980-01-01', 'YYYY-MM-DD' ), '7211271109410', '01099699515', NULL);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '한국남', 20, '부장', 3000, TO_DATE( '1988-11-01', 'YYYY-MM-DD' ), '6002061841224', '01024948424', 1);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '이순신', 20, '과장', 3500, TO_DATE( '1989-03-01', 'YYYY-MM-DD' ), '6209172010520', '01026352672', 2);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '이미라', 30, '대리', 2503, TO_DATE( '1983-04-01', 'YYYY-MM-DD' ), '8409282070226', '01094215694', 17);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '이순라', 20, '사원', 1200, TO_DATE( '1990-05-01', 'YYYY-MM-DD' ), '8401041483626', '01094215694', 3);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '공부만', 30, '과장', 4003, TO_DATE( '1995-05-01', 'YYYY-MM-DD' ), '8402121563616', '01191338328', 17);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '놀기만', 20, '과장', 2300, TO_DATE( '1996-06-01', 'YYYY-MM-DD' ), '8011221713914', '01029463523', 2);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '채송화', 30, '대리', 1703, TO_DATE( '1992-06-01', 'YYYY-MM-DD' ), '8105271014112', '01047111052', 17);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '무궁화', 10, '사원', 1100, TO_DATE( '1984-08-01', 'YYYY-MM-DD' ), '8303291319116', '01025672300', 12);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '공부해', 30, '사원', 1303, TO_DATE( '1988-11-01', 'YYYY-MM-DD' ), '8410031281312', '01027073174', 4);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '류별나', 20, '과장', 1600, TO_DATE( '1989-12-01', 'YYYY-MM-DD' ), '8409181463545', '01071628290', 2);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '류명환', 10, '대리', 1800, TO_DATE( '1990-10-01', 'YYYY-MM-DD' ), '8207211661117', '01042072622', 20);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '무궁화', 10, '부장', 3000, TO_DATE( '1996-11-01', 'YYYY-MM-DD' ), '8603231183011', '01098110955', 1);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '채시라', 20, '사원', 3400, TO_DATE( '1993-10-01', 'YYYY-MM-DD' ), '8001172065410', '01044452437', 3);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '최진실', 10, '사원', 2000, TO_DATE( '1991-04-01', 'YYYY-MM-DD' ), '8303101932611', '01027491145', 12);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '김유신', 30, '사원', 4000, TO_DATE( '1981-04-01', 'YYYY-MM-DD' ), '7912031009014', '01098218448', 4);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '이성계', 30, '부장', 2803, TO_DATE( '1984-05-01', 'YYYY-MM-DD' ), '8102261713921', '0165358075', 1);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '강감찬', 30, '사원', 1003, TO_DATE( '1986-07-01', 'YYYY-MM-DD' ), '8203121977315', '01033583130', 4);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '임꺽정', 20, '사원', 2200, TO_DATE( '1988-04-01', 'YYYY-MM-DD' ), '8701301040111', '01086253078', 7);

INSERT INTO EMPLOYEE VALUES( EMP_SQ.NEXTVAL, '깨똥이', 10, '과장', 4500, TO_DATE( '1990-05-01', 'YYYY-MM-DD' ), '8811232452719', '01090084876', 13);

ALTER TABLE EMPLOYEE ENABLE CONSTRAINT EMPLOYEE_MGR_EMP_NO_FK;

SELECT * FROM EMPLOYEE;

COMMIT;

CREATE TABLE CUSTOMER(

    CUS_NO  NUMBER(3),

    CUS_NAME    VARCHAR2(10)    NOT NULL,

    TEL_NUM VARCHAR2(15),

    JUMIN_NUM   CHAR(13) NOT NULL UNIQUE,

    EMP_NO  NUMBER(3),

    PRIMARY KEY(CUS_NO),

    FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE(EMP_NO) ON DELETE CASCADE

);

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '류민이', '123-1234', '7001131537915', 3 );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '이강민', '343-1454', '6902161627914', 2 );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '이영희', '144-1655', '7503202636215', NULL );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '김철이', '673-1674', '7704301234567', 4 );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '박류완', '123-1674', '7205211123675', 3 );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '서캔디', '673-1764', '6507252534566', NULL );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '신똘이', '176-7677', '0006083648614', 7 );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '도쇠돌', '123-6774', '0008041346574', 9 );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '권홍이', '767-1234', '7312251234689', 13 );

INSERT INTO CUSTOMER VALUES( CUS_SQ.NEXTVAL, '김안나', '767-1677', '7510152432168', 4 );

 SELECT * FROM CUSTOMER;

 COMMIT;

 CREATE TABLE SALARY_GRADE(

    SAL_GRADE_NO    NUMBER(2)   PRIMARY KEY,

    MIN_SAL     NUMBER(5)   NOT NULL,

    MAX_SAL     NUMBER(5)   NOT NULL

);

INSERT INTO SALARY_GRADE VALUES(1, 8001, 10000 );

INSERT INTO SALARY_GRADE VALUES(2, 6001, 8000 );

INSERT INTO SALARY_GRADE VALUES(3, 4001, 6000 );

INSERT INTO SALARY_GRADE VALUES(4, 2001, 4000 );

INSERT INTO SALARY_GRADE VALUES(5, 1000, 2000 );

SELECT * FROM SALARY_GRADE;

 COMMIT;