-- poznamka: 
-- Pro urychleni zpracovani davky na Karlaku jsou prikazy pro vkladani jednotlivych radku zakomentovany a nahrazeny vlozenim radkuu, 
-- vybranych z odpovidajici tabulky schematu OE. Predpokladem je, ze schema OE existuje a tabulky v nem existuji.
-- Jestlize to neplati zakomentujte prikazy "Insert ... Select"  a odkomentujte skupiny prikazuu "Insert Values".
--
set autocommit on
alter session set NLS_NUMERIC_CHARACTERS='.,';
alter session set nls_date_language='AMERICAN';
alter session set constraints=deferred;
set echo on
REM START REGIONS
EXEC execute immediate 'DROP TABLE  REGIONS  cascade constraints PURGE'

  CREATE TABLE  REGIONS  
   (  REGION_ID  NUMBER CONSTRAINT  REGION_ID_NN  NOT NULL  , 
  REGION_NAME  VARCHAR2(25)
   ) ;
  ALTER TABLE  REGIONS  ADD CONSTRAINT  REG_ID_PK  PRIMARY KEY ( REGION_ID )  ;
 -- INSERTING into REGIONS
Insert into REGIONS Select * from HR.regions;
/*
Insert into REGIONS ( REGION_ID , REGION_NAME ) values (1,'Europe');
Insert into REGIONS ( REGION_ID , REGION_NAME ) values (2,'Americas');
Insert into REGIONS ( REGION_ID , REGION_NAME ) values (3,'Asia');
Insert into REGIONS ( REGION_ID , REGION_NAME ) values (4,'Middle East and Africa');
*/
REM END REGIONS

REM START COUNTRIES
EXEC execute immediate 'DROP TABLE  COUNTRIES   cascade constraints PURGE'
  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 NOCOMPRESS ;
  ALTER TABLE  COUNTRIES  ADD CONSTRAINT  COUNTR_REG_FK  FOREIGN KEY ( REGION_ID )
	  REFERENCES  REGIONS  ( REGION_ID ) ENABLE;
 -- INSERTING into COUNTRIES
-- alter session set constraints=deferred;
Insert into COUNTRIES Select * from HR.COUNTRIES;
commit;
/*
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('AR','Argentina',2);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('AU','Australia',3);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('BE','Belgium',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('BR','Brazil',2);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('CA','Canada',2);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('CH','Switzerland',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('CN','China',3);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('DE','Germany',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('DK','Denmark',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('EG','Egypt',4);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('FR','France',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('HK','HongKong',3);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('IL','Israel',4);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('IN','India',3);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('IT','Italy',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('JP','Japan',3);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('KW','Kuwait',4);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('MX','Mexico',2);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('NG','Nigeria',4);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('NL','Netherlands',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('SG','Singapore',3);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('UK','United Kingdom',1);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('US','United States of America',2);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('ZM','Zambia',4);
Insert into COUNTRIES ( COUNTRY_ID , COUNTRY_NAME , REGION_ID ) values ('ZW','Zimbabwe',4);
COMMIT;
*/
REM END COUNTRIES

REM START LOCATIONS
  EXEC execute immediate 'DROP TABLE  LOCATIONS  cascade constraints PURGE'

  CREATE TABLE  LOCATIONS  
   (	 LOCATION_ID  NUMBER(4,0), 
	 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)
   ) ;
  ALTER TABLE  LOCATIONS  ADD CONSTRAINT  LOC_ID_PK  PRIMARY KEY ( LOCATION_ID )  ;
  ALTER TABLE  LOCATIONS  ADD CONSTRAINT  LOC_C_ID_FK  FOREIGN KEY ( COUNTRY_ID )
	  REFERENCES  COUNTRIES  ( COUNTRY_ID )    DEFERRABLE INITIALLY IMMEDIATE;
 -- INSERTING into LOCATIONS
Insert into LOCATIONS  SELECT * FROM HR.LOCATIONS;
Commit;
/*
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1000,'1297 Via Cola di Rie','00989','Roma',null,'IT');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1100,'93091 Calle della Testa','10934','Venice',null,'IT');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1300,'9450 Kamiya-cho','6823','Hiroshima',null,'JP');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1500,'2011 Interiors Blvd','99236','South San Francisco','California','US');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1700,'2004 Charade Rd','98199','Seattle','Washington','US');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2000,'40-5-12 Laogianggen','190518','Beijing',null,'CN');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2300,'198 Clementi North','540198','Singapore',null,'SG');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2400,'8204 Arthur St',null,'London',null,'UK');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (3000,'Murtenstrasse 921','3095','Bern','BE','CH');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL');
Insert into LOCATIONS ( LOCATION_ID , STREET_ADDRESS , POSTAL_CODE , CITY , STATE_PROVINCE , COUNTRY_ID ) values (3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
*/
REM END LOCATIONS
--
REM START JOBS
EXEC execute immediate 'DROP TABLE  JOBS  cascade constraints PURGE'
  CREATE TABLE  JOBS  
   (	 JOB_ID  VARCHAR2(10), 
	 JOB_TITLE  VARCHAR2(35) CONSTRAINT  JOB_TITLE_NN  NOT NULL  , 
	 MIN_SALARY  NUMBER(6,0), 
	 MAX_SALARY  NUMBER(6,0)
   ) ;
  ALTER TABLE  JOBS  ADD CONSTRAINT  JOB_ID_PK  PRIMARY KEY ( JOB_ID )  ;
 -- INSERTING into JOBS
