ORACLE SQL 쿼리 복습을 위한 '사내 DB테이블' 세팅

 

안녕들 하시죠!

이번시간에는 뒤돌아서면 까먹는 SQL 쿼리 복습을 위한 테이블 세팅을 진행해보겠습니다.

 

 

CREATE TABLE DEPT( -- 부서테이블
    DEPTNO NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, -- 부서번호
    DNAME VARCHAR2(14), -- 부서이름
    LOC VARCHAR2(13) -- 부서위치
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP( -- 사원테이블
    EMPNO NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, -- 사원번호
    ENAME VARCHAR2(10), -- 사원이름
    JOB VARCHAR2(9), -- 직급
    MGR NUMBER(4), -- 직속상사 사원번호
    HIREDATE DATE, -- 입사날짜
    SAL NUMBER(7,2), -- 사원급여
    COMM NUMBER(7,2), -- 보너스(커미션)
    DEPTNO NUMBER(2) -- 부서번호
);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',null,TO_DATE('1981-11-17','yyyy-mm-dd'),5000,null,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('1981-04-02','yyyy-mm-dd'),2975,null,20);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1981-05-01','yyyy-mm-dd'),2850,null,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('1981-06-09','yyyy-mm-dd'),2450,null,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('1987-04-19','yyyy-mm-dd'),3000,null,20);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('1981-12-03','yyyy-mm-dd'),3000,null,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('1981-02-20','yyyy-mm-dd'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('1981-02-22','yyyy-mm-dd'),1250,500,30);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('1981-09-28','yyyy-mm-dd'),1250,1400,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('1981-09-08','yyyy-mm-dd'),1500,null,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('1981-12-03','yyyy-mm-dd'),950,null,30);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,TO_DATE('1982-01-23','yyyy-mm-dd'),1300,null,10);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,TO_DATE('1980-12-17','yyyy-mm-dd'),800,null,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('1987-05-23','yyyy-mm-dd'),1100,null,20);
COMMIT;
CREATE TABLE salgrade( -- 급여테이블
    grade number(7,2), -- 급여등급
    losal number(7,2), -- 최저급여
    hisal number(7,2) -- 최고급여
);
INSERT INTO salgrade values(1,700,1200);
INSERT INTO salgrade values(2,1201,1400);
INSERT INTO salgrade values(3,1401,2000);
INSERT INTO salgrade values(4,2001,3000);
INSERT INTO salgrade values(5,3001,9999);
 
COMMIT;