본문 바로가기
Oracle ERP/DB SCRIPTS

오라클 교육용 SQL Script

by 솝 2012. 5. 31.

DROP SEQUENCE departments_seq;
DROP SEQUENCE employees_seq;
DROP SEQUENCE locations_seq;

DROP TABLE regions     CASCADE CONSTRAINTS;
DROP TABLE departments CASCADE CONSTRAINTS;
DROP TABLE locations   CASCADE CONSTRAINTS;
DROP TABLE jobs        CASCADE CONSTRAINTS;
DROP TABLE job_history CASCADE CONSTRAINTS;
DROP TABLE employees   CASCADE CONSTRAINTS;
DROP TABLE countries   CASCADE CONSTRAINTS; 

CREATE TABLE countries
    ( country_id      CHAR(2)
       CONSTRAINT  country_id_nn NOT NULL
    , country_name    VARCHAR2(40)
    , region_id       NUMBER
    , CONSTRAINT     country_c_id_pk
              PRIMARY KEY (country_id)
    )
    ORGANIZATION INDEX;

INSERT INTO countries VALUES('CA', 'Canada', 2);
INSERT INTO countries VALUES('DE', 'Germany', 1);
INSERT INTO countries VALUES('UK', 'United Kingdom', 1);
INSERT INTO countries VALUES('US', 'United States of America', 2);

CREATE TABLE departments
    ( department_id    NUMBER(4)
       CONSTRAINT  department_id_nn NOT NULL
    , department_name  VARCHAR2(30)
      CONSTRAINT  dept_name_nn  NOT NULL
    , manager_id       NUMBER(6)
    , location_id      NUMBER(4)
    , CONSTRAINT dept_id_pk
          PRIMARY KEY (department_id)
    ) ;

INSERT INTO departments VALUES(10, 'Administration', 200, 1700);
INSERT INTO departments VALUES(20, 'Marketing', 201, 1800);
INSERT INTO departments VALUES(50, 'Shipping', 124, 1500);
INSERT INTO departments VALUES(60, 'IT', 103, 1400);
INSERT INTO departments VALUES(80, 'Sales', 149, 2500);
INSERT INTO departments VALUES(90, 'Executive', 100, 1700);
INSERT INTO departments VALUES(110, 'Accounting', 205, 1700);
INSERT INTO departments VALUES( 190, 'Contracting', NULL, 1700);

