(46)[oracle]11일차: CHECK, 제약조건 삭제 및 추가, NOT NULL, DEFAULT 표현식/ (팀과제)HR 샘플스키마 ERD 를 이용한 테이블 재구성

1. 23.10.31(화)

1. 20231031_01_hr.sql

SELECT USER
FROM DUAL;

--■■■ CHECK(CK:C) ■■■--
/*
1. 컬럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건  
    컬럼에 입력되는 데이터를 검사하여 조건에 맞는 데이터만 입력될 수 있도록 한다.
    또한, 컬러멩서 주어되는 데이터를 검사하여 조건에 맞는 데이터로 수정되는 것만 
    허용하는 기능을 수행하게 된다.
    
2. 형식 및 구조
    (1) 컬럼 레벨의 형식
    ``` SQL
    컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] CHECK(컬럼 조건)
    ```
    
    (2) 테이블 레벨의 형식
    ``` SQL
    컬럼명 타입,
    컬럼명 타입,
    CONSTRAINT CONSTRAINT명 CHECK(컬럼 조건)
    ```
*/    
--○ CK 지정 실습((1) 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST8
( COL1 NUMBER(5)    PRIMARY KEY
, COL2 VARCHAR2(30)
, COL3 NUMBER(3)    CHECK(COL3 BETWEEN 0 AND 100)
);
--==>> Table TBL_TEST8이(가) 생성되었습니다.

-- 데이터 입력
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(1,'박범구',100);
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(1,'엄재용',100); --> 에러 발생 (ORA-00001: unique constraint (HR.SYS_C007106) violated)
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(2,'엄재용',101); --> 에러 발생 (ORA-02290: check constraint (HR.SYS_C007105) violated)
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(2,'엄재용',-1);  --> 에러 발생 (ORA-02290: check constraint (HR.SYS_C007105) violated)
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(2,'엄재용',80);

-- 확인
SELECT *
FROM TBL_TEST8;
--==>>
/*
1	박범구	100
2	엄재용	80
*/    

-- 커밋
COMMIT;
--==>> 커밋 완료.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST8';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    SYS_C007105	    TBL_TEST8	    C	        COL3	    COL3 BETWEEN 0 AND 100	(null)
HR	    SYS_C007106	    TBL_TEST8	    P	        COL1		(null)                  (null)
*/
-- *SEARCH_CONDITION: 제약조건 기술*  

--○ CK 지정 실습((2) 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST9
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, COL3 NUMBER(3)
, CONSTRAINT TEST9_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST9_COL3_CK CHECK(COL3 BETWEEN 0 AND 100)
);
--==>> Table TBL_TEST9이(가) 생성되었습니다.

-- 데이터 입력
INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(1,'박범구',100);
INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(1,'엄재용',100); --> 에러 발생 (ORA-00001: unique constraint (HR.SYS_C007106) violated)
INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(2,'엄재용',101); --> 에러 발생 (ORA-02290: check constraint (HR.SYS_C007105) violated)
INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(2,'엄재용',-1);  --> 에러 발생 (ORA-02290: check constraint (HR.SYS_C007105) violated)
INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(2,'엄재용',80);

-- 확인
SELECT *
FROM TBL_TEST9;
--==>>
/*
1	박범구	100
2	엄재용	80
*/    

-- 커밋
COMMIT;
--==>> 커밋 완료.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST9';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    SYS_C007105	    TBL_TEST8	    C	        COL3	    COL3 BETWEEN 0 AND 100	(null)
HR	    SYS_C007106	    TBL_TEST8	    P	        COL1		(null)                  (null)
*/

--○ CK 지정 실습((3) 테이블 생성 이후 제약조건 추가)
--※ 이미 생성된(만들어져 있는) 상태의 테이블에
--   부여하려는 제약조건을 위반한 데이터가 포함되어 있을 경우
--   해당 테이블에 제약조건을 추가하는 것은 불가능하다.
CREATE TABLE TBL_TEST10
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, COL3 NUMBER(3)
);
--==>> Table TBL_TEST10이(가) 생성되었습니다.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST10';
--==>> 조회결과 없음

-- 제약조건 추가
ALTER TABLE TBL_TEST10
ADD ( CONSTRAINT TEST10_COL1_PK PRIMARY KEY(COL1)
    , CONSTRAINT TEST10_COL3_CK CHECK(COL3 BETWEEN 0 AND 100));
--==>> Table TBL_TEST10이(가) 변경되었습니다.
-- *제약조건을 여러개 추가시 ADD ( 제약조건1, 제약조건2,...)로 작성*  

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST10';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    SYS_C007105	    TBL_TEST8	    C	        COL3	    COL3 BETWEEN 0 AND 100	(null)
HR	    SYS_C007106	    TBL_TEST8	    P	        COL1		(null)                  (null)
*/

-- 테이블 생성
CREATE TABLE TBL_TESTMEMBER
( SID   NUMBER
, NAME  VARCHAR2(30)
, SSN   CHAR(14)            -- 입력형태 -> 'YYMMDD-NNNNNNN' -> 14자리
                            --              12345678901234
, TEL   VARCHAR2(40)
);
--==>> Table TBL_TESTMEMBER이(가) 생성되었습니다.

/* 1-문제 & 함께 푼 내용 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
--○ TBL_TESTMEMBER 테이블의 SSN 컬럼(주민등록번호 컬럼)에서
--   데이터 입력이나 수정 시, 성별이 유효한 데이터만 입력될 수 있도록
--   체크 제약조건을 추가할 수 있도록 한다.
--   (-> 주민번호 특정 자리에 입력가능한 데이터를 1,2,3,4 만 가능하도록 처리)
--   또한, SID 컬럼에는 PRIMARY KEY 제약조건을 설정할 수 있도록 한다.

-- 제약조건 삭제
ALTER TABLE TBL_TESTMEMBER DROP CONSTRAINT TESTMEMBER_SSN_CK_01;

-- 제약조건 추가
ALTER TABLE TBL_TESTMEMBER
ADD (
    CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
    ,CONSTRAINT TESTMEMBER_SSN_CK CHECK(주민번호 8번째 자리 1개가 '1' 또는 '2' 또는 '3' 또는 '4')
    );
    
ALTER TABLE TBL_TESTMEMBER
ADD (
    CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
   ,CONSTRAINT TESTMEMBER_SSN_CK CHECK(SUBSTR(SSN,8,1) IN ('1','2','3','4'))
    );
    
ALTER TABLE TBL_TESTMEMBER
ADD (
--    CONSTRAINT TESTMEMBER_SSN_CK CHECK(SSN LIKE ('_______1%'))                              -- 실행됨
--    ,CONSTRAINT TESTMEMBER_SSN_CK CHECK(SSN LIKE ('_______1%') OR SSN LIKE ('_______2%'))   -- 안됨
--    ,CONSTRAINT TESTMEMBER_SSN_CK01 CHECK(SSN LIKE ('_______1%') OR SSN LIKE ('_______2%')) -- 안됨
--    ,CONSTRAINT TESTMEMBER_SSN_CK01 CHECK(SSN LIKE ('_______1%') OR SSN LIKE ('_______2%')) -- 안됨
    );

-- *제약조건 LIKE AND 코드가 맞는지 확인-> 가능*
-- *WHERE의 모든 조건이 제약조건에 작성되는 것은 아님. AND OR 불가.*
SELECT *
FROM TBL_TESTMEMBER
WHERE SSN LIKE ('_______1%') OR SSN LIKE ('_______2%');


-- 테스트 데이터 입력
--INSERT INTO TBL_TESTMEMBER(SID,SSN) VALUES(1,'123456-1901234');
--INSERT INTO TBL_TESTMEMBER(SID,SSN) VALUES(2,'123456-2901234');
--INSERT INTO TBL_TESTMEMBER(SID,SSN) VALUES(3,'123456-3901234');
--INSERT INTO TBL_TESTMEMBER(SID,SSN) VALUES(4,'123456-4901234');
--INSERT INTO TBL_TESTMEMBER(SID,SSN) VALUES(5,'123456-5901234'); --> 에러 발생(ORA-02290: check constraint (HR.TESTMEMBER_SSN_CK) violated)

INSERT INTO TBL_TESTMEMBER(SID,NAME,SSN,TEL) VALUES(1,'이윤수','950106-1234567','010-1111-1111');
INSERT INTO TBL_TESTMEMBER(SID,NAME,SSN,TEL) VALUES(2,'박나영','990208-2234567','010-2222-2222');
INSERT INTO TBL_TESTMEMBER(SID,NAME,SSN,TEL) VALUES(3,'최혜인','070811-4234567','010-3333-3333');
INSERT INTO TBL_TESTMEMBER(SID,NAME,SSN,TEL) VALUES(4,'길현욱','090111-3234567','010-4444-4444');

INSERT INTO TBL_TESTMEMBER(SID,NAME,SSN,TEL) VALUES(4,'정현욱','000220-5234567','010-5555-5555'); --> 에러 발생
INSERT INTO TBL_TESTMEMBER(SID,NAME,SSN,TEL) VALUES(4,'정현욱','000220-6234567','010-5555-5555'); --> 에러 발생


-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TESTMEMBER';
--==>>
/*
OWNER	CONSTRAINT_NAME	    TABLE_NAME	    CONSTRAINT_TYPE	COLUMN_NAME	 SEARCH_CONDITION	                  DELETE_RULE
HR	    TESTMEMBER_SID_PK	TBL_TESTMEMBER	P	            SID		     (null)                               (null)
HR	    TESTMEMBER_SSN_CK	TBL_TESTMEMBER	C	            SSN	         SUBSTR(SSN,8,1) IN ('1','2','3','4') (null)	
*/

