These pictures are as clear as can be.

Please answer correctly!

The Ch08_ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure of the database are shown in Figure P8.1

Note that the ASSIGNMENT table in Figure P8.1 stores the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change will be reflected in the ASSIGNMENT table. Naturally, the employee primary job assignment might also change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are NOT redundant.

Given the structure and contents of the Ch08_ConstructCo database shown in Figure P8.1, use SQL commands to answer the following problems.

2. Having created the table structure shown in TABLE 8.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.

4. Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS - assuming in Oracle DBMS).

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

8. (EMP_2 table is a copy of EMP_1, please refers to Problem 7) Using 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 NUMBER (4, 2)

PROJ_NUM CHAR(3)

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.

12. Using the EMP_2 table, write the SQL command to add 0.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.)

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

Thanks in advance!

Relational diagram Table EMP NU OB LAST UPDATE 103 104 ASS GAL HOURS ASSG CHARGE 106 107 108 PROLBALANCE 109 110 EMP NU EMP.LNAE EMP.FNAME EMP INIAL EMP HOREDATE PROI NAME PROI,VALUE EMP NUM EMP YEARS Table name: JOB 114 115 116 Programmer Systems Analyst Database Designer Electrical Engineer Mechanical Engineer Civil Engineer Clerical Support oSS Analyst 3575 96.75 125 00 84 50 67 S0 5578 26 67 45 95 48.10 34 55 18 36 20-Now-17 20-Nov-17 24-Mar-18 20-Now-17 20-Nov-17 20-Nov-17 20-Nov-17 20-Now-17 24-Mar-18 SO 502 503 118 Table name: ASSI 505 ASSION, NUM ASSIGN DATE 1001 1002 22-Mar-18 22 1003 22-Mar-18 16 1004 22-Mar-18 18 00522-Mar-18 25 1006 22-Mar-18 22 100722-Mar-18 25 008 22-Mar-18 18 1009 23-Mar-18 15 101023-Mar-18 15 01123-Mar-18 25 1012 23-Mar-18 18 1013 23-Mr-18 25 1014 01523-Mar-18 15 1016 23-Mar-18 22 1017 23-Mar-18 18 01823-Mar-18 25 1019 24-Mar-18 25 22-Mar-18 1 508 509 Blo Technician Generail Support 20-Now-17 Table name: PROJECT | PROJ,NUMI pROJNAME | PROJ-VALUE 18 PROJ-BALANCE EMP,NMI 1002350 00 103 1453500 00 3500500.00 805000 00 2650500 00 Evergreen Amber Wave 2110346.00 108 500345 20 102 2309880 00 107 23-Mar-18 22 Rolling Tide Database haime: Cho6Constructlo Table name: EMPLOYEE PROI NU PROLNAM PROLVALUt PROL, BALANCE109 EMP,NUM Table name: ASSIGNMENT Elc 18-25 9 8 19 21 15 17 21 8 6 9 " 5 6 7 89 96 87 93 04 93 91 97 95 91 94 96 93 92 97 . 52 o9 2 2 a 964', 4 0 8 1 7 8 2 9 1 70, $ 32 50 55 55 50 75 75 75 SO 75 55 75 75 75 50 55 55 , a 8 , s 04 34 34 84 96 96 50 84 96 34 05 96 96 64 OS 34 34 10 25 10 10 25 84 34 EN 103 17 17 103 100 104 13 103 15 17 105 100 15 104 03 105 17 17 104 101 100- Pr Fr 22 18 18 25 22 25 18 15 15 25 18 25 22 15 22 18 25 25 15 22 22 22 15 18 22 22 22 22 22 22 22 02 03 104-105 07-110 11 12 13 14 15 16 17 18 m TABLE P8.1 CONSTRUCTCO DATABASE STRUCTURE ATTRIBUTE (FIELD) NAME EMP NUM EMP_LNAME EMP FNAME EMP INITIAL EMP HIREDATE JOB CODE DATA DECLARATION CHAR(3) VARCHAR(15) VARCHAR(15) CHAR(1) DATE CHAR(3) FIGURE P8.2 THE CONTENTS OF THE EMP 1 TABLE EMP NUM EMP LNAME EMP FNAME EMP NTIAL EMP HIREDATE JOB CODE 08-Nov-00 502 101 102 103 104 105 106 107 108 109 David June Anne Alice 12-Jul-89 501 01-Dec-96 500 15-Nov-87 501 01-Feb-93 502 22-Jun-04 500 10-Oct-93 500 Arbough Ramoras Smithfield William Alonzo Washington Ralph Maria 22-Aug-91 501 Larry 18-Jul-97 501