1. https://www.oracle.com/database/technologies/xe-prior-release-downloads.html
2. Products – Oracle Database – Oracle Database 11g Express Edition 다운로드
3. 설치 및 관리자 계정 암호 설정
4. DB 접속확인 및 SQLplus 실행
5. 관리자 계정 로그인
안되면 ORACLE_HOME , PATH 환경 변수 설정
관리자 계정 : SYSTEM
비밀번호 : 다운로드 할 때 설정한 비밀번호 ( 강의실에는 ORACLE로 설정해 둠 )
SQL Developer 설치
1. www.oracle.com 접속
2. Products – Oracle Database – SQL Developer 다운로드
3. 압축 해제 후 바로 실행
- CREATE USER GREEDY IDENTIFIED BY GREEDY; -- 아이디 / 비밀번호
- GRANT RESOURCE, CONNECT TO GREEDY;
- CREATE USER EMPLOYEE IDENTIFIED BY EMPLOYEE;
- GRANT RESOURCE, CONNECT TO EMPLOYEE;
EMPLOYEE.sql
ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;
DROP TABLE JOB;
DROP TABLE LOCATION;
DROP TABLE NATIONAL;
DROP TABLE SAL_GRADE;
--------------------------------------------------------
-- DDL for Table DEPARTMENT
--------------------------------------------------------
CREATE TABLE "EMPLOYEE"."DEPARTMENT"
( "DEPT_ID" CHAR(2 BYTE),
"DEPT_TITLE" VARCHAR2(35 BYTE),
"LOCATION_ID" CHAR(2 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "EMPLOYEE"."DEPARTMENT"."DEPT_ID" IS '부서코드';
COMMENT ON COLUMN "EMPLOYEE"."DEPARTMENT"."DEPT_TITLE" IS '부서명';
COMMENT ON COLUMN "EMPLOYEE"."DEPARTMENT"."LOCATION_ID" IS '지역코드';
COMMENT ON TABLE "EMPLOYEE"."DEPARTMENT" IS '부서';
REM INSERTING into EMPLOYEE.DEPARTMENT
SET DEFINE OFF;
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D1','인사관리부','L1');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D2','회계관리부','L1');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D3','마케팅부','L1');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D4','국내영업부','L1');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D5','해외영업1부','L2');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D6','해외영업2부','L3');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D7','해외영업3부','L4');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D8','기술지원부','L5');
Insert into EMPLOYEE.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D9','총무부','L1');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK2
--------------------------------------------------------
CREATE UNIQUE INDEX "EMPLOYEE"."엔터티1_PK2" ON "EMPLOYEE"."DEPARTMENT" ("DEPT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table DEPARTMENT
--------------------------------------------------------
ALTER TABLE "EMPLOYEE"."DEPARTMENT" ADD CONSTRAINT "DEPARTMENT_PK" PRIMARY KEY ("DEPT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "EMPLOYEE"."DEPARTMENT" MODIFY ("LOCATION_ID" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE"."DEPARTMENT" MODIFY ("DEPT_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table EMPLOYEE
--------------------------------------------------------
CREATE TABLE "EMPLOYEE"."EMPLOYEE"
( "EMP_ID" VARCHAR2(3 BYTE),
"EMP_NAME" VARCHAR2(20 BYTE),
"EMP_NO" CHAR(14 BYTE),
"EMAIL" VARCHAR2(25 BYTE),
"PHONE" VARCHAR2(12 BYTE),
"DEPT_CODE" CHAR(2 BYTE),
"JOB_CODE" CHAR(2 BYTE),
"SAL_LEVEL" CHAR(2 BYTE),
"SALARY" NUMBER,
"BONUS" NUMBER,
"MANAGER_ID" VARCHAR2(3 BYTE),
"HIRE_DATE" DATE,
"ENT_DATE" DATE,
"ENT_YN" CHAR(1 BYTE) DEFAULT 'N'
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."EMP_ID" IS '사원번호';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."EMP_NAME" IS '직원명';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."EMP_NO" IS '주민등록번호';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."EMAIL" IS '이메일';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."PHONE" IS '전화번호';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."DEPT_CODE" IS '부서코드';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."JOB_CODE" IS '직급코드';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."SAL_LEVEL" IS '급여등급';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."SALARY" IS '급여';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."BONUS" IS '보너스율';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."MANAGER_ID" IS '관리자사번';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."HIRE_DATE" IS '입사일';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."ENT_DATE" IS '퇴사일';
COMMENT ON COLUMN "EMPLOYEE"."EMPLOYEE"."ENT_YN" IS '재직여부';
COMMENT ON TABLE "EMPLOYEE"."EMPLOYEE" IS '사원';
REM INSERTING into EMPLOYEE.EMPLOYEE
SET DEFINE OFF;
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('200','선동일','621235-1985634','sun_di@uni.or.kr','01099546325','D9','J1','S1',8000000,0.3,null,to_date('90/02/06','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('201','송종기','631156-1548654','song_jk@uni.or.kr','01045686656','D9','J2','S1',6000000,null,'200',to_date('01/09/01','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('202','노옹철','861015-1356452','no_hc@uni.or.kr','01066656263','D9','J2','S4',3700000,null,'201',to_date('01/01/01','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('203','송은희','631010-2653546','song_eh@uni.or.kr','01077607879','D6','J4','S5',2800000,null,'204',to_date('96/05/03','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('204','유재식','660508-1342154','yoo_js@uni.or.kr','01099999129','D6','J3','S4',3400000,0.2,'200',to_date('00/12/29','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('205','정중하','770102-1357951','jung_jh@uni.or.kr','01036654875','D6','J3','S4',3900000,null,'204',to_date('99/09/09','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('206','박나라','630709-2054321','pack_nr@uni.or.kr','01096935222','D5','J7','S6',1800000,null,'207',to_date('08/04/02','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('207','하이유','690402-2040612','ha_iy@uni.or.kr','01036654488','D5','J5','S5',2200000,0.1,'200',to_date('94/07/07','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('208','김해술','870927-1313564','kim_hs@uni.or.kr','01078634444','D5','J5','S5',2500000,null,'207',to_date('04/04/30','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('209','심봉선','750206-1325546','sim_bs@uni.or.kr','0113654485','D5','J3','S4',3500000,0.15,'207',to_date('11/11/11','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('210','윤은해','650505-2356985','youn_eh@uni.or.kr','0179964233','D5','J7','S5',2000000,null,'207',to_date('01/02/03','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('211','전형돈','830807-1121321','jun_hd@uni.or.kr','01044432222','D8','J6','S5',2000000,null,'200',to_date('12/12/12','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('212','장쯔위','780923-2234542','jang_zw@uni.or.kr','01066682224','D8','J6','S5',2550000,0.25,'211',to_date('15/06/17','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('213','하동운','621111-1785463','ha_dh@uni.or.kr','01158456632',null,'J6','S5',2320000,0.1,null,to_date('99/12/31','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('214','방명수','856795-1313513','bang_ms@uni.or.kr','01074127545','D1','J7','S6',1380000,null,'200',to_date('10/04/04','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('215','대북혼','881130-1050911','dae_bh@uni.or.kr','01088808584','D5','J5','S4',3760000,null,null,to_date('17/06/19','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('216','차태연','770808-1364897','cha_ty@uni.or.kr','01064643212','D1','J6','S5',2780000,0.2,'214',to_date('13/03/01','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('217','전지연','770808-2665412','jun_jy@uni.or.kr','01033624442','D1','J6','S4',3660000,0.3,'214',to_date('07/03/20','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('218','이오리','870427-2232123','loo_or@uni.or.kr','01022306545',null,'J7','S5',2890000,null,null,to_date('16/11/28','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('219','임시환','660712-1212123','im_sw@uni.or.kr',null,'D2','J4','S6',1550000,null,null,to_date('99/09/09','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('220','이중석','770823-1113111','lee_js@uni.or.kr',null,'D2','J4','S5',2490000,null,null,to_date('14/09/18','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('221','유하진','800808-1123341','yoo_hj@uni.or.kr',null,'D2','J4','S5',2480000,null,null,to_date('94/01/20','RR/MM/DD'),null,'N');
Insert into EMPLOYEE.EMPLOYEE (EMP_ID,EMP_NAME,EMP_NO,EMAIL,PHONE,DEPT_CODE,JOB_CODE,SAL_LEVEL,SALARY,BONUS,MANAGER_ID,HIRE_DATE,ENT_DATE,ENT_YN) values ('222','이태림','760918-2854697','lee_tr@uni.or.kr','01033000002','D8','J6','S5',2436240,0.35,'100',to_date('97/09/12','RR/MM/DD'),to_date('17/09/12','RR/MM/DD'),'Y');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "EMPLOYEE"."엔터티1_PK" ON "EMPLOYEE"."EMPLOYEE" ("EMP_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table EMPLOYEE
--------------------------------------------------------
ALTER TABLE "EMPLOYEE"."EMPLOYEE" ADD CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "EMPLOYEE"."EMPLOYEE" MODIFY ("SAL_LEVEL" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE"."EMPLOYEE" MODIFY ("JOB_CODE" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE"."EMPLOYEE" MODIFY ("EMP_NO" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE"."EMPLOYEE" MODIFY ("EMP_NAME" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE"."EMPLOYEE" MODIFY ("EMP_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table JOB
--------------------------------------------------------
CREATE TABLE "EMPLOYEE"."JOB"
( "JOB_CODE" CHAR(2 BYTE),
"JOB_NAME" VARCHAR2(35 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "EMPLOYEE"."JOB"."JOB_CODE" IS '직급코드';
COMMENT ON COLUMN "EMPLOYEE"."JOB"."JOB_NAME" IS '직급명';
COMMENT ON TABLE "EMPLOYEE"."JOB" IS '직급';
REM INSERTING into EMPLOYEE.JOB
SET DEFINE OFF;
Insert into EMPLOYEE.JOB (JOB_CODE,JOB_NAME) values ('J1','대표');
Insert into EMPLOYEE.JOB (JOB_CODE,JOB_NAME) values ('J2','부사장');
Insert into EMPLOYEE.JOB (JOB_CODE,JOB_NAME) values ('J3','부장');
Insert into EMPLOYEE.JOB (JOB_CODE,JOB_NAME) values ('J4','차장');
Insert into EMPLOYEE.JOB (JOB_CODE,JOB_NAME) values ('J5','과장');
Insert into EMPLOYEE.JOB (JOB_CODE,JOB_NAME) values ('J6','대리');
Insert into EMPLOYEE.JOB (JOB_CODE,JOB_NAME) values ('J7','사원');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK1
--------------------------------------------------------
CREATE UNIQUE INDEX "EMPLOYEE"."엔터티1_PK1" ON "EMPLOYEE"."JOB" ("JOB_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table JOB
--------------------------------------------------------
ALTER TABLE "EMPLOYEE"."JOB" ADD CONSTRAINT "JOB_PK" PRIMARY KEY ("JOB_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "EMPLOYEE"."JOB" MODIFY ("JOB_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table LOCATION
--------------------------------------------------------
CREATE TABLE "EMPLOYEE"."LOCATION"
( "LOCAL_CODE" CHAR(2 BYTE),
"NATIONAL_CODE" CHAR(2 BYTE),
"LOCAL_NAME" VARCHAR2(40 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "EMPLOYEE"."LOCATION"."LOCAL_CODE" IS '지역코드';
COMMENT ON COLUMN "EMPLOYEE"."LOCATION"."NATIONAL_CODE" IS '국가코드';
COMMENT ON COLUMN "EMPLOYEE"."LOCATION"."LOCAL_NAME" IS '지역명';
COMMENT ON TABLE "EMPLOYEE"."LOCATION" IS '지역';
REM INSERTING into EMPLOYEE.LOCATION
SET DEFINE OFF;
Insert into EMPLOYEE.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L1','KO','ASIA1');
Insert into EMPLOYEE.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L2','JP','ASIA2');
Insert into EMPLOYEE.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L3','CH','ASIA3');
Insert into EMPLOYEE.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L4','US','AMERICA');
Insert into EMPLOYEE.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L5','RU','EU');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK3
--------------------------------------------------------
CREATE UNIQUE INDEX "EMPLOYEE"."엔터티1_PK3" ON "EMPLOYEE"."LOCATION" ("LOCAL_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table LOCATION
--------------------------------------------------------
ALTER TABLE "EMPLOYEE"."LOCATION" ADD CONSTRAINT "LOCATION_PK" PRIMARY KEY ("LOCAL_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "EMPLOYEE"."LOCATION" MODIFY ("NATIONAL_CODE" NOT NULL ENABLE);
ALTER TABLE "EMPLOYEE"."LOCATION" MODIFY ("LOCAL_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table NATIONAL
--------------------------------------------------------
CREATE TABLE "EMPLOYEE"."NATIONAL"
( "NATIONAL_CODE" CHAR(2 BYTE),
"NATIONAL_NAME" VARCHAR2(35 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "EMPLOYEE"."NATIONAL"."NATIONAL_CODE" IS '국가코드';
COMMENT ON COLUMN "EMPLOYEE"."NATIONAL"."NATIONAL_NAME" IS '국가명';
COMMENT ON TABLE "EMPLOYEE"."NATIONAL" IS '국가';
REM INSERTING into EMPLOYEE.NATIONAL
SET DEFINE OFF;
Insert into EMPLOYEE.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('KO','한국');
Insert into EMPLOYEE.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('JP','일본');
Insert into EMPLOYEE.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('CH','중국');
Insert into EMPLOYEE.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('US','미국');
Insert into EMPLOYEE.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('RU','러시아');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK4
--------------------------------------------------------
CREATE UNIQUE INDEX "EMPLOYEE"."엔터티1_PK4" ON "EMPLOYEE"."NATIONAL" ("NATIONAL_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table NATIONAL
--------------------------------------------------------
ALTER TABLE "EMPLOYEE"."NATIONAL" ADD CONSTRAINT "NATIONAL_PK" PRIMARY KEY ("NATIONAL_CODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "EMPLOYEE"."NATIONAL" MODIFY ("NATIONAL_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table SAL_GRADE
--------------------------------------------------------
CREATE TABLE "EMPLOYEE"."SAL_GRADE"
( "SAL_LEVEL" CHAR(2 BYTE),
"MIN_SAL" NUMBER,
"MAX_SAL" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
COMMENT ON COLUMN "EMPLOYEE"."SAL_GRADE"."SAL_LEVEL" IS '급여등급';
COMMENT ON COLUMN "EMPLOYEE"."SAL_GRADE"."MIN_SAL" IS '최소급여';
COMMENT ON COLUMN "EMPLOYEE"."SAL_GRADE"."MAX_SAL" IS '최대급여';
COMMENT ON TABLE "EMPLOYEE"."SAL_GRADE" IS '급여등급';
REM INSERTING into EMPLOYEE.SAL_GRADE
SET DEFINE OFF;
Insert into EMPLOYEE.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S1',6000000,10000000);
Insert into EMPLOYEE.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S2',5000000,5999999);
Insert into EMPLOYEE.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S3',4000000,4999999);
Insert into EMPLOYEE.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S4',3000000,3999999);
Insert into EMPLOYEE.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S5',2000000,2999999);
Insert into EMPLOYEE.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S6',1000000,1999999);
--------------------------------------------------------
-- DDL for Index 엔터티2_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "EMPLOYEE"."엔터티2_PK" ON "EMPLOYEE"."SAL_GRADE" ("SAL_LEVEL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table SAL_GRADE
--------------------------------------------------------
ALTER TABLE "EMPLOYEE"."SAL_GRADE" ADD CONSTRAINT "엔터티2_PK" PRIMARY KEY ("SAL_LEVEL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "EMPLOYEE"."SAL_GRADE" MODIFY ("SAL_LEVEL" NOT NULL ENABLE);
COMMIT;
'더존 노션 필기 옮기기 > SQL' 카테고리의 다른 글
| MySQL 튜토리얼 (1) | 2022.12.07 |
|---|---|
| Oracle EMPLOYEE DDL DML (0) | 2022.11.22 |
| SQL 3일차 오라클 DB3 조인, 서브쿼리 (0) | 2022.08.30 |
| SQL 2일차 오라클 DB 2 단일행함수 , 그룹함수 (0) | 2022.08.30 |