-- 확인을 위한 테이블 삭제
DROP TABLE TBL_TESTMEMBER;

-- 확인
SELECT *
FROM TBL_TESTMEMBER;
--==>>
/*
1	이윤수	950106-1234567	010-1111-1111
2	박나영	990208-2234567	010-2222-2222
3	최혜인	070811-4234567	010-3333-3333
4	길현욱	090111-3234567	010-4444-4444
*/

--■■■ FOREIGN KEY(FK:F:R) ■■■--

/*
1. 참조 키(R)또는 외래 키(FK:F)는 두 테이블의 데이터 간 연결을 설정하고
    강제 적용시키는데 사용되는 열이다.
    한 테이블의 기본 키 값이 있는 열을
    다른 테이블에 추ㅏㄱ하면 테이블 간 연결을 설정할 수 있다.
    이 때, 두 번째 테이블에 추가되는 열이 외래키가 된다.
    
2. 부모 테이블(참조받는 컬럼이 포함된 테이블)이 먼저 생성된 후
    자식 테이블(참조하는 컬럼이 포함된 테이블)이 생성되어야 한다.
    이 때, 자식 ㅔ이블에 FOREIGN KEY 제약조건이 설정된다.
    
3. 형식 및 구조
    (1) 컬럼 레벨의 형식
    ``` SQL
    컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명]
                      RERERENCES 참조테이블명(참조컬럼명)
                      [ON DELETE CASCADE : ON DELETE SET NULL]  -> 추가옵션
    ```
                      
    (2) 테이블 레벨의 형식
    ``` SQL
    컬럼명 데이터타입,
    컬럼명 데이터타입,
    CONSTRAINT CONSTRAINT명 FOREIGN KEY(컬럼명)
                      RERERENCES 참조테이블명(참조컬럼명)
                      [ON DELETE CASCADE : ON DELETE SET NULL]  -> 추가옵션
    ```
*/

--※ FOREIGN KEY 제약조건을 설정하는 실습을 진행하기 위해서는
--   부모테이블의 생성 작업을 먼저 수행해야 한다.,
--   그리고 이 때, 부모 테이블에는 반드시 PK 또는 UK 제약조건이
--   설정된 컬럼이 존재해야 한다.

-- 부모테이블 생성
CREATE TABLE TBL_JOBS
( JIKWI_ID      NUMBER
, JIKWI_NAME    VARCHAR2(30)
, CONSTRAINT JOBS_ID_PK PRIMARY KEY(JIKWI_ID)
);
--==>> Table TBL_JOBS이(가) 생성되었습니다.

-- 부모 테이블에 데이터 입력
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(1,'사원');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(2,'대리');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(3,'과장');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(4,'부장');
--==> 1 행 이(가) 삽입되었습니다. * 4

-- 확인
SELECT *
FROM TBL_JOBS;
--==>>
/*
1	사원
2	대리
3	과장
4	부장
*/

-- 커밋
COMMIT;
--==>> 커밋 완료.

--○ FK 지정 실습((1) 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_EMP1
( SID       NUMBER          PRIMARY KEY
, NAME      VARCHAR2(30)
, JIKWI_ID  NUMBER          REFERENCES TBL_JOBS(JIKWI_ID)
--                                              -------- TBL_JOBS의 JIKWI_ID
);
--==>> Table TBL_EMP1이(가) 생성되었습니다.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>> 
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007125	    TBL_EMP1	P	            SID		
HR	    SYS_C007126	    TBL_EMP1	R	            JIKWI_ID		                NO ACTION
*/
-- * SEARCH_CONDITION: (NULL)       -> 체크제약조건의 상세 내용
-- * CONSTRAINT_TYPE: R             -> FOREIFN KEY
-- * DELETE_RULE: NO ACTION         -> 두개의 테이블이 연결되어 있는데, FK가 삭제되면 -> 남은 값은 어떻게 할 것인지..

-- 데이터 입력
INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(1, '노은하',1);
INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(2, '박가영',2);
INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(3, '채다선',3);
INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(4, '김수환',4);

INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(5, '김다슬',5); -->> 에러 발생(5의 참조번호가 없어서 실행X)
INSERT INTO TBL_EMP1(SID,NAME, JIKWI_ID) VALUES(5, '김다슬',1);
INSERT INTO TBL_EMP1(SID,NAME) VALUES(6, '오수경'); --*직위ID를 NULL인 상태로 입력하겠다는 뜻
-- *비워두는 것은 가능하지만, 입력을 할 것이라면 있는 번호 입력가능*  

SELECT *
FROM TBL_EMP1;
--==>>
/*
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	4
5	김다슬	1
6	오수경	
*/

COMMIT;

--○ FK 지정 실습((2) 테이블 레벨의 형식)
--  테이블 생성
CREATE TABLE TBL_EMP2
( SID       NUMBER
, NAME      VARCHAR2(30)
, JIKWI_ID  NUMBER
, CONSTRAINT EMP2_SID_PK PRIMARY KEY(SID)
, CONSTRAINT EMP_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)  -- *TBL_EMP2의 직위 ID
             REFERENCES TBL_JOBS(JIKWI_ID)          -- *JOBS 직위ID
);
--==>> Table TBL_EMP2이(가) 생성되었습니다.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP2';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007125	    TBL_EMP1	P	            SID		
HR	    SYS_C007126	    TBL_EMP1	R	            JIKWI_ID		                NO ACTION
*/

