[SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE

2013.02.10 11:55

가을의 곰을... 조회 수:24564

[SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE

 

--------------------------------------------------------------------------------
-- 1) DB계정 생성
--------------------------------------------------------------------------------
-- 사용자 생성
CREATE USER SQLBOOK IDENTIFIED BY SQLBOOK;

-- 기본권한 부여
GRANT CONNECT, RESOURCE TO SQLBOOK;

--------------------------------------------------------------------------------
-- 2) 테이블 생성
--------------------------------------------------------------------------------
CREATE TABLE 부서
(부서코드           VARCHAR2(2)     NOT NULL
,부서명             VARCHAR2(10)    NOT NULL
,지역               VARCHAR2(10)    NULL
);

CREATE TABLE 사원
(사원번호           NUMBER(3)       NOT NULL
,사원명             VARCHAR2(10)    NOT NULL
,부서코드           VARCHAR2(2)     NOT NULL
,직급               VARCHAR2(6)     NOT NULL
,입사일자           DATE            NOT NULL
,퇴사일자           DATE            NULL
,급여구분           VARCHAR2(4)     NOT NULL
,월급여             NUMBER(10)      NOT NULL
,등록일시           DATE            NOT NULL
);

CREATE TABLE 발령이력
(사원번호           NUMBER(3)       NOT NULL
,이력순번           NUMBER(2)       NOT NULL
,발령일자           DATE            NOT NULL
,직급               VARCHAR2(6)     NOT NULL
,부서코드           VARCHAR2(2)     NOT NULL
);

CREATE TABLE 연봉제급여
(사원번호           NUMBER(3)       NOT NULL
,연봉액             NUMBER(10)      NOT NULL
,월기본급           NUMBER(10)      NOT NULL
);

CREATE TABLE 호봉제급여
(사원번호           NUMBER(3)       NOT NULL
,호봉               NUMBER(10)      NOT NULL
,월기본급           NUMBER(10)      NOT NULL
);

CREATE TABLE 금액제급여
(사원번호           NUMBER(3)       NOT NULL
,월기본급           NUMBER(10)      NOT NULL
);

CREATE TABLE 기타급여
(사원번호           NUMBER(3)       NOT NULL
,기본수당지급여부   VARCHAR2(1)     NULL
,기본수당액         NUMBER(10)      NULL
,특별수당지급여부   VARCHAR2(1)     NULL
,특별수당액         NUMBER(10)      NULL
,성과급지급여부     VARCHAR2(1)     NULL
,성과급             NUMBER(10)      NULL
);

CREATE TABLE 급여지급
(급여월             VARCHAR2(6)     NOT NULL
,사원번호           NUMBER(3)       NOT NULL
,월급여             NUMBER(10)      NOT NULL
,기본급             NUMBER(10)      NOT NULL
,수당               NUMBER(10)      NULL
,성과급             NUMBER(10)      NULL
);

ALTER TABLE 부서       ADD CONSTRAINT 부서_PK       PRIMARY KEY(부서코드         );
ALTER TABLE 사원       ADD CONSTRAINT 사원_PK       PRIMARY KEY(사원번호         );
ALTER TABLE 발령이력   ADD CONSTRAINT 발령이력_PK   PRIMARY KEY(사원번호,이력순번);
ALTER TABLE 연봉제급여 ADD CONSTRAINT 연봉제급여_PK PRIMARY KEY(사원번호         );
ALTER TABLE 호봉제급여 ADD CONSTRAINT 호봉제급여_PK PRIMARY KEY(사원번호         );
ALTER TABLE 금액제급여 ADD CONSTRAINT 금액제급여_PK PRIMARY KEY(사원번호         );
ALTER TABLE 기타급여   ADD CONSTRAINT 기타급여_PK   PRIMARY KEY(사원번호         );
ALTER TABLE 급여지급   ADD CONSTRAINT 급여지급_PK   PRIMARY KEY(급여월,사원번호  );


--------------------------------------------------------------------------------
-- 3) 데이터 생성
--------------------------------------------------------------------------------
-- 부서
TRUNCATE TABLE 부서;

INSERT INTO 부서 VALUES('10', '인사팀', '서울');
INSERT INTO 부서 VALUES('20', '영업팀', '대전');
INSERT INTO 부서 VALUES('30', '전산팀', '수원');

COMMIT;

-- 사원
TRUNCATE TABLE 사원;

