Task 18 (100 points. Given the structure of the ConstructCo database shown above, use SQL commands to answer the problems in the following steps. 1. Write the SQL code required to list the employee number, last name, first name, and middle initial of all employees Sort the results by employee number. 2. Write SQL statement that selects only the DISTINCT values from the "Job_code" column in the "Employee" table. Sort the results by the Job_code in ascending order. 3. Write a SQL statement that selects Emp_Num. EMPLNMAE, EMP_FNAME of all employees and creates two aliases, one for the EMP_FNAME column as First Name and one for the EMP LNMAE columnas Last Name. Sort the results the EMP ENAME and EMP_LNMAE in ascending order. 4. Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted by project number in descending order. 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). Sort the results by the assignment number in ascending order. Open SSMS and create a database named "ConstructCo. Here is a link shows how to create a Database in SQL Server Management Studio Download the script file, named "ConstructCoScript.sql. Open the script and hit run. Hit refresh and open Tables. You should be able to see the following: Employee, Job, Projects, and Assignment tables with some data. The following image shows the ConstructCo database. Database name: Choz ConstructCo Table name: EMPLOYEE Relational diagram TOR OD 50 120.00 ++ 20 A 10 AV OL 200 00 10 Mar D Larry OR 10 11 112 * A. 04 Dew A. . 110 re Table nanges JOB Table names ASSIGNMENT Table name: PROJECT uctCo database. 13 Database name: Ch07_ConstructCo Table name: EMPLOYEE EMEINUM EMPENAME EMEFNAME EMPIENTIAL ENDEMREDATE 08_CODE BMP YEARS 101 NEWS John 08-Nov.00 502 17 102 Senior David 1289 501 1103 Arbough E 01 Dec. 96 500 21 104 Ramos Anne 15-NOV-87 501 30 105 Johnson Alice K 01-Feb-93 502 25 106 Smithfield War 22-Jun-04 500 107 Alonzo Mario D 10-Oct-93 500 100 Washington Ralph B 22-Aug-91 501 26 w 109 1897 501 LOTY Smin 110 Olen Gerald 11-Dec-95 505 111 wa Geor B 27 04.Apr.91 505 M Durere 23 23-Oct-94 507 112 Smithson 113 Joenbrood Delbert K 15-Nov-96 500 20-Aug-93 500 Jones Anne 115 Travis B 25-Jan-92 501 05-Mor.97 510 116 Pro Gerald 112 21 H 19-Jun-95 509 Angie woon 13 118 04 Jan-Os 510 J Jos From 24 114 BO ame: ASSIGNMENT Table name: JOB JOB_CODE JOB DESCRIPTION 500 Programmer 501 Systems Analyst 502 Database Designer SOB Electrical Engineer 504 Mechanical Engineer SOS Chi Engineer 506 Clerical Support 507 DSS Analyst 08 Applications Designer S09 Bio Technician S10 General Support JOB CHO MOUR 35.75 96.75 125.00 34 50 67 90 55.78 26.87 45.95 48.10 34.55 18:36 JOB LAST UPDATE 20-NOV-17 20-Nov-17 24-M 18 20-NOV 20-NOV 20-Nov-17 20-Nov17 20-Nov.17 24- M18 20 NO.1 20 Nov 17 Table name: PROJECT PROJ NUM PROJ_NAME PROJ VALUE 15 Evergreen 145350000 18 Amber Wave 350050000 22 Rolling Tide 805000.00 25 Starlight 2650500.00 PROJ BALANCE EMP_NUM 1002350.00 103 2110346 00 108 500345 20 102 2309880.00 107 ASSONE CHO HLASON HOURS AGO LONGE 35 20575 34 55 3455 55 96.75 96 75 50 75 212 403 200 0004 09 16.0 96.75 3456 105 24 C Table name: ASSIGNMENT ASSONNUMLASSKONTOATE PROVINCOMEMNUM LAUSION 00 1001 22-M8 103 503 1002 22 M18 22 117 509 1000 22-M-1010 500 100 22.M.18 18 103 503 100 22- M18 25 108 501 TOO 22-18 22 104 501 22 M-18 25 113 500 22 Mar.1.10 103 500 1000 23- M.1895 115 501 1010 23-M-16.15 117 500 011 23-M-16.25 105 500 1012 23-18 10 100 SOU 0 230 25 115 SOS 1014 23- M18 22 104 501 TOTS 23.M.18 15 103 503 016 23.10 22 TO 502 017 2.10 117 500 101 2 M1025 11 so 24.M.18 25 104 501 00 300 M-1615 102 501 TO 24 M1822 1002 Mw.10 22 501 105 1023 24.16.22 105 502 103 24 MW.10 18 103 1004 M. 39594 2709 95.75 9075 345 10 3455 $105 SO GIOR RO 27 1105 1105 STO 33 CO BO 201 3 ROOL SO 200 COS IOS IOS OS COS 30 Table name: ASSIGNMENT ASSONENUMLASSION DATE OUI NOMENNUM LASSONO 1001 22.M18:10 103 502 1002 22 M18 22 117 502 1003 22-M-1010 117 500 100 22- M1018 TOS 22 M18 25 108 22 M-18 22 104 22-1825 113 000 22-Mart 103 100 23 M.18 15 115 501 10 23 M-18 15 107 500 011 23-M-16.25 105 1012 23 18 18 100 230 25 115 1014 23-M 18:22 104 500 TOTS 23.18 15 109 503 1016 23-10 22 TO 500 TO 2.10 10 11 500 1010 M1025 112 09 104 24. Mw.18 25 501 1010 SO 1615 300 OS 10 501 TO 2.M1822 501 Mw.10 22 102 24.16.22 105 1023 502 1024 24 M-1018 103 TIT 100% 30.10 C ASSONE CHO HRASEON HOURS AGO 35 34 55 34 55 045 96.75 22 96 75 5075 545 09 90.75 1455 105 0675 9575 3075 545 105 34 55 3455 $105 31 1105 27 1105 DOS LOS VOS 2703 4035 780 DI COS STO CE 42 14511