--○ FK 지정 실습((3) 테이블 생성 이후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_EMP3
( SID       NUMBER
, NAME      VARCHAR2(30)
, JIKWI_ID  NUMBER
);
--==>> Table TBL_EMP3이(가) 생성되었습니다.

-- 제약조건 추가
ALTER TABLE TBL_EMP3
ADD ( CONSTRAINT EMP3_SID_PK PRIMARY KEY(SID)
    , CONSTRAINT EMP3_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
                REFERENCES TBL_JOBS(JIKWI_ID)
    );
--==>> Table TBL_EMP3이(가) 변경되었습니다.

-- 제약조건 제거
ALTER TABLE TBL_EMP3
DROP CONSTRAINT EMP3_JIKWI_ID_FK;   -- *오라클이 알아서 이름부여한 경우 꼭 조회후 제거*
--==>> Table TBL_EMP3이(가) 변경되었습니다.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP3';
--==>> HR	EMP3_SID_PK	TBL_EMP3	P	SID		

-- 다시 제약조건 추가
ALTER TABLE TBL_EMP3
ADD CONSTRAINT EMP3_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
               REFERENCES TBL_JOBS(JIKWI_ID);
--==>> Table TBL_EMP3이(가) 변경되었습니다.

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP3';
--==>>
/*
HR	EMP3_SID_PK	TBL_EMP3	P	SID		
HR	EMP3_JIKWI_ID_FK	TBL_EMP3	R	JIKWI_ID		NO ACTION
*/

/*
4. FOREIGN KEY 생성 시 주의사항
    참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
    참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 설정되어 있어야 한다.
    테이블 사이에 PRIMARY KEY 와 FOREIGN KEY 가 정의되어 있으면
    PRIMARY KEY 제약조건이 설정된 데이터 삭제 시
    FOREIGN KEY 컬럼에 그 값이 입력되어 있는 경우 삭제되지 않는다.
    (즉, 자식 테이블에 참조하는 레코드가 존재할 경우
    부모 테이블의 참조받는 해당 레코드는 삭제할 수 없다는 것이다.)
    단, FK 설정 과정에서 <ON DELETE CASCADE>나 <ON DLELETE SET NULL> 옵션을
    -- *정말 특별한 경우가 아니고서는 사용하지 않는 옵션*
    -- *자식 값을 모두 지우고 본인 삭제됨*
    사용하여 설정한 경우에는 삭제가 가능하다.
    또한, 부모 테이블을 제거하기 위해서는 자식 테이블을 먼저 제거해야 한다.
*/

-- *==================================
-- *결론: FOREIGN KEY설정 시
-- *(1) 부모테이블 P.K 또는 UNIQUE가 먼저 생성되어야함
-- *(2) 
-- *==================================

-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1	사원
2	대리
3	과장
4	부장
*/

-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	4
5	김다슬	1
6	오수경	
*/

-- 부모 테이블 제거 시도
DROP TABLE TBL_JOBS; --> 에러 발생(02449. 00000 -  "unique/primary keys in table referenced by foreign keys")

-- 부모 테이블의 부장 직위 데이터 삭제 시도
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID =4;
--==>> 4	부장

DELETE
FROM TBL_JOBS
WHERE JIKWI_ID =4;  --> 에러 발생(ORA-02292: integrity constraint (HR.SYS_C007126) violated - child record found)

-- 김수환 부장의 직위를 사원으로 변경
UPDATE TBL_EMP1
SET JIKWI_ID=1
WHERE SID=4;
--==>> 1 행 이(가) 업데이트되었습니다.

-- 확인
SELECT *
FROM TBL_EMP1;
--==>> 
/*
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	1
5	김다슬	1
6	오수경	
*/

-- 커밋
COMMIT;

-- 부모 테이블(TBL_JOBS)의 부장 데이터를 참조하고 있는
-- 자식 테이블(TBL_EMP1)의 데이터가 존재하지 않는 상황

-- 이와 같은 상황에서 부모 테이블(TBL_JOBS)의
-- 부장 데이터 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID =4;
--==>> 1 행 이(가) 삭제되었습니다.

SELECT *
FROM TBL_JOBS;
--==>>
/*
1	사원
2	대리
3	과장
*/

COMMIT;

--※ 부모 테이블의 데이터를 자유롭게(?) 삭제하기 위해서는 <ON DELETE CASCADE> 옵션 지정이 필요하다.

/* 2-문제 & 함께 푼 내용 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- TBL_EMP1 테이블(자식 테이블)에서 FK 제약조건을 제거한 후
-- CASCADE 옵션을 포함하여 다시 FK 제약조건을 설정한다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007125	    TBL_EMP1	P	            SID		
HR	    SYS_C007126	    TBL_EMP1	R	            JIKWI_ID		                NO ACTION
*/
--> FK 제약조건(CONSTRAINT_TYPE:R)의 이름(CONASTRAINT_NAME): SYS_C007126

-- 제약조건 제거
ALTER TABLE TBL_EMP1
DROP CONSTRAINT SYS_C007126;

-- 제약조건 제거 이후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007125	    TBL_EMP1	P	            SID		
*/

-- <ON DELTET CASCADE> 옵션이 포함된 내용으로 제약조건 다시 설정
ALTER TABLE TBL_EMP1
ADD CONSTRAINT EMP1_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
                REFERENCES TBL_JOBS(JIKWI_ID)
                ON DELETE CASCADE;
--==>> Table TBL_EMP1이(가) 변경되었습니다.

-- 제약조건 생성 이후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
--==>>
/*
OWNER	CONSTRAINT_NAME	  TABLE_NAME    CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007125	      TBL_EMP1	    P	            SID		
HR	    EMP1_JIKWI_ID_FK  TBL_EMP1	    R	            JIKWI_ID		CASCADE
*/

--※ CASCADE 옵션을 지정한 후에는 
--   참조받고 있는 부모테이블의 데이터를 
--   언제든지 자유롭게 삭제하는 것이 가능하다.
--   단, 부모 테이블의 데이터가 삭제될 경우...
--   이를 참조하는 **자식 테이블**의 데이터도 모~~~~~~~~~~두 함께 삭제된다.
-- *참조하고 있는 자식테이블에서 하나라도 있는 경우 삭제되지 않음

-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1	사원
2	대리
3	과장
*/

-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	1
5	김다슬	1
6	오수경	
*/

-- 부모 테이블(TBL_JOBS)에서 과장 데이ㅣ터 삭제
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=3;
--==>> 3	과장

DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=3;

-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1	사원
2	대리
*/

-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1	노은하	1
2	박가영	2
4	김수환	1
5	김다슬	1
6	오수경	
*/

SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>> 1	사원

-- 부모테이블에서(TBL_JOBS)에서 사원 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>> 1 행 이(가) 삭제되었습니다.

-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
2	대리
*/

-- 자식 테이블
SELECT *
FROM TBL_EMP1;
/*
2	박가영	2
6	오수경	
*/

DROP TABLE TBL_EMP2;
--==>> Table TBL_EMP2이(가) 삭제되었습니다.

DROP TABLE TBL_EMP3;
--==>> Table TBL_EMP3이(가) 삭제되었습니다.

DROP TABLE TBL_JOBS;
--==>> 에러 발생(02449. 00000 -  "unique/primary keys in table referenced by foreign keys")

DROP TABLE TBL_EMP1;
--==>> Table TBL_EMP1이(가) 삭제되었습니다.

DROP TABLE TBL_JOBS;
--==>> Table TBL_JOBS이(가) 삭제되었습니다.

-- *FOREGIN에서 자식 테이블의 값이 삭제되더라도 제약조건은 살아있어서 부모테이블은 삭제되지 않는다.*



--■■■ NOT NULL(NN:CK:C) ■■■--

-- 1. 테이블에서 지저한 컬럼의 데이터가 NULL 인 상태를 갖지 못하도록 하는 제약조건.
-- *테이블레벨이 보통은 부여하지만, NOT NULL 제약조건의 경우 컬럼 레벨의 형식이 더 많아서 기본됨.*

-- 2. 형식 및 구조
--  (1) 컬럼 레벨의 형식
--   컬럼명 데이터타입 [CONSTRATINT CONSTRAINT명] NOT NULL

--  (2) 테이블 레벨의 형식
--   컬럼명 데이터 타입,
--   컬러명 데잍타입,
--   CONSTRAINT CONSTRAINT명 CHECK(컬럼명 IS NOT NULL)

-- 3. 기존에 생성되어 있는 테이블에 NOT NULL 제약조건을 추가할 경우
--   ADD 보다 MODIFY 절이 더 많이 사용된다.
--   ALTER TABLE 테이블명
--   MODIFY 컬럼명 데이터타입 NOT NULL;

-- 4. 기존 테이블에 데이터가 이미 들어있지 않은 컬럼(-> NULL인 상태)을
--    NOT NULL 제약조건을 갖도록 수정하는 경우에는 에러 발생한다.

--○ NOT NULL 지정 실습((1) 컬럼 레벨의 형식)
--   테이블 생성
CREATE TABLE TBL_TEST11
( COL1 NUMBER(5)    PRIMARY KEY
, COL2 VARCHAR2(30) NOT NULL
);
--==>> Table TBL_TEST11이(가) 생성되었습니다.

-- 데이터 입력
INSERT INTO TBL_TEST11(COL1,COL2) VALUES(1,'TEST');
INSERT INTO TBL_TEST11(COL1,COL2) VALUES(2,'ABCD');
INSERT INTO TBL_TEST11(COL1,COL2) VALUES(3, NULL); --> 에러발생 (ORA-01400: cannot insert NULL into ("HR"."TBL_TEST11"."COL2"))
INSERT INTO TBL_TEST11(COL1) VALUES(4);            --> 에러발생 (ORA-01400: cannot insert NULL into ("HR"."TBL_TEST11"."COL2"))

-- 확인
SELECT *
FROM TBL_TEST11;
/*
1	TEST
2	ABCD
*/

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST11';
--==>>
/*
HR	SYS_C007133	TBL_TEST11	C	COL2	"COL2" IS NOT NULL	
HR	SYS_C007134	TBL_TEST11	P	COL1		
*/

--○ NOT NULL 지정 실습((2) 테이블 레벨의 형식)
--   테이블 생성
CREATE TABLE TBL_TEST12
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST12_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST12_COL2_NN CHECK(COL2 IS NOT NULL)
);
--==>> Table TBL_TEST12이(가) 생성되었습니다.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST12';
--==>>
/*
HR	TEST12_COL2_NN	TBL_TEST12	C	COL2	COL2 IS NOT NULL	
HR	TEST12_COL1_PK	TBL_TEST12	P	COL1		
*/

--○ NOT NULL 지정 실습((3) 테이블 생성 이후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_TEST13
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
);
--==>> Table BL_TEST13이(가) 생성되었습니다.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST13';
--==>> 조회 결과 없음

--  제약조건 추가
ALTER TABLE TBL_TEST13
ADD ( CONSTRAINT TEST13_COL1_PK PRIMARY KEY(COL1)
    , CONSTRAINT TEST13_COL2_NN CHECK(COL2 IS NOT NULL)
);
--==>> Table TBL_TEST13이(가) 변경되었습니다.