Insert into JOBS SELECT * FROM HR.JOBS;
Commit;
/*
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('AD_PRES','President',20000,40000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('AD_VP','Administration Vice President',15000,30000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('AD_ASST','Administration Assistant',3000,6000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('FI_MGR','Finance Manager',8200,16000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('FI_ACCOUNT','Accountant',4200,9000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('AC_MGR','Accounting Manager',8200,16000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('AC_ACCOUNT','Public Accountant',4200,9000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('SA_MAN','Sales Manager',10000,20000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('SA_REP','Sales Representative',6000,12000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('PU_MAN','Purchasing Manager',8000,15000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('PU_CLERK','Purchasing Clerk',2500,5500);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('ST_MAN','Stock Manager',5500,8500);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('ST_CLERK','Stock Clerk',2000,5000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('SH_CLERK','Shipping Clerk',2500,5500);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('IT_PROG','Programmer',4000,10000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('MK_MAN','Marketing Manager',9000,15000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('MK_REP','Marketing Representative',4000,9000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('OE_REP','Human Resources Representative',4000,9000);
Insert into JOBS ( JOB_ID , JOB_TITLE , MIN_SALARY , MAX_SALARY ) values ('PR_REP','Public Relations Representative',4500,10500);
*/
REM END JOBS

REM START CUSTOMER
EXEC execute immediate 'DROP TABLE  CUSTOMER  cascade constraints PURGE'

  CREATE TABLE  CUSTOMER  
   (	 CUSTID  NUMBER(6,0) NOT NULL  , 
	 NAME  VARCHAR2(45), 
	 ADDRESS  VARCHAR2(40), 
	 CITY  VARCHAR2(30), 
	 STATE  VARCHAR2(2), 
	 ZIP  VARCHAR2(9), 
	 AREA  NUMBER(3,0), 
	 PHONE  VARCHAR2(9), 
	 REPID  NUMBER(4,0) NOT NULL  , 
	 CREDITLIMIT  NUMBER(9,2), 
	 COMMNT VARCHAR2(4000) ) ;
  ALTER TABLE  CUSTOMER  ADD CONSTRAINT  CUSTID_ZERO  CHECK (CUSTID > 0)  ;
  ALTER TABLE  CUSTOMER  ADD CONSTRAINT  CUSTOMER_PRIMARY_KEY  PRIMARY KEY ( CUSTID )  ;
 -- INSERTING into CUSTOMER
Insert into customer Select * from oe.customer;
/*
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMNT)
VALUES ('96711', 'CA', '7844', '598-6609',
 'JOCKSPORTS',
 '100', '5000', 'BELMONT', '415', '345 VIEWRIDGE',
 'Very friendly people to work with -- sales rep likes to be called Mike.');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('94061', 'CA', '7521', '368-1223',
 'TKB SPORT SHOP',
 '101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.',
 'Rep called 5/8 about change in order - contact shipping.');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('95133', 'CA', '7654', '644-3341',
 'VOLLYRITE',
 '102', '7000', 'BURLINGAME', '415', '9722 HAMILTON',
 'Company doing heavy promotion beginning 10/89. Prepare for large orders during
 winter.');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('97544', 'CA', '7521', '677-9312',
 'JUST TENNIS',
 '103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL',
 'Contact rep about new line of tennis rackets.');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('93301', 'CA', '7499', '996-2323',
 'EVERY MOUNTAIN',
 '104', '10000', 'CUPERTINO', '408', '574 SUYYYYY RD.',
 'Customer with high market share (23%) due to aggressive advertising.');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('91003', 'CA', '7844', '376-9966',
 'K + T SPORTS',
 '105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO',
 'Tends to order large amounts of merchandise at once. Accounting is considering
 raising their credit limit. Usually pays on time.');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('94301', 'CA', '7521', '364-9777',
 'SHAPE UP',
 '106', '6000', 'PALO ALTO', '415', '908 SEQUOIA',
 'Support intensive. Orders small amounts (< 800) of merchandise at a time.');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('93301', 'CA', '7499', '967-4398',
 'WOMENS SPORTS',
 '107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE',
 'First sporting goods store geared exclusively towards women. Unusual promotion
al style and very willing to take chances towards new products!');
INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT,
  CITY, AREA, ADDRESS, COMMENTS)
VALUES ('55649', 'MN', '7844', '566-9123',
 'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER',
 '108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', '');
 */
REM END CUSTOMER

REM START DEPARTMENTS
  EXEC execute immediate 'DROP TABLE  DEPARTMENTS  cascade constraints PURGE'

  CREATE TABLE  DEPARTMENTS  
   (	 DEPARTMENT_ID  NUMBER(4,0), 
	 DEPARTMENT_NAME  VARCHAR2(30) CONSTRAINT  DEPT_NAME_NN  NOT NULL  , 
	 MANAGER_ID  NUMBER(6,0), 
	 LOCATION_ID  NUMBER(4,0)
   ) ;
  ALTER TABLE  DEPARTMENTS  ADD CONSTRAINT  DEPT_ID_PK  PRIMARY KEY ( DEPARTMENT_ID )  ;
  
  ALTER TABLE  DEPARTMENTS  ADD CONSTRAINT  DEPT_LOC_FK  FOREIGN KEY ( LOCATION_ID )
	  REFERENCES  LOCATIONS  ( LOCATION_ID ) ENABLE;
 -- INSERTING into DEPARTMENTS
Insert into DEPARTMENTS  SELECT * FROM HR.departments;
Commit;
/*
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (10,'Administration',200,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (20,'Marketing',201,1800);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (30,'Purchasing',114,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (40,'Human Resources',203,2400);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (50,'Shipping',121,1500);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (60,'IT',103,1400);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (70,'Public Relations',204,2700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (80,'Sales',145,2500);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (90,'Executive',100,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (100,'Finance',108,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (110,'Accounting',205,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (120,'Treasury',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (130,'Corporate Tax',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (140,'Control And Credit',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (150,'Shareholder Services',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (160,'Benefits',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (170,'Manufacturing',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (180,'Construction',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (190,'Contracting',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (200,'Operations',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (210,'IT Support',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (220,'NOC',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (230,'IT Helpdesk',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (240,'Government Sales',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (250,'Retail Sales',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (260,'Recruiting',null,1700);
Insert into DEPARTMENTS ( DEPARTMENT_ID , DEPARTMENT_NAME , MANAGER_ID , LOCATION_ID ) values (270,'Payroll',null,1700);
*/
REM END DEPARTMENTS

