1. Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. Relational diagram JOB 500 501 502 503 504 505 506 507 508 Table name: JOB JOB_CODE JOB DESCRIPTION Programmer Systems Analyst Database Designer Electrical Engineer Mechanical Engineer 509 510 JOB CODE JOB DESCRIPTION JOB_CHG_HOUR JOB_LAST_UPDATE EMPLOYEE EMP NUM EMP_LNAME 15 18 22 25 Civil Engineer Clerical Support DSS Analyst Applications Designer Bio Technician General Support EMP_FNAME EMP INITIAL EMP HIREDATE JOB_CODE EMP YEARS JOB_CHO_HOUR 35.75 96.75 125.00 84.50 67.90 55.78 26.87 45.95 48.10 34.55 18.36 Table name: PROJECT PROJ_NUM PROJ_NAME PROJ_VALUE Evergreen 1453500.00 Amber Wave Rolling Tide 3500500.00 805000.00 2650500.00 Starflight ASSIGNMENT ASSIGN_NUM ASSIGN DATE PROJ_NUM EMP_NUM ASSIGN JOB ASSIGN_CHG_HR ASSIGN HOURS ASSIGN_CHARGE JOB_LAST_UPDATE | 20-Nov-17 20-Nov-17 24-Mar-18 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 20-Nov-17 24-Mar-18 20-Nov-17 20-Nov-17 DO PROJ_BALANCE EMP_NUM 1002350.00 103 2110346.00 108 500345.20 102 2309880.00 107 PROJECT PROJ NUM PROJ_NAME PROJ_VALUE PROJ_BALANCE EMP NUM 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 Table name: EMPLOYEE EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_YEARS 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 News Senior John David June Anne Alice William Maria Washington Ralph Larry Gerald Geoff Darlene Delbert Annelise Travis Gerald Angie James Arbough Ramoras Johnson Smithfield Alonzo Smith Olenko Wabash Smithson Joenbrood Jones Bawangi Pratt Williamson Frommer 22-Mar-18 18 22-Mar-18 22 22-Mar-18 18 22-Mar-18 18 22-Mar-18 25 22-Mar-18 22 22-Mar-1B 25 22-Mar-18 18 23-Mar-18 15 23-Mar-18 15 23-Mar-18 25 23-Mar-18 18 23-Mar-18 25 23-Mar-18 22 23-Mar-18 15 23-Mar-18 22 23-Mar-18 18 23-Mar-18 25 24-Mar-18 25 24-Mar-18 15 24-Mar-18 22 24-Mar-18 22 24-Mar-18 22 24-Mar-10 15 24-Mar-18 18 103 117 117 103 108 104 113 103 115 117 105 108 115 104 103 105 117 117 104 101 108 115 105 103 117 Table name: ASSIGNMENT ASSIGN NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHG_HR ASSIGN HOURS ASSIGN_CHARGE 1001 503 509 509 503 501 501 500 503 501 509 502 501 501 501 503 502 Database name: Ch08_ConstructCo 509 509 501 502 501 501 502 503 509 G H E K K D B W A 8 M K B L H J 84.50 34.55 34.55 84.50 93.75 96.75 50.75 84.50 96.75 34.55 105.00 96.75 95.75 96.75 84.50 105.00 34.55 34.55 110.50 125.00 110.50 110.50 125.00 01-Feb-93 502 22-Jun-04 500 10-Oct-93 500 84.50 34.55 22-Aug-91 501 18-Jul-97 501 11-Dec-95 505 04-Apr-91 506 23-Oct-94 507 15-Nov-96 508 20-Aug-93 508 25-Jan-92 501 05-Mar-97 510 19-Jun-96 509 04-Jan-05 510 3.5 4.2 2.0 5.9 2.2 4.2 3.8 0.9 5.6 2.4 4.3 3.4 2.0 2.8 6.1 4.7 3.8 2.2 4.9 3.1 2.7 4.9 3.5 3.3 4.2 295.75 145.11 69.10 498.55 212.05 406.35 192.85 76.05 541.80 82.92 451.50 328.95 193.50 270.90 515.45 493.50 131.29 76.01 541.45 387.50 298.35 541.45 437.50 270.05 145.11 12 23 18 25 19 8 19 21 15 17 21 18 16 19 20 15 16 7 8. Using the EMP_2 table, write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to EMP_2. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are: EMP_PCT PROJ_NUM NUMBER(4,2) CHAR(3) Note: If your SQL implementation requires it, you may use DECIMAL(4,2) or NUMERIC(4,2) rather than NUMBER(4,2). 9. Using the EMP_2 table, write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. 10. Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 5.00 for the people with employee numbers 101, 105, and 107. 11. Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 10.00 for all employees who do not currently have a value for EMP_PCT. 12. Using the EMP_2 table, write the SQL command to add .15 to the EMP_PCT of the employee whose name is Maria D. Alonzo. (Use the employee name in your command to determine the correct employee.) 13. Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500. 14. Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job classification (JOB_CODE) is 502 or higher. 1. Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. 2. Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P8.2. Each row should be inserted individually, without using a subquery. Insert the rows in the order that they are listed in the figure. Figure P8.2 The Contents of the EMP_1 Table EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 101 102 103 104 105 106 107 108 109 News Senior John David June Anne Alice William Maria Washington Ralph Smith Larry Arbough Ramoras Johnson Smithfield Alonzo G XXMI H E K K D B W 22-Jun-04 500 10-Oct-93 500 22-Aug-91 501 18-Jul-97 501 3. Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure. 4. Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS). 5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. 6. Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem. Remember, if you are using MySQL, you will have to first disable "safe mode.") 7. Write the SQL code to create a copy of EMP_1, including all of its data, and naming the copy EMP_2. 15. Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 7, 8, 9, 10, 11, 12, 13, 14, and 15, the EMP_2 table will contain the data shown in Figure P8.15. Figure P8.15 The EMP_2 Table Contents EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL 101 News Senior 102 103 104 105 107 108 109 Arbough Ramoras Johnson Alonzo Washington Smith John David June Anne Alice Maria Ralph Larry G H E K K D B W EMP_HIREDATE JOB_CODE EMP_PCT | PROJ_NUM + 5 25 10 14 08-Nov-00 502 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 10-Oct-93 501 22-Aug-91 501 18-Jul-97 501 3.85 18 10 14 5 14 5.15 14 10 14 10 Table P8.1 Constructco Database Structure ATTRIBUTE (FIELD) DATA DECLARATION NAME EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE CHAR(3) VARCHAR(15) VARCHAR(15) CHAR(1) DATE CHAR(3)