Question

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 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');

Answer 1

Question 1:

SQL query :

select EMP_Num,Emp_FName,Emp_LName,Emp_Initial,EMP_HireDate from employee
order by EMP_HireDate desc;

Query result :

*******************************************

Question 2:

SQL query :

select EMP_Num,EMP_FNAME + ' '+Emp_Initial +' ' + EMP_LNAME as EMP_NAME,
EMP_HireDate from employee
order by EMP_HireDate desc;

Query result :

*******************************************

Question 3:

SQL query :

select distinct(proj_num) as 'distinct project numbers' from ASSIGNMENT
order by proj_num ;

Query result :

*******************************************

Question 4:

SQL query :

select proj_name,proj_num,Emp_FName+' '+EMP_LNAME as 'Employee Name'
from project , employee
where
project.emp_num=employee.emp_num;

Query result :

*******************************************

Question 5:

SQL query :

select proj_name,proj_num,Emp_FName+' '+EMP_LNAME as 'Employee Name'
from project right join employee
on
project.emp_num=employee.emp_num;

Query result :

*******************************************

Question 7:

SQL query :

select JOB_Description , Emp_FName +' '+Emp_LName as 'Employee'
from job left join employee
on
job.JOB_CODE=EMPLOYEE.JOB_CODE;

Query result :

*******************************************

Question 8:

SQL query :

select e1.Emp_FName +' '+e1.Emp_LName as 'Employee Name' , Proj_Name,
e2.Emp_FName +' '+e2.Emp_LName as 'Lead Employee Name'
from employee e1 left join ASSIGNMENT on e1.EMP_NUM=ASSIGNMENT.EMP_NUM
left join PROJECT ON ASSIGNMENT.PROJ_NUM=PROJECT.PROJ_NUM
left join EMPLOYEE E2 ON project.emp_num=e2.emp_num;

Query result :

This SQL query join four tables and return result

*******************************************