REM START EMPLOYEES
  EXEC execute immediate 'DROP TABLE  EMPLOYEES  cascade constraints PURGE'

  CREATE TABLE  EMPLOYEES  
   (	 EMPLOYEE_ID  NUMBER(6,0), 
	 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,0), 
	 DEPARTMENT_ID  NUMBER(4,0)
   ) ;
  ALTER TABLE  EMPLOYEES  ADD CONSTRAINT  EMP_SALARY_MIN  CHECK (salary > 0)  ;
  ALTER TABLE  EMPLOYEES  ADD CONSTRAINT  EMP_EMAIL_UK  UNIQUE ( EMAIL )  ;
  ALTER TABLE  EMPLOYEES  ADD CONSTRAINT  EMP_EMP_ID_PK  PRIMARY KEY ( EMPLOYEE_ID )  ;
  ALTER TABLE  EMPLOYEES  ADD CONSTRAINT  EMP_JOB_FK  FOREIGN KEY ( JOB_ID )
	  REFERENCES  JOBS  ( JOB_ID ) DEFERRABLE INITIALLY IMMEDIATE;
  ALTER TABLE  EMPLOYEES  ADD CONSTRAINT  EMP_DEPT_FK  FOREIGN KEY ( DEPARTMENT_ID )
	  REFERENCES  DEPARTMENTS  ( DEPARTMENT_ID ) ;
 -- INSERTING into EMPLOYEES