-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST13';
--==>>
/*
HR	TEST13_COL1_PK	TBL_TEST13	P	COL1		
HR	TEST13_COL2_NN	TBL_TEST13	C	COL2	COL2 IS NOT NULL	
*/

--※ NOT NULL 제약조건만 TBL_TEST3 테이블의 COL2 에 추가하는 경우
--   다음과 같은 방법을 사용하는 것도 가능하다.
ALTER TABLE TBL_TEST13
MODIFY COL2 NOT NULL;
--==>> Table TBL_TEST13이(가) 변경되었습니다.

-- 컬럼 레벨에서 NOT NULL 제약조건을 지정한 테이블(TBL_TEST11)
DESC TBL_TEST11;
--==>>
/*
이름   널?       유형           
---- -------- ------------ 
COL1 NOT NULL NUMBER(5)    
COL2 NOT NULL VARCHAR2(30)
*/
-- *가장 많이쓰는 구문 DESC 에서 NOT NULL 확인

-- 테이블 레벨에서 NOT NULL 제약조건을 지정한 테이블(TBL_TEST12)
DESC TBL_TEST12;
--==>>
/*
이름   널?       유형           
---- -------- ------------ 
COL1 NOT NULL NUMBER(5)    
COL2          VARCHAR2(30)
*/
-- *가장 많이쓰는 구문 DESC 에서 NOT NULL 미확인됨(VIEW로만 가능함)

-- 테이블 생성 이후 ADD를 통해 NOT NULL 제약조건을 추가하였으며
-- 여기에 더하여, MODIFY 절을 통해 NOT NULL 제약조건을 추가한 테이블
DESC TBL_TEST13;
--==>>
/*
이름   널?       유형           
---- -------- ------------ 
COL1 NOT NULL NUMBER(5)    
COL2 NOT NULL VARCHAR2(30) 
*/
-- * NOT NULL 확인가능*
SELECT *
FROM  VIEW_CONSTCHECK
WHERE TABLE_NAME IN ('TBL_TEST11', 'TBL_TEST12', 'TBL_TEST13');
--==>>
/*
--○ CK 지정 실습((1) 컬럼 레벨의 형식)
HR	SYS_C007133	TBL_TEST11	    C	COL2	"COL2" IS NOT NULL	
HR	SYS_C007134	TBL_TEST11	    P	COL1		

--○ CK 지정 실습((2) 테이블 레벨의 형식)
HR	TEST12_COL2_NN	TBL_TEST12	C	COL2	COL2 IS NOT NULL	
HR	TEST12_COL1_PK	TBL_TEST12	P	COL1		

--○ CK 지정 실습((3) 테이블 생성 이후 제약조건 추가)
HR	TEST13_COL1_PK	TBL_TEST13	P	COL1		
HR	TEST13_COL2_NN	TBL_TEST13	C	COL2	COL2 IS NOT NULL	
HR	SYS_C007139	TBL_TEST13	    C	COL2	"COL2" IS NOT NULL	-> MODIFY절에서 구분 된 것
*/
-- *========================================================*
-- * 결론: NOT NULL에서는 컬럼 레벨의 형식으로 부여하는 것이  DESC로 널?(NOT NULL)을 확인할 수 있고
-- * 제약조건을 VIEW로 볼 때도 'SEARCH_CONDITION'에서 NULL을 확일 할 수있다.
-- *========================================================*

-- *컬럼레벨에서 이름을부여하면서 NOT NULL을 넣는게 바람직함

-- ■■■ DEFAULT 표현식 ■■■--

-- 1. ISERT 와 UPDATE 문에서
--    틀정 값이 아닌 기본 값을 입력하도록 한다.

-- 2. 형식 및 구조
--    컬럼명 데이터타입 DEFAULT 기본값

-- 3. INSERT 명령 시 해당 컬럼에 입력된 값을 할당 하지 않거나
--    DEFAULT 키워드를 활용하여 기본으로 설정된 값을 입력하도록 한다.

-- 4. DEFAULT 키워드와 다른 제약(NOT NULL 등) 표기가 함께 사용되어야하는 경우
--    DFAULT 키워드를 먼저 표기(작성)할 것을 권장한다.

--○ DEFAULT 표현식 적용 실습
-- 테이블 생성
CREATE TABLE TBL_BBS                            -- 게시판 테이블 생성
( SID       NUMBER          PRIMARY KEY         -- 게시물 번호-> 식별자 -> 자동 증가
, NAME      VARCHAR2(20)                        -- 게시물 작성자
, CONTENTS  VARCHAR2(200)                       -- 게시물 내용
, WRITEDAY  DATE            DEFAULT SYSDATE     -- 게시물 작성일
, COUNTS    NUMBER          DEFAULT 0           -- 게시물 조회수
, COMMENTS  NUMBER          DEFAULT 0           -- 게시물 댓글 갯수
);
--==>> Table TBL_BBS이(가) 생성되었습니다.

-- *입력항목에서 제외하면, 넘어오지 못하는 상태가 줄어들지만
--※ SID 를 자동 증가 값으로 운영하려면 시퀀스 객체가 필요하다.
--   자동으로 입력되는 컬럼은 사용자들의 입력 항목에서 제외시킬 수 있다.

-- 사퀀스 생성
CREATE SEQUENCE SEQ_BBS
NOCACHE;
--==>> Sequence SEQ_BBS이(가) 생성되었습니다.

-- 날짜 관련 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- 게시물 작성
INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '김다슬', '오라클 DEFAULT 표현식을 실습중입니다.'
    , TO_DATE('2023-10-31 14:39:10', 'YYYY-MM-DD HH24:MI:SS'),0,0);
--==>> 1 행 이(가) 삽입되었습니다.

INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '김다슬', '오라클 DEFAULT 표현식을 실습중입니다.', SYSDATE,0,0);
--==>> 1 행 이(가) 삽입되었습니다.

INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '노은하', '계속 실습중입니다.', DEFAULT,0,0);
--==>> 1 행 이(가) 삽입되었습니다.

INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '문정환', '열심히 실습중입니다.', DEFAULT, DEFAULT, DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.

INSERT INTO TBL_BBS(SID, NAME, CONTENTS)
VALUES(SEQ_BBS.NEXTVAL, '이윤수', '무진장 실습중입니다.');
--==>> 1 행 이(가) 삽입되었습니다.

-- 확인
SELECT *
FROM TBL_BBS;

--○ DEFAULT 표현식 조회(확인)
SELECT *
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME='TBL_BBS';
--==>>
/*
TABLE_NAME	COLUMN_NAME	DATA_TYPE		DATA_LENGTH	NULLABLE	COLUMN_ID	DEFAULT_LENGTH	DATA_DEFAULT	
TBL_BBS	    SID	        NUMBER			22			N	        1											
TBL_BBS	    NAME	    VARCHAR2		20			Y	        2											
TBL_BBS	    CONTENTS	VARCHAR2		200			Y	        3											
TBL_BBS	    WRITEDAY	DATE			7			Y	        4	        35	            "SYSDATE     -- 게시물 작성일" 
TBL_BBS	    COUNTS	    NUMBER			22			Y	        5	        35	            "0           -- 게시물 조회수"
TBL_BBS	    COMMENTS	NUMBER			22			Y	        6	        39	            "0           -- 게시물 댓글 갯수"
*/

--○ 테이블 생성 이후 DEFAULT 표현식 추가 / 변경
ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT 기본값;

-- *DEFAULT 표현식은 제약조건의 범주안에 들어가지 않음*  
-- *DEFAULT는 제거하는 표현식이 없음. 따라서, NULL로 표현*  

--○ 기존의 DEFAULT 표현식 제거
ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT NULL;


COMMIT;
--==>> 커밋 완료.

2. 20231031_02_scott.sql

SELECT USER
FROM DUAL;
--==>> HR

-- *[단축키] FROM 부터 작성할 때 왼쪽으로 가고 싶으면 HOME/END 를 이용*

-- ■■■ 팀 별 실습 수행 과제 ■■■--

-- 제출 파일명
--  20231031_03_hr(3팀_오수경).sql
--  20231031_03_hr(3팀_오수경) 수행후기.txt

-- HR 샘플스키마 ERD 를 이용한 테이블 재구성~!!!

-- 팀별로... HR 샘플스키마에 있는 기본 테이블(7개)
-- 

PURGE RECYCLEBIN;


SELECT *
FROM TAB;
-- COUNTRIES / DEPARTMENTS / EMPLOYEES / JOBS / JOB_HISTORY / LOCATIONS / REGIONS 
-- 다선(52-68)  수환(86~102)
-- 을 똑~~~~~~~~~~~~ 같이 새로 구성한다.

-- 단, 생성하는 테이블의 이름은 <테이블명+팀번호>

