Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Oracle HR schema

By: a guest on Jul 10th, 2011  |  syntax: SQL  |  size: 67.25 KB  |  views: 467  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Rem
  2. Rem
  3. Rem    NAME
  4. Rem      FULL.SQL - Human Resources,  HR schema
  5. Rem
  6. Rem    DESCRIPTION
  7. Rem      This script creates six TABLES, associated constraints
  8. Rem      AND indexes IN the human resources (HR) schema.
  9. Rem
  10. Rem    NOTES
  11. Rem
  12. Rem    Oracle Corporation 2001.
  13. Rem    CREATED BY Nancy Greenberg, Nagavalli Pataballa - 06/01/00
  14. Rem    INTEGRATED BY Nelson Piedra nopiedra.wordpress.com
  15. Rem
  16. Rem    MODIFIED   (MM/DD/YY)
  17. Rem    ahunold     09/14/00 - Added emp_details_view
  18. Rem    ahunold     02/20/01 - NEW header
  19. Rem    vpatabal  03/02/01 - Added regions TABLE, modified regions
  20. Rem             COLUMN IN countries TABLE TO NUMBER.
  21. Rem             Added FOREIGN KEY FROM countries TABLE
  22. Rem             TO regions TABLE ON region_id.
  23. Rem                     Removed currency name, currency symbol
  24. Rem             COLUMNS FROM the countries TABLE.
  25. Rem                    Removed dn COLUMNS FROM employees AND
  26. Rem             departments TABLES.
  27. Rem             Added sequences.
  28. Rem             Removed NOT NULL CONSTRAINT FROM
  29. Rem              salary COLUMN OF the employees TABLE.
  30. SET FEEDBACK 1
  31. SET NUMWIDTH 10
  32. SET LINESIZE 80
  33. SET TRIMSPOOL ON
  34. SET TAB OFF
  35. SET PAGESIZE 100
  36. SET ECHO OFF
  37. REM ********************************************************************
  38. REM CREATE the REGIONS TABLE TO hold region information FOR locations
  39. REM HR.LOCATIONS TABLE has a FOREIGN KEY TO this TABLE.
  40. Prompt ******  Creating REGIONS TABLE ....
  41. CREATE TABLE regions
  42.     ( region_id      NUMBER
  43.        CONSTRAINT  region_id_nn NOT NULL
  44.     , region_name    VARCHAR2(25)
  45.     );
  46. CREATE UNIQUE INDEX reg_id_pk
  47. ON regions (region_id);
  48. ALTER TABLE regions
  49. ADD ( CONSTRAINT reg_id_pk
  50.          PRIMARY KEY (region_id)
  51.     ) ;
  52. REM ********************************************************************
  53. REM CREATE the COUNTRIES TABLE TO hold country information FOR customers
  54. REM AND company locations.
  55. REM OE.CUSTOMERS TABLE AND HR.LOCATIONS have a FOREIGN KEY TO this TABLE.
  56. Prompt ******  Creating COUNTRIES TABLE ....
  57. CREATE TABLE countries
  58.     ( country_id      CHAR(2)
  59.        CONSTRAINT  country_id_nn NOT NULL
  60.     , country_name    VARCHAR2(40)
  61.     , region_id       NUMBER
  62.     , CONSTRAINT     country_c_id_pk
  63.               PRIMARY KEY (country_id)
  64.     )
  65.     ORGANIZATION INDEX;
  66. ALTER TABLE countries
  67. ADD ( CONSTRAINT countr_reg_fk
  68.           FOREIGN KEY (region_id)
  69.              REFERENCES regions(region_id)
  70.     ) ;
  71. REM ********************************************************************
  72. REM CREATE the LOCATIONS TABLE TO hold address information FOR company
  73. departments.
  74. REM HR.DEPARTMENTS has a FOREIGN KEY TO this TABLE.
  75. Prompt ******  Creating LOCATIONS TABLE ....
  76. CREATE TABLE locations
  77.     ( location_id    NUMBER(4)
  78.     , street_address VARCHAR2(40)
  79.     , postal_code    VARCHAR2(12)
  80.     , city       VARCHAR2(30)
  81. CONSTRAINT     loc_city_nn  NOT NULL
  82.     , state_province VARCHAR2(25)
  83.     , country_id     CHAR(2)
  84.     ) ;
  85. CREATE UNIQUE INDEX loc_id_pk
  86. ON locations (location_id) ;
  87. ALTER TABLE locations
  88. ADD ( CONSTRAINT loc_id_pk
  89.          PRIMARY KEY (location_id)
  90.     , CONSTRAINT loc_c_id_fk
  91.          FOREIGN KEY (country_id)
  92.            REFERENCES countries(country_id)
  93.     ) ;
  94. Rem  Useful FOR any subsequent addition OF ROWS TO locations TABLE
  95. Rem  Starts WITH 3300
  96. CREATE SEQUENCE locations_seq
  97.  START WITH     3300
  98.  INCREMENT BY   100
  99.  MAXVALUE       9900
  100.  NOCACHE
  101.  NOCYCLE;
  102. REM ********************************************************************
  103. REM CREATE the DEPARTMENTS TABLE TO hold company department information.
  104. REM HR.EMPLOYEES AND HR.JOB_HISTORY have a FOREIGN KEY TO this TABLE.
  105. Prompt ******  Creating DEPARTMENTS TABLE ....
  106. CREATE TABLE departments
  107.     ( department_id    NUMBER(4)
  108.     , department_name  VARCHAR2(30)
  109. CONSTRAINT  dept_name_nn  NOT NULL
  110.     , manager_id       NUMBER(6)
  111.     , location_id      NUMBER(4)
  112.     ) ;
  113. CREATE UNIQUE INDEX dept_id_pk
  114. ON departments (department_id) ;
  115. ALTER TABLE departments
  116. ADD ( CONSTRAINT dept_id_pk
  117.          PRIMARY KEY (department_id)
  118.     , CONSTRAINT dept_loc_fk
  119.          FOREIGN KEY (location_id)
  120.            REFERENCES locations (location_id)
  121.      ) ;
  122. Rem  Useful FOR any subsequent addition OF ROWS TO departments TABLE
  123. Rem  Starts WITH 280
  124. CREATE SEQUENCE departments_seq
  125.  START WITH     280
  126.  INCREMENT BY   10
  127.  MAXVALUE       9990
  128.  NOCACHE
  129.  NOCYCLE;
  130. REM ********************************************************************
  131. REM CREATE the JOBS TABLE TO hold the different names OF job roles WITHIN
  132. the company.
  133. REM HR.EMPLOYEES has a FOREIGN KEY TO this TABLE.
  134. Prompt ******  Creating JOBS TABLE ....
  135. CREATE TABLE jobs
  136.     ( job_id         VARCHAR2(10)
  137.     , job_title      VARCHAR2(35)
  138. CONSTRAINT     job_title_nn  NOT NULL
  139.     , min_salary     NUMBER(6)
  140.     , max_salary     NUMBER(6)
  141.     ) ;
  142. CREATE UNIQUE INDEX job_id_pk
  143. ON jobs (job_id) ;
  144. ALTER TABLE jobs
  145. ADD ( CONSTRAINT job_id_pk
  146.         PRIMARY KEY(job_id)
  147.     ) ;
  148. REM ********************************************************************
  149. REM CREATE the EMPLOYEES TABLE TO hold the employee personnel
  150. REM information FOR the company.
  151. REM HR.EMPLOYEES has a SELF REFERENCING FOREIGN KEY TO this TABLE.
  152. Prompt ******  Creating EMPLOYEES TABLE ....
  153. CREATE TABLE employees
  154.     ( employee_id    NUMBER(6)
  155.     , first_name     VARCHAR2(20)
  156.     , last_name      VARCHAR2(25)
  157.  CONSTRAINT     emp_last_name_nn  NOT NULL
  158.     , email          VARCHAR2(25)
  159. CONSTRAINT     emp_email_nn  NOT NULL
  160.     , phone_number   VARCHAR2(20)
  161.     , hire_date      DATE
  162. CONSTRAINT     emp_hire_date_nn  NOT NULL
  163.     , job_id         VARCHAR2(10)
  164. CONSTRAINT     emp_job_nn  NOT NULL
  165.     , salary         NUMBER(8,2)
  166.     , commission_pct NUMBER(2,2)
  167.     , manager_id     NUMBER(6)
  168.     , department_id  NUMBER(4)
  169.     , CONSTRAINT     emp_salary_min
  170.                      CHECK (salary > 0)
  171.     , CONSTRAINT     emp_email_uk
  172.                      UNIQUE (email)
  173.     ) ;
  174. CREATE UNIQUE INDEX emp_emp_id_pk
  175. ON employees (employee_id) ;
  176. ALTER TABLE employees
  177. ADD ( CONSTRAINT     emp_emp_id_pk
  178.                      PRIMARY KEY (employee_id)
  179.     , CONSTRAINT     emp_dept_fk
  180.                      FOREIGN KEY (department_id)
  181.                       REFERENCES departments
  182.     , CONSTRAINT     emp_job_fk
  183.                      FOREIGN KEY (job_id)
  184.                       REFERENCES jobs (job_id)
  185.     , CONSTRAINT     emp_manager_fk
  186.                      FOREIGN KEY (manager_id)
  187.                       REFERENCES employees
  188.     ) ;
  189. ALTER TABLE departments
  190. ADD ( CONSTRAINT dept_mgr_fk
  191.         FOREIGN KEY (manager_id)
  192.          REFERENCES employees (employee_id)
  193.     ) ;
  194. Rem  Useful FOR any subsequent addition OF ROWS TO employees TABLE
  195. Rem  Starts WITH 207
  196. CREATE SEQUENCE employees_seq
  197.  START WITH     207
  198.  INCREMENT BY   1
  199.  NOCACHE
  200.  NOCYCLE;
  201. REM ********************************************************************
  202. REM CREATE the JOB_HISTORY TABLE TO hold the history OF jobs that
  203. REM employees have held IN the past.
  204. REM HR.JOBS, HR_DEPARTMENTS, AND HR.EMPLOYEES have a FOREIGN KEY TO this
  205. TABLE.
  206. Prompt ******  Creating JOB_HISTORY TABLE ....
  207. CREATE TABLE job_history
  208.     ( employee_id   NUMBER(6)
  209.  CONSTRAINT    jhist_employee_nn  NOT NULL
  210.     , start_date    DATE
  211. CONSTRAINT    jhist_start_date_nn  NOT NULL
  212.     , end_date      DATE
  213. CONSTRAINT    jhist_end_date_nn  NOT NULL
  214.     , job_id        VARCHAR2(10)
  215. CONSTRAINT    jhist_job_nn  NOT NULL
  216.     , department_id NUMBER(4)
  217.     , CONSTRAINT    jhist_date_interval
  218.                     CHECK (end_date > start_date)
  219.     ) ;
  220. CREATE UNIQUE INDEX jhist_emp_id_st_date_pk
  221. ON job_history (employee_id, start_date) ;
  222. ALTER TABLE job_history
  223. ADD ( CONSTRAINT jhist_emp_id_st_date_pk
  224.       PRIMARY KEY (employee_id, start_date)
  225.     , CONSTRAINT     jhist_job_fk
  226.                      FOREIGN KEY (job_id)
  227.                      REFERENCES jobs
  228.     , CONSTRAINT     jhist_emp_fk
  229.                      FOREIGN KEY (employee_id)
  230.                      REFERENCES employees
  231.     , CONSTRAINT     jhist_dept_fk
  232.                      FOREIGN KEY (department_id)
  233.                      REFERENCES departments
  234.     ) ;
  235. REM ********************************************************************
  236. REM CREATE the EMP_DETAILS_VIEW that joins the employees, jobs,
  237. REM departments, jobs, countries, AND locations TABLE TO provide details
  238. REM about employees.
  239. Prompt ******  Creating EMP_DETAILS_VIEW VIEW ...
  240. CREATE OR REPLACE VIEW emp_details_view
  241.   (employee_id,
  242.    job_id,
  243.    manager_id,
  244.    department_id,
  245.    location_id,
  246.    country_id,
  247.    first_name,
  248.    last_name,
  249.    salary,
  250.    commission_pct,
  251.    department_name,
  252.    job_title,
  253.    city,
  254.    state_province,
  255.    country_name,
  256.    region_name)
  257. AS SELECT
  258.   e.employee_id,
  259.   e.job_id,
  260.   e.manager_id,
  261.   e.department_id,
  262.   d.location_id,
  263.   l.country_id,
  264.   e.first_name,
  265.   e.last_name,
  266.   e.salary,
  267.   e.commission_pct,
  268.   d.department_name,
  269.   j.job_title,
  270.   l.city,
  271.   l.state_province,
  272.   c.country_name,
  273.   r.region_name
  274. FROM
  275.   employees e,
  276.   departments d,
  277.   jobs j,
  278.   locations l,
  279.   countries c,
  280.   regions r
  281. WHERE e.department_id = d.department_id
  282.   AND d.location_id = l.location_id
  283.   AND l.country_id = c.country_id
  284.   AND c.region_id = r.region_id
  285.   AND j.job_id = e.job_id
  286. WITH READ ONLY;
  287. COMMIT;
  288. rem
  289. rem Header: hr_popul.SQL 09-jan-01
  290. rem
  291. rem Copyright (c) 2001 Oracle Corporation.  ALL rights reserved.
  292. rem
  293. rem Owner  : ahunold
  294. rem
  295. rem NAME
  296. rem   hr_popul.SQL - Populate script FOR HR schema
  297. rem
  298. rem DESCRIPTON
  299. rem
  300. rem
  301. rem NOTES
  302. rem   There IS a circular FOREIGN KEY reference BETWEEN
  303. rem   EMPLOYESS AND DEPARTMENTS. That's why we disable
  304. rem   the FK constraints here
  305. rem
  306. rem CREATED
  307. rem   Nancy Greenberg, Nagavalli Pataballa - 06/01/00
  308. rem
  309. rem MODIFIED   (MM/DD/YY)
  310. rem   ahunold   03/07/01 - small data errors corrected
  311. rem                      - Modified region values of countries table
  312. rem                      - Replaced ID sequence values for employees
  313. rem                        and departments tables with numbers
  314. rem                      - Moved create sequence statements to hr_cre
  315. rem                      - Removed dn values for employees and
  316. rem                        departments tables
  317. rem                      - Removed currency columns values from
  318. rem                        countries table
  319. rem   ngreenbe           - Updated employee 178 for no department
  320. rem   pnathan            - Insert new rows to job_history table
  321. rem   ahunold   02/20/01 - NLS_LANGUAGE, replacing non American
  322. rem   ahunold   01/09/01 - checkin ADE
  323. SET VERIFY OFF
  324. ALTER SESSION SET NLS_LANGUAGE=American;
  325. REM ***************************insert data into the REGIONS table
  326. Prompt ******  Populating REGIONS table ....
  327. INSERT INTO regions VALUES
  328.        ( 1
  329.        , 'Europe'
  330.        );
  331. INSERT INTO regions VALUES
  332.        ( 2
  333.        , 'Americas'
  334.        );
  335. INSERT INTO regions VALUES
  336.        ( 3
  337.        , 'Asia'
  338.        );
  339. INSERT INTO regions VALUES
  340.        ( 4
  341.        , 'Middle East AND Africa'
  342.        );
  343. REM ***************************insert data into the COUNTRIES table
  344. Prompt ******  Populating COUNTIRES table ....
  345. INSERT INTO countries VALUES
  346.        ( 'IT'
  347.        , 'Italy'
  348.        , 1
  349.        );
  350. INSERT INTO countries VALUES
  351.        ( 'JP'
  352.        , 'Japan'
  353. , 3
  354.        );
  355. INSERT INTO countries VALUES
  356.        ( 'US'
  357.        , 'United States OF America'
  358.        , 2
  359.        );
  360. INSERT INTO countries VALUES
  361.        ( 'CA'
  362.        , 'Canada'
  363.        , 2
  364.        );
  365. INSERT INTO countries VALUES
  366.        ( 'CN'
  367.        , 'China'
  368.        , 3
  369.        );
  370. INSERT INTO countries VALUES
  371.        ( 'IN'
  372.        , 'India'
  373.        , 3
  374.        );
  375. INSERT INTO countries VALUES
  376.        ( 'AU'
  377.        , 'Australia'
  378.        , 3
  379.        );
  380. INSERT INTO countries VALUES
  381.        ( 'ZW'
  382.        , 'Zimbabwe'
  383.        , 4
  384.        );
  385. INSERT INTO countries VALUES
  386.        ( 'SG'
  387.        , 'Singapore'
  388.        , 3
  389.        );
  390. INSERT INTO countries VALUES
  391.        ( 'UK'
  392.        , 'United Kingdom'
  393.        , 1
  394.        );
  395. INSERT INTO countries VALUES
  396.        ( 'FR'
  397.        , 'France'
  398.        , 1
  399.        );
  400. INSERT INTO countries VALUES
  401.        ( 'DE'
  402.        , 'Germany'
  403.        , 1
  404.        );
  405. INSERT INTO countries VALUES
  406.        ( 'ZM'
  407.        , 'Zambia'
  408.        , 4
  409.        );
  410. INSERT INTO countries VALUES
  411.        ( 'EG'
  412.        , 'Egypt'
  413.        , 4
  414.        );
  415. INSERT INTO countries VALUES
  416.        ( 'BR'
  417.        , 'Brazil'
  418.        , 2
  419.        );
  420. INSERT INTO countries VALUES
  421.        ( 'CH'
  422.        , 'Switzerland'
  423.        , 1
  424.        );
  425. INSERT INTO countries VALUES
  426.        ( 'NL'
  427.        , 'Netherlands'
  428.        , 1
  429.        );
  430. INSERT INTO countries VALUES
  431.        ( 'MX'
  432.        , 'Mexico'
  433.        , 2
  434.        );
  435. INSERT INTO countries VALUES
  436.        ( 'KW'
  437.        , 'Kuwait'
  438.        , 4
  439.        );
  440. INSERT INTO countries VALUES
  441.        ( 'IL'
  442.        , 'Israel'
  443.        , 4
  444.        );
  445. INSERT INTO countries VALUES
  446.        ( 'DK'
  447.        , 'Denmark'
  448.        , 1
  449.        );
  450. INSERT INTO countries VALUES
  451.        ( 'HK'
  452.        , 'HongKong'
  453.        , 3
  454.        );
  455. INSERT INTO countries VALUES
  456.        ( 'NG'
  457.        , 'Nigeria'
  458.        , 4
  459.        );
  460. INSERT INTO countries VALUES
  461.        ( 'AR'
  462.        , 'Argentina'
  463.        , 2
  464.        );
  465. INSERT INTO countries VALUES
  466.        ( 'BE'
  467.        , 'Belgium'
  468.        , 1
  469.        );
  470. REM ***************************insert data into the LOCATIONS table
  471. Prompt ******  Populating LOCATIONS table ....
  472. INSERT INTO locations VALUES
  473.        ( 1000
  474.        , '1297 Via Cola di Rie'
  475.        , '00989'
  476.        , 'Roma'
  477.        , NULL
  478.        , 'IT'
  479.        );
  480. INSERT INTO locations VALUES
  481.        ( 1100
  482.        , '93091 Calle della Testa'
  483.        , '10934'
  484.        , 'Venice'
  485.        , NULL
  486.        , 'IT'
  487.        );
  488. INSERT INTO locations VALUES
  489.        ( 1200
  490.        , '2017 Shinjuku-ku'
  491.        , '1689'
  492.        , 'Tokyo'
  493.        , 'Tokyo Prefecture'
  494.        , 'JP'
  495.        );
  496. INSERT INTO locations VALUES
  497.        ( 1300
  498.        , '9450 Kamiya-cho'
  499.        , '6823'
  500.        , 'Hiroshima'
  501.        , NULL
  502.        , 'JP'
  503.        );
  504. INSERT INTO locations VALUES
  505.        ( 1400
  506.        , '2014 Jabberwocky Rd'
  507.        , '26192'
  508.        , 'Southlake'
  509.        , 'Texas'
  510.        , 'US'
  511.        );
  512. INSERT INTO locations VALUES
  513.        ( 1500
  514.        , '2011 Interiors Blvd'
  515.        , '99236'
  516.        , 'South San Francisco'
  517.        , 'California'
  518.        , 'US'
  519.        );
  520. INSERT INTO locations VALUES
  521.        ( 1600
  522.        , '2007 Zagora St'
  523.        , '50090'
  524.        , 'South Brunswick'
  525.        , 'NEW Jersey'
  526.        , 'US'
  527.        );
  528. INSERT INTO locations VALUES
  529.        ( 1700
  530.        , '2004 Charade Rd'
  531.        , '98199'
  532.        , 'Seattle'
  533.        , 'Washington'
  534.        , 'US'
  535.        );
  536. INSERT INTO locations VALUES
  537.        ( 1800
  538.        , '147 Spadina Ave'
  539.        , 'M5V 2L7'
  540.        , 'Toronto'
  541.        , 'Ontario'
  542.        , 'CA'
  543.        );
  544. INSERT INTO locations VALUES
  545.        ( 1900
  546.        , '6092 Boxwood St'
  547.        , 'YSW 9T2'
  548.        , 'Whitehorse'
  549.        , 'Yukon'
  550.        , 'CA'
  551.        );
  552. INSERT INTO locations VALUES
  553.        ( 2000
  554.        , '40-5-12 Laogianggen'
  555.        , '190518'
  556.        , 'Beijing'
  557.        , NULL
  558.        , 'CN'
  559.        );
  560. INSERT INTO locations VALUES
  561.        ( 2100
  562.        , '1298 Vileparle (E)'
  563.        , '490231'
  564.        , 'Bombay'
  565.        , 'Maharashtra'
  566.        , 'IN'
  567.        );
  568. INSERT INTO locations VALUES
  569.        ( 2200
  570.        , '12-98 Victoria Street'
  571.        , '2901'
  572.        , 'Sydney'
  573.        , 'NEW South Wales'
  574.        , 'AU'
  575.        );
  576. INSERT INTO locations VALUES
  577.        ( 2300
  578.        , '198 Clementi North'
  579.        , '540198'
  580.        , 'Singapore'
  581.        , NULL
  582.        , 'SG'
  583.        );
  584. INSERT INTO locations VALUES
  585.        ( 2400
  586.        , '8204 Arthur St'
  587.        , NULL
  588.        , 'London'
  589.        , NULL
  590.        , 'UK'
  591.        );
  592. INSERT INTO locations VALUES
  593.        ( 2500
  594.        , 'Magdalen Centre, The Oxford Science Park'
  595.        , 'OX9 9ZB'
  596.        , 'Oxford'
  597.        , 'Oxford'
  598.        , 'UK'
  599.        );
  600. INSERT INTO locations VALUES
  601.        ( 2600
  602.        , '9702 Chester Road'
  603.        , '09629850293'
  604.        , 'Stretford'
  605.        , 'Manchester'
  606.        , 'UK'
  607.        );
  608. INSERT INTO locations VALUES
  609.        ( 2700
  610.        , 'Schwanthalerstr. 7031'
  611.        , '80925'
  612.        , 'Munich'
  613.        , 'Bavaria'
  614.        , 'DE'
  615.        );
  616. INSERT INTO locations VALUES
  617.        ( 2800
  618.        , 'Rua Frei Caneca 1360 '
  619.        , '01307-002'
  620.        , 'Sao Paulo'
  621.        , 'BR'
  622.        );
  623. INSERT INTO locations VALUES
  624.        ( 2900
  625.        , '20 Rue des Corps-Saints'
  626.        , '1730'
  627.        , 'Geneva'
  628.        , 'Geneve'
  629.        , 'CH'
  630.        );
  631. INSERT INTO locations VALUES
  632.        ( 3000
  633.        , 'Murtenstrasse 921'
  634.        , '3095'
  635.        , 'Bern'
  636.        , 'BE'
  637.        , 'CH'
  638.        );
  639. INSERT INTO locations VALUES
  640.        ( 3100
  641.        , 'Pieter Breughelstraat 837'
  642.        , '3029SK'
  643.        , 'Utrecht'
  644.        , 'NL'
  645.        );
  646. INSERT INTO locations VALUES
  647.        ( 3200
  648.        , 'Mariano Escobedo 9991'
  649.        , '11932'
  650.        , 'Mexico City'
  651.        , 'Distrito Federal,'
  652.        , 'MX'
  653.        );
  654. REM ****************************insert data into the DEPARTMENTS table
  655. Prompt ******  Populating DEPARTMENTS table ....
  656. REM disable integrity constraint to EMPLOYEES to load data
  657. ALTER TABLE departments
  658.  DISABLE CONSTRAINT dept_mgr_fk;
  659. INSERT INTO departments VALUES
  660.        ( 10
  661.        , 'Administration'
  662.        , 200
  663.        , 1700
  664.        );
  665. INSERT INTO departments VALUES
  666.        ( 20
  667.        , 'Marketing'
  668.        , 201
  669.        , 1800
  670.        );
  671.                                
  672. INSERT INTO departments VALUES
  673.        ( 30
  674.        , 'Purchasing'
  675.        , 114
  676.        , 1700
  677. );
  678.                
  679. INSERT INTO departments VALUES
  680.        ( 40
  681.        , 'Human Resources'
  682.        , 203
  683.        , 2400
  684.        );
  685. INSERT INTO departments VALUES
  686.        ( 50
  687.        , 'Shipping'
  688.        , 121
  689.        , 1500
  690.        );
  691.                
  692. INSERT INTO departments VALUES
  693.        ( 60
  694.        , 'IT'
  695.        , 103
  696.        , 1400
  697.        );
  698.                
  699. INSERT INTO departments VALUES
  700.        ( 70
  701.        , 'Public Relations'
  702.        , 204
  703.        , 2700
  704.        );
  705.                
  706. INSERT INTO departments VALUES
  707.        ( 80
  708.        , 'Sales'
  709.        , 145
  710.        , 2500
  711.        );
  712.                
  713. INSERT INTO departments VALUES
  714.        ( 90
  715.        , 'Executive'
  716.        , 100
  717.        , 1700
  718.        );
  719. INSERT INTO departments VALUES
  720.        ( 100
  721.        , 'Finance'
  722.        , 108
  723.        , 1700
  724.        );
  725.                
  726. INSERT INTO departments VALUES
  727.        ( 110
  728.        , 'Accounting'
  729.        , 205
  730.        , 1700
  731.        );
  732. INSERT INTO departments VALUES
  733.        ( 120
  734.        , 'Treasury'
  735.        , NULL
  736.        , 1700
  737.        );
  738. INSERT INTO departments VALUES
  739.        ( 130
  740.        , 'Corporate Tax'
  741.        , NULL
  742.        , 1700
  743.        );
  744. INSERT INTO departments VALUES
  745.        ( 140
  746.        , 'Control AND Credit'
  747.        , NULL
  748.        , 1700
  749.        );
  750. INSERT INTO departments VALUES
  751.        ( 150
  752.        , 'Shareholder Services'
  753.        , NULL
  754.        , 1700
  755.        );
  756. INSERT INTO departments VALUES
  757.        ( 160
  758.        , 'Benefits'
  759.        , NULL
  760.        , 1700
  761.        );
  762. INSERT INTO departments VALUES
  763.        ( 170
  764.        , 'Manufacturing'
  765.        , NULL
  766.        , 1700
  767.        );
  768. INSERT INTO departments VALUES
  769.        ( 180
  770.        , 'Construction'
  771.        , NULL
  772.        , 1700
  773.        );
  774. INSERT INTO departments VALUES
  775.        ( 190
  776.        , 'Contracting'
  777.        , NULL
  778.        , 1700
  779.        );
  780. INSERT INTO departments VALUES
  781.        ( 200
  782.        , 'Operations'
  783.        , NULL
  784.        , 1700
  785.        );
  786. INSERT INTO departments VALUES
  787.        ( 210
  788.        , 'IT Support'
  789.        , NULL
  790.        , 1700
  791.        );
  792. INSERT INTO departments VALUES
  793.        ( 220
  794.        , 'NOC'
  795.        , NULL
  796.        , 1700
  797.        );
  798. INSERT INTO departments VALUES
  799.        ( 230
  800.        , 'IT Helpdesk'
  801.        , NULL
  802.        , 1700
  803.        );
  804. INSERT INTO departments VALUES
  805.        ( 240
  806.        , 'Government Sales'
  807.        , NULL
  808.        , 1700
  809.        );
  810. INSERT INTO departments VALUES
  811.        ( 250
  812.        , 'Retail Sales'
  813.        , NULL
  814.        , 1700
  815.        );
  816. INSERT INTO departments VALUES
  817.        ( 260
  818.        , 'Recruiting'
  819.        , NULL
  820.        , 1700
  821.        );
  822. INSERT INTO departments VALUES
  823.        ( 270
  824.        , 'Payroll'
  825.        , NULL
  826.        , 1700
  827.        );
  828. REM ***************************insert data into the JOBS table
  829. Prompt ******  Populating JOBS table ....
  830. INSERT INTO jobs VALUES
  831.        ( 'AD_PRES'
  832.        , 'President'
  833.        , 20000
  834.        , 40000
  835.        );
  836. INSERT INTO jobs VALUES
  837.        ( 'AD_VP'
  838.        , 'Administration Vice President'
  839.        , 15000
  840.        , 30000
  841.        );
  842. INSERT INTO jobs VALUES
  843.        ( 'AD_ASST'
  844.        , 'Administration Assistant'
  845.        , 3000
  846.        , 6000
  847.        );
  848. INSERT INTO jobs VALUES
  849.        ( 'FI_MGR'
  850.        , 'Finance Manager'
  851.        , 8200
  852.        , 16000
  853.        );
  854. INSERT INTO jobs VALUES
  855.        ( 'FI_ACCOUNT'
  856.        , 'Accountant'
  857.        , 4200
  858.        , 9000
  859.        );
  860. INSERT INTO jobs VALUES
  861.        ( 'AC_MGR'
  862.        , 'Accounting Manager'
  863.        , 8200
  864.        , 16000
  865.        );
  866. INSERT INTO jobs VALUES
  867.        ( 'AC_ACCOUNT'
  868.        , 'Public Accountant'
  869.        , 4200
  870.        , 9000
  871.        );
  872. INSERT INTO jobs VALUES
  873.        ( 'SA_MAN'
  874.        , 'Sales Manager'
  875.        , 10000
  876.        , 20000
  877.        );
  878. INSERT INTO jobs VALUES
  879.        ( 'SA_REP'
  880.        , 'Sales Representative'
  881.        , 6000
  882.        , 12000
  883.        );
  884. INSERT INTO jobs VALUES
  885.        ( 'PU_MAN'
  886.        , 'Purchasing Manager'
  887.        , 8000
  888.        , 15000
  889.        );
  890. INSERT INTO jobs VALUES
  891.        ( 'PU_CLERK'
  892.        , 'Purchasing Clerk'
  893.        , 2500
  894.        , 5500
  895.        );
  896. INSERT INTO jobs VALUES
  897.        ( 'ST_MAN'
  898.        , 'Stock Manager'
  899.        , 5500
  900.        , 8500
  901.        );
  902. INSERT INTO jobs VALUES
  903.        ( 'ST_CLERK'
  904.        , 'Stock Clerk'
  905.        , 2000
  906.        , 5000
  907.        );
  908. INSERT INTO jobs VALUES
  909.        ( 'SH_CLERK'
  910.        , 'Shipping Clerk'
  911.        , 2500
  912.        , 5500
  913.        );
  914. INSERT INTO jobs VALUES
  915.        ( 'IT_PROG'
  916.        , 'Programmer'
  917.        , 4000
  918.        , 10000
  919.        );
  920. INSERT INTO jobs VALUES
  921.        ( 'MK_MAN'
  922.        , 'Marketing Manager'
  923.        , 9000
  924.        , 15000
  925.        );
  926. INSERT INTO jobs VALUES
  927.        ( 'MK_REP'
  928.        , 'Marketing Representative'
  929.        , 4000
  930.        , 9000
  931.        );
  932. INSERT INTO jobs VALUES
  933.        ( 'HR_REP'
  934.        , 'Human Resources Representative'
  935.        , 4000
  936.        , 9000
  937.        );
  938. INSERT INTO jobs VALUES
  939.        ( 'PR_REP'
  940.        , 'Public Relations Representative'
  941.        , 4500
  942.        , 10500
  943.        );
  944. REM ***************************insert data into the EMPLOYEES table
  945. Prompt ******  Populating EMPLOYEES table ....
  946. INSERT INTO employees VALUES
  947.        ( 100
  948.        , 'Steven'
  949.        , 'King'
  950.        , 'SKING'
  951.        , '515.123.4567'
  952.        , TO_DATE('17-JUN-1987', 'dd-MON-yyyy')
  953.        , 'AD_PRES'
  954.        , 24000
  955.        , NULL
  956.        , 90
  957.        );
  958. INSERT INTO employees VALUES
  959.        ( 101
  960.        , 'Neena'
  961.        , 'Kochhar'
  962.        , 'NKOCHHAR'
  963.        , '515.123.4568'
  964.        , TO_DATE('21-SEP-1989', 'dd-MON-yyyy')
  965.        , 'AD_VP'
  966.        , 17000
  967.        , NULL
  968.        , 100
  969.        , 90
  970.        );
  971. INSERT INTO employees VALUES
  972.        ( 102
  973.        , 'Lex'
  974.        , 'De Haan'
  975.        , 'LDEHAAN'
  976.        , '515.123.4569'
  977.        , TO_DATE('13-JAN-1993', 'dd-MON-yyyy')
  978.        , 'AD_VP'
  979.        , 17000
  980.        , NULL
  981.        , 100
  982.        , 90
  983.        );
  984. INSERT INTO employees VALUES
  985.        ( 103
  986.        , 'Alexander'
  987.        , 'Hunold'
  988.        , 'AHUNOLD'
  989.        , '590.423.4567'
  990.        , TO_DATE('03-JAN-1990', 'dd-MON-yyyy')
  991.        , 'IT_PROG'
  992.        , 9000
  993.        , NULL
  994.        , 102
  995.        , 60
  996.        );
  997. INSERT INTO employees VALUES
  998.        ( 104
  999.        , 'Bruce'
  1000.        , 'Ernst'
  1001.        , 'BERNST'
  1002.        , '590.423.4568'
  1003.        , TO_DATE('21-MAY-1991', 'dd-MON-yyyy')
  1004.        , 'IT_PROG'
  1005.        , 6000
  1006.        , NULL
  1007.        , 103
  1008.        , 60
  1009.        );
  1010. INSERT INTO employees VALUES
  1011.        ( 105
  1012.        , 'David'
  1013.        , 'Austin'
  1014.        , 'DAUSTIN'
  1015.        , '590.423.4569'
  1016.        , TO_DATE('25-JUN-1997', 'dd-MON-yyyy')
  1017.        , 'IT_PROG'
  1018.        , 4800
  1019.        , NULL
  1020.        , 103
  1021.        , 60
  1022.        );
  1023. INSERT INTO employees VALUES
  1024.        ( 106
  1025.        , 'Valli'
  1026.        , 'Pataballa'
  1027.        , 'VPATABAL'
  1028.        , '590.423.4560'
  1029.        , TO_DATE('05-FEB-1998', 'dd-MON-yyyy')
  1030.        , 'IT_PROG'
  1031.        , 4800
  1032.        , NULL
  1033.        , 103
  1034.        , 60
  1035.        );
  1036. INSERT INTO employees VALUES
  1037.        ( 107
  1038.        , 'Diana'
  1039.        , 'Lorentz'
  1040.        , 'DLORENTZ'
  1041.        , '590.423.5567'
  1042.        , TO_DATE('07-FEB-1999', 'dd-MON-yyyy')
  1043.        , 'IT_PROG'
  1044.        , 4200
  1045.        , NULL
  1046.        , 103
  1047.        , 60
  1048.        );
  1049. INSERT INTO employees VALUES
  1050.        ( 108
  1051.        , 'Nancy'
  1052.        , 'Greenberg'
  1053.        , 'NGREENBE'
  1054.        , '515.124.4569'
  1055.        , TO_DATE('17-AUG-1994', 'dd-MON-yyyy')
  1056.        , 'FI_MGR'
  1057.        , 12000
  1058.        , NULL
  1059.        , 101
  1060.        , 100
  1061.        );
  1062. INSERT INTO employees VALUES
  1063.        ( 109
  1064.        , 'Daniel'
  1065.        , 'Faviet'
  1066.        , 'DFAVIET'
  1067.        , '515.124.4169'
  1068.        , TO_DATE('16-AUG-1994', 'dd-MON-yyyy')
  1069.        , 'FI_ACCOUNT'
  1070.        , 9000
  1071.        , NULL
  1072.        , 108
  1073.        , 100
  1074.        );
  1075. INSERT INTO employees VALUES
  1076.        ( 110
  1077.        , 'John'
  1078.        , 'Chen'
  1079.        , 'JCHEN'
  1080.        , '515.124.4269'
  1081.        , TO_DATE('28-SEP-1997', 'dd-MON-yyyy')
  1082.        , 'FI_ACCOUNT'
  1083.        , 8200
  1084.        , NULL
  1085.        , 108
  1086.        , 100
  1087.        );
  1088. INSERT INTO employees VALUES
  1089.        ( 111
  1090.        , 'Ismael'
  1091.        , 'Sciarra'
  1092.        , 'ISCIARRA'
  1093.        , '515.124.4369'
  1094.        , TO_DATE('30-SEP-1997', 'dd-MON-yyyy')
  1095.        , 'FI_ACCOUNT'
  1096.        , 7700
  1097.        , NULL
  1098.        , 108
  1099.        , 100
  1100.        );
  1101. INSERT INTO employees VALUES
  1102.        ( 112
  1103.        , 'Jose Manuel'
  1104.        , 'Urman'
  1105.        , 'JMURMAN'
  1106.        , '515.124.4469'
  1107.        , TO_DATE('07-MAR-1998', 'dd-MON-yyyy')
  1108.        , 'FI_ACCOUNT'
  1109.        , 7800
  1110.        , NULL
  1111.        , 108
  1112.        , 100
  1113.        );
  1114. INSERT INTO employees VALUES
  1115.        ( 113
  1116.        , 'Luis'
  1117.        , 'Popp'
  1118.        , 'LPOPP'
  1119.        , '515.124.4567'
  1120.        , TO_DATE('07-DEC-1999', 'dd-MON-yyyy')
  1121.        , 'FI_ACCOUNT'
  1122.        , 6900
  1123.        , NULL
  1124.        , 108
  1125.        , 100
  1126.        );
  1127. INSERT INTO employees VALUES
  1128.        ( 114
  1129.        , 'Den'
  1130.        , 'Raphaely'
  1131.        , 'DRAPHEAL'
  1132.        , '515.127.4561'
  1133.        , TO_DATE('07-DEC-1994', 'dd-MON-yyyy')
  1134.        , 'PU_MAN'
  1135.        , 11000
  1136.        , NULL
  1137.        , 100
  1138.        , 30
  1139.        );
  1140. INSERT INTO employees VALUES
  1141.        ( 115
  1142.        , 'Alexander'
  1143.        , 'Khoo'
  1144.        , 'AKHOO'
  1145.        , '515.127.4562'
  1146.        , TO_DATE('18-MAY-1995', 'dd-MON-yyyy')
  1147.        , 'PU_CLERK'
  1148.        , 3100
  1149.        , NULL
  1150.        , 114
  1151.        , 30
  1152.        );
  1153. INSERT INTO employees VALUES
  1154.        ( 116
  1155.        , 'Shelli'
  1156.        , 'Baida'
  1157.        , 'SBAIDA'
  1158.        , '515.127.4563'
  1159.        , TO_DATE('24-DEC-1997', 'dd-MON-yyyy')
  1160.        , 'PU_CLERK'
  1161.        , 2900
  1162.        , NULL
  1163.        , 114
  1164.        , 30
  1165.        );
  1166. INSERT INTO employees VALUES
  1167.        ( 117
  1168.        , 'Sigal'
  1169.        , 'Tobias'
  1170.        , 'STOBIAS'
  1171.        , '515.127.4564'
  1172.        , TO_DATE('24-JUL-1997', 'dd-MON-yyyy')
  1173.        , 'PU_CLERK'
  1174.        , 2800
  1175.        , NULL
  1176.        , 114
  1177.        , 30
  1178.        );
  1179. INSERT INTO employees VALUES
  1180.        ( 118
  1181.        , 'Guy'
  1182.        , 'Himuro'
  1183.        , 'GHIMURO'
  1184.        , '515.127.4565'
  1185.        , TO_DATE('15-NOV-1998', 'dd-MON-yyyy')
  1186.        , 'PU_CLERK'
  1187.        , 2600
  1188.        , NULL
  1189.        , 114
  1190.        , 30
  1191.        );
  1192. INSERT INTO employees VALUES
  1193.        ( 119
  1194.        , 'Karen'
  1195.        , 'Colmenares'
  1196.        , 'KCOLMENA'
  1197.        , '515.127.4566'
  1198.        , TO_DATE('10-AUG-1999', 'dd-MON-yyyy')
  1199.        , 'PU_CLERK'
  1200.        , 2500
  1201.        , NULL
  1202.        , 114
  1203.        , 30
  1204.        );
  1205. INSERT INTO employees VALUES
  1206.        ( 120
  1207.        , 'Matthew'
  1208.        , 'Weiss'
  1209.        , 'MWEISS'
  1210.        , '650.123.1234'
  1211.        , TO_DATE('18-JUL-1996', 'dd-MON-yyyy')
  1212.        , 'ST_MAN'
  1213.        , 8000
  1214.        , NULL
  1215.        , 100
  1216.        , 50
  1217.        );
  1218. INSERT INTO employees VALUES
  1219.        ( 121
  1220.        , 'Adam'
  1221.        , 'Fripp'
  1222.        , 'AFRIPP'
  1223.        , '650.123.2234'
  1224.        , TO_DATE('10-APR-1997', 'dd-MON-yyyy')
  1225.        , 'ST_MAN'
  1226.        , 8200
  1227.        , NULL
  1228.        , 100
  1229.        , 50
  1230.        );
  1231. INSERT INTO employees VALUES
  1232.        ( 122
  1233.        , 'Payam'
  1234.        , 'Kaufling'
  1235.        , 'PKAUFLIN'
  1236.        , '650.123.3234'
  1237.        , TO_DATE('01-MAY-1995', 'dd-MON-yyyy')
  1238.        , 'ST_MAN'
  1239.        , 7900
  1240.        , NULL
  1241.        , 100
  1242.        , 50
  1243.        );
  1244. INSERT INTO employees VALUES
  1245.        ( 123
  1246.        , 'Shanta'
  1247.        , 'Vollman'
  1248.        , 'SVOLLMAN'
  1249.        , '650.123.4234'
  1250.        , TO_DATE('10-OCT-1997', 'dd-MON-yyyy')
  1251.        , 'ST_MAN'
  1252.        , 6500
  1253.        , NULL
  1254.        , 100
  1255.        , 50
  1256.        );
  1257. INSERT INTO employees VALUES
  1258.        ( 124
  1259.        , 'Kevin'
  1260.        , 'Mourgos'
  1261.        , 'KMOURGOS'
  1262.        , '650.123.5234'
  1263.        , TO_DATE('16-NOV-1999', 'dd-MON-yyyy')
  1264.        , 'ST_MAN'
  1265.        , 5800
  1266.        , NULL
  1267.        , 100
  1268.        , 50
  1269.        );
  1270. INSERT INTO employees VALUES
  1271.        ( 125
  1272.        , 'Julia'
  1273.        , 'Nayer'
  1274.        , 'JNAYER'
  1275.        , '650.124.1214'
  1276.        , TO_DATE('16-JUL-1997', 'dd-MON-yyyy')
  1277.        , 'ST_CLERK'
  1278.        , 3200
  1279.        , NULL
  1280.        , 120
  1281.        , 50
  1282.        );
  1283. INSERT INTO employees VALUES
  1284.        ( 126
  1285.        , 'Irene'
  1286.        , 'Mikkilineni'
  1287.        , 'IMIKKILI'
  1288.        , '650.124.1224'
  1289.        , TO_DATE('28-SEP-1998', 'dd-MON-yyyy')
  1290.        , 'ST_CLERK'
  1291.        , 2700
  1292.        , NULL
  1293.        , 120
  1294.        , 50
  1295.        );
  1296. INSERT INTO employees VALUES
  1297.        ( 127
  1298.        , 'James'
  1299.        , 'Landry'
  1300.        , 'JLANDRY'
  1301.        , '650.124.1334'
  1302.        , TO_DATE('14-JAN-1999', 'dd-MON-yyyy')
  1303.        , 'ST_CLERK'
  1304.        , 2400
  1305.        , NULL
  1306.        , 120
  1307.        , 50
  1308.        );
  1309. INSERT INTO employees VALUES
  1310.        ( 128
  1311.        , 'Steven'
  1312.        , 'Markle'
  1313.        , 'SMARKLE'
  1314.        , '650.124.1434'
  1315.        , TO_DATE('08-MAR-2000', 'dd-MON-yyyy')
  1316.        , 'ST_CLERK'
  1317.        , 2200
  1318.        , NULL
  1319.        , 120
  1320.        , 50
  1321.        );
  1322. INSERT INTO employees VALUES
  1323.        ( 129
  1324.        , 'Laura'
  1325.        , 'Bissot'
  1326.        , 'LBISSOT'
  1327.        , '650.124.5234'
  1328.        , TO_DATE('20-AUG-1997', 'dd-MON-yyyy')
  1329.        , 'ST_CLERK'
  1330.        , 3300
  1331.        , NULL
  1332.        , 121
  1333.        , 50
  1334.        );
  1335. INSERT INTO employees VALUES
  1336.        ( 130
  1337.        , 'Mozhe'
  1338.        , 'Atkinson'
  1339.        , 'MATKINSO'
  1340.        , '650.124.6234'
  1341.        , TO_DATE('30-OCT-1997', 'dd-MON-yyyy')
  1342.        , 'ST_CLERK'
  1343.        , 2800
  1344.        , NULL
  1345.        , 121
  1346.        , 50
  1347.        );
  1348. INSERT INTO employees VALUES
  1349.        ( 131
  1350.        , 'James'
  1351.        , 'Marlow'
  1352.        , 'JAMRLOW'
  1353.        , '650.124.7234'
  1354.        , TO_DATE('16-FEB-1997', 'dd-MON-yyyy')
  1355.        , 'ST_CLERK'
  1356.        , 2500
  1357.        , NULL
  1358.        , 121
  1359.        , 50
  1360.        );
  1361. INSERT INTO employees VALUES
  1362.        ( 132
  1363.        , 'TJ'
  1364.        , 'Olson'
  1365.        , 'TJOLSON'
  1366.        , '650.124.8234'
  1367.        , TO_DATE('10-APR-1999', 'dd-MON-yyyy')
  1368.        , 'ST_CLERK'
  1369.        , 2100
  1370.        , NULL
  1371.        , 121
  1372.        , 50
  1373.        );
  1374. INSERT INTO employees VALUES
  1375.        ( 133
  1376.        , 'Jason'
  1377.        , 'Mallin'
  1378.        , 'JMALLIN'
  1379.        , '650.127.1934'
  1380.        , TO_DATE('14-JUN-1996', 'dd-MON-yyyy')
  1381.        , 'ST_CLERK'
  1382.        , 3300
  1383.        , NULL
  1384.        , 122
  1385.        , 50
  1386.        );
  1387. INSERT INTO employees VALUES
  1388.        ( 134
  1389.        , 'Michael'
  1390.        , 'Rogers'
  1391.        , 'MROGERS'
  1392.        , '650.127.1834'
  1393.        , TO_DATE('26-AUG-1998', 'dd-MON-yyyy')
  1394.        , 'ST_CLERK'
  1395.        , 2900
  1396.        , NULL
  1397.        , 122
  1398.        , 50
  1399.        );
  1400. INSERT INTO employees VALUES
  1401.        ( 135
  1402.        , 'Ki'
  1403.        , 'Gee'
  1404.        , 'KGEE'
  1405.        , '650.127.1734'
  1406.        , TO_DATE('12-DEC-1999', 'dd-MON-yyyy')
  1407.        , 'ST_CLERK'
  1408.        , 2400
  1409.        , NULL
  1410.        , 122
  1411.        , 50
  1412.        );
  1413. INSERT INTO employees VALUES
  1414.        ( 136
  1415.        , 'Hazel'
  1416.        , 'Philtanker'
  1417.        , 'HPHILTAN'
  1418.        , '650.127.1634'
  1419.        , TO_DATE('06-FEB-2000', 'dd-MON-yyyy')
  1420.        , 'ST_CLERK'
  1421.        , 2200
  1422.        , NULL
  1423.        , 122
  1424.        , 50
  1425.        );
  1426. INSERT INTO employees VALUES
  1427.        ( 137
  1428.        , 'Renske'
  1429.        , 'Ladwig'
  1430.        , 'RLADWIG'
  1431.        , '650.121.1234'
  1432.        , TO_DATE('14-JUL-1995', 'dd-MON-yyyy')
  1433.        , 'ST_CLERK'
  1434.        , 3600
  1435.        , NULL
  1436.        , 123
  1437.        , 50
  1438.        );
  1439. INSERT INTO employees VALUES
  1440.        ( 138
  1441.        , 'Stephen'
  1442.        , 'Stiles'
  1443.        , 'SSTILES'
  1444.        , '650.121.2034'
  1445.        , TO_DATE('26-OCT-1997', 'dd-MON-yyyy')
  1446.        , 'ST_CLERK'
  1447.        , 3200
  1448.        , NULL
  1449.        , 123
  1450.        , 50
  1451.        );
  1452. INSERT INTO employees VALUES
  1453.        ( 139
  1454.        , 'John'
  1455.        , 'Seo'
  1456.        , 'JSEO'
  1457.        , '650.121.2019'
  1458.        , TO_DATE('12-FEB-1998', 'dd-MON-yyyy')
  1459.        , 'ST_CLERK'
  1460.        , 2700
  1461.        , NULL
  1462.        , 123
  1463.        , 50
  1464.        );
  1465. INSERT INTO employees VALUES
  1466.        ( 140
  1467.        , 'Joshua'
  1468.        , 'Patel'
  1469.        , 'JPATEL'
  1470.        , '650.121.1834'
  1471.        , TO_DATE('06-APR-1998', 'dd-MON-yyyy')
  1472.        , 'ST_CLERK'
  1473.        , 2500
  1474.        , NULL
  1475.        , 123
  1476.        , 50
  1477.        );
  1478. INSERT INTO employees VALUES
  1479.        ( 141
  1480.        , 'Trenna'
  1481.        , 'Rajs'
  1482.        , 'TRAJS'
  1483.        , '650.121.8009'
  1484.        , TO_DATE('17-OCT-1995', 'dd-MON-yyyy')
  1485.        , 'ST_CLERK'
  1486.        , 3500
  1487.        , NULL
  1488.        , 124
  1489.        , 50
  1490.        );
  1491. INSERT INTO employees VALUES
  1492.        ( 142
  1493.        , 'Curtis'
  1494.        , 'Davies'
  1495.        , 'CDAVIES'
  1496.        , '650.121.2994'
  1497.        , TO_DATE('29-JAN-1997', 'dd-MON-yyyy')
  1498.        , 'ST_CLERK'
  1499.        , 3100
  1500.        , NULL
  1501.        , 124
  1502.        , 50
  1503.        );
  1504. INSERT INTO employees VALUES
  1505.        ( 143
  1506.        , 'Randall'
  1507.        , 'Matos'
  1508.        , 'RMATOS'
  1509.        , '650.121.2874'
  1510.        , TO_DATE('15-MAR-1998', 'dd-MON-yyyy')
  1511.        , 'ST_CLERK'
  1512.        , 2600
  1513.        , NULL
  1514.        , 124
  1515.        , 50
  1516.        );
  1517. INSERT INTO employees VALUES
  1518.        ( 144
  1519.        , 'Peter'
  1520.        , 'Vargas'
  1521.        , 'PVARGAS'
  1522.        , '650.121.2004'
  1523.        , TO_DATE('09-JUL-1998', 'dd-MON-yyyy')
  1524.        , 'ST_CLERK'
  1525.        , 2500
  1526.        , NULL
  1527.        , 124
  1528.        , 50
  1529.        );
  1530. INSERT INTO employees VALUES
  1531.        ( 145
  1532.        , 'John'
  1533.        , 'Russell'
  1534.        , 'JRUSSEL'
  1535.        , '011.44.1344.429268'
  1536.        , TO_DATE('01-OCT-1996', 'dd-MON-yyyy')
  1537.        , 'SA_MAN'
  1538.        , 14000
  1539.        , .4
  1540.        , 100
  1541.        , 80
  1542.        );
  1543. INSERT INTO employees VALUES
  1544.        ( 146
  1545.        , 'Karen'
  1546.        , 'Partners'
  1547.        , 'KPARTNER'
  1548.        , '011.44.1344.467268'
  1549.        , TO_DATE('05-JAN-1997', 'dd-MON-yyyy')
  1550.        , 'SA_MAN'
  1551.        , 13500
  1552.        , .3
  1553.        , 100
  1554.        , 80
  1555.        );
  1556. INSERT INTO employees VALUES
  1557.        ( 147
  1558.        , 'Alberto'
  1559.        , 'Errazuriz'
  1560.        , 'AERRAZUR'
  1561.        , '011.44.1344.429278'
  1562.        , TO_DATE('10-MAR-1997', 'dd-MON-yyyy')
  1563.        , 'SA_MAN'
  1564.        , 12000
  1565.        , .3
  1566.        , 100
  1567.        , 80
  1568.        );
  1569. INSERT INTO employees VALUES
  1570.        ( 148
  1571.        , 'Gerald'
  1572.        , 'Cambrault'
  1573.        , 'GCAMBRAU'
  1574.        , '011.44.1344.619268'
  1575.        , TO_DATE('15-OCT-1999', 'dd-MON-yyyy')
  1576.        , 'SA_MAN'
  1577.        , 11000
  1578.        , .3
  1579.        , 100
  1580.        , 80
  1581.        );
  1582. INSERT INTO employees VALUES
  1583.        ( 149
  1584.        , 'Eleni'
  1585.        , 'Zlotkey'
  1586.        , 'EZLOTKEY'
  1587.        , '011.44.1344.429018'
  1588.        , TO_DATE('29-JAN-2000', 'dd-MON-yyyy')
  1589.        , 'SA_MAN'
  1590.        , 10500
  1591.        , .2
  1592.        , 100
  1593.        , 80
  1594.        );
  1595. INSERT INTO employees VALUES
  1596.        ( 150
  1597.        , 'Peter'
  1598.        , 'Tucker'
  1599.        , 'PTUCKER'
  1600.        , '011.44.1344.129268'
  1601.        , TO_DATE('30-JAN-1997', 'dd-MON-yyyy')
  1602.        , 'SA_REP'
  1603.        , 10000
  1604.        , .3
  1605.        , 145
  1606.        , 80
  1607.        );
  1608. INSERT INTO employees VALUES
  1609.        ( 151
  1610.        , 'David'
  1611.        , 'Bernstein'
  1612.        , 'DBERNSTE'
  1613.        , '011.44.1344.345268'
  1614.        , TO_DATE('24-MAR-1997', 'dd-MON-yyyy')
  1615.        , 'SA_REP'
  1616.        , 9500
  1617.        , .25
  1618.        , 145
  1619.        , 80
  1620.        );
  1621. INSERT INTO employees VALUES
  1622.        ( 152
  1623.        , 'Peter'
  1624.        , 'Hall'
  1625.        , 'PHALL'
  1626.        , '011.44.1344.478968'
  1627.        , TO_DATE('20-AUG-1997', 'dd-MON-yyyy')
  1628.        , 'SA_REP'
  1629.        , 9000
  1630.        , .25
  1631.        , 145
  1632.        , 80
  1633.        );
  1634. INSERT INTO employees VALUES
  1635.        ( 153
  1636.        , 'Christopher'
  1637.        , 'Olsen'
  1638.        , 'COLSEN'
  1639.        , '011.44.1344.498718'
  1640.        , TO_DATE('30-MAR-1998', 'dd-MON-yyyy')
  1641.        , 'SA_REP'
  1642.        , 8000
  1643.        , .2
  1644.        , 145
  1645.        , 80
  1646.        );
  1647. INSERT INTO employees VALUES
  1648.        ( 154
  1649.        , 'Nanette'
  1650.        , 'Cambrault'
  1651.        , 'NCAMBRAU'
  1652.        , '011.44.1344.987668'
  1653.        , TO_DATE('09-DEC-1998', 'dd-MON-yyyy')
  1654.        , 'SA_REP'
  1655.        , 7500
  1656.        , .2
  1657.        , 145
  1658.        , 80
  1659.        );
  1660. INSERT INTO employees VALUES
  1661.        ( 155
  1662.        , 'Oliver'
  1663.        , 'Tuvault'
  1664.        , 'OTUVAULT'
  1665.        , '011.44.1344.486508'
  1666.        , TO_DATE('23-NOV-1999', 'dd-MON-yyyy')
  1667.        , 'SA_REP'
  1668.        , 7000
  1669.        , .15
  1670.        , 145
  1671.        , 80
  1672.        );
  1673. INSERT INTO employees VALUES
  1674.        ( 156
  1675.        , 'Janette'
  1676.        , 'King'
  1677.        , 'JKING'
  1678.        , '011.44.1345.429268'
  1679.        , TO_DATE('30-JAN-1996', 'dd-MON-yyyy')
  1680.        , 'SA_REP'
  1681.        , 10000
  1682.        , .35
  1683.        , 146
  1684.        , 80
  1685.        );
  1686. INSERT INTO employees VALUES
  1687.        ( 157
  1688.        , 'Patrick'
  1689.        , 'Sully'
  1690.        , 'PSULLY'
  1691.        , '011.44.1345.929268'
  1692.        , TO_DATE('04-MAR-1996', 'dd-MON-yyyy')
  1693.        , 'SA_REP'
  1694.        , 9500
  1695.        , .35
  1696.        , 146
  1697.        , 80
  1698.        );
  1699. INSERT INTO employees VALUES
  1700.        ( 158
  1701.        , 'Allan'
  1702.        , 'McEwen'
  1703.        , 'AMCEWEN'
  1704.        , '011.44.1345.829268'
  1705.        , TO_DATE('01-AUG-1996', 'dd-MON-yyyy')
  1706.        , 'SA_REP'
  1707.        , 9000
  1708.        , .35
  1709.        , 146
  1710.        , 80
  1711.        );
  1712. INSERT INTO employees VALUES
  1713.        ( 159
  1714.        , 'Lindsey'
  1715.        , 'Smith'
  1716.        , 'LSMITH'
  1717.        , '011.44.1345.729268'
  1718.        , TO_DATE('10-MAR-1997', 'dd-MON-yyyy')
  1719.        , 'SA_REP'
  1720.        , 8000
  1721.        , .3
  1722.        , 146
  1723.        , 80
  1724.        );
  1725. INSERT INTO employees VALUES
  1726.        ( 160
  1727.        , 'Louise'
  1728.        , 'Doran'
  1729.        , 'LDORAN'
  1730.        , '011.44.1345.629268'
  1731.        , TO_DATE('15-DEC-1997', 'dd-MON-yyyy')
  1732.        , 'SA_REP'
  1733.        , 7500
  1734.        , .3
  1735.        , 146
  1736.        , 80
  1737.        );
  1738. INSERT INTO employees VALUES
  1739.        ( 161
  1740.        , 'Sarath'
  1741.        , 'Sewall'
  1742.        , 'SSEWALL'
  1743.        , '011.44.1345.529268'
  1744.        , TO_DATE('03-NOV-1998', 'dd-MON-yyyy')
  1745.        , 'SA_REP'
  1746.        , 7000
  1747.        , .25
  1748.        , 146
  1749.        , 80
  1750.        );
  1751. INSERT INTO employees VALUES
  1752.        ( 162
  1753.        , 'Clara'
  1754.        , 'Vishney'
  1755.        , 'CVISHNEY'
  1756.        , '011.44.1346.129268'
  1757.        , TO_DATE('11-NOV-1997', 'dd-MON-yyyy')
  1758.        , 'SA_REP'
  1759.        , 10500
  1760.        , .25
  1761.        , 147
  1762.        , 80
  1763.        );
  1764. INSERT INTO employees VALUES
  1765.        ( 163
  1766.        , 'Danielle'
  1767.        , 'Greene'
  1768.        , 'DGREENE'
  1769.        , '011.44.1346.229268'
  1770.        , TO_DATE('19-MAR-1999', 'dd-MON-yyyy')
  1771.        , 'SA_REP'
  1772.        , 9500
  1773.        , .15
  1774.        , 147
  1775.        , 80
  1776.        );
  1777. INSERT INTO employees VALUES
  1778.        ( 164
  1779.        , 'Mattea'
  1780.        , 'Marvins'
  1781.        , 'MMARVINS'
  1782.        , '011.44.1346.329268'
  1783.        , TO_DATE('24-JAN-2000', 'dd-MON-yyyy')
  1784.        , 'SA_REP'
  1785.        , 7200
  1786.        , .10
  1787.        , 147
  1788.        , 80
  1789.        );
  1790. INSERT INTO employees VALUES
  1791.        ( 165
  1792.        , 'David'
  1793.        , 'Lee'
  1794.        , 'DLEE'
  1795.        , '011.44.1346.529268'
  1796.        , TO_DATE('23-FEB-2000', 'dd-MON-yyyy')
  1797.        , 'SA_REP'
  1798.        , 6800
  1799.        , .1
  1800.        , 147
  1801.        , 80
  1802.        );
  1803. INSERT INTO employees VALUES
  1804.        ( 166
  1805.        , 'Sundar'
  1806.        , 'Ande'
  1807.        , 'SANDE'
  1808.        , '011.44.1346.629268'
  1809.        , TO_DATE('24-MAR-2000', 'dd-MON-yyyy')
  1810.        , 'SA_REP'
  1811.        , 6400
  1812.        , .10
  1813.        , 147
  1814.        , 80
  1815.        );
  1816. INSERT INTO employees VALUES
  1817.        ( 167
  1818.        , 'Amit'
  1819.        , 'Banda'
  1820.        , 'ABANDA'
  1821.        , '011.44.1346.729268'
  1822.        , TO_DATE('21-APR-2000', 'dd-MON-yyyy')
  1823.        , 'SA_REP'
  1824.        , 6200
  1825.        , .10
  1826.        , 147
  1827.        , 80
  1828.        );
  1829. INSERT INTO employees VALUES
  1830.        ( 168
  1831.        , 'Lisa'
  1832.        , 'Ozer'
  1833.        , 'LOZER'
  1834.        , '011.44.1343.929268'
  1835.        , TO_DATE('11-MAR-1997', 'dd-MON-yyyy')
  1836.        , 'SA_REP'
  1837.        , 11500
  1838.        , .25
  1839.        , 148
  1840.        , 80
  1841.        );
  1842. INSERT INTO employees VALUES
  1843.        ( 169  
  1844.        , 'Harrison'
  1845.        , 'Bloom'
  1846.        , 'HBLOOM'
  1847.        , '011.44.1343.829268'
  1848.        , TO_DATE('23-MAR-1998', 'dd-MON-yyyy')
  1849.        , 'SA_REP'
  1850.        , 10000
  1851.        , .20
  1852.        , 148
  1853.        , 80
  1854.        );
  1855. INSERT INTO employees VALUES
  1856.        ( 170
  1857.        , 'Tayler'
  1858.        , 'Fox'
  1859.        , 'TFOX'
  1860.        , '011.44.1343.729268'
  1861.        , TO_DATE('24-JAN-1998', 'dd-MON-yyyy')
  1862.        , 'SA_REP'
  1863.        , 9600
  1864.        , .20
  1865.        , 148
  1866.        , 80
  1867.        );
  1868. INSERT INTO employees VALUES
  1869.        ( 171
  1870.        , 'William'
  1871.        , 'Smith'
  1872.        , 'WSMITH'
  1873.        , '011.44.1343.629268'
  1874.        , TO_DATE('23-FEB-1999', 'dd-MON-yyyy')
  1875.        , 'SA_REP'
  1876.        , 7400
  1877.        , .15
  1878.        , 148
  1879.        , 80
  1880.        );
  1881. INSERT INTO employees VALUES
  1882.        ( 172
  1883.        , 'Elizabeth'
  1884.        , 'Bates'
  1885.        , 'EBATES'
  1886.        , '011.44.1343.529268'
  1887.        , TO_DATE('24-MAR-1999', 'dd-MON-yyyy')
  1888.        , 'SA_REP'
  1889.        , 7300
  1890.        , .15
  1891.        , 148
  1892.        , 80
  1893.        );
  1894. INSERT INTO employees VALUES
  1895.        ( 173
  1896.        , 'Sundita'
  1897.        , 'Kumar'
  1898.        , 'SKUMAR'
  1899.        , '011.44.1343.329268'
  1900.        , TO_DATE('21-APR-2000', 'dd-MON-yyyy')
  1901.        , 'SA_REP'
  1902.        , 6100
  1903.        , .10
  1904.        , 148
  1905.        , 80
  1906.        );
  1907. INSERT INTO employees VALUES
  1908.        ( 174
  1909.        , 'Ellen'
  1910.        , 'Abel'
  1911.        , 'EABEL'
  1912.        , '011.44.1644.429267'
  1913.        , TO_DATE('11-MAY-1996', 'dd-MON-yyyy')
  1914.        , 'SA_REP'
  1915.        , 11000
  1916.        , .30
  1917.        , 149
  1918.        , 80
  1919.        );
  1920. INSERT INTO employees VALUES
  1921.        ( 175
  1922.        , 'Alyssa'
  1923.        , 'Hutton'
  1924.        , 'AHUTTON'
  1925.        , '011.44.1644.429266'
  1926.        , TO_DATE('19-MAR-1997', 'dd-MON-yyyy')
  1927.        , 'SA_REP'
  1928.        , 8800
  1929.        , .25
  1930.        , 149
  1931.        , 80
  1932.        );
  1933. INSERT INTO employees VALUES
  1934.        ( 176
  1935.        , 'Jonathon'
  1936.        , 'Taylor'
  1937.        , 'JTAYLOR'
  1938.        , '011.44.1644.429265'
  1939.        , TO_DATE('24-MAR-1998', 'dd-MON-yyyy')
  1940.        , 'SA_REP'
  1941.        , 8600
  1942.        , .20
  1943.        , 149
  1944.        , 80
  1945.        );
  1946. INSERT INTO employees VALUES
  1947.        ( 177
  1948.        , 'Jack'
  1949.        , 'Livingston'
  1950.        , 'JLIVINGS'
  1951.        , '011.44.1644.429264'
  1952.        , TO_DATE('23-APR-1998', 'dd-MON-yyyy')
  1953.        , 'SA_REP'
  1954.        , 8400
  1955.        , .20
  1956.        , 149
  1957.        , 80
  1958.        );
  1959. INSERT INTO employees VALUES
  1960.        ( 178
  1961.        , 'Kimberely'
  1962.        , 'GRANT'
  1963.        , 'KGRANT'
  1964.        , '011.44.1644.429263'
  1965.        , TO_DATE('24-MAY-1999', 'dd-MON-yyyy')
  1966.        , 'SA_REP'
  1967.        , 7000
  1968.        , .15
  1969.        , 149
  1970.        , NULL
  1971.        );
  1972. INSERT INTO employees VALUES
  1973.        ( 179
  1974.        , 'Charles'
  1975.        , 'Johnson'
  1976.        , 'CJOHNSON'
  1977.        , '011.44.1644.429262'
  1978.        , TO_DATE('04-JAN-2000', 'dd-MON-yyyy')
  1979.        , 'SA_REP'
  1980.        , 6200
  1981.        , .10
  1982.        , 149
  1983.        , 80
  1984.        );
  1985. INSERT INTO employees VALUES
  1986.        ( 180
  1987.        , 'Winston'
  1988.        , 'Taylor'
  1989.        , 'WTAYLOR'
  1990.        , '650.507.9876'
  1991.        , TO_DATE('24-JAN-1998', 'dd-MON-yyyy')
  1992.        , 'SH_CLERK'
  1993.        , 3200
  1994.        , NULL
  1995.        , 120
  1996.        , 50
  1997.        );
  1998. INSERT INTO employees VALUES
  1999.        ( 181
  2000.        , 'Jean'
  2001.        , 'Fleaur'
  2002.        , 'JFLEAUR'
  2003.        , '650.507.9877'
  2004.        , TO_DATE('23-FEB-1998', 'dd-MON-yyyy')
  2005.        , 'SH_CLERK'
  2006.        , 3100
  2007.        , NULL
  2008.        , 120
  2009.        , 50
  2010.        );
  2011. INSERT INTO employees VALUES
  2012.        ( 182
  2013.        , 'Martha'
  2014.        , 'Sullivan'
  2015.        , 'MSULLIVA'
  2016.        , '650.507.9878'
  2017.        , TO_DATE('21-JUN-1999', 'dd-MON-yyyy')
  2018.        , 'SH_CLERK'
  2019.        , 2500
  2020.        , NULL
  2021.        , 120
  2022.        , 50
  2023.        );
  2024. INSERT INTO employees VALUES
  2025.        ( 183
  2026.        , 'Girard'
  2027.        , 'Geoni'
  2028.        , 'GGEONI'
  2029.        , '650.507.9879'
  2030.        , TO_DATE('03-FEB-2000', 'dd-MON-yyyy')
  2031.        , 'SH_CLERK'
  2032.        , 2800
  2033.        , NULL
  2034.        , 120
  2035.        , 50
  2036.        );
  2037. INSERT INTO employees VALUES
  2038.        ( 184
  2039.        , 'Nandita'
  2040.        , 'Sarchand'
  2041.        , 'NSARCHAN'
  2042.        , '650.509.1876'
  2043.        , TO_DATE('27-JAN-1996', 'dd-MON-yyyy')
  2044.        , 'SH_CLERK'
  2045.        , 4200
  2046.        , NULL
  2047.        , 121
  2048.        , 50
  2049.        );
  2050. INSERT INTO employees VALUES
  2051.        ( 185
  2052.        , 'Alexis'
  2053.        , 'Bull'
  2054.        , 'ABULL'
  2055.        , '650.509.2876'
  2056.        , TO_DATE('20-FEB-1997', 'dd-MON-yyyy')
  2057.        , 'SH_CLERK'
  2058.        , 4100
  2059.        , NULL
  2060.        , 121
  2061.        , 50
  2062.        );
  2063. INSERT INTO employees VALUES
  2064.        ( 186
  2065.        , 'Julia'
  2066.        , 'Dellinger'
  2067.        , 'JDELLING'
  2068.        , '650.509.3876'
  2069.        , TO_DATE('24-JUN-1998', 'dd-MON-yyyy')
  2070.        , 'SH_CLERK'
  2071.        , 3400
  2072.        , NULL
  2073.        , 121
  2074.        , 50
  2075.        );
  2076. INSERT INTO employees VALUES
  2077.        ( 187
  2078.        , 'Anthony'
  2079.        , 'Cabrio'
  2080.        , 'ACABRIO'
  2081.        , '650.509.4876'
  2082.        , TO_DATE('07-FEB-1999', 'dd-MON-yyyy')
  2083.        , 'SH_CLERK'
  2084.        , 3000
  2085.        , NULL
  2086.        , 121
  2087.        , 50
  2088.        );
  2089. INSERT INTO employees VALUES
  2090.        ( 188
  2091.        , 'Kelly'
  2092.        , 'Chung'
  2093.        , 'KCHUNG'
  2094.        , '650.505.1876'
  2095.        , TO_DATE('14-JUN-1997', 'dd-MON-yyyy')
  2096.        , 'SH_CLERK'
  2097.        , 3800
  2098.        , NULL
  2099.        , 122
  2100.        , 50
  2101.        );
  2102. INSERT INTO employees VALUES
  2103.        ( 189
  2104.        , 'Jennifer'
  2105.        , 'Dilly'
  2106.        , 'JDILLY'
  2107.        , '650.505.2876'
  2108.        , TO_DATE('13-AUG-1997', 'dd-MON-yyyy')
  2109.        , 'SH_CLERK'
  2110.        , 3600
  2111.        , NULL
  2112.        , 122
  2113.        , 50
  2114.        );
  2115. INSERT INTO employees VALUES
  2116.        ( 190
  2117.        , 'Timothy'
  2118.        , 'Gates'
  2119.        , 'TGATES'
  2120.        , '650.505.3876'
  2121.        , TO_DATE('11-JUL-1998', 'dd-MON-yyyy')
  2122.        , 'SH_CLERK'
  2123.        , 2900
  2124.        , NULL
  2125.        , 122
  2126.        , 50
  2127.        );
  2128. INSERT INTO employees VALUES
  2129.        ( 191
  2130.        , 'Randall'
  2131.        , 'Perkins'
  2132.        , 'RPERKINS'
  2133.        , '650.505.4876'
  2134.        , TO_DATE('19-DEC-1999', 'dd-MON-yyyy')
  2135.        , 'SH_CLERK'
  2136.        , 2500
  2137.        , NULL
  2138.        , 122
  2139.        , 50
  2140.        );
  2141. INSERT INTO employees VALUES
  2142.        ( 192
  2143.        , 'Sarah'
  2144.        , 'Bell'
  2145.        , 'SBELL'
  2146.        , '650.501.1876'
  2147.        , TO_DATE('04-FEB-1996', 'dd-MON-yyyy')
  2148.        , 'SH_CLERK'
  2149.        , 4000
  2150.        , NULL
  2151.        , 123
  2152.        , 50
  2153.        );
  2154. INSERT INTO employees VALUES
  2155.        ( 193
  2156.        , 'Britney'
  2157.        , 'Everett'
  2158.        , 'BEVERETT'
  2159.        , '650.501.2876'
  2160.        , TO_DATE('03-MAR-1997', 'dd-MON-yyyy')
  2161.        , 'SH_CLERK'
  2162.        , 3900
  2163.        , NULL
  2164.        , 123
  2165.        , 50
  2166.        );
  2167. INSERT INTO employees VALUES
  2168.        ( 194
  2169.        , 'Samuel'
  2170.        , 'McCain'
  2171.        , 'SMCCAIN'
  2172.        , '650.501.3876'
  2173.        , TO_DATE('01-JUL-1998', 'dd-MON-yyyy')
  2174.        , 'SH_CLERK'
  2175.        , 3200
  2176.        , NULL
  2177.        , 123
  2178.        , 50
  2179.        );
  2180. INSERT INTO employees VALUES
  2181.        ( 195
  2182.        , 'Vance'
  2183.        , 'Jones'
  2184.        , 'VJONES'
  2185.        , '650.501.4876'
  2186.        , TO_DATE('17-MAR-1999', 'dd-MON-yyyy')
  2187.        , 'SH_CLERK'
  2188.        , 2800
  2189.        , NULL
  2190.        , 123
  2191.        , 50
  2192.        );
  2193. INSERT INTO employees VALUES
  2194.        ( 196
  2195.        , 'Alana'
  2196.        , 'Walsh'
  2197.        , 'AWALSH'
  2198.        , '650.507.9811'
  2199.        , TO_DATE('24-APR-1998', 'dd-MON-yyyy')
  2200.        , 'SH_CLERK'
  2201.        , 3100
  2202.        , NULL
  2203.        , 124
  2204.        , 50
  2205.        );
  2206. INSERT INTO employees VALUES
  2207.        ( 197
  2208.        , 'Kevin'
  2209.        , 'Feeney'
  2210.        , 'KFEENEY'
  2211.        , '650.507.9822'
  2212.        , TO_DATE('23-MAY-1998', 'dd-MON-yyyy')
  2213.        , 'SH_CLERK'
  2214.        , 3000
  2215.        , NULL
  2216.        , 124
  2217.        , 50
  2218.        );
  2219. INSERT INTO employees VALUES
  2220.        ( 198
  2221.        , 'Donald'
  2222.        , 'OConnell'
  2223.        , 'DOCONNEL'
  2224.        , '650.507.9833'
  2225.        , TO_DATE('21-JUN-1999', 'dd-MON-yyyy')
  2226.        , 'SH_CLERK'
  2227.        , 2600
  2228.        , NULL
  2229.        , 124
  2230.        , 50
  2231.        );
  2232. INSERT INTO employees VALUES
  2233.        ( 199
  2234.        , 'Douglas'
  2235.        , 'GRANT'
  2236.        , 'DGRANT'
  2237.        , '650.507.9844'
  2238.        , TO_DATE('13-JAN-2000', 'dd-MON-yyyy')
  2239.        , 'SH_CLERK'
  2240.        , 2600
  2241.        , NULL
  2242.        , 124
  2243.        , 50
  2244.        );
  2245. INSERT INTO employees VALUES
  2246.        ( 200
  2247.        , 'Jennifer'
  2248.        , 'Whalen'
  2249.        , 'JWHALEN'
  2250.        , '515.123.4444'
  2251.        , TO_DATE('17-SEP-1987', 'dd-MON-yyyy')
  2252.        , 'AD_ASST'
  2253.        , 4400
  2254.        , NULL
  2255.        , 101
  2256.        , 10
  2257.        );
  2258. INSERT INTO employees VALUES
  2259.        ( 201
  2260.        , 'Michael'
  2261.        , 'Hartstein'
  2262.        , 'MHARTSTE'
  2263.        , '515.123.5555'
  2264.        , TO_DATE('17-FEB-1996', 'dd-MON-yyyy')
  2265.        , 'MK_MAN'
  2266.        , 13000
  2267.        , NULL
  2268.        , 100
  2269.        , 20
  2270.        );
  2271. INSERT INTO employees VALUES
  2272.        ( 202
  2273.        , 'Pat'
  2274.        , 'Fay'
  2275.        , 'PFAY'
  2276.        , '603.123.6666'
  2277.        , TO_DATE('17-AUG-1997', 'dd-MON-yyyy')
  2278.        , 'MK_REP'
  2279.        , 6000
  2280.        , NULL
  2281.        , 201
  2282.        , 20
  2283.        );
  2284. INSERT INTO employees VALUES
  2285.        ( 203
  2286.        , 'Susan'
  2287.        , 'Mavris'
  2288.        , 'SMAVRIS'
  2289.        , '515.123.7777'
  2290.        , TO_DATE('07-JUN-1994', 'dd-MON-yyyy')
  2291.        , 'HR_REP'
  2292.        , 6500
  2293.        , NULL
  2294.        , 101
  2295.        , 40
  2296.        );
  2297. INSERT INTO employees VALUES
  2298.        ( 204
  2299.        , 'Hermann'
  2300.        , 'Baer'
  2301.        , 'HBAER'
  2302.        , '515.123.8888'
  2303.        , TO_DATE('07-JUN-1994', 'dd-MON-yyyy')
  2304.        , 'PR_REP'
  2305.        , 10000
  2306.        , NULL
  2307.        , 101
  2308.        , 70
  2309.        );
  2310. INSERT INTO employees VALUES
  2311.        ( 205
  2312.        , 'Shelley'
  2313.        , 'Higgins'
  2314.        , 'SHIGGINS'
  2315.        , '515.123.8080'
  2316.        , TO_DATE('07-JUN-1994', 'dd-MON-yyyy')
  2317.        , 'AC_MGR'
  2318.        , 12000
  2319.        , NULL
  2320.        , 101
  2321.        , 110
  2322.        );
  2323. INSERT INTO employees VALUES
  2324.        ( 206
  2325.        , 'William'
  2326.        , 'Gietz'
  2327.        , 'WGIETZ'
  2328.        , '515.123.8181'
  2329.        , TO_DATE('07-JUN-1994', 'dd-MON-yyyy')
  2330.        , 'AC_ACCOUNT'
  2331.        , 8300
  2332.        , NULL
  2333.        , 205
  2334.        , 110
  2335.        );
  2336. REM ********* insert data into the JOB_HISTORY table
  2337. Prompt ******  Populating JOB_HISTORY table ....
  2338. INSERT INTO job_history
  2339. VALUES (102
  2340.       , TO_DATE('13-JAN-1993', 'dd-MON-yyyy')
  2341.       , TO_DATE('24-JUL-1998', 'dd-MON-yyyy')
  2342.       , 'IT_PROG'
  2343.       , 60);
  2344. INSERT INTO job_history
  2345. VALUES (101
  2346.       , TO_DATE('21-SEP-1989', 'dd-MON-yyyy')
  2347.       , TO_DATE('27-OCT-1993', 'dd-MON-yyyy')
  2348.       , 'AC_ACCOUNT'
  2349.       , 110);
  2350. INSERT INTO job_history
  2351. VALUES (101
  2352.       , TO_DATE('28-OCT-1993', 'dd-MON-yyyy')
  2353.       , TO_DATE('15-MAR-1997', 'dd-MON-yyyy')
  2354.       , 'AC_MGR'
  2355.       , 110);
  2356. INSERT INTO job_history
  2357. VALUES (201
  2358.       , TO_DATE('17-FEB-1996', 'dd-MON-yyyy')
  2359.       , TO_DATE('19-DEC-1999', 'dd-MON-yyyy')
  2360.       , 'MK_REP'
  2361.       , 20);
  2362. INSERT INTO job_history
  2363. VALUES  (114
  2364.        , TO_DATE('24-MAR-1998', 'dd-MON-yyyy')
  2365.        , TO_DATE('31-DEC-1999', 'dd-MON-yyyy')
  2366.        , 'ST_CLERK'
  2367.        , 50
  2368.        );
  2369. INSERT INTO job_history
  2370. VALUES  (122
  2371.        , TO_DATE('01-JAN-1999', 'dd-MON-yyyy')
  2372.        , TO_DATE('31-DEC-1999', 'dd-MON-yyyy')
  2373.        , 'ST_CLERK'
  2374.        , 50
  2375.        );
  2376. INSERT INTO job_history
  2377. VALUES  (200
  2378.        , TO_DATE('17-SEP-1987', 'dd-MON-yyyy')
  2379.        , TO_DATE('17-JUN-1993', 'dd-MON-yyyy')
  2380.        , 'AD_ASST'
  2381.        , 90
  2382.        );
  2383. INSERT INTO job_history
  2384. VALUES  (176
  2385.        , TO_DATE('24-MAR-1998', 'dd-MON-yyyy')
  2386.        , TO_DATE('31-DEC-1998', 'dd-MON-yyyy')
  2387.        , 'SA_REP'
  2388.        , 80
  2389.        );
  2390. INSERT INTO job_history
  2391. VALUES  (176
  2392.        , TO_DATE('01-JAN-1999', 'dd-MON-yyyy')
  2393.        , TO_DATE('31-DEC-1999', 'dd-MON-yyyy')
  2394.        , 'SA_MAN'
  2395.        , 80
  2396.        );
  2397. INSERT INTO job_history
  2398. VALUES  (200
  2399.        , TO_DATE('01-JUL-1994', 'dd-MON-yyyy')
  2400.        , TO_DATE('31-DEC-1998', 'dd-MON-yyyy')
  2401.        , 'AC_ACCOUNT'
  2402.        , 90
  2403.        );
  2404. REM enable integrity constraint to DEPARTMENTS
  2405. ALTER TABLE departments
  2406.  ENABLE CONSTRAINT dept_mgr_fk;
  2407. COMMIT;
  2408. Rem
  2409. Rem $Header: hr_idx.sql 03-mar-2001.10:05:15 ahunold Exp $
  2410. Rem
  2411. Rem hr_idx.sql
  2412. Rem
  2413. Rem  Copyright (c) Oracle Corporation 2001. All Rights Reserved.
  2414. Rem
  2415. Rem    NAME
  2416. Rem      hr_idx.sql - Create indexes for HR schema
  2417. Rem
  2418. Rem    DESCRIPTION
  2419. Rem
  2420. Rem    NOTES
  2421. Rem
  2422. Rem    CREATED by Nancy Greenberg - 06/01/00
  2423. Rem    MODIFIED   (MM/DD/YY)
  2424. Rem    ahunold     02/20/01 - New header
  2425. Rem    vpatabal    03/02/01 - Removed DROP INDEX statements
  2426. SET FEEDBACK 1
  2427. SET NUMWIDTH 10
  2428. SET LINESIZE 80
  2429. SET TRIMSPOOL ON
  2430. SET TAB OFF
  2431. SET PAGESIZE 100
  2432. SET ECHO OFF
  2433. CREATE INDEX emp_department_ix
  2434.       ON employees (department_id);
  2435. CREATE INDEX emp_job_ix
  2436.       ON employees (job_id);
  2437. CREATE INDEX emp_manager_ix
  2438.       ON employees (manager_id);
  2439. CREATE INDEX emp_name_ix
  2440.       ON employees (last_name, first_name);
  2441. CREATE INDEX dept_location_ix
  2442.       ON departments (location_id);
  2443. CREATE INDEX jhist_job_ix
  2444.       ON job_history (job_id);
  2445. CREATE INDEX jhist_employee_ix
  2446.       ON job_history (employee_id);
  2447. CREATE INDEX jhist_department_ix
  2448.       ON job_history (department_id);
  2449. CREATE INDEX loc_city_ix
  2450.       ON locations (city);
  2451. CREATE INDEX loc_state_province_ix
  2452.       ON locations (state_province);
  2453. CREATE INDEX loc_country_ix
  2454.       ON locations (country_id);
  2455. COMMIT;
  2456. Rem
  2457. Rem $Header: hr_code.sql 03-mar-2001.10:05:12 ahunold Exp $
  2458. Rem
  2459. Rem hr_code.sql
  2460. Rem
  2461. Rem  Copyright (c) Oracle Corporation 2001. All Rights Reserved.
  2462. Rem
  2463. Rem    NAME
  2464. Rem      hr_code.sql - Create procedural objects for HR schema
  2465. Rem
  2466. Rem    DESCRIPTION
  2467. Rem      Create a statement level trigger on EMPLOYEES
  2468. Rem      to allow DML during business hours.
  2469. Rem      Create a row level trigger on the EMPLOYEES table,
  2470. Rem      after UPDATES on the department_id or job_id columns.
  2471. Rem      Create a stored procedure to insert a row into the
  2472. Rem      JOB_HISTORY table.  Have the above row level trigger
  2473. Rem      row level trigger call this stored procedure.
  2474. Rem
  2475. Rem    NOTES
  2476. Rem
  2477. Rem    CREATED by Nancy Greenberg - 06/01/00
  2478. Rem
  2479. Rem    MODIFIED   (MM/DD/YY)
  2480. Rem    ahunold     03/03/01 - HR simplification, REGIONS table
  2481. Rem    ahunold     02/20/01 - Created
  2482. Rem
  2483. SET FEEDBACK 1
  2484. SET NUMWIDTH 10
  2485. SET LINESIZE 80
  2486. SET TRIMSPOOL ON
  2487. SET TAB OFF
  2488. SET PAGESIZE 100
  2489. SET ECHO OFF
  2490. REM
  2491. **************************************************************************
  2492. REM procedure and statement trigger to allow dmls during business hours:
  2493. CREATE OR REPLACE PROCEDURE secure_dml
  2494. IS
  2495. BEGIN
  2496.  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
  2497.        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
  2498. RAISE_APPLICATION_ERROR (-20205,
  2499. 'You may ONLY make changes during normal office hours');
  2500.  END IF;
  2501. END secure_dml;
  2502. /
  2503. CREATE OR REPLACE TRIGGER secure_employees
  2504.  BEFORE INSERT OR UPDATE OR DELETE ON employees
  2505. BEGIN
  2506.  secure_dml;
  2507. END secure_employees;
  2508. /
  2509. REM
  2510. **************************************************************************
  2511. REM procedure to add a row to the JOB_HISTORY table and row trigger
  2512. REM to call the procedure when data is updated in the job_id or
  2513. REM department_id columns in the EMPLOYEES table:
  2514. CREATE OR REPLACE PROCEDURE add_job_history
  2515.  (  p_emp_id          job_history.employee_id%type
  2516.   , p_start_date      job_history.start_date%type
  2517.   , p_end_date        job_history.end_date%type
  2518.   , p_job_id          job_history.job_id%type
  2519.   , p_department_id   job_history.department_id%type
  2520.   )
  2521. IS
  2522. BEGIN
  2523.  INSERT INTO job_history (employee_id, start_date, end_date,
  2524.                           job_id, department_id)
  2525.    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
  2526. END add_job_history;
  2527. /
  2528. CREATE OR REPLACE TRIGGER update_job_history
  2529.  AFTER UPDATE OF job_id, department_id ON employees
  2530.  FOR EACH ROW
  2531. BEGIN
  2532.  add_job_history(:old.employee_id, :old.hire_date, sysdate,
  2533.                  :old.job_id, :old.department_id);
  2534. END;
  2535. /
  2536. COMMIT;
  2537. Rem
  2538. Rem $Header: hr_comnt.sql 03-mar-2001.10:05:12 ahunold Exp $
  2539. Rem
  2540. Rem hr_comnt.sql
  2541. Rem
  2542. Rem  Copyright (c) Oracle Corporation 2001. All Rights Reserved.
  2543. Rem
  2544. Rem    NAME
  2545. Rem      hr_comnt.sql - Create comments for HR schema
  2546. Rem
  2547. Rem    DESCRIPTION
  2548. Rem
  2549. Rem    CREATED by Nancy Greenberg, Nagavalli Pataballa - 06/01/00
  2550. Rem    MODIFIED   (MM/DD/YY)
  2551. Rem    ahunold     02/20/01 - New header
  2552. Rem    vpatabal    03/02/01 - Added comments for Regions table
  2553. Rem                         - Removed references to currency symbol
  2554. Rem                           and currency name columns of countries
  2555. Rem                         - Removed comments to DN column of    
  2556. Rem                           employees and departments.
  2557. Rem     - Removed references to sequences
  2558. SET FEEDBACK 1
  2559. SET NUMWIDTH 10
  2560. SET LINESIZE 80
  2561. SET TRIMSPOOL ON
  2562. SET TAB OFF
  2563. SET PAGESIZE 100
  2564. SET ECHO OFF
  2565. COMMENT ON TABLE regions
  2566. IS 'Regions TABLE that contains region numbers AND names. Contains 4 ROWS;
  2567. REFERENCES WITH the Countries TABLE.' ;
  2568. COMMENT ON COLUMN regions.region_id
  2569. IS 'PRIMARY KEY OF regions TABLE.' ;
  2570. COMMENT ON COLUMN regions.region_name
  2571. IS 'Names OF regions. Locations are IN the countries OF these regions.' ;
  2572. COMMENT ON TABLE locations
  2573. IS 'Locations TABLE that contains specific address OF a specific office,
  2574. warehouse, AND/OR production site OF a company. Does NOT store addresses /
  2575. locations OF customers. Contains 23 ROWS; REFERENCES WITH the
  2576. departments AND countries TABLES. ';
  2577. COMMENT ON COLUMN locations.location_id
  2578. IS 'PRIMARY KEY OF locations TABLE';
  2579. COMMENT ON COLUMN locations.street_address
  2580. IS 'Street address OF an office, warehouse, OR production site OF a
  2581. company.
  2582. Contains building NUMBER AND street name';
  2583. COMMENT ON COLUMN locations.postal_code
  2584. IS 'Postal code OF the location OF an office, warehouse, OR production site
  2585. OF a company. ';
  2586. COMMENT ON COLUMN locations.city
  2587. IS 'A NOT NULL COLUMN that shows city WHERE an office, warehouse, OR
  2588. production site OF a company IS located. ';
  2589. COMMENT ON COLUMN locations.state_province
  2590. IS 'State OR Province WHERE an office, warehouse, OR production site OF a
  2591. company IS located.';
  2592. COMMENT ON COLUMN locations.country_id
  2593. IS 'Country WHERE an office, warehouse, OR production site OF a company IS
  2594. located. FOREIGN KEY TO country_id COLUMN OF the countries TABLE.';
  2595. REM *********************************************
  2596. COMMENT ON TABLE departments
  2597. IS 'Departments TABLE that shows details OF departments WHERE employees
  2598. WORK. Contains 27 ROWS; REFERENCES WITH locations, employees, AND
  2599. job_history TABLES.';
  2600. COMMENT ON COLUMN departments.department_id
  2601. IS 'PRIMARY KEY COLUMN OF departments TABLE.';
  2602. COMMENT ON COLUMN departments.department_name
  2603. IS 'A NOT NULL COLUMN that shows name OF a department. Administration,
  2604. Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
  2605. Relations, Sales, Finance, AND Accounting. ';
  2606. COMMENT ON COLUMN departments.manager_id
  2607. IS 'Manager_id OF a department. FOREIGN KEY TO employee_id COLUMN OF
  2608. employees TABLE. The manager_id COLUMN OF the employee TABLE REFERENCES
  2609. this COLUMN.';
  2610. COMMENT ON COLUMN departments.location_id
  2611. IS 'Location id WHERE a department IS located. FOREIGN KEY TO location_id
  2612. COLUMN OF locations TABLE.';
  2613. REM *********************************************
  2614. COMMENT ON TABLE job_history
  2615. IS 'TABLE that stores job history OF the employees. IF an employee
  2616. changes departments WITHIN the job OR changes jobs WITHIN the department,
  2617. NEW ROWS GET inserted INTO this TABLE WITH OLD job information OF the
  2618. employee. Contains a complex PRIMARY KEY: employee_id+start_date.
  2619. Contains 25 ROWS. REFERENCES WITH jobs, employees, AND departments
  2620. TABLES.';
  2621. COMMENT ON COLUMN job_history.employee_id
  2622. IS 'A NOT NULL COLUMN IN the complex PRIMARY KEY employee_id+start_date.
  2623. FOREIGN KEY TO employee_id COLUMN OF the employee TABLE';
  2624. COMMENT ON COLUMN job_history.start_date
  2625. IS 'A NOT NULL COLUMN IN the complex PRIMARY KEY employee_id+start_date.
  2626. Must be less than the end_date OF the job_history TABLE. (enforced BY
  2627. CONSTRAINT jhist_date_interval)';
  2628. COMMENT ON COLUMN job_history.end_date
  2629. IS 'LAST DAY OF the employee IN this job ROLE. A NOT NULL COLUMN. Must be
  2630. greater than the start_date OF the job_history TABLE.
  2631. (enforced BY CONSTRAINT jhist_date_interval)';
  2632. COMMENT ON COLUMN job_history.job_id
  2633. IS 'Job ROLE IN which the employee worked IN the past; FOREIGN KEY TO
  2634. job_id COLUMN IN the jobs TABLE. A NOT NULL COLUMN.';
  2635. COMMENT ON COLUMN job_history.department_id
  2636. IS 'Department id IN which the employee worked IN the past; FOREIGN KEY TO
  2637. deparment_id COLUMN IN the departments TABLE';
  2638. REM *********************************************
  2639. COMMENT ON TABLE countries
  2640. IS 'country TABLE. Contains 25 ROWS. REFERENCES WITH locations TABLE.';
  2641. COMMENT ON COLUMN countries.country_id
  2642. IS 'PRIMARY KEY OF countries TABLE.';
  2643. COMMENT ON COLUMN countries.country_name
  2644. IS 'Country name';
  2645. COMMENT ON COLUMN countries.region_id
  2646. IS 'Region ID FOR the country. FOREIGN KEY TO region_id COLUMN IN the
  2647. departments TABLE.';
  2648. REM *********************************************
  2649. COMMENT ON TABLE jobs
  2650. IS 'jobs TABLE WITH job titles AND salary ranges. Contains 19 ROWS.
  2651. REFERENCES WITH employees AND job_history TABLE.';
  2652. COMMENT ON COLUMN jobs.job_id
  2653. IS 'PRIMARY KEY OF jobs TABLE.';
  2654. COMMENT ON COLUMN jobs.job_title
  2655. IS 'A NOT NULL COLUMN that shows job title, e.g. AD_VP, FI_ACCOUNTANT';
  2656. COMMENT ON COLUMN jobs.min_salary
  2657. IS 'Minimum salary FOR a job title.';
  2658. COMMENT ON COLUMN jobs.max_salary
  2659. IS 'Maximum salary FOR a job title';
  2660. REM *********************************************
  2661. COMMENT ON TABLE employees
  2662. IS 'employees TABLE. Contains 107 ROWS. REFERENCES WITH departments,
  2663. jobs, job_history TABLES. Contains a SELF reference.';
  2664. COMMENT ON COLUMN employees.employee_id
  2665. IS 'PRIMARY KEY OF employees TABLE.';
  2666. COMMENT ON COLUMN employees.first_name
  2667. IS 'FIRST name OF the employee. A NOT NULL COLUMN.';
  2668. COMMENT ON COLUMN employees.last_name
  2669. IS 'LAST name OF the employee. A NOT NULL COLUMN.';
  2670. COMMENT ON COLUMN employees.email
  2671. IS 'Email id OF the employee';
  2672. COMMENT ON COLUMN employees.phone_number
  2673. IS 'Phone NUMBER OF the employee; includes country code AND area code';
  2674. COMMENT ON COLUMN employees.hire_date
  2675. IS 'DATE WHEN the employee started ON this job. A NOT NULL COLUMN.';
  2676. COMMENT ON COLUMN employees.job_id
  2677. IS 'CURRENT job OF the employee; FOREIGN KEY TO job_id COLUMN OF the
  2678. jobs TABLE. A NOT NULL COLUMN.';
  2679. COMMENT ON COLUMN employees.salary
  2680. IS 'Monthly salary OF the employee. Must be greater
  2681. than zero (enforced BY CONSTRAINT emp_salary_min)';
  2682. COMMENT ON COLUMN employees.commission_pct
  2683. IS 'Commission percentage OF the employee; ONLY employees IN sales
  2684. department elgible FOR commission percentage';
  2685. COMMENT ON COLUMN employees.manager_id
  2686. IS 'Manager id OF the employee; has same DOMAIN AS manager_id IN
  2687. departments TABLE. FOREIGN KEY TO employee_id COLUMN OF employees TABLE.
  2688. (useful FOR reflexive joins AND CONNECT BY query)';
  2689. COMMENT ON COLUMN employees.department_id
  2690. IS 'Department id WHERE employee works; FOREIGN KEY TO department_id
  2691. COLUMN OF the departments TABLE';
  2692. COMMIT;
  2693. REM  Script:   del_data.sql
  2694. REM  Purpose:  To remove rows, constraints, and code from the hr sample
  2695. schema
  2696. REM  Created:  By Nancy Greenberg on 18-MAR-2001
  2697. REM            for the Introduction to Oracle9i:SQL course
  2698. REM  This script is invoked by the hr_main script
  2699. ALTER TABLE departments
  2700. DISABLE CONSTRAINT DEPT_MGR_FK;
  2701. ALTER TABLE job_history
  2702. DISABLE CONSTRAINT JHIST_EMP_FK;
  2703. DROP TRIGGER secure_employees;
  2704. DROP TRIGGER update_job_history;
  2705. DROP PROCEDURE add_job_history;
  2706. DROP PROCEDURE secure_dml;
  2707. DELETE FROM employees
  2708. WHERE manager_id IN (108, 114, 120, 121, 122, 123, 145, 146, 147, 148);
  2709. DELETE FROM employees
  2710. WHERE employee_id IN (114, 120, 121, 122, 123, 145, 146, 147, 148,
  2711.                      196, 197, 198, 199, 105, 106, 108, 175, 177,
  2712.                      179, 203, 204);
  2713. DELETE FROM locations
  2714. WHERE location_id NOT IN
  2715.  (SELECT DISTINCT location_id
  2716.   FROM departments);
  2717. DELETE FROM countries
  2718. WHERE country_id NOT IN
  2719.  (SELECT country_id
  2720.   FROM locations);
  2721. DELETE FROM jobs
  2722. WHERE job_id NOT IN
  2723.  (SELECT job_id
  2724.   FROM employees);
  2725. DELETE FROM departments
  2726. WHERE department_id NOT IN
  2727.  (SELECT DISTINCT department_id
  2728.   FROM employees
  2729.   WHERE department_id IS NOT NULL);
  2730. UPDATE departments
  2731. SET manager_id = 124
  2732. WHERE department_id = 50;
  2733. UPDATE departments
  2734. SET manager_id = 149
  2735. WHERE department_id = 80;
  2736. DELETE FROM locations
  2737. WHERE location_id IN (2700, 2400);
  2738. UPDATE locations
  2739. SET street_address = '460 Bloor St. W.',
  2740.    postal_code = 'ON M5S 1X8'
  2741. WHERE location_id = 1800;
  2742. ALTER TABLE departments
  2743. ENABLE CONSTRAINT DEPT_MGR_FK;
  2744. CREATE TABLE job_grades
  2745. (grade_level VARCHAR2(3),
  2746. lowest_sal  NUMBER,
  2747. highest_sal NUMBER);
  2748. INSERT INTO job_grades
  2749. VALUES ('A', 1000, 2999);
  2750. INSERT INTO job_grades
  2751. VALUES ('B', 3000, 5999);
  2752. INSERT INTO job_grades
  2753. VALUES('C', 6000, 9999);
  2754. INSERT INTO job_grades
  2755. VALUES('D', 10000, 14999);
  2756. INSERT INTO job_grades
  2757. VALUES('E', 15000, 24999);
  2758. INSERT INTO job_grades
  2759. VALUES('F', 25000, 40000);
  2760. INSERT INTO departments VALUES
  2761.        ( 190
  2762.        , 'Contracting'
  2763.        , NULL
  2764.        , 1700
  2765.        );
  2766. COMMIT;