[SQL] DEPT & EMP 샘플 스키마

SQL을 공부할 때 흔히 접하는 오라클의 대표적인 샘플 스키마 DEPT와 EMP를 포스팅한다.

CREATE TABLE DEPT(

DEPTNO NUMBER,

DNAME VARCHAR2(14),

LOC VARCHAR2(13),

CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)

);

 

CREATE TABLE EMP(

EMPNO NUMBER,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4,0),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER,

CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),

CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)

);

 

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');

 

INSERT INTO EMP VALUES( 7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1996-11-17','YYYY-MM-DD'), 5000, NULL, 10);

INSERT INTO EMP VALUES( 7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1991-1-05','YYYY-MM-DD'), 2850, NULL, 30);

INSERT INTO EMP VALUES( 7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1999-9-06','YYYY-MM-DD'), 2450, NULL, 10);

INSERT INTO EMP VALUES( 7566, 'JONES', 'MANAGER', 7839, TO_DATE('2001-02-04','YYYY-MM-DD'), 2975, NULL, 20);

INSERT INTO EMP VALUES( 7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('2003-06-17','YYYY-MM-DD'), 3000, NULL, 20);

INSERT INTO EMP VALUES( 7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-03-12','YYYY-MM-DD'), 3000, NULL, 20);

INSERT INTO EMP VALUES( 7369, 'SMITH', 'CLERK', 7902, TO_DATE('2007-12-1','YYYY-MM-DD'), 800, NULL, 20);

INSERT INTO EMP VALUES( 7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-2-1981','DD-MM-YYYY'), 1600, 300, 30);

INSERT INTO EMP VALUES( 7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-2-1981','DD-MM-YYYY'), 1250, 500, 30);

INSERT INTO EMP VALUES( 7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-9-1981','DD-MM-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES( 7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('8-9-1981','MM-DD-YYYY'), 1500, 0, 30);

INSERT INTO EMP VALUES( 7876, 'ADAMS', 'CLERK', 7788, TO_DATE('13-7-87', 'DD-MM-YY') - 51, 1100, NULL, 20);

INSERT INTO EMP VALUES( 7900, 'JAMES', 'CLERK', 7698, TO_DATE('3-12-1981','MM-DD-YYYY'), 950, NULL, 30);

INSERT INTO EMP VALUES( 7934, 'MILLER', 'CLERK', 7782, TO_DATE('2003-1-23','YYYY-MM-DD'), 1300, NULL, 10);

---

읽어주셔서 감사합니다. 😊