Insert into EMPLOYEES  SELECT * FROM HR.employees;
Commit;
/* 
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (100,'Steven','King','SKING','515.123.4567',to_date('17.06.87','DD.MM.RR'),'AD_PRES',24000,null,null,90);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',to_date('21.09.89','DD.MM.RR'),'AD_VP',17000,null,100,90);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (102,'Lex','De Haan','LDEHAAN','515.123.4569',to_date('13.01.93','DD.MM.RR'),'AD_VP',17000,null,100,90);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (103,'Alexander','Hunold','AHUNOLD','590.423.4567',to_date('03.01.90','DD.MM.RR'),'IT_PROG',9000,null,102,60);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (104,'Bruce','Ernst','BERNST','590.423.4568',to_date('21.05.91','DD.MM.RR'),'IT_PROG',6000,null,103,60);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (105,'David','Austin','DAUSTIN','590.423.4569',to_date('25.06.97','DD.MM.RR'),'IT_PROG',4800,null,103,60);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (106,'Valli','Pataballa','VPATABAL','590.423.4560',to_date('05.02.98','DD.MM.RR'),'IT_PROG',4800,null,103,60);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (107,'Diana','Lorentz','DLORENTZ','590.423.5567',to_date('07.02.99','DD.MM.RR'),'IT_PROG',4200,null,103,60);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (108,'Nancy','Greenberg','NGREENBE','515.124.4569',to_date('17.08.94','DD.MM.RR'),'FI_MGR',12000,null,101,100);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (109,'Daniel','Faviet','DFAVIET','515.124.4169',to_date('16.08.94','DD.MM.RR'),'FI_ACCOUNT',9000,null,108,100);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (110,'John','Chen','JCHEN','515.124.4269',to_date('28.09.97','DD.MM.RR'),'FI_ACCOUNT',8200,null,108,100);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (111,'Ismael','Sciarra','ISCIARRA','515.124.4369',to_date('30.09.97','DD.MM.RR'),'FI_ACCOUNT',7700,null,108,100);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (112,'Jose Manuel','Urman','JMURMAN','515.124.4469',to_date('07.03.98','DD.MM.RR'),'FI_ACCOUNT',7800,null,108,100);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (113,'Luis','Popp','LPOPP','515.124.4567',to_date('07.12.99','DD.MM.RR'),'FI_ACCOUNT',6900,null,108,100);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (114,'Den','Raphaely','DRAPHEAL','515.127.4561',to_date('07.12.94','DD.MM.RR'),'PU_MAN',11000,null,100,30);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (115,'Alexander','Khoo','AKHOO','515.127.4562',to_date('18.05.95','DD.MM.RR'),'PU_CLERK',3100,null,114,30);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (116,'Shelli','Baida','SBAIDA','515.127.4563',to_date('24.12.97','DD.MM.RR'),'PU_CLERK',2900,null,114,30);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (117,'Sigal','Tobias','STOBIAS','515.127.4564',to_date('24.07.97','DD.MM.RR'),'PU_CLERK',2800,null,114,30);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (118,'Guy','Himuro','GHIMURO','515.127.4565',to_date('15.11.98','DD.MM.RR'),'PU_CLERK',2600,null,114,30);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (119,'Karen','Colmenares','KCOLMENA','515.127.4566',to_date('10.08.99','DD.MM.RR'),'PU_CLERK',2500,null,114,30);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (120,'Matthew','Weiss','MWEISS','650.123.1234',to_date('18.07.96','DD.MM.RR'),'ST_MAN',8000,null,100,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (121,'Adam','Fripp','AFRIPP','650.123.2234',to_date('10.04.97','DD.MM.RR'),'ST_MAN',8200,null,100,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (122,'Payam','Kaufling','PKAUFLIN','650.123.3234',to_date('01.05.95','DD.MM.RR'),'ST_MAN',7900,null,100,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (123,'Shanta','Vollman','SVOLLMAN','650.123.4234',to_date('10.10.97','DD.MM.RR'),'ST_MAN',6500,null,100,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (124,'Kevin','Mourgos','KMOURGOS','650.123.5234',to_date('16.11.99','DD.MM.RR'),'ST_MAN',5800,null,100,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (125,'Julia','Nayer','JNAYER','650.124.1214',to_date('16.07.97','DD.MM.RR'),'ST_CLERK',3200,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224',to_date('28.09.98','DD.MM.RR'),'ST_CLERK',2700,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (127,'James','Landry','JLANDRY','650.124.1334',to_date('14.01.99','DD.MM.RR'),'ST_CLERK',2400,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (128,'Steven','Markle','SMARKLE','650.124.1434',to_date('08.03.00','DD.MM.RR'),'ST_CLERK',2200,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (129,'Laura','Bissot','LBISSOT','650.124.5234',to_date('20.08.97','DD.MM.RR'),'ST_CLERK',3300,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (130,'Mozhe','Atkinson','MATKINSO','650.124.6234',to_date('30.10.97','DD.MM.RR'),'ST_CLERK',2800,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (131,'James','Marlow','JAMRLOW','650.124.7234',to_date('16.02.97','DD.MM.RR'),'ST_CLERK',2500,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (132,'TJ','Olson','TJOLSON','650.124.8234',to_date('10.04.99','DD.MM.RR'),'ST_CLERK',2100,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (133,'Jason','Mallin','JMALLIN','650.127.1934',to_date('14.06.96','DD.MM.RR'),'ST_CLERK',3300,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (134,'Michael','Rogers','MROGERS','650.127.1834',to_date('26.08.98','DD.MM.RR'),'ST_CLERK',2900,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (135,'Ki','Gee','KGEE','650.127.1734',to_date('12.12.99','DD.MM.RR'),'ST_CLERK',2400,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (136,'Hazel','Philtanker','HPHILTAN','650.127.1634',to_date('06.02.00','DD.MM.RR'),'ST_CLERK',2200,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (137,'Renske','Ladwig','RLADWIG','650.121.1234',to_date('14.07.95','DD.MM.RR'),'ST_CLERK',3600,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (138,'Stephen','Stiles','SSTILES','650.121.2034',to_date('26.10.97','DD.MM.RR'),'ST_CLERK',3200,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (139,'John','Seo','JSEO','650.121.2019',to_date('12.02.98','DD.MM.RR'),'ST_CLERK',2700,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (140,'Joshua','Patel','JPATEL','650.121.1834',to_date('06.04.98','DD.MM.RR'),'ST_CLERK',2500,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (141,'Trenna','Rajs','TRAJS','650.121.8009',to_date('17.10.95','DD.MM.RR'),'ST_CLERK',3500,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (142,'Curtis','Davies','CDAVIES','650.121.2994',to_date('29.01.97','DD.MM.RR'),'ST_CLERK',3100,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (143,'Randall','Matos','RMATOS','650.121.2874',to_date('15.03.98','DD.MM.RR'),'ST_CLERK',2600,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (144,'Peter','Vargas','PVARGAS','650.121.2004',to_date('09.07.98','DD.MM.RR'),'ST_CLERK',2500,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (145,'John','Russell','JRUSSEL','011.44.1344.429268',to_date('01.10.96','DD.MM.RR'),'SA_MAN',14000,0.4,100,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (146,'Karen','Partners','KPARTNER','011.44.1344.467268',to_date('05.01.97','DD.MM.RR'),'SA_MAN',13500,0.3,100,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278',to_date('10.03.97','DD.MM.RR'),'SA_MAN',12000,0.3,100,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268',to_date('15.10.99','DD.MM.RR'),'SA_MAN',11000,0.3,100,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',to_date('29.01.00','DD.MM.RR'),'SA_MAN',10500,0.2,100,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (150,'Peter','Tucker','PTUCKER','011.44.1344.129268',to_date('30.01.97','DD.MM.RR'),'SA_REP',10000,0.3,145,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (151,'David','Bernstein','DBERNSTE','011.44.1344.345268',to_date('24.03.97','DD.MM.RR'),'SA_REP',9500,0.25,145,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (152,'Peter','Hall','PHALL','011.44.1344.478968',to_date('20.08.97','DD.MM.RR'),'SA_REP',9000,0.25,145,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (153,'Christopher','Olsen','COLSEN','011.44.1344.498718',to_date('30.03.98','DD.MM.RR'),'SA_REP',8000,0.2,145,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668',to_date('09.12.98','DD.MM.RR'),'SA_REP',7500,0.2,145,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508',to_date('23.11.99','DD.MM.RR'),'SA_REP',7000,0.15,145,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (156,'Janette','King','JKING','011.44.1345.429268',to_date('30.01.96','DD.MM.RR'),'SA_REP',10000,0.35,146,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (157,'Patrick','Sully','PSULLY','011.44.1345.929268',to_date('04.03.96','DD.MM.RR'),'SA_REP',9500,0.35,146,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268',to_date('01.08.96','DD.MM.RR'),'SA_REP',9000,0.35,146,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (159,'Lindsey','Smith','LSMITH','011.44.1345.729268',to_date('10.03.97','DD.MM.RR'),'SA_REP',8000,0.3,146,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (160,'Louise','Doran','LDORAN','011.44.1345.629268',to_date('15.12.97','DD.MM.RR'),'SA_REP',7500,0.3,146,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268',to_date('03.11.98','DD.MM.RR'),'SA_REP',7000,0.25,146,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268',to_date('11.11.97','DD.MM.RR'),'SA_REP',10500,0.25,147,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (163,'Danielle','Greene','DGREENE','011.44.1346.229268',to_date('19.03.99','DD.MM.RR'),'SA_REP',9500,0.15,147,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268',to_date('24.01.00','DD.MM.RR'),'SA_REP',7200,0.1,147,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (165,'David','Lee','DLEE','011.44.1346.529268',to_date('23.02.00','DD.MM.RR'),'SA_REP',6800,0.1,147,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (166,'Sundar','Ande','SANDE','011.44.1346.629268',to_date('24.03.00','DD.MM.RR'),'SA_REP',6400,0.1,147,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (167,'Amit','Banda','ABANDA','011.44.1346.729268',to_date('21.04.00','DD.MM.RR'),'SA_REP',6200,0.1,147,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (168,'Lisa','Ozer','LOZER','011.44.1343.929268',to_date('11.03.97','DD.MM.RR'),'SA_REP',11500,0.25,148,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268',to_date('23.03.98','DD.MM.RR'),'SA_REP',10000,0.2,148,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (170,'Tayler','Fox','TFOX','011.44.1343.729268',to_date('24.01.98','DD.MM.RR'),'SA_REP',9600,0.2,148,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (171,'William','Smith','WSMITH','011.44.1343.629268',to_date('23.02.99','DD.MM.RR'),'SA_REP',7400,0.15,148,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (172,'Elizabeth','Bates','EBATES','011.44.1343.529268',to_date('24.03.99','DD.MM.RR'),'SA_REP',7300,0.15,148,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268',to_date('21.04.00','DD.MM.RR'),'SA_REP',6100,0.1,148,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (174,'Ellen','Abel','EABEL','011.44.1644.429267',to_date('11.05.96','DD.MM.RR'),'SA_REP',11000,0.3,149,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266',to_date('19.03.97','DD.MM.RR'),'SA_REP',8800,0.25,149,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265',to_date('24.03.98','DD.MM.RR'),'SA_REP',8600,0.2,149,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264',to_date('23.04.98','DD.MM.RR'),'SA_REP',8400,0.2,149,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (178,'Kimberely','Grant','KGRANT','011.44.1644.429263',to_date('24.05.99','DD.MM.RR'),'SA_REP',7000,0.15,149,null);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262',to_date('04.01.00','DD.MM.RR'),'SA_REP',6200,0.1,149,80);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (180,'Winston','Taylor','WTAYLOR','650.507.9876',to_date('24.01.98','DD.MM.RR'),'SH_CLERK',3200,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (181,'Jean','Fleaur','JFLEAUR','650.507.9877',to_date('23.02.98','DD.MM.RR'),'SH_CLERK',3100,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (182,'Martha','Sullivan','MSULLIVA','650.507.9878',to_date('21.06.99','DD.MM.RR'),'SH_CLERK',2500,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (183,'Girard','Geoni','GGEONI','650.507.9879',to_date('03.02.00','DD.MM.RR'),'SH_CLERK',2800,null,120,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (184,'Nandita','Sarchand','NSARCHAN','650.509.1876',to_date('27.01.96','DD.MM.RR'),'SH_CLERK',4200,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (185,'Alexis','Bull','ABULL','650.509.2876',to_date('20.02.97','DD.MM.RR'),'SH_CLERK',4100,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (186,'Julia','Dellinger','JDELLING','650.509.3876',to_date('24.06.98','DD.MM.RR'),'SH_CLERK',3400,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (187,'Anthony','Cabrio','ACABRIO','650.509.4876',to_date('07.02.99','DD.MM.RR'),'SH_CLERK',3000,null,121,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (188,'Kelly','Chung','KCHUNG','650.505.1876',to_date('14.06.97','DD.MM.RR'),'SH_CLERK',3800,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (189,'Jennifer','Dilly','JDILLY','650.505.2876',to_date('13.08.97','DD.MM.RR'),'SH_CLERK',3600,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (190,'Timothy','Gates','TGATES','650.505.3876',to_date('11.07.98','DD.MM.RR'),'SH_CLERK',2900,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (191,'Randall','Perkins','RPERKINS','650.505.4876',to_date('19.12.99','DD.MM.RR'),'SH_CLERK',2500,null,122,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (192,'Sarah','Bell','SBELL','650.501.1876',to_date('04.02.96','DD.MM.RR'),'SH_CLERK',4000,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (193,'Britney','Everett','BEVERETT','650.501.2876',to_date('03.03.97','DD.MM.RR'),'SH_CLERK',3900,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (194,'Samuel','McCain','SMCCAIN','650.501.3876',to_date('01.07.98','DD.MM.RR'),'SH_CLERK',3200,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (195,'Vance','Jones','VJONES','650.501.4876',to_date('17.03.99','DD.MM.RR'),'SH_CLERK',2800,null,123,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (196,'Alana','Walsh','AWALSH','650.507.9811',to_date('24.04.98','DD.MM.RR'),'SH_CLERK',3100,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (197,'Kevin','Feeney','KFEENEY','650.507.9822',to_date('23.05.98','DD.MM.RR'),'SH_CLERK',3000,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (198,'Donald','OConnell','DOCONNEL','650.507.9833',to_date('21.06.99','DD.MM.RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (199,'Douglas','Grant','DGRANT','650.507.9844',to_date('13.01.00','DD.MM.RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (200,'Jennifer','Whalen','JWHALEN','515.123.4444',to_date('17.09.87','DD.MM.RR'),'AD_ASST',4400,null,101,10);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (201,'Michael','Hartstein','MHARTSTE','515.123.5555',to_date('17.02.96','DD.MM.RR'),'MK_MAN',13000,null,100,20);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (202,'Pat','Fay','PFAY','603.123.6666',to_date('17.08.97','DD.MM.RR'),'MK_REP',6000,null,201,20);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (203,'Susan','Mavris','SMAVRIS','515.123.7777',to_date('07.06.94','DD.MM.RR'),'OE_REP',6500,null,101,40);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (204,'Hermann','Baer','HBAER','515.123.8888',to_date('07.06.94','DD.MM.RR'),'PR_REP',10000,null,101,70);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (205,'Shelley','Higgins','SHIGGINS','515.123.8080',to_date('07.06.94','DD.MM.RR'),'AC_MGR',12000,null,101,110);
Insert into EMPLOYEES ( EMPLOYEE_ID , FIRST_NAME , LAST_NAME , EMAIL , PHONE_NUMBER , HIRE_DATE , JOB_ID , SALARY , COMMISSION_PCT , MANAGER_ID , DEPARTMENT_ID ) values (206,'William','Gietz','WGIETZ','515.123.8181',to_date('07.06.94','DD.MM.RR'),'AC_ACCOUNT',8300,null,205,110);
*/
REM END EMPLOYEES
ALTER TABLE  EMPLOYEES  ADD CONSTRAINT  EMP_MANAGER_FK  FOREIGN KEY ( MANAGER_ID )
	REFERENCES  EMPLOYEES  ( EMPLOYEE_ID ) ;
