-- 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