[Oracle] Oracle 기본 테이블 정보

The EMP and DEPT tables in Oracle

I often use the EMP and DEPT tables for test and demonstration purposes. Both these tables are owned by the SCOTT user, together with two less frequently used tables: BONUS and SALGRADE. Execute the below code snippets to create and seed the EMP and DEPT tables in your own schema. The BONUS and SALGRADE tables are included as well, but are commented out. The DDL (data definition language) part creates the tables, the DML (data manipulation language) part inserts the data.

DDL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

create table dept(   deptno number(2,0),   dname  varchar2(14),   loc    varchar2(13),   constraint pk_dept primary key (deptno) );   create table emp(   empno    number(4,0),   ename    varchar2(10),   job      varchar2(9),   mgr      number(4,0),   hiredate date,   sal      number(7,2),   comm     number(7,2),   deptno   number(2,0),   constraint pk_emp primary key (empno),   constraint fk_deptno foreign key (deptno) references dept (deptno) );   /* create table bonus(   ename varchar2(10),   job   varchar2(9),   sal   number,   comm  number );   create table salgrade(   grade number,   losal number,   hisal number ); */

DML

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108

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('17-11-1981','dd-mm-yyyy'),  5000, null, 10 ); insert into emp values(  7698, 'BLAKE', 'MANAGER', 7839,  to_date('1-5-1981','dd-mm-yyyy'),  2850, null, 30 ); insert into emp values(  7782, 'CLARK', 'MANAGER', 7839,  to_date('9-6-1981','dd-mm-yyyy'),  2450, null, 10 ); insert into emp values(  7566, 'JONES', 'MANAGER', 7839,  to_date('2-4-1981','dd-mm-yyyy'),  2975, null, 20 ); insert into emp values(  7788, 'SCOTT', 'ANALYST', 7566,  to_date('13-JUL-87','dd-mm-rr') - 85,  3000, null, 20 ); insert into emp values(  7902, 'FORD', 'ANALYST', 7566,  to_date('3-12-1981','dd-mm-yyyy'),  3000, null, 20 ); insert into emp values(  7369, 'SMITH', 'CLERK', 7902,  to_date('17-12-1980','dd-mm-yyyy'),  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','dd-mm-yyyy'),  1500, 0, 30 ); insert into emp values(  7876, 'ADAMS', 'CLERK', 7788,  to_date('13-JUL-87', 'dd-mm-rr') - 51,  1100, null, 20 ); insert into emp values(  7900, 'JAMES', 'CLERK', 7698,  to_date('3-12-1981','dd-mm-yyyy'),  950, null, 30 ); insert into emp values(  7934, 'MILLER', 'CLERK', 7782,  to_date('23-1-1982','dd-mm-yyyy'),  1300, null, 10 );   /* 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;