ALTER TABLE  DEPARTMENTS  ADD CONSTRAINT  DEPT_MGR_FK  FOREIGN KEY ( MANAGER_ID )
	  REFERENCES  EMPLOYEES  ( EMPLOYEE_ID );

REM START JOB_HISTORY
  EXEC execute immediate 'DROP TABLE  JOB_HISTORY  cascade constraints PURGE'


  CREATE TABLE  JOB_HISTORY  
   (	 EMPLOYEE_ID  NUMBER(6,0) 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,0)
   ) ;
  ALTER TABLE  JOB_HISTORY  ADD CONSTRAINT  JHIST_DATE_INTERVAL  CHECK (end_date > start_date)  ;
  ALTER TABLE  JOB_HISTORY  ADD CONSTRAINT  JHIST_EMP_ID_ST_DATE_PK  PRIMARY KEY ( EMPLOYEE_ID ,  START_DATE )  ;
  ALTER TABLE  JOB_HISTORY  ADD CONSTRAINT  JHIST_DEPT_FK  FOREIGN KEY ( DEPARTMENT_ID )
	  REFERENCES  DEPARTMENTS  ( DEPARTMENT_ID )    DEFERRABLE INITIALLY IMMEDIATE;
  ALTER TABLE  JOB_HISTORY  ADD CONSTRAINT  JHIST_EMP_FK  FOREIGN KEY ( EMPLOYEE_ID )
	  REFERENCES  EMPLOYEES  ( EMPLOYEE_ID )    DEFERRABLE INITIALLY IMMEDIATE;
  ALTER TABLE  JOB_HISTORY  ADD CONSTRAINT  JHIST_JOB_FK  FOREIGN KEY ( JOB_ID )
	  REFERENCES  JOBS  ( JOB_ID )   DEFERRABLE INITIALLY IMMEDIATE;
 -- INSERTING into JOB_HISTORY
