The Ch07_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 and contents of the Ch07_ConstructCo database are shown in Figure P7.1 below.

5. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (CALC_ASSIGN_CHARGE, calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS and rounded to two decimal places). Sort the results by the assignment number. The results of running that query are shown in Figure P7.5.

6. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked, sorted by employee number. The results of running that query are shown in Figure P7.6.

7. Write a query to produce the total number of hours and charges for each of the projects represented in the ASSIGNMENT table, sorted by project number. The output is shown in Figure P7.7.

8. Write the SQL code to generate the total hours worked and the total charges made by all employees. The results are shown in Figure P7.8.

FIGURE P7.1 THE CHOZ_CONSTRUCTCO DATABASE Relational diagram Database name: Ch07_ConstructCo Table name: EMPLOYEE ASSIGNMENT EMP_NUM EMP_LNAME EMP_FNAME EMP_NTIAL BMP_HIREDATE JOB_CODE EMP_YEARS PK Asian Num 101 News John 08-Nov-00 502 17 Assign Date 102 Senior David H 12-Jul-89 501 28 JOB EMPLOYEE PKZ Pro Num 103 June Arough FK1 Emp Num E 01-Dec-06 500 PK me Num PK Job Code 21 PROJECT Assignol FH---O --- 104 Ramores K Arne 15 Nov 87 501 30 Anb_Description Imp Lame Assign_che_Hr --HPK Praj Num 105 Emp FName Johnson K Assign_Hours Alice Job Chr_Hour 01-Feb-93 502 25 oblast_update Emp Initial Assign Charge Pro Name 106 Smithfield Wiliam 22-Jun-04 500 13 Emp Hire Date Proj. Value FK EH OX Ki Jab_code 107 Praj Balance Alonzo Maria D 10-Oct. COD 24 Emp_Years FKA Emp Num 10B Washington Ralph B 22-Aug-91 501 28 109 Smith Larry WV 18-Jul-97 401 21 110 Olenko Gerald A 11-Dec-96 505 22 111 Wabash Geort B 04-Apr-91 505 27 112 Smithson Darlene M 23-Oct-94 507 23 113 Joenbrood Delbert K 15-Nov-96 508 21 Table name: JOB 114 Jones Arnelise 20-Aug-80 500 24 115 Dawrangi Travis B 25-Jan-92 501 20 JOB_CODE JOB_DESCRIPTION JCB_CHO_HOR JOB_LAST_UPDATE 11B Pratt Gerald L 05-Mar-97 510 21 SID Programmer 35.75 20-luv-17 117 Viliamson . 19-Jun-96 909 501 86.75 Argic 21 Systems Analyst 20. Mov-17 S02 Database Designer 125.00 11B 24-Mar-18 Frommer J Jainco D4-Jan-05 510 13 503 Electrical Engineer B4.50 20-Nov-17 504 Mechanical Engineer 67.90 Table name: ASSIGNMENT 20-pov-17 SOS QMI Engineer 55.78 20-luoy-17 - ASSION_NUM ASSIGN_DATE PROJ_NUM EMP_NUM ASSIGN_JOE ASSIGN_CHO_HR ASSIGN_HOURS ASSIGN_CHARGE 506 Clerical Support 20.07 20. Nov. 17 1001 22- Mar-18 18 103 503 3.5 295.75 SOT DOS Analyst 45.95 20-luv-17 22- Mar-18 22 117 509 34 55 4.2 145.11 508 spications Designer 18.10 24-Mar-18 1008 22- Mar-18 18 117 509 34.55 2 691 509 Bo Technicien 34.SS 20..17 1004 22- Mar-18 18 103 503 845 59 498.56 510 General Support 18.35 20-Nov-17 1006 22-Mar-18 25 108 501 96.75 212.85 1006 22-Mar-18 22 104 501 96.75 4.2 408.38 1007 22- Mar-18 25 113 508 50.75 192.86 1008 22-Mar-18 18 108 503 84.5 0.9 78.05 1009 23- Mar-18 15 115 501 96.75 Table name: PROJECT 5.8 5418 1010 23- Mar-18 15 117 509 34.55 2.4 82.92 PROJ_NLM PROJ_NAVE 1011 PROJ_VALUE PROU_BALANCE EMP_NUM 23-Mar-18 25 105 502 105 4.3 4515 15 1012 108 23- Mar-18 18 501 96.75 3.4 Evergreen 1453500.00 1002350 00 103 328.85 1013 23- Mar-18 25 115 501 96.75 2 193.5 13 Amber Wave 3600500.00 2110345 00 100 1014 23-Mar-18 22 104 501 96.75 2.8 2709 22 Roling Tide SUS000.00 SODE 45 20 102 1015 23- Mar-18 15 103 503 84.5 61 515.45 25 Starlight 2E50500.00 2309880 00 107 1016 23-Mar-10 22 105 302 105 4.7 4995 1017 29-Mar-10 10 117 309 $4.55 3.8 131.29 1010 23-Mar-18 25 117 309 $4.55 78.01 1019 24-Mer 18 25 104 501 110.5 4.9 541.45 1020 24-Mar-18 15 101 502 125 3.1 3878 1021 24-Mar-18 22 108 301 110.5 2.7 298.98 1022 24-Mar-18 22 115 301 110.5 4.9 541.45 1023 24-Mer-18 22 105 125 3.5 4375 1024 24- Mar-18 15 106 003 84.5 278.85 1025 24- Mar-18 18 117 34.55 4.2 145.11 1002