-- 1. 기존 대상 테이블들의 정보 수집
-- 2. 테이블 생성(컬럼이름, 자료형, DEFAULT 표현식, NOT NULL 등...)
-- 3. 제약조건 설정(PK, U, FK, CK, ...)
-- 4. 데이터 입력
--* CREATE TABLE AS..로 입력하면 안됨*


SELECT *
FROM REGIONS;

-- 데이터
SELECT *
FROM REGIONS;
/*
REGION_ID	REGION_NAME
1	        Europe
2	        Americas
3	        Asia
4	        Middle East and Africa
*/

-- 제약조건: 유형, NULL
DESC REGIONS;
/*
이름          널?       유형           
----------- -------- ------------ 
REGION_ID   NOT NULL NUMBER       
REGION_NAME          VARCHAR2(25)
*/

-- 제약조건: 
-- ㄴ SEARCH_CONDITION :
-- ㄴ CONSTRAINT_TYPE  :
-- ㄴ DELETE_RULE      :
-- ㄴ DATA_DEFAULT     :
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'REGIONS';
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	       DELETE_RULE
HR	    REGION_ID_NN	REGIONS	        C	        REGION_ID	"REGION_ID" IS NOT NULL	
HR	    REG_ID_PK	    REGIONS	        P	        REGION_ID		
*/


-- 1. 기존 대상 테이블들의 정보 수집
-- 2. 테이블 생성(컬럼이름, 자료형, DEFAULT 표현식, NOT NULL 등...)
-- 3. 제약조건 설정(PK, U, FK, CK, ...)
-- 4. 데이터 입력

DROP TABLE REGIONS3;

-- 3-R-1) 테이블 생성 & 제약조건 설정: 컬럼이름, 자료형, NOT NULL
CREATE TABLE REGIONS3
( REGION_ID     NUMBER  
, REGION_NAME   VARCHAR2(25)
, CONSTRAINT REG3_ID_PK PRIMARY KEY(REGION_ID)
, CONSTRAINT REGION3_ID_NN CHECK("REGION_ID" IS NOT NULL)
);

-- 3-R-2) 데이터 입력
INSERT INTO REGIONS3(REGION_ID, REGION_NAME) VALUES(1,'Europe');
INSERT INTO REGIONS3(REGION_ID, REGION_NAME) VALUES(2,'Americas');
INSERT INTO REGIONS3(REGION_ID, REGION_NAME) VALUES(3,'Asia');
INSERT INTO REGIONS3(REGION_ID, REGION_NAME) VALUES(4,'Middle East and Africa');

-- 3-R-3) 데이터 확인
SELECT *
FROM REGIONS3;

DESC REGIONS3;

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'REGIONS3';


-------------------------------------------------------------------------------
-- 3-E-1) 테이블 생성 & 제약조건 설정: 컬럼이름, 자료형, NOT NULL
-- 3-E-2) 제약조건 설정: 컬럼이름, 자료형, NOT NULL
-- 3-E-2) 데이터 입력
-- 3-E-3) 데이터 확인
DROP TABLE JOB_HISTORY3;   
DROP TABLE REGIONS3;
DROP TABLE COUNTRIES3;        
DROP TABLE LOCATIONS3;        
DROP TABLE DEPARTMENTS3;
DROP TABLE EMPLOYEES3;        
DROP TABLE JOBS3;
;
SELECT *
FROM EMPLOYEES;

DESC EMPLOYEES;

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'EMPLOYEES';

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'DEPARTMENTS';

SELECT *
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'DEPARTMENTS';

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'REGIONS4';

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'REGIONS5';

DROP TABLE REGIONS4;
DROP TABLE REGIONS5;

CREATE TABLE REGIONS4
( REGION_ID     NUMBER  
);

CREATE TABLE REGIONS5
( REGION_ID     NUMBER  
);

/*
- 방법1: PK로 만들고 ALTER로 수정
- 방법2: 
*/

ALTER TABLE REGIONS4
ADD CONSTRAINT REGIONS4_FK FOREIGN KEY(REGION_ID)
             REFERENCES REGIONS5(REGION_ID);
             
ALTER TABLE REGIONS5
ADD CONSTRAINT REGIONS5_FK PRIMARY KEY(REGION_ID);

ALTER TABLE REGIONS5
ADD CONSTRAINT REGIONS5_FK FOREIGN KEY(REGION_ID)
             REFERENCES REGIONS5(REGION_ID);

--FOREIGN KEY(REGION_ID)
             REFERENCES REGIONS4(REGION_ID);















-- 3-E-1) 테이블 생성
CREATE TABLE EMPLOYEES3
( EMPLOYEE_ID       NUMBER(6)
, FIRST_NAME        VARCHAR2(20) 
, LAST_NAME         VARCHAR2(25) 
, EMAIL             VARCHAR2(25) 
, PHONE_NUMBER      VARCHAR2(20)
, HIRE_DATE         DATE
, JOB_ID            VARCHAR2(10) 
, SALARY            NUMBER(8,2)
, COMMISSION_PCT    NUMBER(2,2)
, MANAGER_ID        NUMBER(6)
, DEPARTMENT_ID     NUMBER(4)
, CONSTRAINT EMP3_EMP_ID_PK PRIMARY KEY(EMPLOYEE_ID)
);

-- 3-E-2) 제약조건 설정: 컬럼이름, 자료형, NOT NULL
/*
OWNER	CONSTRAINT_NAME	    TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    EMP_LAST_NAME_NN	EMPLOYEES	C	            LAST_NAME	"LAST_NAME" IS NOT NULL	
HR	    EMP_EMAIL_NN	    EMPLOYEES	C	            EMAIL	    "EMAIL" IS NOT NULL	
HR	    EMP_HIRE_DATE_NN	EMPLOYEES	C	            HIRE_DATE	"HIRE_DATE" IS NOT NULL	
HR	    EMP_JOB_NN	        EMPLOYEES	C	            JOB_ID	    "JOB_ID" IS NOT NULL	
HR	    EMP_SALARY_MIN	    EMPLOYEES	C	            SALARY	    salary > 0	
HR	    EMP_EMAIL_UK	    EMPLOYEES	U	            EMAIL		
HR	    EMP_EMP_ID_PK	    EMPLOYEES	P	            EMPLOYEE_ID		

HR	    EMP_DEPT_FK	        EMPLOYEES	R	            DEPARTMENT_ID		            NO ACTION
HR	    EMP_JOB_FK	        EMPLOYEES	R	            JOB_ID		                    NO ACTION
HR	    EMP_MANAGER_FK	    EMPLOYEES	R	            MANAGER_ID		                NO ACTION
*/
ALTER TABLE EMPLOYEES3
ADD( CONSTRAINT EMP3_LAST_NAME_NN CHECK("LAST_NAME" IS NOT NULL)
    , CONSTRAINT EMP3_EMAIL_NN CHECK("EMAIL" IS NOT NULL)
    , CONSTRAINT EMP3_HIRE_DATE_NN CHECK("HIRE_DATE" IS NOT NULL)
    , CONSTRAINT EMP3_JOB_NN CHECK("JOB_ID" IS NOT NULL)
    , CONSTRAINT EMP3_EMAIL_NN CHECK(salary > 0)
    , CONSTRAINT EMP3_EMAIL_UK UNIQUE(EMAIL)
--    , CONSTRAINT EMP3_EMP_ID_PK PRIMARY KEY(EMPLOYEE_ID)
/*
    , CONSTRAINT EMP3_DEPT_FK FOREIGN KEY(DEPARTMENT_ID)
             REFERENCES DEPARTMENTS3(DEPARTMENT_ID)
    , CONSTRAINT EMP3_JOB_FK FOREIGN KEY(JOB_ID)
             REFERENCES JOBS3(JOB_ID)
    , CONSTRAINT EMP3_MANAGER_FK FOREIGN KEY(MANAGER_ID)
             REFERENCES DEPARTMENTS3(MANAGER_ID)
*/
    );
    
    , CONSTRAINT TEST9_COL3_CK CHECK(COL3 BETWEEN 0 AND 100)
    , CONSTRAINT TEST7_COL1_PK PRIMARY KEY(COL1)
    , CONSTRAINT TEST7_COL2_UK UNIQUE(COL2));
    , CONSTRAINT EMP_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
             REFERENCES TBL_JOBS(JIKWI_ID)
    , CONSTRAINT EMP_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)  -- *TBL_EMP2의 직위 ID
             REFERENCES TBL_JOBS(JIKWI_ID)          -- *JOBS 직위ID
    



    
-- 3-E-3) 데이터 입력
-- 3-E-4) 데이터 확인