Insert into JOB_HISTORY  SELECT * FROM HR.JOB_HISTORY; 
Commit;
/*
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (102,to_date('13.01.93','DD.MM.RR'),to_date('24.07.98','DD.MM.RR'),'IT_PROG',60);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (101,to_date('21.09.89','DD.MM.RR'),to_date('27.10.93','DD.MM.RR'),'AC_ACCOUNT',110);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (101,to_date('28.10.93','DD.MM.RR'),to_date('15.03.97','DD.MM.RR'),'AC_MGR',110);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (201,to_date('17.02.96','DD.MM.RR'),to_date('19.12.99','DD.MM.RR'),'MK_REP',20);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (114,to_date('24.03.98','DD.MM.RR'),to_date('31.12.99','DD.MM.RR'),'ST_CLERK',50);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (122,to_date('01.01.99','DD.MM.RR'),to_date('31.12.99','DD.MM.RR'),'ST_CLERK',50);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (200,to_date('17.09.87','DD.MM.RR'),to_date('17.06.93','DD.MM.RR'),'AD_ASST',90);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (176,to_date('24.03.98','DD.MM.RR'),to_date('31.12.98','DD.MM.RR'),'SA_REP',80);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (176,to_date('01.01.99','DD.MM.RR'),to_date('31.12.99','DD.MM.RR'),'SA_MAN',80);
Insert into JOB_HISTORY ( EMPLOYEE_ID , START_DATE , END_DATE , JOB_ID , DEPARTMENT_ID ) values (200,to_date('01.07.94','DD.MM.RR'),to_date('31.12.98','DD.MM.RR'),'AC_ACCOUNT',90);
*/
REM END JOB_HISTORY


REM START MESSAGES
  EXEC execute immediate 'DROP TABLE  MESSAGES  cascade constraints PURGE'

  CREATE TABLE  MESSAGES  
   (	 CHARCOL1  VARCHAR2(60), 
	 CHARCOL2  VARCHAR2(60), 
	 NUMCOL1  NUMBER(9,2), 
	 NUMCOL2  NUMBER(9,2), 
	 DATECOL1  DATE, 
	 DATECOL2  DATE
   ) ;
 -- INSERTING into MESSAGES

REM END MESSAGES

REM START ORD
  EXEC execute immediate 'DROP TABLE  ORD  cascade constraints PURGE'

  CREATE TABLE  ORD  
   (	 ORDID  NUMBER(4,0) NOT NULL  , 
	 ORDERDATE  DATE, 
	 COMMPLAN  VARCHAR2(1), 
	 CUSTID  NUMBER(6,0) NOT NULL  , 
	 SHIPDATE  DATE, 
	 TOTAL  NUMBER(8,2)
   ) ;
  ALTER TABLE  ORD  ADD UNIQUE ( ORDID )  ;
 -- INSERTING into ORD
Insert into ord Select * from oe.ord;
REM END ORD

REM START PRODUCT
  EXEC execute immediate 'DROP TABLE  PRODUCT  cascade constraints PURGE'

  CREATE TABLE  PRODUCT  
   (	 PRODID  NUMBER(*,0), 
	 DESCRIPTION  VARCHAR2(40)
   ) ;
  ALTER TABLE  PRODUCT  ADD UNIQUE ( PRODID )  ;
 -- INSERTING into PRODUCT
Insert into PRODUCT   SELECT * FROM OE.PRODUCT;