CREATE TABLE employees
    ( employee_id    NUMBER(6)
  CONSTRAINT     emp_employee_id_nn  NOT NULL
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
  CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
 CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
 CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
 CONSTRAINT     emp_job_nn  NOT NULL
    , salary         NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;

INSERT INTO employees VALUES(100, 'Steven', 'King', 'SKING', '515.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'AD_PRES', 24000, NULL, NULL, 90);
INSERT INTO employees VALUES(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', TO_DATE('21-SEP-1989', 'dd-MON-yyyy'), 'AD_VP', 17000, NULL, 100, 90);
INSERT INTO employees VALUES(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', TO_DATE('13-JAN-1993', 'dd-MON-yyyy'), 'AD_VP', 17000, NULL, 100, 90);
INSERT INTO employees VALUES(103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', TO_DATE('03-JAN-1990', 'dd-MON-yyyy'), 'IT_PROG', 9000, NULL, 102, 60);
INSERT INTO employees VALUES(104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', TO_DATE('21-MAY-1991', 'dd-MON-yyyy'), 'IT_PROG', 6000, NULL, 103, 60);
INSERT INTO employees VALUES(107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', TO_DATE('07-FEB-1999', 'dd-MON-yyyy'), 'IT_PROG', 4200, NULL, 103, 60);
INSERT INTO employees VALUES(124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', TO_DATE('16-NOV-1999', 'dd-MON-yyyy'), 'ST_MAN', 5800, NULL, 100, 50);
INSERT INTO employees VALUES(141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', TO_DATE('17-OCT-1995', 'dd-MON-yyyy'), 'ST_CLERK', 3500, NULL, 124, 50);
INSERT INTO employees VALUES(142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', TO_DATE('29-JAN-1997', 'dd-MON-yyyy'), 'ST_CLERK', 3100, NULL, 124, 50);
INSERT INTO employees VALUES(143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', TO_DATE('15-MAR-1998', 'dd-MON-yyyy'), 'ST_CLERK', 2600, NULL, 124, 50);
INSERT INTO employees VALUES(144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', TO_DATE('09-JUL-1998', 'dd-MON-yyyy'), 'ST_CLERK', 2500, NULL, 124, 50);
INSERT INTO employees VALUES(149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', TO_DATE('29-JAN-2000', 'dd-MON-yyyy'), 'SA_MAN', 10500, .2, 100, 80);
INSERT INTO employees VALUES(174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', TO_DATE('11-MAY-1996', 'dd-MON-yyyy'), 'SA_REP', 11000, .30, 149, 80);
INSERT INTO employees VALUES(176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', TO_DATE('24-MAR-1998', 'dd-MON-yyyy'), 'SA_REP', 8600, .20, 149, 80);
INSERT INTO employees VALUES(178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', TO_DATE('24-MAY-1999', 'dd-MON-yyyy'), 'SA_REP', 7000, .15, 149, NULL);
INSERT INTO employees VALUES(200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', TO_DATE('17-SEP-1987', 'dd-MON-yyyy'), 'AD_ASST', 4400, NULL, 101, 10);
INSERT INTO employees VALUES(201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', TO_DATE('17-FEB-1996', 'dd-MON-yyyy'), 'MK_MAN', 13000, NULL, 100, 20);
INSERT INTO employees VALUES(202, 'Pat', 'Fay', 'PFAY', '603.123.6666', TO_DATE('17-AUG-1997', 'dd-MON-yyyy'), 'MK_REP', 6000, NULL, 201, 20);
INSERT INTO employees VALUES(205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', TO_DATE('07-JUN-1994', 'dd-MON-yyyy'), 'AC_MGR', 12000, NULL, 101, 110);
INSERT INTO employees VALUES(206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', TO_DATE('07-JUN-1994', 'dd-MON-yyyy'), 'AC_ACCOUNT', 8300, NULL, 205, 110);

CREATE TABLE jobs
    ( job_id         VARCHAR2(10)
 CONSTRAINT     job_id_nn  NOT NULL
    , job_title      VARCHAR2(35)
 CONSTRAINT     job_title_nn  NOT NULL
    , min_salary     NUMBER(6)
    , max_salary     NUMBER(6)
    , CONSTRAINT job_id_pk
         PRIMARY KEY(job_id)
    ) ;

INSERT INTO jobs VALUES('AD_PRES', 'President', 20000, 40000);
INSERT INTO jobs VALUES('AD_VP', 'Administration Vice President', 15000, 30000);
INSERT INTO jobs VALUES('AD_ASST', 'Administration Assistant', 3000, 6000);
INSERT INTO jobs VALUES('AC_MGR', 'Accounting Manager', 8200, 16000);
INSERT INTO jobs VALUES('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
INSERT INTO jobs VALUES('SA_MAN', 'Sales Manager', 10000, 20000);
INSERT INTO jobs VALUES('SA_REP', 'Sales Representative', 6000, 12000);
INSERT INTO jobs VALUES('ST_MAN', 'Stock Manager', 5500, 8500);
INSERT INTO jobs VALUES('ST_CLERK', 'Stock Clerk', 2000, 5000);
INSERT INTO jobs VALUES('IT_PROG', 'Programmer', 4000, 10000);
INSERT INTO jobs VALUES('MK_MAN', 'Marketing Manager', 9000, 15000);
INSERT INTO jobs VALUES('MK_REP', 'Marketing Representative', 4000, 9000);

CREATE TABLE job_grades
    ( grade_level    VARCHAR2(3)
   , lowest_sal     NUMBER
    , highest_sal    NUMBER
    ) ;

INSERT INTO job_grades VALUES('A', 1000, 2999);
INSERT INTO job_grades VALUES('B', 3000, 5999);
INSERT INTO job_grades VALUES('C', 6000, 9999);
INSERT INTO job_grades VALUES('D', 10000, 14999);
INSERT INTO job_grades VALUES('E', 15000, 24999);
INSERT INTO job_grades VALUES('F', 25000, 40000);

CREATE TABLE job_history
    ( employee_id   NUMBER(6)
  CONSTRAINT    jhist_employee_nn  NOT NULL
    , start_date    DATE
 CONSTRAINT    jhist_start_date_nn  NOT NULL
    , end_date      DATE
 CONSTRAINT    jhist_end_date_nn  NOT NULL
    , job_id        VARCHAR2(10)
 CONSTRAINT    jhist_job_nn  NOT NULL
    , department_id NUMBER(4)
    , CONSTRAINT jhist_emp_id_st_date_pk
      PRIMARY KEY (employee_id, start_date)
    , CONSTRAINT    jhist_date_interval
                    CHECK (end_date > start_date)
    ) ;

INSERT INTO job_history VALUES(102, TO_DATE('13-JAN-1993', 'dd-MON-yyyy'), TO_DATE('24-JUL-1998', 'dd-MON-yyyy'), 'IT_PROG', 60);
INSERT INTO job_history VALUES(101, TO_DATE('21-SEP-1989', 'dd-MON-yyyy'), TO_DATE('27-OCT-1993', 'dd-MON-yyyy'), 'AC_ACCOUNT', 110);
INSERT INTO job_history VALUES(101, TO_DATE('28-OCT-1993', 'dd-MON-yyyy'), TO_DATE('15-MAR-1997', 'dd-MON-yyyy'), 'AC_MGR', 110);
INSERT INTO job_history VALUES(201, TO_DATE('17-FEB-1996', 'dd-MON-yyyy'), TO_DATE('19-DEC-1999', 'dd-MON-yyyy'), 'MK_REP', 20);
INSERT INTO job_history VALUES(104, TO_DATE('24-MAR-1998', 'dd-MON-yyyy'), TO_DATE('31-DEC-1999', 'dd-MON-yyyy'), 'ST_CLERK', 50);
INSERT INTO job_history VALUES(142, TO_DATE('01-JAN-1999', 'dd-MON-yyyy'), TO_DATE('31-DEC-1999', 'dd-MON-yyyy'), 'ST_CLERK', 50);
INSERT INTO job_history VALUES(200, TO_DATE('17-SEP-1987', 'dd-MON-yyyy'), TO_DATE('17-JUN-1993', 'dd-MON-yyyy'), 'AD_ASST', 90);
INSERT INTO job_history VALUES(176, TO_DATE('24-MAR-1998', 'dd-MON-yyyy'), TO_DATE('31-DEC-1998', 'dd-MON-yyyy'), 'SA_REP', 80);
INSERT INTO job_history VALUES(176, TO_DATE('01-JAN-1999', 'dd-MON-yyyy'), TO_DATE('31-DEC-1999', 'dd-MON-yyyy'), 'SA_MAN', 80);
INSERT INTO job_history VALUES(200, TO_DATE('01-JUL-1994', 'dd-MON-yyyy'), TO_DATE('31-DEC-1998', 'dd-MON-yyyy'), 'AC_ACCOUNT', 90);

CREATE TABLE locations
    ( location_id    NUMBER(4)
 CONSTRAINT     loc_location_id_nn  NOT NULL
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city           VARCHAR2(30)
 CONSTRAINT     loc_city_nn  NOT NULL
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    , CONSTRAINT loc_id_pk
          PRIMARY KEY (location_id)
    ) ;

INSERT INTO locations VALUES(1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO locations VALUES(1500,'2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO locations VALUES(1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
INSERT INTO locations VALUES(1800, '460 Bloor St. W.', 'ON M5S 1X8', 'Toronto', 'Ontario', 'CA');
INSERT INTO locations VALUES(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');

CREATE TABLE regions
    ( region_id      NUMBER
       CONSTRAINT  region_id_nn NOT NULL
    , region_name    VARCHAR2(25)
    , CONSTRAINT reg_id_pk
          PRIMARY KEY (region_id)
    );

INSERT INTO regions VALUES(1, 'Europe');
INSERT INTO regions VALUES(2, 'Americas');
INSERT INTO regions VALUES(3, 'Asia');
INSERT INTO regions VALUES(4, 'Middle East and Africa');

ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
          FOREIGN KEY (region_id)
             REFERENCES regions(region_id)
    ) ;

ALTER TABLE departments
ADD ( CONSTRAINT dept_loc_fk
          FOREIGN KEY (location_id)
           REFERENCES locations (location_id)
    , CONSTRAINT dept_mgr_fk
         FOREIGN KEY (manager_id)
          REFERENCES employees (employee_id)
    ) ;

ALTER TABLE employees
ADD ( CONSTRAINT     emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES departments
    , CONSTRAINT     emp_job_fk
                     FOREIGN KEY (job_id)
                      REFERENCES jobs (job_id)
    , CONSTRAINT     emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES employees
    ) ;

ALTER TABLE locations
ADD ( CONSTRAINT loc_c_id_fk
          FOREIGN KEY (country_id)
           REFERENCES countries(country_id)
    ) ;

ALTER TABLE job_history
ADD ( CONSTRAINT     jhist_job_fk
                     FOREIGN KEY (job_id)
                     REFERENCES jobs
    , CONSTRAINT     jhist_emp_fk
                     FOREIGN KEY (employee_id)
                     REFERENCES employees
    , CONSTRAINT     jhist_dept_fk
                     FOREIGN KEY (department_id)
                     REFERENCES departments
    ) ;

CREATE SEQUENCE departments_seq
 START WITH     280
 INCREMENT BY   10
 MAXVALUE       9990
 NOCACHE
 NOCYCLE;

CREATE SEQUENCE employees_seq
 START WITH     207
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

CREATE SEQUENCE locations_seq
 START WITH     3300
 INCREMENT BY   100
 MAXVALUE       9900
 NOCACHE
 NOCYCLE;

COMMIT;

 

'Oracle ERP > DB SCRIPTS' 카테고리의 다른 글

table type을 return하는 패키지 function  (0) 2012.04.04
Kill Locked Session  (0) 2010.03.10