/*
EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000			90
101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21	AD_VP	17000		100	90
102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000		100	90
103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000		102	60
104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000		103	60
105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800		103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800		103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200		103	60
108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17	FI_MGR	12008		101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16	FI_ACCOUNT	9000		108	100
110	John	Chen	JCHEN	515.124.4269	2005-09-28	FI_ACCOUNT	8200		108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	2005-09-30	FI_ACCOUNT	7700		108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	2006-03-07	FI_ACCOUNT	7800		108	100
113	Luis	Popp	LPOPP	515.124.4567	2007-12-07	FI_ACCOUNT	6900		108	100
114	Den	Raphaely	DRAPHEAL	515.127.4561	2002-12-07	PU_MAN	11000		100	30
115	Alexander	Khoo	AKHOO	515.127.4562	2003-05-18	PU_CLERK	3100		114	30
116	Shelli	Baida	SBAIDA	515.127.4563	2005-12-24	PU_CLERK	2900		114	30
*/
INSERT INTO REGIONS3(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID) 
    VALUES(100,'Europe');




SELECT /*insert*/ * FROM JOB_HISTORY;   
SELECT /*insert*/ * FROM REGIONS;
SELECT /*insert*/ * FROM COUNTRIES;        
SELECT /*insert*/ * FROM LOCATIONS;        
SELECT /*insert*/ * FROM DEPARTMENTS;
SELECT /*insert*/ * FROM EMPLOYEES;        
SELECT /*insert*/ * FROM JOBS;


INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (102,TO_DATE('2001-01-13','YYYY-MM-DD'),TO_DATE('2006-07-24','YYYY-MM-DD'),'IT_PROG',60);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (101,TO_DATE('1997-09-21','YYYY-MM-DD'),TO_DATE('2001-10-27','YYYY-MM-DD'),'AC_ACCOUNT',110);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (101,TO_DATE('2001-10-28','YYYY-MM-DD'),TO_DATE('2005-03-15','YYYY-MM-DD'),'AC_MGR',110);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (201,TO_DATE('2004-02-17','YYYY-MM-DD'),TO_DATE('2007-12-19','YYYY-MM-DD'),'MK_REP',20);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (114,TO_DATE('2006-03-24','YYYY-MM-DD'),TO_DATE('2007-12-31','YYYY-MM-DD'),'ST_CLERK',50);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (122,TO_DATE('2007-01-01','YYYY-MM-DD'),TO_DATE('2007-12-31','YYYY-MM-DD'),'ST_CLERK',50);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (200,TO_DATE('1995-09-17','YYYY-MM-DD'),TO_DATE('2001-06-17','YYYY-MM-DD'),'AD_ASST',90);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (176,TO_DATE('2006-03-24','YYYY-MM-DD'),TO_DATE('2006-12-31','YYYY-MM-DD'),'SA_REP',80);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (176,TO_DATE('2007-01-01','YYYY-MM-DD'),TO_DATE('2007-12-31','YYYY-MM-DD'),'SA_MAN',80);
INSERT INTO JOB_HISTORY3 (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES (200,TO_DATE('2002-07-01','YYYY-MM-DD'),TO_DATE('2006-12-31','YYYY-MM-DD'),'AC_ACCOUNT',90);

INSERT INTO REGIONS3 (REGION_ID,REGION_NAME) VALUES (1,'Europe');
INSERT INTO REGIONS3 (REGION_ID,REGION_NAME) VALUES (2,'Americas');
INSERT INTO REGIONS3 (REGION_ID,REGION_NAME) VALUES (3,'Asia');
INSERT INTO REGIONS3 (REGION_ID,REGION_NAME) VALUES (4,'Middle East and Africa');

INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('AR','Argentina',2);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('AU','Australia',3);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('BE','Belgium',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('BR','Brazil',2);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('CA','Canada',2);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('CH','Switzerland',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('CN','China',3);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('DE','Germany',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('DK','Denmark',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('EG','Egypt',4);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('FR','France',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('IL','Israel',4);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('IN','India',3);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('IT','Italy',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('JP','Japan',3);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('KW','Kuwait',4);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('ML','Malaysia',3);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('MX','Mexico',2);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('NG','Nigeria',4);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('NL','Netherlands',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('SG','Singapore',3);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('UK','United Kingdom',1);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('US','United States of America',2);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('ZM','Zambia',4);
INSERT INTO COUNTRIES3 (COUNTRY_ID,COUNTRY_NAME,REGION_ID) VALUES ('ZW','Zimbabwe',4);

INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1000,'1297 Via Cola di Rie','00989','Roma',null,'IT');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1100,'93091 Calle della Testa','10934','Venice',null,'IT');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1300,'9450 Kamiya-cho','6823','Hiroshima',null,'JP');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1700,'2004 Charade Rd','98199','Seattle','Washington','US');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2000,'40-5-12 Laogianggen','190518','Beijing',null,'CN');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2300,'198 Clementi North','540198','Singapore',null,'SG');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2400,'8204 Arthur St',null,'London',null,'UK');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (3000,'Murtenstrasse 921','3095','Bern','BE','CH');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL');
INSERT INTO LOCATIONS3 (LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES (3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');

INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (10,'Administration',200,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (20,'Marketing',201,1800);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (30,'Purchasing',114,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (40,'Human Resources',203,2400);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (50,'Shipping',121,1500);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (60,'IT',103,1400);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (70,'Public Relations',204,2700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (80,'Sales',145,2500);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (90,'Executive',100,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (100,'Finance',108,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (110,'Accounting',205,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (120,'Treasury',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (130,'Corporate Tax',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (140,'Control And Credit',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (150,'Shareholder Services',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (160,'Benefits',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (170,'Manufacturing',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (180,'Construction',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (190,'Contracting',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (200,'Operations',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (210,'IT Support',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (220,'NOC',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (230,'IT Helpdesk',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (240,'Government Sales',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (250,'Retail Sales',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (260,'Recruiting',null,1700);
INSERT INTO DEPARTMENTS3 (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) VALUES (270,'Payroll',null,1700);

INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (100,'Steven','King','SKING','515.123.4567',TO_DATE('2003-06-17','YYYY-MM-DD'),'AD_PRES',24000,null,null,90);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',TO_DATE('2005-09-21','YYYY-MM-DD'),'AD_VP',17000,null,100,90);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (102,'Lex','De Haan','LDEHAAN','515.123.4569',TO_DATE('2001-01-13','YYYY-MM-DD'),'AD_VP',17000,null,100,90);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (103,'Alexander','Hunold','AHUNOLD','590.423.4567',TO_DATE('2006-01-03','YYYY-MM-DD'),'IT_PROG',9000,null,102,60);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (104,'Bruce','Ernst','BERNST','590.423.4568',TO_DATE('2007-05-21','YYYY-MM-DD'),'IT_PROG',6000,null,103,60);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (105,'David','Austin','DAUSTIN','590.423.4569',TO_DATE('2005-06-25','YYYY-MM-DD'),'IT_PROG',4800,null,103,60);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (106,'Valli','Pataballa','VPATABAL','590.423.4560',TO_DATE('2006-02-05','YYYY-MM-DD'),'IT_PROG',4800,null,103,60);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (107,'Diana','Lorentz','DLORENTZ','590.423.5567',TO_DATE('2007-02-07','YYYY-MM-DD'),'IT_PROG',4200,null,103,60);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (108,'Nancy','Greenberg','NGREENBE','515.124.4569',TO_DATE('2002-08-17','YYYY-MM-DD'),'FI_MGR',12008,null,101,100);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (109,'Daniel','Faviet','DFAVIET','515.124.4169',TO_DATE('2002-08-16','YYYY-MM-DD'),'FI_ACCOUNT',9000,null,108,100);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (110,'John','Chen','JCHEN','515.124.4269',TO_DATE('2005-09-28','YYYY-MM-DD'),'FI_ACCOUNT',8200,null,108,100);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (111,'Ismael','Sciarra','ISCIARRA','515.124.4369',TO_DATE('2005-09-30','YYYY-MM-DD'),'FI_ACCOUNT',7700,null,108,100);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (112,'Jose Manuel','Urman','JMURMAN','515.124.4469',TO_DATE('2006-03-07','YYYY-MM-DD'),'FI_ACCOUNT',7800,null,108,100);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (113,'Luis','Popp','LPOPP','515.124.4567',TO_DATE('2007-12-07','YYYY-MM-DD'),'FI_ACCOUNT',6900,null,108,100);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (114,'Den','Raphaely','DRAPHEAL','515.127.4561',TO_DATE('2002-12-07','YYYY-MM-DD'),'PU_MAN',11000,null,100,30);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (115,'Alexander','Khoo','AKHOO','515.127.4562',TO_DATE('2003-05-18','YYYY-MM-DD'),'PU_CLERK',3100,null,114,30);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (116,'Shelli','Baida','SBAIDA','515.127.4563',TO_DATE('2005-12-24','YYYY-MM-DD'),'PU_CLERK',2900,null,114,30);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (117,'Sigal','Tobias','STOBIAS','515.127.4564',TO_DATE('2005-07-24','YYYY-MM-DD'),'PU_CLERK',2800,null,114,30);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (118,'Guy','Himuro','GHIMURO','515.127.4565',TO_DATE('2006-11-15','YYYY-MM-DD'),'PU_CLERK',2600,null,114,30);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (119,'Karen','Colmenares','KCOLMENA','515.127.4566',TO_DATE('2007-08-10','YYYY-MM-DD'),'PU_CLERK',2500,null,114,30);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (120,'Matthew','Weiss','MWEISS','650.123.1234',TO_DATE('2004-07-18','YYYY-MM-DD'),'ST_MAN',8000,null,100,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (121,'Adam','Fripp','AFRIPP','650.123.2234',TO_DATE('2005-04-10','YYYY-MM-DD'),'ST_MAN',8200,null,100,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (122,'Payam','Kaufling','PKAUFLIN','650.123.3234',TO_DATE('2003-05-01','YYYY-MM-DD'),'ST_MAN',7900,null,100,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (123,'Shanta','Vollman','SVOLLMAN','650.123.4234',TO_DATE('2005-10-10','YYYY-MM-DD'),'ST_MAN',6500,null,100,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (124,'Kevin','Mourgos','KMOURGOS','650.123.5234',TO_DATE('2007-11-16','YYYY-MM-DD'),'ST_MAN',5800,null,100,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (125,'Julia','Nayer','JNAYER','650.124.1214',TO_DATE('2005-07-16','YYYY-MM-DD'),'ST_CLERK',3200,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224',TO_DATE('2006-09-28','YYYY-MM-DD'),'ST_CLERK',2700,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (127,'James','Landry','JLANDRY','650.124.1334',TO_DATE('2007-01-14','YYYY-MM-DD'),'ST_CLERK',2400,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (128,'Steven','Markle','SMARKLE','650.124.1434',TO_DATE('2008-03-08','YYYY-MM-DD'),'ST_CLERK',2200,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (129,'Laura','Bissot','LBISSOT','650.124.5234',TO_DATE('2005-08-20','YYYY-MM-DD'),'ST_CLERK',3300,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (130,'Mozhe','Atkinson','MATKINSO','650.124.6234',TO_DATE('2005-10-30','YYYY-MM-DD'),'ST_CLERK',2800,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (131,'James','Marlow','JAMRLOW','650.124.7234',TO_DATE('2005-02-16','YYYY-MM-DD'),'ST_CLERK',2500,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (132,'TJ','Olson','TJOLSON','650.124.8234',TO_DATE('2007-04-10','YYYY-MM-DD'),'ST_CLERK',2100,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (133,'Jason','Mallin','JMALLIN','650.127.1934',TO_DATE('2004-06-14','YYYY-MM-DD'),'ST_CLERK',3300,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (134,'Michael','Rogers','MROGERS','650.127.1834',TO_DATE('2006-08-26','YYYY-MM-DD'),'ST_CLERK',2900,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (135,'Ki','Gee','KGEE','650.127.1734',TO_DATE('2007-12-12','YYYY-MM-DD'),'ST_CLERK',2400,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (136,'Hazel','Philtanker','HPHILTAN','650.127.1634',TO_DATE('2008-02-06','YYYY-MM-DD'),'ST_CLERK',2200,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (137,'Renske','Ladwig','RLADWIG','650.121.1234',TO_DATE('2003-07-14','YYYY-MM-DD'),'ST_CLERK',3600,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (138,'Stephen','Stiles','SSTILES','650.121.2034',TO_DATE('2005-10-26','YYYY-MM-DD'),'ST_CLERK',3200,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (139,'John','Seo','JSEO','650.121.2019',TO_DATE('2006-02-12','YYYY-MM-DD'),'ST_CLERK',2700,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (140,'Joshua','Patel','JPATEL','650.121.1834',TO_DATE('2006-04-06','YYYY-MM-DD'),'ST_CLERK',2500,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (141,'Trenna','Rajs','TRAJS','650.121.8009',TO_DATE('2003-10-17','YYYY-MM-DD'),'ST_CLERK',3500,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (142,'Curtis','Davies','CDAVIES','650.121.2994',TO_DATE('2005-01-29','YYYY-MM-DD'),'ST_CLERK',3100,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (143,'Randall','Matos','RMATOS','650.121.2874',TO_DATE('2006-03-15','YYYY-MM-DD'),'ST_CLERK',2600,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (144,'Peter','Vargas','PVARGAS','650.121.2004',TO_DATE('2006-07-09','YYYY-MM-DD'),'ST_CLERK',2500,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (145,'John','Russell','JRUSSEL','011.44.1344.429268',TO_DATE('2004-10-01','YYYY-MM-DD'),'SA_MAN',14000,0.4,100,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (146,'Karen','Partners','KPARTNER','011.44.1344.467268',TO_DATE('2005-01-05','YYYY-MM-DD'),'SA_MAN',13500,0.3,100,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278',TO_DATE('2005-03-10','YYYY-MM-DD'),'SA_MAN',12000,0.3,100,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268',TO_DATE('2007-10-15','YYYY-MM-DD'),'SA_MAN',11000,0.3,100,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',TO_DATE('2008-01-29','YYYY-MM-DD'),'SA_MAN',10500,0.2,100,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (150,'Peter','Tucker','PTUCKER','011.44.1344.129268',TO_DATE('2005-01-30','YYYY-MM-DD'),'SA_REP',10000,0.3,145,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (151,'David','Bernstein','DBERNSTE','011.44.1344.345268',TO_DATE('2005-03-24','YYYY-MM-DD'),'SA_REP',9500,0.25,145,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (152,'Peter','Hall','PHALL','011.44.1344.478968',TO_DATE('2005-08-20','YYYY-MM-DD'),'SA_REP',9000,0.25,145,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (153,'Christopher','Olsen','COLSEN','011.44.1344.498718',TO_DATE('2006-03-30','YYYY-MM-DD'),'SA_REP',8000,0.2,145,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668',TO_DATE('2006-12-09','YYYY-MM-DD'),'SA_REP',7500,0.2,145,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508',TO_DATE('2007-11-23','YYYY-MM-DD'),'SA_REP',7000,0.15,145,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (156,'Janette','King','JKING','011.44.1345.429268',TO_DATE('2004-01-30','YYYY-MM-DD'),'SA_REP',10000,0.35,146,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (157,'Patrick','Sully','PSULLY','011.44.1345.929268',TO_DATE('2004-03-04','YYYY-MM-DD'),'SA_REP',9500,0.35,146,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268',TO_DATE('2004-08-01','YYYY-MM-DD'),'SA_REP',9000,0.35,146,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (159,'Lindsey','Smith','LSMITH','011.44.1345.729268',TO_DATE('2005-03-10','YYYY-MM-DD'),'SA_REP',8000,0.3,146,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (160,'Louise','Doran','LDORAN','011.44.1345.629268',TO_DATE('2005-12-15','YYYY-MM-DD'),'SA_REP',7500,0.3,146,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268',TO_DATE('2006-11-03','YYYY-MM-DD'),'SA_REP',7000,0.25,146,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268',TO_DATE('2005-11-11','YYYY-MM-DD'),'SA_REP',10500,0.25,147,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (163,'Danielle','Greene','DGREENE','011.44.1346.229268',TO_DATE('2007-03-19','YYYY-MM-DD'),'SA_REP',9500,0.15,147,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268',TO_DATE('2008-01-24','YYYY-MM-DD'),'SA_REP',7200,0.1,147,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (165,'David','Lee','DLEE','011.44.1346.529268',TO_DATE('2008-02-23','YYYY-MM-DD'),'SA_REP',6800,0.1,147,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (166,'Sundar','Ande','SANDE','011.44.1346.629268',TO_DATE('2008-03-24','YYYY-MM-DD'),'SA_REP',6400,0.1,147,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (167,'Amit','Banda','ABANDA','011.44.1346.729268',TO_DATE('2008-04-21','YYYY-MM-DD'),'SA_REP',6200,0.1,147,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (168,'Lisa','Ozer','LOZER','011.44.1343.929268',TO_DATE('2005-03-11','YYYY-MM-DD'),'SA_REP',11500,0.25,148,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268',TO_DATE('2006-03-23','YYYY-MM-DD'),'SA_REP',10000,0.2,148,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (170,'Tayler','Fox','TFOX','011.44.1343.729268',TO_DATE('2006-01-24','YYYY-MM-DD'),'SA_REP',9600,0.2,148,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (171,'William','Smith','WSMITH','011.44.1343.629268',TO_DATE('2007-02-23','YYYY-MM-DD'),'SA_REP',7400,0.15,148,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (172,'Elizabeth','Bates','EBATES','011.44.1343.529268',TO_DATE('2007-03-24','YYYY-MM-DD'),'SA_REP',7300,0.15,148,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268',TO_DATE('2008-04-21','YYYY-MM-DD'),'SA_REP',6100,0.1,148,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (174,'Ellen','Abel','EABEL','011.44.1644.429267',TO_DATE('2004-05-11','YYYY-MM-DD'),'SA_REP',11000,0.3,149,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266',TO_DATE('2005-03-19','YYYY-MM-DD'),'SA_REP',8800,0.25,149,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265',TO_DATE('2006-03-24','YYYY-MM-DD'),'SA_REP',8600,0.2,149,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264',TO_DATE('2006-04-23','YYYY-MM-DD'),'SA_REP',8400,0.2,149,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (178,'Kimberely','Grant','KGRANT','011.44.1644.429263',TO_DATE('2007-05-24','YYYY-MM-DD'),'SA_REP',7000,0.15,149,null);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262',TO_DATE('2008-01-04','YYYY-MM-DD'),'SA_REP',6200,0.1,149,80);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (180,'Winston','Taylor','WTAYLOR','650.507.9876',TO_DATE('2006-01-24','YYYY-MM-DD'),'SH_CLERK',3200,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (181,'Jean','Fleaur','JFLEAUR','650.507.9877',TO_DATE('2006-02-23','YYYY-MM-DD'),'SH_CLERK',3100,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (182,'Martha','Sullivan','MSULLIVA','650.507.9878',TO_DATE('2007-06-21','YYYY-MM-DD'),'SH_CLERK',2500,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (183,'Girard','Geoni','GGEONI','650.507.9879',TO_DATE('2008-02-03','YYYY-MM-DD'),'SH_CLERK',2800,null,120,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (184,'Nandita','Sarchand','NSARCHAN','650.509.1876',TO_DATE('2004-01-27','YYYY-MM-DD'),'SH_CLERK',4200,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (185,'Alexis','Bull','ABULL','650.509.2876',TO_DATE('2005-02-20','YYYY-MM-DD'),'SH_CLERK',4100,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (186,'Julia','Dellinger','JDELLING','650.509.3876',TO_DATE('2006-06-24','YYYY-MM-DD'),'SH_CLERK',3400,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (187,'Anthony','Cabrio','ACABRIO','650.509.4876',TO_DATE('2007-02-07','YYYY-MM-DD'),'SH_CLERK',3000,null,121,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (188,'Kelly','Chung','KCHUNG','650.505.1876',TO_DATE('2005-06-14','YYYY-MM-DD'),'SH_CLERK',3800,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (189,'Jennifer','Dilly','JDILLY','650.505.2876',TO_DATE('2005-08-13','YYYY-MM-DD'),'SH_CLERK',3600,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (190,'Timothy','Gates','TGATES','650.505.3876',TO_DATE('2006-07-11','YYYY-MM-DD'),'SH_CLERK',2900,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (191,'Randall','Perkins','RPERKINS','650.505.4876',TO_DATE('2007-12-19','YYYY-MM-DD'),'SH_CLERK',2500,null,122,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (192,'Sarah','Bell','SBELL','650.501.1876',TO_DATE('2004-02-04','YYYY-MM-DD'),'SH_CLERK',4000,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (193,'Britney','Everett','BEVERETT','650.501.2876',TO_DATE('2005-03-03','YYYY-MM-DD'),'SH_CLERK',3900,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876',TO_DATE('2006-07-01','YYYY-MM-DD'),'SH_CLERK',3200,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (195,'Vance','Jones','VJONES','650.501.4876',TO_DATE('2007-03-17','YYYY-MM-DD'),'SH_CLERK',2800,null,123,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (196,'Alana','Walsh','AWALSH','650.507.9811',TO_DATE('2006-04-24','YYYY-MM-DD'),'SH_CLERK',3100,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822',TO_DATE('2006-05-23','YYYY-MM-DD'),'SH_CLERK',3000,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (198,'Donald','OConnell','DOCONNEL','650.507.9833',TO_DATE('2007-06-21','YYYY-MM-DD'),'SH_CLERK',2600,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (199,'Douglas','Grant','DGRANT','650.507.9844',TO_DATE('2008-01-13','YYYY-MM-DD'),'SH_CLERK',2600,null,124,50);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (200,'Jennifer','Whalen','JWHALEN','515.123.4444',TO_DATE('2003-09-17','YYYY-MM-DD'),'AD_ASST',4400,null,101,10);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (201,'Michael','Hartstein','MHARTSTE','515.123.5555',TO_DATE('2004-02-17','YYYY-MM-DD'),'MK_MAN',13000,null,100,20);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (202,'Pat','Fay','PFAY','603.123.6666',TO_DATE('2005-08-17','YYYY-MM-DD'),'MK_REP',6000,null,201,20);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (203,'Susan','Mavris','SMAVRIS','515.123.7777',TO_DATE('2002-06-07','YYYY-MM-DD'),'HR_REP',6500,null,101,40);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (204,'Hermann','Baer','HBAER','515.123.8888',TO_DATE('2002-06-07','YYYY-MM-DD'),'PR_REP',10000,null,101,70);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (205,'Shelley','Higgins','SHIGGINS','515.123.8080',TO_DATE('2002-06-07','YYYY-MM-DD'),'AC_MGR',12008,null,101,110);
INSERT INTO EMPLOYEES3 (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (206,'William','Gietz','WGIETZ','515.123.8181',TO_DATE('2002-06-07','YYYY-MM-DD'),'AC_ACCOUNT',8300,null,205,110);

INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('AD_PRES','President',20080,40000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('AD_VP','Administration Vice President',15000,30000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('AD_ASST','Administration Assistant',3000,6000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('FI_MGR','Finance Manager',8200,16000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('FI_ACCOUNT','Accountant',4200,9000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('AC_MGR','Accounting Manager',8200,16000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('AC_ACCOUNT','Public Accountant',4200,9000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('SA_MAN','Sales Manager',10000,20080);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('SA_REP','Sales Representative',6000,12008);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('PU_MAN','Purchasing Manager',8000,15000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('PU_CLERK','Purchasing Clerk',2500,5500);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('ST_MAN','Stock Manager',5500,8500);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('ST_CLERK','Stock Clerk',2008,5000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('SH_CLERK','Shipping Clerk',2500,5500);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('IT_PROG','Programmer',4000,10000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('MK_MAN','Marketing Manager',9000,15000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('MK_REP','Marketing Representative',4000,9000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('HR_REP','Human Resources Representative',4000,9000);
INSERT INTO JOBS3 (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES ('PR_REP','Public Relations Representative',4500,10500);

CREATE TABLE D3
( D_ID         NUMBER(4)          
, M_ID            NUMBER(6)
, CONSTRAINT D3_ID_PK PRIMARY KEY(D_ID)
, CONSTRAINT D3_M_FK FOREIGN KEY(M_ID)
            REFERENCES E3 (M_ID)
);

CREATE TABLE E3
( E_ID NUMBER(6)
, M_ID NUMBER(6)
, D_ID NUMBER(4)
, CONSTRAINT E3_E_PK PRIMARY KEY(E_ID)
, CONSTRAINT E3_D_FK FOREIGN KEY(D_ID)
             REFERENCES D3 (D_ID)
, CONSTRAINT E3_M_FK FOREIGN KEY(M_ID)
             REFERENCES E3 (E_ID)
);

------------------------------------------------------

CREATE TABLE D3
( D_ID         NUMBER(4)          
, M_ID            NUMBER(6)
, CONSTRAINT D3_ID_PK PRIMARY KEY(D_ID)
-- , CONSTRAINT D3_M_FK FOREIGN KEY(M_ID)
--             REFERENCES E3 (M_ID)
);

CREATE TABLE E3
( E_ID NUMBER(6)
, M_ID NUMBER(6)
, D_ID NUMBER(4)
, CONSTRAINT E3_E_PK PRIMARY KEY(E_ID)
, CONSTRAINT E3_D_FK FOREIGN KEY(D_ID)
             REFERENCES D3 (D_ID)
, CONSTRAINT E3_M_FK FOREIGN KEY(M_ID)
             REFERENCES E3 (E_ID)
);
-- E3_M_FK 에서 참조하는 것: 나자신의 ID

ALTER TABLE D3
ADD CONSTRAINT D3_M_FK FOREIGN KEY(M_ID)
                REFERENCES E3 (M_ID);

INSERT INTO D3(D_ID,M_ID) VALUES(1,2);
INSERT INTO E3(E_ID,M_ID,D_ID) VALUES(1,1,1);





PURGE RECYCLEBIN;

DROP TABLE JOB_HISTORY3;   
DROP TABLE REGIONS3;
DROP TABLE COUNTRIES3;        
DROP TABLE LOCATIONS3;        
DROP TABLE DEPARTMENTS3;
DROP TABLE EMPLOYEES3;        
DROP TABLE JOBS3;