Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

How can I fix this statment to do the following Using JOB and EMPLOYEE tables, list the jobs and the names of employees who currently

How can I fix this statment to do the following

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.

CODE Statment:

SELECT * FROM JOB J LEFT JOIN EMPLOYEE E WHERE J.JOB_CODE = E.JOB_CODE

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Professional Visual Basic 6 Databases

Authors: Charles Williams

1st Edition

1861002025, 978-1861002020

More Books

Students also viewed these Databases questions

Question

1 of 4 Answered: 1 week ago

Answered: 1 week ago