DBMS
데이터의 보관, 보안을 담당
쿼리문
SQL 명령을 통해 데이터를 관리하는 솔루션
DDL - create / drop / alter
create -> table -> 테이블 구조 (컬럼명 자료형 제약조건)
primary key, unique, not null, check, Foreign key (참조 무결성
테이블 목록 보기
select table_name from user_tables;
default
create table beverage(
text number default 10 not null
);
insert into beverage values(default);
alter - add 기존에 존재하는 테이블에 칼럼 추가하기
-- alter
create table members(
user_id varchar(20) primary key,
user_pw varchar(20) not null
);
-- 기존에 존재하는 테이블에 컬럼 추가하기
alter table members add (user_name varchar(20) not null);
alter table members add (user_age number default 0 not null);
desc members;
alter - modify 기존에 존재하는 테이블 컬럼의 자료형 또는 제약조건 수정
-- 기존에 존재하는 테이블 컬럼의 자료형 또는 제약조건 수정
alter table members modify (user_name varchar(30) constraint user_name_uq unique );
desc members;
alter - rename 기존에 존재하는 테이블 컬럼의 컬럼명을 수정
컬럼명이 변경되어도 제약조건은 존재
제약조건 이름을 변경하는 방법 1 - drop 후 수정
제먁조건 이름을 변경하는 방법 2 - rename constraint
기존 테이블의 컬럼 삭제
테이블 이름 자체 수정 rename
예제
create table Product(
pid varchar(20) primary key,
pname varchar(30) constraint pname_uq unique,
pdate timestamp default sysdate
);
create table trade_history(
tid number primary key,
tdate timestamp default sysdate constraint trade_history_tdate_nn not null,
pid varchar(20) references Product(pid)
);
alter table Product add (price number default 0);
alter table Product modify (pdate timestamp default sysdate constraint Product_pdate_nn not null );
alter table Product modify (pname varchar(50));
alter table Product rename column pname to psign;
alter table Product drop constraint pname_uq;
select * from user_constraints;
desc Product;
desc trade_history;
drop table trade_history;
drop table Product;SQL
- DDL : Create / Drop / Alter (다음주 화요일 평가에서는 DDL 내용만 나온다.)
- DML : insert / delete / update / select (select는 DQL로 따로 분류하기도 한다.)
- TCL
Select
새 연습용 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 "KH"."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 "KH"."DEPARTMENT"."DEPT_ID" IS '부서코드';
COMMENT ON COLUMN "KH"."DEPARTMENT"."DEPT_TITLE" IS '부서명';
COMMENT ON COLUMN "KH"."DEPARTMENT"."LOCATION_ID" IS '지역코드';
COMMENT ON TABLE "KH"."DEPARTMENT" IS '부서';
REM INSERTING into KH.DEPARTMENT
SET DEFINE OFF;
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D1','인사관리부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D2','회계관리부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D3','마케팅부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D4','국내영업부','L1');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D5','해외영업1부','L2');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D6','해외영업2부','L3');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D7','해외영업3부','L4');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D8','기술지원부','L5');
Insert into KH.DEPARTMENT (DEPT_ID,DEPT_TITLE,LOCATION_ID) values ('D9','총무부','L1');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK2
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK2" ON "KH"."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 "KH"."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 "KH"."DEPARTMENT" MODIFY ("LOCATION_ID" NOT NULL ENABLE);
ALTER TABLE "KH"."DEPARTMENT" MODIFY ("DEPT_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table EMPLOYEE
--------------------------------------------------------
CREATE TABLE "KH"."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 "KH"."EMPLOYEE"."EMP_ID" IS '사원번호';
COMMENT ON COLUMN "KH"."EMPLOYEE"."EMP_NAME" IS '직원명';
COMMENT ON COLUMN "KH"."EMPLOYEE"."EMP_NO" IS '주민등록번호';
COMMENT ON COLUMN "KH"."EMPLOYEE"."EMAIL" IS '이메일';
COMMENT ON COLUMN "KH"."EMPLOYEE"."PHONE" IS '전화번호';
COMMENT ON COLUMN "KH"."EMPLOYEE"."DEPT_CODE" IS '부서코드';
COMMENT ON COLUMN "KH"."EMPLOYEE"."JOB_CODE" IS '직급코드';
COMMENT ON COLUMN "KH"."EMPLOYEE"."SAL_LEVEL" IS '급여등급';
COMMENT ON COLUMN "KH"."EMPLOYEE"."SALARY" IS '급여';
COMMENT ON COLUMN "KH"."EMPLOYEE"."BONUS" IS '보너스율';
COMMENT ON COLUMN "KH"."EMPLOYEE"."MANAGER_ID" IS '관리자사번';
COMMENT ON COLUMN "KH"."EMPLOYEE"."HIRE_DATE" IS '입사일';
COMMENT ON COLUMN "KH"."EMPLOYEE"."ENT_DATE" IS '퇴사일';
COMMENT ON COLUMN "KH"."EMPLOYEE"."ENT_YN" IS '재직여부';
COMMENT ON TABLE "KH"."EMPLOYEE" IS '사원';
REM INSERTING into KH.EMPLOYEE
SET DEFINE OFF;
Insert into KH.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@kh.or.kr','01099546325','D9','J1','S1',8000000,0.3,null,to_date('90/02/06','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01045686656','D9','J2','S1',6000000,null,'200',to_date('01/09/01','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01066656263','D9','J2','S4',3700000,null,'201',to_date('01/01/01','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01077607879','D6','J4','S5',2800000,null,'204',to_date('96/05/03','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01099999129','D6','J3','S4',3400000,0.2,'200',to_date('00/12/29','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01036654875','D6','J3','S4',3900000,null,'204',to_date('99/09/09','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01096935222','D5','J7','S6',1800000,null,'207',to_date('08/04/02','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01036654488','D5','J5','S5',2200000,0.1,'200',to_date('94/07/07','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01078634444','D5','J5','S5',2500000,null,'207',to_date('04/04/30','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','0113654485','D5','J3','S4',3500000,0.15,'207',to_date('11/11/11','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','0179964233','D5','J7','S5',2000000,null,'207',to_date('01/02/03','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01044432222','D8','J6','S5',2000000,null,'200',to_date('12/12/12','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01066682224','D8','J6','S5',2550000,0.25,'211',to_date('15/06/17','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01158456632',null,'J6','S5',2320000,0.1,null,to_date('99/12/31','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01074127545','D1','J7','S6',1380000,null,'200',to_date('10/04/04','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01088808584','D5','J5','S4',3760000,null,null,to_date('17/06/19','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01064643212','D1','J6','S5',2780000,0.2,'214',to_date('13/03/01','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01033624442','D1','J6','S4',3660000,0.3,'214',to_date('07/03/20','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr','01022306545',null,'J7','S5',2890000,null,null,to_date('16/11/28','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr',null,'D2','J4','S6',1550000,null,null,to_date('99/09/09','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr',null,'D2','J4','S5',2490000,null,null,to_date('14/09/18','RR/MM/DD'),null,'N');
Insert into KH.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@kh.or.kr',null,'D2','J4','S5',2480000,null,null,to_date('94/01/20','RR/MM/DD'),null,'N');
Insert into KH.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@kh.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 "KH"."엔터티1_PK" ON "KH"."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 "KH"."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 "KH"."EMPLOYEE" MODIFY ("SAL_LEVEL" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("JOB_CODE" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("EMP_NO" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("EMP_NAME" NOT NULL ENABLE);
ALTER TABLE "KH"."EMPLOYEE" MODIFY ("EMP_ID" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table JOB
--------------------------------------------------------
CREATE TABLE "KH"."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 "KH"."JOB"."JOB_CODE" IS '직급코드';
COMMENT ON COLUMN "KH"."JOB"."JOB_NAME" IS '직급명';
COMMENT ON TABLE "KH"."JOB" IS '직급';
REM INSERTING into KH.JOB
SET DEFINE OFF;
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J1','대표');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J2','부사장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J3','부장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J4','차장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J5','과장');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J6','대리');
Insert into KH.JOB (JOB_CODE,JOB_NAME) values ('J7','사원');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK1
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK1" ON "KH"."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 "KH"."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 "KH"."JOB" MODIFY ("JOB_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table LOCATION
--------------------------------------------------------
CREATE TABLE "KH"."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 "KH"."LOCATION"."LOCAL_CODE" IS '지역코드';
COMMENT ON COLUMN "KH"."LOCATION"."NATIONAL_CODE" IS '국가코드';
COMMENT ON COLUMN "KH"."LOCATION"."LOCAL_NAME" IS '지역명';
COMMENT ON TABLE "KH"."LOCATION" IS '지역';
REM INSERTING into KH.LOCATION
SET DEFINE OFF;
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L1','KO','ASIA1');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L2','JP','ASIA2');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L3','CH','ASIA3');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L4','US','AMERICA');
Insert into KH.LOCATION (LOCAL_CODE,NATIONAL_CODE,LOCAL_NAME) values ('L5','RU','EU');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK3
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK3" ON "KH"."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 "KH"."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 "KH"."LOCATION" MODIFY ("NATIONAL_CODE" NOT NULL ENABLE);
ALTER TABLE "KH"."LOCATION" MODIFY ("LOCAL_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table NATIONAL
--------------------------------------------------------
CREATE TABLE "KH"."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 "KH"."NATIONAL"."NATIONAL_CODE" IS '국가코드';
COMMENT ON COLUMN "KH"."NATIONAL"."NATIONAL_NAME" IS '국가명';
COMMENT ON TABLE "KH"."NATIONAL" IS '국가';
REM INSERTING into KH.NATIONAL
SET DEFINE OFF;
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('KO','한국');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('JP','일본');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('CH','중국');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('US','미국');
Insert into KH.NATIONAL (NATIONAL_CODE,NATIONAL_NAME) values ('RU','러시아');
--------------------------------------------------------
-- DDL for Index 엔터티1_PK4
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티1_PK4" ON "KH"."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 "KH"."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 "KH"."NATIONAL" MODIFY ("NATIONAL_CODE" NOT NULL ENABLE);
--------------------------------------------------------
-- DDL for Table SAL_GRADE
--------------------------------------------------------
CREATE TABLE "KH"."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 "KH"."SAL_GRADE"."SAL_LEVEL" IS '급여등급';
COMMENT ON COLUMN "KH"."SAL_GRADE"."MIN_SAL" IS '최소급여';
COMMENT ON COLUMN "KH"."SAL_GRADE"."MAX_SAL" IS '최대급여';
COMMENT ON TABLE "KH"."SAL_GRADE" IS '급여등급';
REM INSERTING into KH.SAL_GRADE
SET DEFINE OFF;
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S1',6000000,10000000);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S2',5000000,5999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S3',4000000,4999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S4',3000000,3999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S5',2000000,2999999);
Insert into KH.SAL_GRADE (SAL_LEVEL,MIN_SAL,MAX_SAL) values ('S6',1000000,1999999);
--------------------------------------------------------
-- DDL for Index 엔터티2_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "KH"."엔터티2_PK" ON "KH"."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 "KH"."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 "KH"."SAL_GRADE" MODIFY ("SAL_LEVEL" NOT NULL ENABLE);
COMMIT;
result set 보기
컬럼명 선택해서 result set 보기
column alias
컬럼 헤더에 보일 별명 붙이기 (메타데이터 이므로 쌍따옴표) (띄어쓰기가 없다면 쌍따옴표 없이 써도 된다)
where절
사용할수도 안할수도 있음
조건에 따라 테이블 한줄씩 비교 검사
(=) 같다는 의미 / 자바의 대입역할을 하는 문법은 따로 있음
문자는 따옴표 안에
select
emp_id , emp_name , salary
from
employee
where emp_id=204;select
emp_id , emp_name , salary
from
employee
where emp_name='송은희';예제
-- 1. JOB 테이블 내에서 JOB_NAME 정보만 출력해보세요.
select JOB_NAME from JOB;
-- 2. DEPARTMENT 테이블의 모든 내용을 출력해보세요.
select * from DEPARTMENT;
-- 3. EMPLOYEE 테이블에서 사원명, 이메일, 전화번호, 고용일만 전원 출력해보세요.
select emp_name, email, phone, hire_date from employee;
where절에 조건에 여러개일 경우
and , or , 부등식 사용가능
select
emp_id , emp_name , salary
from
employee
where emp_name='송은희' and emp_id=204;
select
emp_id , emp_name , salary
from
employee
where emp_name='송은희' or emp_id=204;
select
emp_id , emp_name , salary
from
employee
where emp_name='송은희' or emp_id=204 or salary<2000000;
-- 4. employee 테이블에서 급여가 250만원 이상인 사람의 사원명과 sal_level을 출력해 보세요
select emp_name, sal_level from employee where salary>2500000;
-- 5. employee 테이블에서 급여가 200만원 이상 350만원 이하이고,
-- 직급코드가 J3인 직원들의 사원명과 직급코드, 전화번호를 출력해보세요
select
emp_name, JOB_CODE, phone
from
employee
where
3500000>salary and salary>2000000 and JOB_CODE = 'J3';연산 가능
select
emp_name 사원명, salary * 12 연봉
from
employee;
인위적으로 컬럼을 만들어서 문자값 반복
result set에서 보여지는 출력부를 변경한다고 실제 데이터에 저장되는 것은 아님
select
emp_name 사원명, salary * 12 연봉 , '원' 단위
from
employee;
컬럼을 새로 만드는게 아니라 뒤에 연결하기 ||
select
emp_name 사원명, salary * 12||'원' 연봉
from
employee;
예제 - 이번 달 보너스
select
emp_id 사번, emp_name 사원명, salary * bonus ||'원' "이번 달 보너스"
from
employee;
예제 - 1년 보너스
select
emp_id 사번, emp_name 사원명, salary * bonus*12 ||'원' "올해 보너스"
from
employee;
예제 - 총급여
select
emp_id 사번, emp_name 사원명,
salary * bonus ||'원' "이번 달 보너스",
salary * bonus*12 ||'원' "1년간 보너스",
salary * 12 + salary*12||'원' "1년 간 급여총액"
from
employee;
날짜에 정수 연산 가능
덧셈과 뺄셈 가능 (곱셈, 나눗셈 불가)
정수값을 더하거나 빼면 일수로 계산
select emp_name, sysdate - 3 from employee;
날짜 간의 연산은 빼기만 가능
더 큰값(현재) - 더 작은 값(과거) : 날짜 사이의 일수
select emp_name, sysdate-hire_date from employee;
소수점 버리기 floor method
select emp_name, floor(sysdate - hire_date) from employee;
날짜 더하기는 안된다.
select emp_name 이름, floor(sysdate + hire_date)||'일' 근무일수 from employee;ORA-00975: date + date not allowed
00975. 00000 - "date + date not allowed"
*Cause:
*Action:
55행, 39열에서 오류 발생예제 - employee 테이블 내에서 이름, 근무일수를 ??일 형태로 출력
-- employee 테이블 내에서 이름, 근무일수를 ??일 형태로 출력
select emp_name 이름, floor(sysdate - hire_date)||'일' 근무일수 from employee;
예제
-- 2. employee 테이블 내에서 20년이상 근무한 직원의 이름, 급여, 보너스율을 출력
select
emp_name 이름, salary 급여, bonus 보너스율, floor((sysdate - hire_date)/365 )||'년' 근속연수
from
employee
where
((sysdate - hire_date)/365 )>= 20 ;
날짜 표기
select emp_name, hire_date from employee
where '05/01/01' <= hire_date and hire_date <= '10/12/31';
--between 을 사용한 문법 : 결과는 같다. inclusive한 표현(<=, >=)
select emp_name, hire_date from employee
where hire_date between '05/01/01' and '10/12/31';null 값을 찾아라 is null , is not null
select emp_name, bonus from employee
where bonus is null;
select emp_name, bonus from employee
where bonus is not null;
or 연산자가 여러개 쓰이는 경우 표현
-- 부서코드가 D2 이거나 D5인 사원들의 이름만 출력하세요.
select emp_name, dept_code from employee
where dept_code = 'D2' or dept_code = 'D5';
-- 부서코드가 D7 이거나 D9인 사원들의 이름을 추가로 출력하세요.
select emp_name, dept_code from employee
where dept_code = 'D2' or dept_code = 'D5' or dept_code = 'D7' or dept_code = 'D9';in 같은 기능이지만 더 깔끔하게 표현
select emp_name, dept_code from employee
where dept_code in ( 'D2' , 'D5' );
-- 부서코드가 D7 이거나 D9인 사원들의 이름을 추가로 출력하세요.
select emp_name, dept_code from employee
where dept_code in ( 'D2' , 'D5' , 'D7' , 'D9' );not in 부정
-- 부서코드가 D2 이거나 D5인 사원들의 이름만 빼고 출력하세요.
select emp_name, dept_code from employee
where dept_code not in ( 'D2' , 'D5' );중복 제거
-- 중복제거
select distinct dept_code from employee;
-- distinct 에러
select emp_name, distinct dept_code from employee;
--ORA-00936: missing expression
--00936. 00000 - "missing expression"
--*Cause:
--*Action:
--80행, 18열에서 오류 발생-- distinct는 맨 앞으로 와야 함
select distinct dept_code, emp_name from employee;
-- 이름이 중복이 아니면 지역코드 중복제거 포기
검색어를 특정 할 수 없을 때
-- 검색어를 특정 할 수 없을 때
-- % 어떤글자가 있을 수도 있고, 없을 수도 있고, 여러개일 수도 있는 모든 경우
select emp_id, emp_name, dept_code from employee
where emp_name like '하%유';
-- %와 같이 불특정한 기호를 쓰고 싶다면 (=)이 아니라 like로
-- (_) 한 글자가 있다.
select emp_id, emp_name, dept_code from employee
where emp_name like '하___유';
-- 이름에 '전'자가 들어가는 경우
select emp_id, emp_name, dept_code from employee
where emp_name like '%전%';-- 이름이 '연'으로 끝나는 경우
select emp_id, emp_name, dept_code from employee
where emp_name like '%연';(%) (_)를 쓰기 곤란한 경우
ESCAPE
-- employee 이메일 값의 _ 앞자리가 3자리인 직원만 검색
select email from employee
where email like '___#_%' ESCAPE '#';
-- #을 이스케이프 문자로 변경
-- # 뒤의 _는 '어떤 한 문자'라는 기능에서 그냥 (_)문자로 변환
ESCAPE '문자' : 특정 문자를 이스케이프 문자로 만듦
이스케이프 문자 뒤의 문자는 본래의 기능을 잃어버임
자바의 이스케이프 문자는 역슬래쉬\
System.out.print("\"Apple\"")
// "Apple"
System.out.print("\\")
// \-- employee 이메일 값의 _ 앞자리가 3자리인 직원만 빼고 검색
select email from employee
where email not like '___#_%' ESCAPE '#';
퀴즈 성씨가 '이'씨가 아닌 모든 직원 출력
-- Quiz 성씨가 '이'씨가 아닌 모든 직원 출력
select emp_name from employee
where emp_name not like '이%';예제
-- 1. employee 테이블에서 이름 끝이 연으로 끝나는 사원의 이름을 출력하세요.
select emp_name from employee
where emp_name like '%연';
-- 2. employee 테이블에서 전화번호 처음 3자리가 010이 아닌
-- 사원의 이름, 전화번호를 출력하세요.
select emp_name, phone from employee
where phone not like '010%';
-- 3. employee 테이블에서 메일주소의 's'가 들어가면서,
-- DEPT_CODE가 D9또는 D6이고,
-- 고용일이 90/01/01 ~ 00/ 12/01 이면서,
-- 월급이 270만원 이상인 사원의 전체 정보를 출력하시오
select * from employee
where email like '%s%' and
DEPT_CODE in ('D9' , 'D6') and
hire_date between '90/01/01' and '00/12/01' and
salary > 2700000;
-- 4. 직속 상사도 없고 부서코드도 없는 직원의 사번, 이름, 부서코드, 상사ID를 출력하세요.
select emp_id 사번, emp_name 이름 , dept_code 부서코드 , manager_id 상사ID from employee
where manager_id is null and dept_code is null;
-- 5. 부서가 없음에도 보너스가 지급되는 직원의 사번 및 이름과 보너스를 출력하세요.
select emp_id 사번, emp_name 이름 , bonus 보너스 from employee
where dept_code is null and bonus is not null;
-- 6. 부서원 중 직급코드가 J7 또는 J2이고,
-- 급여가 2,000,000원 초과인 사람의 이름 급여,직급코드를 출력
select emp_name, salary, job_code from employee
where job_code in ('J7','J2') and salary>2000000;연산 우선순위
and연산이 or연산보다 빠르다
-- 연산 우선순위
select emp_name, salary, job_code from employee
where job_code='J7' or job_code='J2' and salary>2000000;
-- and가 우선이다.
select emp_name, salary, job_code from employee
where (job_code='J7' or job_code='J2') and salary>2000000;
-- 연산우선순위를 외우기 보다는 필요하다면 괄호를 쓰자정렬
-- 정렬, 뒤에 아무것도 안쓰면 asc로 동작 (오름차순)
select emp_id, emp_name, salary from employee
order by salary asc;
-- 내림차순
select emp_id, emp_name, salary from employee
order by salary desc;-- 컬럼 숫자로 정렬 가능
select emp_id, emp_name, salary from employee
order by 1 desc;
-- 1번컬럼 emp_id 기준
select emp_id, emp_name, salary from employee
order by 2 desc;
-- 2번컬럼 emp_name 기준-- 정렬 기준 2개
select dept_code, salary
from
employee
order by 1 , 2;
-- dept_code로 정렬한 후 salary로 정렬-- null을 위로
select dept_code, salary
from
employee
order by 1 nulls first, 2;
-- 문제1.
-- 입사일이 5년이상, 10년 이하인 직원의
-- 이름, 주민번호, 급여, 입사일을 검색하세요.
select emp_name 이름, emp_no 주민번호, salary 급여, hire_date 입사일
from employee
where (sysdate-hire_date)/365 between 5 and 10 ;
select emp_name 이름, emp_no 주민번호, salary 급여, hire_date 입사일
from employee
where (sysdate-hire_date)/365 >= 5 and (sysdate-hire_date)/365 <=10 ;
-- 문제2.
-- 퇴사한 직원의 이름, 부서코드, 고용일, 근무기간, 퇴사일 검색하세요. (퇴사 여부 : ENT_YN)
select emp_name 이름, dept_code 부서코드, hire_date 고용일, ent_date-hire_date 근무기간, ent_date 퇴사일
from employee
where ent_yn = 'Y' ;