Use the attached code to create and populate ConstructCo

Write the SQL queries to answer the following questions based on the ConstructCo database. Save your queries in a text or a Word file and submit here.

1. Write the SQL code required to list the employee number, first and last name, middle initial, and the hire date. Sort your selection by the hire date, to display the newly hired employees first.

2. Modify the previous query and list the employee first, last name and middle initial as one column with the title EMP_NAME. Make sure the names are listed in the following format: First name, space, initial, space, last name (e.g. John T Doe). Hint: use + to concatenate the fields and use ' ' to add a space to your text. E. g. EMP_FNAME + ' ' + EMP_LNAME.

3. Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted by project number.

4. Using the EMPLOYEE and PROJECT tables, write the SQL code that will join the tables on their common attribute and display the names and numbers of the projects and the employees who lead these projects.

5. Modify the query in 5 to display all employees and not just the ones who lead projects.

6. Using the EMPLOYEE, JOB, and PROJECT tables in the ConstructCo database, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in the attached screen shot, sorted by project value.

7. Using JOB and EMPLOYEE tables, list the jobs and the names of employees who currently have these job categories. List all jobs, even the ones that do not have any matches in the EMPLOYEE table.

8. Write a query to list the names of all employees, the names of the projects to which they are assigned, and the name of employees who lead these projects.

Hint for Question 8: in this question, you will need to join EMPLOYEE to ASSIGNMENT to PROJECT to EMPLOYEE again. Being in the join twice, EMPLOYEE needs aliases:

SELECT ... FROM EMPLOYEE E1 LEFT JOIN ASSIGNMENT ON ... LEFT JOIN PROJECT ON ... LEFT JOIN EMPLOYEE E2 ON ...

When you have aliases, every field from the table should be preceded by the table alias, e.g. SELECT E1.EMP_LNAME, not SELECT EMP_LNAME.

