SQL 오라클 SQL 쿼리문

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-07-1987', 'dd-MM-YY'), 'AD_PRES', 24000, NULL, NULL, 90);

INSERT INTO employees VALUES(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', TO_DATE('21-11-1989', 'dd-MM-YY'), 'AD_VP', 17000, NULL, 100, 90);

INSERT INTO employees VALUES(102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', TO_DATE('13-01-1993', 'dd-MM-YY'), 'AD_VP', 17000, NULL, 100, 90);

INSERT INTO employees VALUES(103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', TO_DATE('03-01-1990', 'dd-MM-YY'), 'IT_PROG', 9000, NULL, 102, 60);

INSERT INTO employees VALUES(104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', TO_DATE('21-06-1991', 'dd-MM-YY'), 'IT_PROG', 6000, NULL, 103, 60);

INSERT INTO employees VALUES(107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', TO_DATE('07-02-1999', 'dd-MM-YY'), 'IT_PROG', 4200, NULL, 103, 60);

INSERT INTO employees VALUES(124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', TO_DATE('16-09-1999', 'dd-MM-YY'), 'ST_MAN', 5800, NULL, 100, 50);

INSERT INTO employees VALUES(141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', TO_DATE('17-11-1995', 'dd-MM-YY'), 'ST_CLERK', 3500, NULL, 124, 50);

INSERT INTO employees VALUES(142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', TO_DATE('29-01-1997', 'dd-MM-YY'), 'ST_CLERK', 3100, NULL, 124, 50);

INSERT INTO employees VALUES(143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', TO_DATE('15-06-1998', 'dd-MM-YY'), 'ST_CLERK', 2600, NULL, 124, 50);

INSERT INTO employees VALUES(144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', TO_DATE('09-07-1998', 'dd-MM-YY'), 'ST_CLERK', 2500, NULL, 124, 50);

INSERT INTO employees VALUES(149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', TO_DATE('29-01-2000', 'dd-MM-YY'), 'SA_MAN', 10500, .2, 100, 80);

INSERT INTO employees VALUES(174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', TO_DATE('11-05-1996', 'dd-MM-YY'), 'SA_REP', 11000, .30, 149, 80);

INSERT INTO employees VALUES(176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', TO_DATE('24-05-1998', 'dd-MM-YY'), 'SA_REP', 8600, .20, 149, 80);

INSERT INTO employees VALUES(178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', TO_DATE('24-06-1999', 'dd-MM-YY'), 'SA_REP', 7000, .15, 149, NULL);

INSERT INTO employees VALUES(200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', TO_DATE('17-11-1987', 'dd-MM-YY'), 'AD_ASST', 4400, NULL, 101, 10);

INSERT INTO employees VALUES(201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', TO_DATE('17-02-1996', 'dd-MM-YY'), 'MK_MAN', 13000, NULL, 100, 20);

INSERT INTO employees VALUES(202, 'Pat', 'Fay', 'PFAY', '603.123.6666', TO_DATE('17-08-1997', 'dd-MM-YY'), 'MK_REP', 6000, NULL, 201, 20);

INSERT INTO employees VALUES(205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', TO_DATE('07-07-1994', 'dd-MM-YY'), 'AC_MGR', 12000, NULL, 101, 110);

INSERT INTO employees VALUES(206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', TO_DATE('07-07-1994', 'dd-MM-YY'), '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-01-1993', 'dd-MM-YY'), TO_DATE('24-06-1998', 'dd-MM-YY'), 'IT_PROG', 60);

INSERT INTO job_history VALUES(101, TO_DATE('21-11-1989', 'dd-MM-YY'), TO_DATE('27-11-1993', 'dd-MM-YY'), 'AC_ACCOUNT', 110);

INSERT INTO job_history VALUES(101, TO_DATE('28-12-1993', 'dd-MM-YY'), TO_DATE('15-07-1997', 'dd-MM-YY'), 'AC_MGR', 110);

INSERT INTO job_history VALUES(201, TO_DATE('17-02-1996', 'dd-MM-YY'), TO_DATE('19-10-1999', 'dd-MM-YY'), 'MK_REP', 20);

INSERT INTO job_history VALUES(104, TO_DATE('24-04-1998', 'dd-MM-YY'), TO_DATE('31-10-1999', 'dd-MM-YY'), 'ST_CLERK', 50);

INSERT INTO job_history VALUES(142, TO_DATE('01-01-1999', 'dd-MM-YY'), TO_DATE('31-10-1999', 'dd-MM-YY'), 'ST_CLERK', 50);

INSERT INTO job_history VALUES(200, TO_DATE('17-11-1987', 'dd-MM-YY'), TO_DATE('17-07-1993', 'dd-MM-YY'), 'AD_ASST', 90);

INSERT INTO job_history VALUES(176, TO_DATE('24-04-1998', 'dd-MM-YY'), TO_DATE('31-10-1998', 'dd-MM-YY'), 'SA_REP', 80);

INSERT INTO job_history VALUES(176, TO_DATE('01-01-1999', 'dd-MM-YY'), TO_DATE('31-10-1999', 'dd-MM-YY'), 'SA_MAN', 80);

INSERT INTO job_history VALUES(200, TO_DATE('01-07-1994', 'dd-MM-YY'), TO_DATE('31-10-1998', 'dd-MM-YY'), '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;