-- POPULATE LOCATIONS TABLE INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(1800,'460 Bloor St. W.','ON M5S 1X8','Toronto','ON','CA'); INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','OXxford','OX','UK'); INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(1400,'2014 Jabberwocky Rd','26192','Southlake','TX','US'); INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(1500,'2011 Interiors Blvd','99236','South San Francisco','CA','US'); INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(1700,'2004 Charade Rd','98199','Seattle','WA','US'); INSERT INTO HOL_LOCATIONS (location_id, street, postal_code, city, state_province, country_id) VALUES (99999,'TBD','TBD','TBD','IN','US') ;

-- POPULATE JOBS TABLE INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(1,'President',20000,40000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(2,'Administration Vice President',15000,30000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(3,'Administration Assistant',3000,6000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(4,'Accounting Manager',8200,16000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(5,'Public Accountant',4200,9000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(6,'Sales Manager',10000,20000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(7,'Sales Representative',6000,12000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(8,'Stock Manager',5500,8500); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(9,'Stock Clerk',2000,5000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(10,'Programmer',4000,10000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(11,'Marketing Manager',9000,15000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(12,'Marketing Representative',4000,9000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(20,'DBA',5000,9000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(21,'Data Analyst',4000,8000); INSERT INTO HOL_JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES(22,'Data Analysis Manager',75000,15000);

-- POPULATE DEPARTMENTS TABLE INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(10,'Administration',NULL,1700); INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(20,'Marketing',NULL,1800); INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(50,'Shipping',NULL,1500); INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(60,'IT',NULL,1400); INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(80,'Sales',NULL,2500); INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(90,'Executive',NULL,1700); INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(110,'Accounting',NULL,1700); INSERT INTO HOL_departments (department_id, department_name, manager_id, location_id) Values(190,'Contracting',NULL,1700);

-- POPULATE EMPLOYEES TABLE INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(100,'Steven','King','SKING@smallcompany.com','515.123.4567',TO_DATE('1987-06-17','yyyy-mm-dd'),1,24000,null,null,90); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(101,'Neena','Kochhar','NKOCHHAR@smallcompany.com','515.123.4568',TO_DATE('1989-09-21','yyyy-mm-dd'),2,17000,null,100,90 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(102,'Lex','De Haan','LDEHAAN@smallcompany.com','515.123.4569',TO_DATE('1993-01-13','yyyy-mm-dd'),2,17000,null,100,90 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(200,'Jennifer','Whalen','JWHALEN@smallcompany.com','515.123.4444',TO_DATE('1987-09-17','yyyy-mm-dd'),3,4400,null,101,10 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(205,'Shelley','Higgins','SHIGGINS@smallcompany.com','515.123.8080',TO_DATE('1994-06-07','yyyy-mm-dd'),4,12000,null,101,110 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(206,'William','Gietz','WGIETZ@smallcompany.com','515.123.8181',TO_DATE('1994-06-07','yyyy-mm-dd'),NULL,8300,null,205,110 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID, BONUS) VALUES(149,'Eleni','Zlotkey','EZLOTKEY@smallcompany.com','011.44.1344.429018',TO_DATE('2000-01-29','yyyy-mm-dd'),6,10500,.2,100,80, '1500' ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID, BONUS) VALUES(174,'Ellen','Abel','EABEL@smallcompany.com','011.44.1644.429267',TO_DATE('1996-05-11','yyyy-mm-dd'),7,11000,.3,149,80,'1700' ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID, BONUS) VALUES(176,'Jonathon','Taylor','JTAYLOR@smallcompany.com','011.44.1644.429265',TO_DATE('1998-03-24','yyyy-mm-dd'),7,8600,.2,149,80,'1250' ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(178,'Kimberely','Grant','KGRANT@smallcompany.com','011.44.1644.429263',TO_DATE('1999-05-24','yyyy-mm-dd'),7,7000,.15,149,null ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(124,'Kevin','Mourgos','KMOURGOS@smallcompany.com','650.123.5234',TO_DATE('1999-11-16','yyyy-mm-dd'),8,5800,null,100,50); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(141,'Trenna','Rajs','TRAJS@smallcompany.com','650.121.8009',TO_DATE('1995-10-17','yyyy-mm-dd'),9,3500,null,124,50 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(142,'Curtis','Davies','CDAVIES@smallcompany.com','650.121.2994',TO_DATE('1997-01-29','yyyy-mm-dd'),NULL,3100,null,124,50 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(143,'Randall','Matos','RMATOS@smallcompany.com','650.121.2874',TO_DATE('1998-03-15','yyyy-mm-dd'),9,2600,null,124,50 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(144,'Peter','Vargas','PVARGAS@smallcompany.com','650.121.2004',TO_DATE('1998-07-09','yyyy-mm-dd'),NULL,2500,null,124,50 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(103,'Alexander','Hunold','AHUNOLD@smallcompany.com','590.423.4567',TO_DATE('1990-01-03','yyyy-mm-dd'),10,9000,null,102,60 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(104,'Bruce','Ernst','BERNST@smallcompany.com','590.423.4568',TO_DATE('1991-05-21','yyyy-mm-dd'),10,6000,null,103,60 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(107,'Diana','Lorentz','DLORENTZ@smallcompany.com','590.423.5567',TO_DATE('1999-02-07','yyyy-mm-dd'),10,4200,null,103,60 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(201,'Michael','Hartstein','MHARTSTE@smallcompany.com','515.123.5555',TO_DATE('1996-02-17','yyyy-mm-dd'),11,13000,null,100,20 ); INSERT INTO HOL_EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE,HIRE_DATE,JOB_ID,CURRENT_SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES(202,'Pat','Fay','PFAY@smallcompany.com','603.123.6666',TO_DATE('1997-08-17','yyyy-mm-dd'),12,6000,null,201,20);

-- ASSIGN MANAGERS TO DEPARTMENTS UPDATE HOL_DEPARTMENTS SET Manager_ID=200 WHERE Department_ID = 10;

UPDATE HOL_DEPARTMENTS SET Manager_ID=201 WHERE Department_ID = 20;

UPDATE HOL_DEPARTMENTS SET Manager_ID=124 WHERE Department_ID = 50;

UPDATE HOL_DEPARTMENTS SET Manager_ID=103 WHERE Department_ID = 60;

UPDATE HOL_DEPARTMENTS SET Manager_ID=149 WHERE Department_ID = 80;

UPDATE HOL_DEPARTMENTS SET Manager_ID=100 WHERE Department_ID = 90;

UPDATE HOL_DEPARTMENTS SET Manager_ID=205 WHERE Department_ID = 110;

-- POPULATE JOB_HISTORY TABLE INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(200,TO_DATE('09-17-1987','mm-dd-yyyy'),TO_DATE('06-17-1993','mm-dd-yyyy'),3,90 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(101,TO_DATE('10-28-1993','mm-dd-yyyy'),TO_DATE('03-15-1997','mm-dd-yyyy'),4,110 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(200,TO_DATE('07-01-1994','mm-dd-yyyy'),TO_DATE('12-31-1998','mm-dd-yyyy'),5,90 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(101,TO_DATE('09-21-1989','mm-dd-yyyy'),TO_DATE('10-27-1993','mm-dd-yyyy'),5,110 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(176,TO_DATE('01-01-1999','mm-dd-yyyy'),TO_DATE('12-31-1999','mm-dd-yyyy'),6,80 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(176,TO_DATE('03-24-1998','mm-dd-yyyy'),TO_DATE('12-31-1998','mm-dd-yyyy'),7,80 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(122,TO_DATE('01-01-1999','mm-dd-yyyy'),TO_DATE('12-31-1999','mm-dd-yyyy'),9,50 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(114,TO_DATE('03-24-1998','mm-dd-yyyy'),TO_DATE('12-31-1999','mm-dd-yyyy'),9,50 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(102,TO_DATE('01-13-1993','mm-dd-yyyy'),TO_DATE('07-24-1998','mm-dd-yyyy'),10,60 ); INSERT INTO HOL_JOB_HISTORY(EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) VALUES(201,TO_DATE('02-17-1996','mm-dd-yyyy'),TO_DATE('12-19-1999','mm-dd-yyyy'),12,20 );

M01_002_HOL_Script_INSERT_UPDATE_YH (1).sql - Notepad File Edit Format View Help / SCRIPT : MO1_HOL_Script INSERT PURPOSE: Load the Hands On Lab Database M01 Hands-On Lab and subsequent Hands On Labs 2-8. .- "CLEAR OUT" OLD DATA: NOT NORMALLY DONE IN "REAL WORLD". DELETE FROM HOL_JOB_HISTORY; UPDATE HOL_DEPARTMENTS SET Manager_ID=NULL; DELETE FROM HOL_EMPLOYEES; DELETE FROM HOL_JOBS; DELETE FROM HOL_DEPARTMENTS; DELETE FROM HOL_LOCATIONS; DELETE FROM HOL_COUNTRIES; DELETE FROM HOL_REGIONS; -. POPULATE REGIONS TABLE INSERT INTO HOL_regions (region_id, region_name) Values(1,'Europe'); INSERT INTO HOL_regions (region_id, region_name) Values( 2, 'Americas'); INSERT INTO HOL_regions (region_id, region_name) Values( 3, 'Asia'); INSERT INTO HOL_regions (region_id, region_name) Values(4, 'Middle East and Africa'); - POPULATE COUNTRIES TABLE INSERT INTO HOL_countries (country_id, country_name, region_id) Values( 'CA', 'Canada ', 2); INSERT INTO HOL_countries (country_id, country_name, region_id) Values( 'DE', 'Germany', 1); INSERT INTO HOL_countries (country_id, country_name, region_id) Values('UK', 'United Kingdom', 1); INSERT INTO HOL_countries (country_id, country_name, region_id) Values("US', 'United States of America',2); - POPULATE LOCATIONS TABLE INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(1800, '460 Bloor St. W.', 'ON M5S 1X8', 'Toronto', 'ON', 'CA'); INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'OXxford', 'OX', 'UK'); INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(1400, '2014 Jabberwocky Rd', '26192', 'Southlake ', 'TX', 'US'); INSERT INTO HOL_locations (location_id, street, postal_code, city, state_province, country_id) Values(1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'CA', 'US'); INSERT INTO HOL_locations (location id, street, postal code, citv, state province, country id) Follow the steps below to learn how to navigate the database environment you will use for all the hands-on lab assignments in the course. A. Environment Login and Setup ( 30 points) 1. Download these two script files to your device. a. M01_001_HOL_Script_CREATE_yourInitials.sql b. M01_002_HOL_Script_INSERT_UPDATE_yourlnitials.sql 2. Rename these files by replacing yourlnitials with your own initials. 3. Log into APEX. a. Use our class workspace and your IVY Tech email as user ID. b. The initial password will be Spring2023. You will be required to change this password the first time you log into APEX. 4. Upload the two renamed files from your device into your APEX schema using the APEX Scripts tool. 5. Run the first script file until you receive no errors. a. This may take 2-3 runs. b. If you still have errors after run 3 , please let me know. 6. After the first script file runs successfully, run the second script file until you receive no errors. a. This may take 2-3 runs. b. If you still have errors after run 3 , please let me know. 7. I will grade this part of the assignment directly in APEX. 8. Then, complete the following questions and submit these for grading. 19. Which table has unique constraint? On which column is the unique constraint? Use format Jable. Name. Columnan Name. 20. How does this constraint impact the data that can be entered into this column? 21. Which table and column does the foreign key constraint with the name FK_EMP_DEPT reference that is contained in the EMPLOYEES table? Use format Table Name.Columan Name. 22. Which table and column does the foreign key constraint with the name FK_EMP_JOB reference that is contained in the EMPLOYEES table? Use format Table Name Columnon Name. 23. Which table and column does the foreign key constraint with the name FK_JOB_HIST_DEPT reference that is contained in the JOB_HISTORY table? Use format Table Name. Columnoname. 24. Which table and column does the foreign key constraint with the name FK_EMP_MGR reference that is contained in the EMPLOYEES table? Use format Table Name Columononame. 25. Why is the foreign key constraint necessary? That is, what is the purpose of foreign keys? Answer these miscellaneous questions. 26. How many countries are in the database? 27. What departments are located at 2004 Charade Rd, Seattle, WA 98199