/*
Insert into PRODUCT ( PRODID , DESCRIPTION ) values (70,'aa');
Insert into PRODUCT ( PRODID , DESCRIPTION ) values (610,'aaa');
Insert into PRODUCT ( PRODID , DESCRIPTION ) values (300,'Novy vyrobek');
commit;         	*/
REM END PRODUCT
REM START ITEM
  EXEC execute immediate 'DROP TABLE  ITEM  cascade constraints PURGE'

  CREATE TABLE  ITEM  
   (	 ORDID  NUMBER(4,0) NOT NULL  , 
	 ITEMID  NUMBER(4,0) NOT NULL  , 
	 PRODID  NUMBER(6,0), 
	 ACTUALPRICE  NUMBER(8,2), 
	 QTY  NUMBER(8,0), 
	 ITEMTOT  NUMBER(8,2)
   ) ;
  ALTER TABLE  ITEM  ADD UNIQUE ( ORDID,ITEMID )  ;
 -- INSERTING into ITEM
Insert into item Select * from oe.item;
commit;
/*
Insert into ITEM ( ORDID , ITEMID , PRODID , ACTUALPRICE , QTY , ITEMTOT ) values (612,5,70,3.4,1,3.4);
Insert into ITEM ( ORDID , ITEMID , PRODID , ACTUALPRICE , QTY , ITEMTOT ) values (610,4,100860,30,200,6000);
Commit;
*/
REM END ITEM


REM START EMP_DETAILS_VIEW
  DROP VIEW  EMP_DETAILS_VIEW ;

  CREATE OR REPLACE VIEW  EMP_DETAILS_VIEW  ( EMPLOYEE_ID ,  JOB_ID ,  MANAGER_ID ,  DEPARTMENT_ID ,  LOCATION_ID ,  COUNTRY_ID ,  FIRST_NAME ,  LAST_NAME ,  SALARY ,  COMMISSION_PCT ,  DEPARTMENT_NAME ,  JOB_TITLE ,  CITY ,  STATE_PROVINCE ,  COUNTRY_NAME ,  REGION_NAME ) AS 
  SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY;

REM END EMP_DETAILS_VIEW

REM START ADD_ITEM
  DROP PROCEDURE  ADD_ITEM ;

  CREATE OR REPLACE PROCEDURE  ADD_ITEM  
           ( a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_PRODID      ITEM.PRODID%Type,
            a_ACTUALPRICE ITEM.ACTUALPRICE%Type,
            a_QTY         ITEM.QTY%Type Default 1)
As
v_ITEMTOT   ITEM.ITEMTOT%Type := 0;
e_F_K   Exception;
e_P_K   Exception;
v_Rowid Rowid;
Pragma Exception_init (e_F_K,-2291);
Pragma Exception_init (e_P_K,-1);
Begin
  Select ROWID Into v_Rowid From PRODUCT
         Where PRODID = a_PRODID;
  v_ITEMTOT := a_QTY * NVL(a_ACTUALPRICE,0);
  Insert Into ITEM
         (ORDID,   ITEMID,   PRODID,   ACTUALPRICE,   QTY,   ITEMTOT)
  Values (a_ORDID, a_ITEMID, a_PRODID, a_ACTUALPRICE, a_QTY, v_ITEMTOT);
  Update ORD
         Set TOTAL = TOTAL + v_ITEMTOT
         Where ORDID = a_ORDID;
Exception
  When No_Data_Found Then Raise_Application_Error
                  (-20399, 'Chybne cislo zbozi');
  When e_F_K Then Raise_Application_Error
                  (-20399, 'Chybne cislo objednavky nebo zbozi');
  When e_P_K Then Raise_Application_Error
                  (-20399, 'Duplicitni cislo obj. polozky');
End Add_Item;
/
 
REM END ADD_ITEM

REM START ADD_JOB_HISTORY
  DROP PROCEDURE  ADD_JOB_HISTORY ;

  CREATE OR REPLACE PROCEDURE  ADD_JOB_HISTORY  
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
/
 
REM END ADD_JOB_HISTORY

REM START ADD_PROD
  DROP PROCEDURE  ADD_PROD ;

  CREATE OR REPLACE PROCEDURE  ADD_PROD  (v_ProdId PRODUCT.PRODID%Type, v_Descrip PRODUCT.Description%Type)
As
   Cons_vio Exception ;
   Pragma Exception_Init (Cons_vio, -1);
begin
   Insert Into PRODUCT (Prodid, Description)
               Values(v_ProdId, v_Descrip);
Exception
   When Cons_vio Then
        Raise_Application_Error
        (-20203,'Zadani cislo ma jiz jiny vyrobek');
End ADD_PROD;
/
 
REM END ADD_PROD

REM START DEL_PROD
  DROP PROCEDURE  DEL_PROD ;

  CREATE OR REPLACE PROCEDURE  DEL_PROD  (v_ProdId PRODUCT.PRODID%Type)
As
Begin
   Delete PRODUCT Where Prodid = v_Prodid;
   If Sql%NotFound Then
      Raise_Application_Error
      (-20203, 'Vyrobek zadaneho cisla '||v_Prodid||' neexistuje');
   End If;
End DEL_PROD;
/
 
REM END DEL_PROD

REM START KTERE_MA_PODRIZENE
CREATE OR REPLACE PROCEDURE  KTERE_MA_PODRIZENE 
 (p_employee_id in employees.employee_id%type DEFAULT 100,
  p_hloubka in PLS_integer default 0)
is
v_hloubka integer:=coalesce(p_hloubka,0) + 1;
v_emp_rec employees%rowtype;
v_manager_id employees.manager_id%type;
cursor cur_podrizeni is
select * from employees
where manager_id=p_employee_id;
begin
For p_emp_cur in cur_podrizeni loop
dbms_output.put_line(p_emp_cur.employee_id||
lpad('_',v_hloubka*4,'_')||
p_emp_cur.last_name||'(uroven podrizeni='
||to_char(v_hloubka)||')');
ktere_ma_podrizene(p_emp_cur.employee_id, v_hloubka);
end loop;
end;
/
show errors
REM END KTERE_MA_PODRIZENE

REM START KTERE_MA_SEFY

