[SQL 기초 및 PL-SQL 강의자료] 실습 테이블 데이터 생성

개발자에게 배우는 개발자교육! 구로 오라클자바교육학원 www.oraclejava.co.kr

 

 

 

개발자에게 배우는 개발자교육! 구로 오라클자바교육학원 www.oraclejava.co.kr

 

 

 

 

※ 실습 테이블 데이터 생성 ※

 

CREATE TABLE DEPT
        (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
         DNAME VARCHAR2(14),
  LOC   VARCHAR2(13) ) ;
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) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
CREATE TABLE BONUS
 (ENAME VARCHAR2(10),
  JOB   VARCHAR2(9),
  SAL   NUMBER,
  COMM  NUMBER) ;
CREATE TABLE SALGRADE
        (GRADE NUMBER,
  LOSAL NUMBER,
  HISAL NUMBER );

 

 

 

 

 

CREATE TABLE EMPLOYEE(
    EMP_ID            NUMBER(5)    NOT NULL,
    FNAME             VARCHAR2(20),
    LNAME             VARCHAR2(20),
    DEPT_ID           NUMBER(5)    NOT NULL,
    MANAGER_EMP_ID    NUMBER(5),
    SALARY            NUMBER(5),
    HIRE_DATE         DATE,
    JOB_ID            NUMBER(3),
    CONSTRAINT employee_pk PRIMARY KEY (EMP_ID)
);

CREATE TABLE EMPLOYEE_EXPENSE(
    EMP_ID            NUMBER(5),
    YEAR              NUMBER(4),
    MONTH             NUMBER(2),
    EXPENSE_CLAIM     NUMBER(7,2),
    APPROVED_AMT      NUMBER(7,2),
    PAID_DATE         DATE
);

CREATE TABLE DEPARTMENT(
    DEPT_ID    NUMBER(5)       NOT NULL,
    NAME       VARCHAR2(20),
    LOCATION_ID NUMBER(3),
    CONSTRAINT department_pk PRIMARY KEY (DEPT_ID)
);