INSERT INTO 사원 VALUES(101, '조동진', '30', '대리', TO_DATE('2008-03-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '연봉', 1400000, TO_DATE('2008/03/01 14:11:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(102, '한영애', '30', '대리', TO_DATE('2008-05-01', 'YYYY-MM-DD'), TO_DATE('2010-12-01', 'YYYY-MM-DD'), '연봉', 1540000, TO_DATE('2008/05/01 14:12:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(103, '조규찬', '20', '대리', TO_DATE('2008-12-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '금액', 1600000, TO_DATE('2008/12/01 14:03:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(104, '이상은', '10', '과장', TO_DATE('2003-05-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '연봉', 1680000, TO_DATE('2003/05/01 13:54:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(105, '조덕배', '10', '과장', TO_DATE('2004-08-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '호봉', 1540000, TO_DATE('2004/08/01 13:55:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(106, '장필순', '10', '과장', TO_DATE('2005-09-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '호봉', 1700000, TO_DATE('2005/09/01 13:56:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(107, '오석준', '30', '차장', TO_DATE('2001-10-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '금액', 1770000, TO_DATE('2001/10/01 14:17:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(108, '이규석', '20', '차장', TO_DATE('2001-10-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '호봉', 1600000, TO_DATE('2001/10/01 14:08:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(109, '전수경', '30', '대리', TO_DATE('2007-03-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '연봉', 1470000, TO_DATE('2007/03/01 14:19:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(110, '이선희', '10', '차장', TO_DATE('2000-03-01', 'YYYY-MM-DD'), TO_DATE('2007-12-01', 'YYYY-MM-DD'), '호봉', 1790000, TO_DATE('2000/03/01 14:00:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(111, '김창완', '30', '차장', TO_DATE('2003-03-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '연봉', 1530000, TO_DATE('2003/03/01 14:21:00', 'YYYY/MM/DD HH24:MI:SS'));
INSERT INTO 사원 VALUES(112, '이현우', '20', '과장', TO_DATE('2004-03-01', 'YYYY-MM-DD'), TO_DATE('', 'YYYY-MM-DD'), '금액', 1500000, TO_DATE('2004/03/01 14:12:00', 'YYYY/MM/DD HH24:MI:SS'));


COMMIT;

-- 발령이력
TRUNCATE TABLE 발령이력;

INSERT INTO 발령이력 VALUES(101, 1, TO_DATE('2008-03-01', 'YYYY-MM-DD'), '사원', '30');
INSERT INTO 발령이력 VALUES(101, 2, TO_DATE('2010-03-01', 'YYYY-MM-DD'), '대리', '30');
INSERT INTO 발령이력 VALUES(102, 1, TO_DATE('2008-05-01', 'YYYY-MM-DD'), '대리', '30');
INSERT INTO 발령이력 VALUES(103, 1, TO_DATE('2008-12-01', 'YYYY-MM-DD'), '사원', '20');
INSERT INTO 발령이력 VALUES(103, 2, TO_DATE('2010-12-01', 'YYYY-MM-DD'), '대리', '20');
INSERT INTO 발령이력 VALUES(104, 1, TO_DATE('2003-05-01', 'YYYY-MM-DD'), '사원', '10');
INSERT INTO 발령이력 VALUES(104, 2, TO_DATE('2005-05-01', 'YYYY-MM-DD'), '대리', '10');
INSERT INTO 발령이력 VALUES(104, 3, TO_DATE('2008-05-01', 'YYYY-MM-DD'), '과장', '10');
INSERT INTO 발령이력 VALUES(105, 1, TO_DATE('2004-08-01', 'YYYY-MM-DD'), '사원', '10');
INSERT INTO 발령이력 VALUES(105, 2, TO_DATE('2007-08-01', 'YYYY-MM-DD'), '대리', '10');
INSERT INTO 발령이력 VALUES(105, 3, TO_DATE('2010-08-01', 'YYYY-MM-DD'), '과장', '10');
INSERT INTO 발령이력 VALUES(106, 1, TO_DATE('2005-09-01', 'YYYY-MM-DD'), '과장', '10');
INSERT INTO 발령이력 VALUES(107, 1, TO_DATE('2001-10-01', 'YYYY-MM-DD'), '차장', '30');
INSERT INTO 발령이력 VALUES(108, 1, TO_DATE('2001-10-01', 'YYYY-MM-DD'), '차장', '20');
INSERT INTO 발령이력 VALUES(109, 1, TO_DATE('2007-03-01', 'YYYY-MM-DD'), '대리', '30');
INSERT INTO 발령이력 VALUES(110, 1, TO_DATE('2000-03-01', 'YYYY-MM-DD'), '차장', '10');
INSERT INTO 발령이력 VALUES(111, 1, TO_DATE('2003-03-01', 'YYYY-MM-DD'), '차장', '30');
INSERT INTO 발령이력 VALUES(112, 1, TO_DATE('2004-03-01', 'YYYY-MM-DD'), '과장', '20');

COMMIT;

-- 연봉제급여
TRUNCATE TABLE 연봉제급여;

INSERT INTO 연봉제급여 VALUES(101, 15600000, 1300000);
INSERT INTO 연봉제급여 VALUES(102, 15840000, 1320000);
INSERT INTO 연봉제급여 VALUES(104, 17520000, 1460000);
INSERT INTO 연봉제급여 VALUES(109, 16200000, 1350000);
INSERT INTO 연봉제급여 VALUES(111, 18360000, 1530000);

COMMIT;

-- 호봉제급여
TRUNCATE TABLE 호봉제급여;

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

INSERT INTO 호봉제급여 VALUES(105, 9, 1440000);
INSERT INTO 호봉제급여 VALUES(106, 8, 1400000);
INSERT INTO 호봉제급여 VALUES(108, 11, 1500000);
INSERT INTO 호봉제급여 VALUES(110, 13, 1590000);

COMMIT;

-- 금액제금여
TRUNCATE TABLE 금액제급여;

INSERT INTO 금액제급여 VALUES(103, 1300000);
INSERT INTO 금액제급여 VALUES(107, 1550000);
INSERT INTO 금액제급여 VALUES(112, 1400000);

COMMIT;

-- 기타급여
TRUNCATE TABLE 기타급여;

INSERT INTO 기타급여 VALUES(101, 'Y', 100000, '', NULL, '', NULL);
INSERT INTO 기타급여 VALUES(102, 'Y', 100000, 'Y', 120000, '', NULL);
INSERT INTO 기타급여 VALUES(103, 'Y', 100000, '', NULL, 'Y', 200000);
INSERT INTO 기타급여 VALUES(104, 'Y', 100000, 'Y', 120000, '', NULL);
INSERT INTO 기타급여 VALUES(105, 'Y', 100000, '', NULL, '', NULL);
INSERT INTO 기타급여 VALUES(106, 'Y', 100000, '', NULL, 'Y', 200000);
INSERT INTO 기타급여 VALUES(107, 'Y', 100000, 'Y', 120000, '', NULL);
INSERT INTO 기타급여 VALUES(108, 'Y', 100000, '', NULL, '', NULL);
INSERT INTO 기타급여 VALUES(109, '', NULL, 'Y', 120000, '', NULL);
INSERT INTO 기타급여 VALUES(110, '', NULL, '', NULL, 'Y', 200000);
INSERT INTO 기타급여 VALUES(112, 'Y', 100000, '', NULL, '', NULL);

COMMIT;

-- 급여지급
TRUNCATE TABLE 급여지급;

INSERT INTO 급여지급 VALUES('201001', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201001', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201001', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201001', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201001', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201001', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201001', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201001', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201001', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201001', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201001', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201002', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201002', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201002', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201002', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201002', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201002', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201002', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201002', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201002', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201002', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201002', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201003', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201003', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201003', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201003', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201003', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201003', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201003', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201003', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201003', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201003', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201003', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201004', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201004', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201004', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201004', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201004', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201004', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201004', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201004', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201004', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201004', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201004', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201005', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201005', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201005', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201005', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201005', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201005', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201005', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201005', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201005', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201005', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201005', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201006', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201006', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201006', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201006', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201006', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201006', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201006', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201006', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201006', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201006', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201006', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201007', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201007', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201007', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201007', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201007', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201007', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201007', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201007', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201007', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201007', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201007', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201008', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201008', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201008', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201008', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201008', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201008', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201008', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201008', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201008', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201008', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201008', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201009', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201009', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201009', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201009', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201009', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201009', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201009', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201009', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201009', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201009', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201009', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201010', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201010', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201010', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201010', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201010', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201010', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201010', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201010', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201010', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201010', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201010', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201011', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201011', 102, 1540000, 1320000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201011', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201011', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201011', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201011', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201011', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201011', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201011', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201011', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201011', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201012', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201012', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201012', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201012', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201012', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201012', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201012', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201012', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201012', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201012', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201101', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201101', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201101', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201101', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201101', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201101', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201101', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201101', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201101', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201101', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201102', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201102', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201102', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201102', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201102', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201102', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201102', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201102', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201102', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201102', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201103', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201103', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201103', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201103', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201103', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201103', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201103', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201103', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201103', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201103', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201104', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201104', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201104', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201104', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201104', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201104', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201104', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201104', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201104', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201104', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201105', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201105', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201105', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201105', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201105', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201105', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201105', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201105', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201105', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201105', 112, 1500000, 1400000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201106', 101, 1400000, 1300000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201106', 103, 1600000, 1300000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201106', 104, 1680000, 1460000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201106', 105, 1540000, 1440000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201106', 106, 1700000, 1400000, 100000, 200000);
INSERT INTO 급여지급 VALUES('201106', 107, 1770000, 1550000, 220000, NULL);
INSERT INTO 급여지급 VALUES('201106', 108, 1600000, 1500000, 100000, NULL);
INSERT INTO 급여지급 VALUES('201106', 109, 1470000, 1350000, 120000, NULL);
INSERT INTO 급여지급 VALUES('201106', 111, 1530000, 1530000, NULL, NULL);
INSERT INTO 급여지급 VALUES('201106', 112, 1500000, 1400000, 100000, NULL);

COMMIT;

--------------------------------------------------------------------------------
-- 4) 테이블과 컬럼 코멘트
--------------------------------------------------------------------------------
COMMENT ON TABLE 부서 IS '부서';
COMMENT ON TABLE 사원 IS '사원';
COMMENT ON TABLE 발령이력 IS '발령이력';
COMMENT ON TABLE 연봉제급여 IS '연봉제급여';
COMMENT ON TABLE 호봉제급여 IS '호봉제급여';
COMMENT ON TABLE 금액제급여 IS '금액제급여';
COMMENT ON TABLE 기타급여 IS '기타급여';
COMMENT ON TABLE 급여지급 IS '급여지급';

COMMENT ON COLUMN 금액제급여.사원번호 IS '사원번호';
COMMENT ON COLUMN 금액제급여.월기본급 IS '월기본급';
COMMENT ON COLUMN 급여지급.급여월 IS '급여월';
COMMENT ON COLUMN 급여지급.사원번호 IS '사원번호';
COMMENT ON COLUMN 급여지급.월급여 IS '월급여';
COMMENT ON COLUMN 급여지급.기본급 IS '기본급';
COMMENT ON COLUMN 급여지급.수당 IS '수당';
COMMENT ON COLUMN 급여지급.성과급 IS '성과급';
COMMENT ON COLUMN 기타급여.사원번호 IS '사원번호';
COMMENT ON COLUMN 기타급여.기본수당지급여부 IS '기본수당지급여부';
COMMENT ON COLUMN 기타급여.기본수당액 IS '기본수당액';
COMMENT ON COLUMN 기타급여.특별수당지급여부 IS '특별수당지급여부';
COMMENT ON COLUMN 기타급여.특별수당액 IS '특별수당액';
COMMENT ON COLUMN 기타급여.성과급지급여부 IS '성과급지급여부';
COMMENT ON COLUMN 기타급여.성과급 IS '성과급';
COMMENT ON COLUMN 발령이력.사원번호 IS '사원번호';
COMMENT ON COLUMN 발령이력.이력순번 IS '이력순번';
COMMENT ON COLUMN 발령이력.발령일자 IS '발령일자';
COMMENT ON COLUMN 발령이력.직급 IS '직급';
COMMENT ON COLUMN 발령이력.부서코드 IS '부서코드';
COMMENT ON COLUMN 부서.부서코드 IS '부서코드';
COMMENT ON COLUMN 부서.부서명 IS '부서명';
COMMENT ON COLUMN 부서.지역 IS '지역';
COMMENT ON COLUMN 사원.사원번호 IS '사원번호';
COMMENT ON COLUMN 사원.사원명 IS '사원명';
COMMENT ON COLUMN 사원.부서코드 IS '부서코드';
COMMENT ON COLUMN 사원.직급 IS '직급';
COMMENT ON COLUMN 사원.입사일자 IS '입사일자';
COMMENT ON COLUMN 사원.퇴사일자 IS '퇴사일자';
COMMENT ON COLUMN 사원.급여구분 IS '급여구분';
COMMENT ON COLUMN 사원.월급여 IS '월급여';
COMMENT ON COLUMN 사원.등록일시 IS '등록일시';
COMMENT ON COLUMN 연봉제급여.사원번호 IS '사원번호';
COMMENT ON COLUMN 연봉제급여.연봉액 IS '연봉액';
COMMENT ON COLUMN 연봉제급여.월기본급 IS '월기본급';
COMMENT ON COLUMN 호봉제급여.사원번호 IS '사원번호';
COMMENT ON COLUMN 호봉제급여.호봉 IS '호봉';
COMMENT ON COLUMN 호봉제급여.월기본급 IS '월기본급';

 

 

 

 

 

 

 

 

 

 

 

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
공지 오라클 기본 샘플 데이터베이스 졸리운_곰 2014.01.02 25085
» [SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE 가을의 곰을... 2013.02.10 24564
공지 [G_SQL] Sample Database 가을의 곰을... 2012.05.20 25943
1085 [MYSQL] MySQL 기간에 따른 데이터 가져오기 (ex 3 개월 전, 일주일 전, 하루 전) 졸리운_곰 2024.04.18 0
1084 [데이터분석][파이썬][python] 한글 글꼴 사용 (matplotlib) 졸리운_곰 2024.04.18 3
1083 [암호화폐] 솔리디티를 이용해 이더리움 스마트 계약 시작하기 file 졸리운_곰 2024.04.05 2
1082 암호화폐 (비트코인, cryptocurrency, bitcoin) 파이썬을 이용한 가상화폐 시세 분석 file 졸리운_곰 2024.03.28 4
1081 [데이터베이스 모델링] 테이블정의양식 file 졸리운_곰 2024.02.23 5
1080 [MYSQL] MYSQL JSON 관련 함수 file 졸리운_곰 2024.02.18 11
1079 [통계 R 언어] R 언어에서 Python 코드 호출 : Calling Python from R 졸리운_곰 2024.01.28 3
1078 암호화폐 (비트코인, cryptocurrency, bitcoin) Solidity 이더리움 Solidity Tutorial: How to build and deploy a smart contract to send Ether from one account to another file 졸리운_곰 2024.01.23 2
1077 암호화폐 (비트코인, cryptocurrency, bitcoin) Solidity 이더리움 Cheatsheet 졸리운_곰 2024.01.23 1
1076 [데이터분석 & 데이터 사이언스] 데이터에 관한 꼭 알아야 할 오해와 진실 12가지 졸리운_곰 2024.01.17 5
1075 [tensorflow 1.13 1.x 버전 설치법] [TensorFlow] Anaconda 가상환경 이용하여 TensorFlow GPU 설치 졸리운_곰 2024.01.08 2
1074 [R lang 크롤링] 비트코인(암호화폐) 가격 불러오기 in R 졸리운_곰 2023.12.11 6
1073 [통계 R 언어] 이동평균이 표시된 plotly 동적 주가 그래프 in R file 졸리운_곰 2023.12.11 5
1072 [R lang 크롤링] 금융 데이터 분석 주가 데이터 가져오기 in R 졸리운_곰 2023.12.11 4
1071 [R lang 크롤링] (R) [전략적가치투자] - 증액 투자법 file 졸리운_곰 2023.12.10 2
1070 [R lang 크롤링] (R) [전략적가치투자] - 정액 투자법 file 졸리운_곰 2023.12.10 3
1069 [R lang 크롤링] (R) [전략적가치투자] - 매입 보유법, 정률 투자법, 변율 투자법 file 졸리운_곰 2023.12.10 5
1068 [R lang 크롤링] (R) wordcloud2로 더 예쁘게 시각화하기 file 졸리운_곰 2023.12.10 6
1067 [R lang 크롤링] (R) 나만의 주식차트만들기 / quantmod 패키지(2) file 졸리운_곰 2023.12.10 5
1066 [R lang 크롤링] (R) R에서 주가정보 가져오기 / quantmod 패키지(1) file 졸리운_곰 2023.12.10 3
쓰기
태그
대표 김성준 주소 : 경기 용인 분당수지 U타워 등록번호 : 142-07-27414
통신판매업 신고 : 제2012-용인수지-0185호 출판업 신고 : 수지구청 제 123호 개인정보보호최고책임자 : 김성준 sjkim70@stechstar.com
대표전화 : 010-4589-2193 [fax] 02-6280-1294 COPYRIGHT(C) stechstar.com ALL RIGHTS RESERVED