[Oracle] Create Table (테이블 생성 예제) EMP, DEPT 테이블 스크립트

Create Table

테이블을 생성하는 예제는 다음과 같습니다.

CREATE TABLE user_name.table_name
(
   V   VARCHAR2 (1000 BYTE),
   N   NUMBER (10),
   D   DATE
)
TABLESPACE tablespace_name
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 64 M
         NEXT 1 M
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

Table created.

테이블에 코멘트를 추가합니다. 스키마.테이블로 설정할 수 있습니다.

COMMENT ON TABLE user_name.table_name IS 'This is Table Test';

Comment created.

컬럼에 코멘트를 추가합니다. 스키마.테이블.컬럼으로 설정할 수 있습니다.

COMMENT ON COLUMN user_name.table_name.V IS 'This is Column V';

Comment created.

테이블에 동의어를 추가하는 예시입니다.

CREATE OR REPLACE PUBLIC SYNONYM table_name FOR user_name.table_name;

Synonym created.

인덱스를 추가해보겠습니다. 인덱스 이름은 임의로 'inx_table_name_01'이라고 지었습니다. 인덱스는 이름만으로도 어떠한 테이블에서 무슨 역할을 하는지 알 수 있도록 하는 것이, 활용도 측면에서 매우 유용하니 네이밍 시 고려해 주세요.

CREATE INDEX user_name.inx_table_name_01
   ON user_name.table_name (V)
   LOGGING
   TABLESPACE tablespace_name
   PCTFREE 10
   INITRANS 2
   MAXTRANS 255
   STORAGE (INITIAL 64 M
            NEXT 1 M
            MINEXTENTS 1
            MAXEXTENTS UNLIMITED
            PCTINCREASE 0
            BUFFER_POOL DEFAULT)
   NOPARALLEL;

Index created.

오라클 데이터베이스에서 테스트를 하기 위한 테스트 테이블 생성 예제 자료입니다.

  • DEPT 테이블 생성 스크립트
  • EMP 테이블 생성 스크립트
  • EMP, DEPT 테이블 데이터

DEPT 테이블 생성 스크립트

부서, 사원 테이블 생성 스크립트 및 각 테이블의 데이터 예제 자료입니다. 계정명, 테이블스페이스 이름, 인덱스 이름에서 차이가 있을 수 있으니 확인 이후 본인에게 필요한 대로 수정해 주세요.

CREATE TABLE USER_NAME.DEPT
(
  DEPTNO  NUMBER(2),
  DNAME   VARCHAR2(14 BYTE),
  LOC     VARCHAR2(13 BYTE)
)
TABLESPACE TABLESPACE_NAME
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX USER_NAME.PK_DEPT ON USER_NAME.DEPT
(DEPTNO)
LOGGING
TABLESPACE TABLESPACE_NAME
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE USER_NAME.DEPT ADD (
  CONSTRAINT PK_DEPT
  PRIMARY KEY
  (DEPTNO)
  USING INDEX USER_NAME.PK_DEPT);

EMP 테이블 생성 스크립트

CREATE TABLE USER_NAME.EMP
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
)
TABLESPACE TABLESPACE_NAME
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX USER_NAME.PK_EMP ON USER_NAME.EMP
(EMPNO)
LOGGING
TABLESPACE TABLESPACE_NAME
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE USER_NAME.EMP ADD (
  CONSTRAINT PK_EMP
  PRIMARY KEY
  (EMPNO)
  USING INDEX USER_NAME.PK_EMP);

ALTER TABLE USER_NAME.EMP ADD (
  CONSTRAINT FK_DEPTNO 
  FOREIGN KEY (DEPTNO) 
  REFERENCES USER_NAME.DEPT (DEPTNO));

EMP, DEPT 테이블 데이터

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

commit;

기타 스크립트

--오라클 테스트 테이블 EMP 생성입니다.
CREATE TABLE EMP
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
)
TABLESPACE tablespaceName
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX PK_EMP ON EMP
(EMPNO)
LOGGING
TABLESPACE tablespaceName
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE EMP ADD (
  CONSTRAINT PK_EMP
  PRIMARY KEY
  (EMPNO)
  USING INDEX PK_EMP);

ALTER TABLE EMP ADD (
  CONSTRAINT FK_DEPTNO 
  FOREIGN KEY (DEPTNO) 
  REFERENCES DEPT (DEPTNO));

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    800, NULL, 20);
    
Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    1600, 300, 30);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    1250, 500, 30);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2975, NULL, 20);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    1250, 1400, 30);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2850, NULL, 30);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2450, NULL, 10);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('04/19/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    3000, NULL, 20);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    5000, NULL, 10);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    1500, 0, 30);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('05/23/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    1100, NULL, 20);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7900, 'JAMES', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    950, NULL, 30);

Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    3000, NULL, 20);
    
Insert into EMP
   (EMPNO, 
    ENAME, JOB, MGR, HIREDATE, SAL, 
    COMM, DEPTNO)
 Values
   (7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    1300, NULL, 10);

COMMIT;

Create Table DEPT (Scott User)

Create Table DEPT
Scott User
--오라클 테스트 테이블 DEPT 생성입니다.

CREATE TABLE DEPT
(
   DEPTNO   NUMBER (2),
   DNAME    VARCHAR2 (14 BYTE),
   LOC      VARCHAR2 (13 BYTE)
)
TABLESPACE tablespaceName
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 64 K
         NEXT 1 M
         MINEXTENTS 1
         MAXEXTENTS UNLIMITED
         PCTINCREASE 0
         BUFFER_POOL DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX PK_DEPT
   ON DEPT (DEPTNO)
   LOGGING
   TABLESPACE EON_HIS_DAT_A00
   PCTFREE 10
   INITRANS 2
   MAXTRANS 255
   STORAGE (INITIAL 64 K
            NEXT 1 M
            MINEXTENTS 1
            MAXEXTENTS UNLIMITED
            PCTINCREASE 0
            BUFFER_POOL DEFAULT)
   NOPARALLEL;

ALTER TABLE DEPT ADD (
  CONSTRAINT PK_DEPT
  PRIMARY KEY
  (DEPTNO)
  USING INDEX PK_DEPT);

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
     VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
     VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
     VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT (DEPTNO, DNAME, LOC)
     VALUES (40, 'OPERATIONS', 'BOSTON');

COMMIT;

댓글

댓글 쓰기