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.

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.

FIGURE P8.1 THE CH08_CONSTRUCTCO DATABASE Database name: Ch08_ConstructCo Relational diagram Table name: EMPLOYEE JOB JOB_CODE JOB DESCRIPTION JOB_CHG_HOUR JOB_LAST UPDATE 00 ASSIGNMENT Y ASSIGN_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGNUJOB ASSIGN_CHG_HR ASSIGN_HOURS ASSIGN CHARGE Alice EMPLOYEE EMP_NUM EMP LNAME EMP FNAME EMP INITIAL EMP HIREDATE JOB_CODE EMP_VEARS 00 Table name: JOB Travis JOB_CODE JOB_DESCRIPTION JOB_CHG_HOUR JOB_LAST_UPDATE 500 Programmer 35.75 20-Nov-17 501 Systems Analyst 96.75 20-Nov-17 502 Database Designer 125.00 24-Mar-18 503 Electrical Engineer 84.50 20-Nov-17 504 Mechanical Engineer 67.90 20-Nov-17 505 Civil Engineer 100 55.78 20-Nov-17 506 26.87 20-Nov-17 00 20.01 20-NOV- 507 DSS Analyst 45.95 20-Nov-17 508 Applications Designer 48.10 24-Mar-18 509 Bio Technician 34.55 20-Nov-17 510 General Support 18.36 20-Nov-17 EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE EMP_YEARS 101 News John G 08-Nov-00 502 12 102 Senior David H 12-Jul-89 501 23 103 Arbough June E 01-Dec-96 500 18 104 Ramoras Anne K 15-Nov-87 501 25 105 Johnson K 01-Feb-93 502 19 PROJECT 106 Smithfield William 22-Jun-04 500 8 Y PROJ_NUM 107 Alonzo Maria D 10-Oct-93 500 19 PROJ_NAME PROJ_VALUE 108 Washington Ralph B 22-Aug-91 501 21 PROJ_BALANCE 109 Smith Larry w 18-Jul-97 501 15 EMP_NUM 110 Olenko Gerald 11-Dec-95 505 17 111 Wabash Geoff 04-Apr-91 506 21 112 Smithson Darlene M 23-Oct-94 507 18 113 Joenbrood Delbert K 15-Nov-96 508 16 114 Jones Annelise 20-Aug-93 508 19 115 Bawangi B 25-Jan-92 501 20 116 Pratt Gerald L 05-Mar-97 510 15 117 Williamson Angie H 19-Jun-96 509 16 118 Frommer James J 04-Jan-05 510 7 Table name: ASSIGNMENT ASSIGN_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOB ASSIGN_CHG_HR ASSIGN_HOURS ASSIGN_CHARGE 1001 22-Mar-18 18 103 503 84.50 3.5 295.75 1002 22-Mar-18 22 117 509 34.55 4.2 145.11 1003 22-Mar-18 18 117 509 34.55 2.0 69.10 1004 22-Mar-18 18 103 SO3 84.50 5.9 498.55 1005 22-Mar-18 25 108 501 96.75 2.2 212.85 4000 200 1006 22-Mar-10 22 104 501 96.75 4.2 406.35 1007 42 co CO E 22-Mar-18 25 1007 20 113 10 OC 508 50.75 3.B 192.85 1008 102 22-Mar-18 1B 103 503 502 84.50 76.05 1009 23-Mar-18 15 115 501 96.75 58 5.6 541.80 1010 23-Mar-18 15 117 117 509 34.55 2.4 82.92 1011 105 23-Mar-18 25 Tom TUS 502 105.00 4.3 4.5 451.50 1012 23-Mar-18 18 TUT2 108 501 OUT 96.75 3.4 328.95 23-Mar-18 25 115 501 96.75 2.0 To Wou 20! 2.0 T. 193.50 1014 23-Mar-18 22 104 501 TO 2.B 96.75 270.90 www. 0! 40.00 1015 23-Mar-18 15 103 503 84.50 6.1 515.45 one 1016 23-Mar-18 22 105 502 105.00 4.7 "w.co 493.50 Tod.de 1017 23-Mar-18 18 117 509 34.55 3.B Www 131.29 1018 23-Mar-18 25 117 509 34.55 2.2 76.01 1019 24-Mar-10 25 104 501 110.50 4.9 541.45 2000 1020 24-Mar-18 15 101 502 125.00 3.1 387.50 1021 24-Mar-18 22 108 501 110.50 2.7 298.35 1022 24-Mar-18 22 115 501 110.50 4.9 541.45 24-Mar-18 22 105 502 125.00 3.5 437.50 1024 24-Mar-18 15 103 503 84.50 3.3 278.85 1025 24-Mar-18 1B 117 509 34.55 4.2 145.11 Clerical Support Table name: PROJECT PROJ_NUM PROJ_NAME PROJ_VALUE PROJ_BALANCE EMP_NUM 15 Evergreen 1453500.00 1002350.00 103 18 Amber Wave 3500500.00 2110346.00 108 22 Rolling Tide 805000.00 500345.20 102 25 Starflight 2650500.00 2309880.00 107 1013 1023 Field Type EMP_NUM varchar(3) EMP_LNAME varchar(15) EMP_FNAME varchar(15) EMP_INITIAL varchar(1) EMP_HIREDATE date JOB_CODE varchar(3) EMP_NUM EMP_LNAME EMP FNAME EMP_INITIAL EMP_HIREDATE JOB_CODE 101 News John 2000-11-08 502 102 Senior David H 1989-07-12 501 103 Arbough June E 1996-12-01 500 104 Ramoras Anne 1987-11-15 501 105 Johnson Alice K 1993-02-01 502 106 Smithfield William NULL 2004-06-22 500 107 Alonzo Maria D 1993-10-10 500 108 Washington Ralph B 1991-08-22 501 109 Smith Larry W 1997-07-18 501