CODE:  /* Database Systems, Coronel/Morris */ /* Type of SQL : SQL Server */ CREATE TABLE ASSIGNMENT ( ASSIGN_NUM int, ASSIGN_DATE datetime, PROJ_NUM varchar(3), EMP_NUM varchar(3), ASSIGN_JOB varchar(3), ASSIGN_CHG_HR numeric(8,2), ASSIGN_HOURS numeric(8,2), ASSIGN_CHARGE numeric(8,2) ); INSERT INTO ASSIGNMENT VALUES('1001','3/22/2018','18','103','503','84.5','3.5','295.75'); INSERT INTO ASSIGNMENT VALUES('1002','3/22/2018','22','117','509','34.55','4.2','145.11'); INSERT INTO ASSIGNMENT VALUES('1003','3/22/2018','18','117','509','34.55','2','69.10'); INSERT INTO ASSIGNMENT VALUES('1004','3/22/2018','18','103','503','84.5','5.9','498.55'); INSERT INTO ASSIGNMENT VALUES('1005','3/22/2018','25','108','501','96.75','2.2','212.85'); INSERT INTO ASSIGNMENT VALUES('1006','3/22/2018','22','104','501','96.75','4.2','406.35'); INSERT INTO ASSIGNMENT VALUES('1007','3/22/2018','25','113','508','50.75','3.8','192.85'); INSERT INTO ASSIGNMENT VALUES('1008','3/22/2018','18','103','503','84.5','0.9','76.05'); INSERT INTO ASSIGNMENT VALUES('1009','3/23/2018','15','115','501','96.75','5.6','541.80'); INSERT INTO ASSIGNMENT VALUES('1010','3/23/2018','15','117','509','34.55','2.4','82.92'); INSERT INTO ASSIGNMENT VALUES('1011','3/23/2018','25','105','502','105','4.3','451.5'); INSERT INTO ASSIGNMENT VALUES('1012','3/23/2018','18','108','501','96.75','3.4','328.95'); INSERT INTO ASSIGNMENT VALUES('1013','3/23/2018','25','115','501','96.75','2','193.5'); INSERT INTO ASSIGNMENT VALUES('1014','3/23/2018','22','104','501','96.75','2.8','270.9'); INSERT INTO ASSIGNMENT VALUES('1015','3/23/2018','15','103','503','84.5','6.1','515.45'); INSERT INTO ASSIGNMENT VALUES('1016','3/23/2018','22','105','502','105','4.7','493.5'); INSERT INTO ASSIGNMENT VALUES('1017','3/23/2018','18','117','509','34.55','3.8','131.29'); INSERT INTO ASSIGNMENT VALUES('1018','3/23/2018','25','117','509','34.55','2.2','76.01'); INSERT INTO ASSIGNMENT VALUES('1019','3/24/2018','25','104','501','110.5','4.9','541.45'); INSERT INTO ASSIGNMENT VALUES('1020','3/24/2018','15','101','502','125','3.1','387.5'); INSERT INTO ASSIGNMENT VALUES('1021','3/24/2018','22','108','501','110.5','2.7','298.35'); INSERT INTO ASSIGNMENT VALUES('1022','3/24/2018','22','115','501','110.5','4.9','541.45'); INSERT INTO ASSIGNMENT VALUES('1023','3/24/2018','22','105','502','125','3.5','437.5'); INSERT INTO ASSIGNMENT VALUES('1024','3/24/2018','15','103','503','84.5','3.3','278.85'); INSERT INTO ASSIGNMENT VALUES('1025','3/24/2018','18','117','509','34.55','4.2','145.11'); /* -- */ CREATE TABLE EMPLOYEE ( EMP_NUM varchar(3), EMP_LNAME varchar(15), EMP_FNAME varchar(15), EMP_INITIAL varchar(1), EMP_HIREDATE datetime, JOB_CODE varchar(3), EMP_YEARS int ); INSERT INTO EMPLOYEE VALUES('101','News','John','G','11/8/2000','502','4'); INSERT INTO EMPLOYEE VALUES('102','Senior','David','H','7/12/1989','501','15'); INSERT INTO EMPLOYEE VALUES('103','Arbough','June','E','12/1/1996','503','8'); INSERT INTO EMPLOYEE VALUES('104','Ramoras','Anne','K','11/15/1987','501','17'); INSERT INTO EMPLOYEE VALUES('105','Johnson','Alice','K','2/1/1993','502','12'); INSERT INTO EMPLOYEE VALUES('106','Smithfield','William','','6/22/2004','500','0'); INSERT INTO EMPLOYEE VALUES('107','Alonzo','Maria','D','10/10/1993','500','11'); INSERT INTO EMPLOYEE VALUES('108','Washington','Ralph','B','8/22/1991','501','13'); INSERT INTO EMPLOYEE VALUES('109','Smith','Larry','W','7/18/1997','501','7'); INSERT INTO EMPLOYEE VALUES('110','Olenko','Gerald','A','12/11/1995','505','9'); INSERT INTO EMPLOYEE VALUES('111','Wabash','Geoff','B','4/4/1991','506','14'); INSERT INTO EMPLOYEE VALUES('112','Smithson','Darlene','M','10/23/1994','507','10'); INSERT INTO EMPLOYEE VALUES('113','Joenbrood','Delbert','K','11/15/1996','508','8'); INSERT INTO EMPLOYEE VALUES('114','Jones','Annelise','','8/20/1993','508','11'); INSERT INTO EMPLOYEE VALUES('115','Bawangi','Travis','B','1/25/1992','501','13'); INSERT INTO EMPLOYEE VALUES('116','Pratt','Gerald','L','3/5/1997','510','8'); INSERT INTO EMPLOYEE VALUES('117','Williamson','Angie','H','6/19/1996','509','8'); INSERT INTO EMPLOYEE VALUES('118','Frommer','James','J','1/4/2005','510','0'); /* -- */ CREATE TABLE JOB ( JOB_CODE varchar(3), JOB_DESCRIPTION varchar(25), JOB_CHG_HOUR numeric(8,2), JOB_LAST_UPDATE datetime ); INSERT INTO JOB VALUES('500','Programmer', '35.75','11/20/2017'); INSERT INTO JOB VALUES('501','Systems Analyst', '96.75','11/20/2017'); INSERT INTO JOB VALUES('502','Database Designer', '125', '3/24/2018'); INSERT INTO JOB VALUES('503','Electrical Engineer', '84.5', '11/20/2017'); INSERT INTO JOB VALUES('504','Mechanical Engineer', '67.9', '11/20/2017'); INSERT INTO JOB VALUES('505','Civil Engineer', '55.78','11/20/2017'); INSERT INTO JOB VALUES('506','Clerical Support', '26.87','11/20/2017'); INSERT INTO JOB VALUES('507','DSS Analyst', '45.95','11/20/2017'); INSERT INTO JOB VALUES('508','Applications Designer','48.1', '3/24/2018'); INSERT INTO JOB VALUES('509','Bio Technician', '34.55','11/20/2017'); INSERT INTO JOB VALUES('510','General Support', '18.36','11/20/2017'); /* -- */ CREATE TABLE PROJECT ( PROJ_NUM varchar(3), PROJ_NAME varchar(25), PROJ_VALUE numeric(10,2), PROJ_BALANCE numeric(10,2), EMP_NUM varchar(3) ); INSERT INTO PROJECT VALUES('15','Evergreen','1453500','1002350','103'); INSERT INTO PROJECT VALUES('18','Amber Wave','3500500','2110346','108'); INSERT INTO PROJECT VALUES('22','Rolling Tide','805000','500345.2','102'); INSERT INTO PROJECT VALUES('25','Starflight','2650500','2309880','107');