CREATE OR REPLACE PROCEDURE  KTERE_MA_SEFY  (p_employee_id in employees.employee_id%type)
is
v_emp_rec employees%rowtype;
v_manager_id employees.manager_id%type;
begin
Select manager_id into v_manager_id
From employees
where employee_id=p_employee_id;
If v_manager_id is NULL then return; end if;
Select * into v_emp_rec From employees
where employee_id=v_manager_id;
dbms_output.put_line(v_emp_rec.last_name);
ktere_ma_sefy(v_emp_rec.employee_id);
return;
end;
/
show errors 
REM END KTERE_MA_SEFY

REM START SECURE_DML
  DROP PROCEDURE  SECURE_DML ;

  CREATE OR REPLACE PROCEDURE  SECURE_DML  
As
Begin
     If To_Char(sysdate,'hh24:mi') not Between '08:45' and '17:30' Or
        To_Char(sysdate,'d') not Between '2' and '6'
        Then Raise_Application_Error
        (-20101,'Zmena dat je povolena pouze v urednich hodinach');
     End If;
End;
/
 
REM END SECURE_DML

REM START UPDATE_COMM
  DROP PROCEDURE  UPDATE_COMM ;

  CREATE OR REPLACE PROCEDURE  UPDATE_COMM  
   (p_custid In CUSTOMER.CUSTID%Type, -- zakaznik, ktery zadal objednavku
    p_stary_ORD_total  EMPLOYEES.COMMISSION_PCT%Type, -- u nove objednavky bude NULL
    p_novy_ORD_total  EMPLOYEES.COMMISSION_PCT%Type) -- celk. cena zbozi na objednavce
As
v_RepId CUSTOMER.REPID%Type;
v_prirustek  EMPLOYEES.COMMISSION_PCT%Type;
Cursor Get_RepId
       Is Select REPID
                 From CUSTOMER
                 Where CUSTID = p_custid;
Begin
  Open Get_RepId;
  Fetch Get_Repid Into V_Repid;
  Close Get_Repid;
  v_prirustek := (NVL(p_novy_ORD_total,0)-NVL(p_stary_ORD_total,0))*0.05;
  Update EMPLOYEES
         Set COMMISSION_PCT = COMMISSION_PCT + v_prirustek
         Where EMPLOYEE_ID = v_RepId;
End Update_Comm;
/
 
REM END UPDATE_COMM

REM START UPD_ITEM
  DROP PROCEDURE  UPD_ITEM ;

  CREATE OR REPLACE PROCEDURE  UPD_ITEM  
           (a_ORDID      ITEM.ORDID%Type,
            a_ITEMID      ITEM.ITEMID%Type,
            a_ADDQTY      Number Default 1)
 As
 v_Nove_QTY       ITEM.QTY%Type;
 v_Nove_ITEMTOT   ITEM.ITEMTOT%Type := 0;
 v_prirustek_ceny Number;
 v_OrdTotal ORD.TOTAL%Type;
 v_Rowid Rowid;
Begin
  Select  QTY + a_ADDQTY,
          a_ADDQTY * NVL(ACTUALPRICE,0),
          ITEMTOT + a_ADDQTY * NVL(ACTUALPRICE,0)
     Into v_Nove_QTY, v_prirustek_ceny, v_Nove_ITEMTOT
     From ITEM
     Where ORDID = a_ORDID And
           ITEMID = a_ITEMID;
  If v_Nove_QTY <= 0 Then
     Raise_Application_Error
           (-20250,'Nelze nastavit QTY na hodnotu '||v_Nove_QTY);
  End If;
  Update ORD
         Set TOTAL = TOTAL + v_prirustek_ceny
         Where ORDID = a_ORDID;
  If SQL%NotFound Then Raise_Application_Error
                  (-20399, 'Chybne cislo objednavky');
  End If;
-- kdyz zadana polozka neexistuje, tak to sem nedojde.
  Update ITEM
         Set QTY     = v_Nove_QTY,
             ITEMTOT = v_Nove_ITEMTOT
         Where ORDID  = a_ORDID And ITEMID = a_ITEMID;
Exception
   When No_Data_Found Then Raise_Application_Error
                  (-20399, 'Chybne cislo objednaci polozky');
End Upd_Item;
/
 
REM END UPD_ITEM

REM START UPD_PROD
  DROP PROCEDURE  UPD_PROD ;

  CREATE OR REPLACE PROCEDURE  UPD_PROD  (v_Prodid PRODUCT.PRODID%Type,v_Descrip PRODUCT.DESCRIPTION%Type)
As
Begin
   Update PRODUCT
          Set DESCRIPTION = v_Descrip
          Where Prodid = v_Prodid;
   If Sql%NotFound Then
      Raise_Application_Error
      (-20203, 'Vyrobek zadaneho cisla '||v_Prodid||' neexistuje');
   End If;
End UPD_PROD;
/
 
REM END UPD_PROD

REM START SECURE_EMPLOYEES

  CREATE OR REPLACE TRIGGER  SECURE_EMPLOYEES  
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/
ALTER TRIGGER  SECURE_EMPLOYEES  DISABLE;
 
REM END SECURE_EMPLOYEES

REM START SECURE_PROD
  CREATE OR REPLACE TRIGGER  SECURE_PROD  
Before Insert Or Update Or Delete On PRODUCT
Begin
Secure_DML;
End Secure_Prod;
/
ALTER TRIGGER  SECURE_PROD  DISABLE ;
 
REM END SECURE_PROD

REM START UPDATE_EMP_COMM
  CREATE OR REPLACE TRIGGER  UPDATE_EMP_COMM  
       After Insert Or Update  Or Delete on ORD
       For Each Row
Begin
Update_Comm(:New.CUSTID,
            :Old.TOTAL,

            :New.TOTAL);
End Update_Emp_Comm;
/

 
REM END UPDATE_EMP_COMM

REM START UPDATE_JOB_HISTORY

  CREATE OR REPLACE TRIGGER  UPDATE_JOB_HISTORY  
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
/
 
REM END UPDATE_JOB_HISTORY

