add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table JOB2019(
JOB_CODE2019 int,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 decimal(5,2),
PRIMARY KEY(JOB_CODE2019 )
);


create table EMPLOYEE2019(
EMP_NUM2019 int,
EMP_LNAME2019 varchar(20),
EMP_FNAME2019 varchar(20),
EMP_INITIAL2019 varchar(1),
EMP_HIREDATE2019 varchar(50),
JOB_CODE2019 int,
primary key (EMP_NUM2019),
foreign key (JOB_CODE2019) references JOB2019(JOB_CODE2019)
);
create table PROJECT2019(
PROJ_NUM2019 int,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 int,
primary key (PROJ_NUM2019),
foreign key (EMP_NUM2019) references EMPLOYEE2019(EMP_NUM2019)
);
create table ASSIGNMENT2019(
ASSIGN_NUM2019 int,
ASSIGN_DATE2019 varchar(50),
PROJ_NUM2019 int,
EMP_NUM2019 int,
ASSIGN_HOURS2019 decimal(5,2),
ASSIGN_CHG_HOUR2019 decimal(5,2),
ASSIGN_CHARGE2019 decimal(7,2) ,
primary key (ASSIGN_NUM2019),
foreign key (PROJ_NUM2019) references PROJECT2019(PROJ_NUM2019),
foreign key (EMP_NUM2019) references EMPLOYEE2019(EMP_NUM2019)
);
insert into JOB2019
values (500,'Programmer',35.75)
,(501,'Systems Analyst',96.75)
,(502,'atabase Designer',105.00)
,(503,'Electrical Engineer',84.50)
,(504,'Mechanical Engineer',67.90)
,(505,'Civil Engineer',55.78)
,(506,'Clerical Support',26.87)
,(507,'SS Analyst',45.95)
,(508,'Applications Designer',48.10)
,(509,'Bio Technician',34.55)
,(510,'General Support',18.36);

insert into EMPLOYEE2019
values (101,'News','John','G','08Nov2000',502)
,(102,'Senior','avid','H','12Jun1989',501)
,(103,'Arbough','June','E','01Dec1997',503)
,(104,'Ramoras','Anne','K','15Nov1988',501)
,(105,'Johnson','Alice','K','01Feb1994',502)
,(106,'Smithfield','William',' ','22Jun2005',500)
,(107,'Alonzo','Maria','','10Oct1994',500)
,(108,'Washington','Ralph','B','22Aug1889',501)
,(109,'Smith','Larry','W','18Jul1999',501)
,(110,'Olenko','Gerald','A','11Dec1996',505)
,(111,'Wabash','Geoff','B','04Apr1989',506)
,(112,'Smithson','arlene','M','23Oct1995',507)
,(113,'Joenbrood','elbert','K','15Nov1994',508)
,(114,'Jones','Annelise',' ','20Aug1991',508)
,(115,'Bawangi','Travis','B','25Jan1990',501)
,(116,'Pratt','Gerald','L','05Mar1995',510)
,(117,'Williamson','Angie','H','19Jun1994',509)
,(118,'Frommer','James','J','04Jan2006',510);
insert INTO PROJECT2019
values (15,'Evergreen',105)
,(18,'Amber Wave',104)
,(22,'Rolling Tide',113)
,(25,'Starflight',101);
insert into ASSIGNMENT2019(ASSIGN_NUM2019,ASSIGN_DATE2019,PROJ_NUM2019,EMP_NUM2019,ASSIGN_HOURS2019,ASSIGN_CHG_HOUR2019)
values(1001,'04Mar2012',15,103,2.6,84.50)
,(1002,'04Mar2012',18,118,1.4,18.36)
,(1003,'05Mar2012',15,101,3.6,105.00)
,(1004,'05Mar2012',22,113,2.5,48.10)
,(1005,'05Mar2012',15,103,1.9,84.50)
,(1006,'05Mar2012',25,115,4.2,96.75)
,(1007,'05Mar2012',22,105,5.2,105.00)
,(1008,'05Mar2012',25,101,1.7,105.00)
,(1009,'05Mar2012',15,105,2.0,105.00)
,(1010,'06Mar2012',15,102,3.8,96.75)
,(1011,'06Mar2012',22,104,2.6,96.75)
,(1012,'06Mar2012',15,101,2.3,105.00)
,(1013,'06Mar2012',25,114,1.8,48.10)
,(1014,'06Mar2012',22,111,4.0,26.87)
,(1015,'06Mar2012',25,114,3.4,48.10)
,(1016,'06Mar2012',18,112,1.2,45.95)
,(1017,'06Mar2012',18,118,2.0,18.36)
,(1018,'06Mar2012',18,104,2.6,96.75)
,(1019,'06Mar2012',15,103,3.0,84.50)
,(1020,'07Mar2012',22,105,2.7,105.00)
,(1021,'08Mar2012',25,108,4.2,96.75)
,(1022,'07Mar2012',25,114,5.8,48.10)
,(1023,'07Mar2012',22,106,2.4,35.75);

Update ASSIGNMENT2019
set ASSIGN_CHARGE2019 = ASSIGN_HOURS2019 * ASSIGN_CHG_HOUR2019;

SELECT t2.JOB_CODE2019,maxcharge,MIN(CONCAT(t1.EMP_FNAME2019,' ' ,t1.EMP_LNAME2019)) empnamemax,mincharge,
MIN(CONCAT(t3.EMP_FNAME2019,' ' ,t3.EMP_LNAME2019)) empnamemin
FROM
(select t.JOB_CODE2019,MAX(t.SUM_CHARGE) maxcharge,MIN(t.SUM_CHARGE) mincharge
from
(select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
from ASSIGNMENT2019 a inner join EMPLOYEE2019 e
on a.EMP_NUM2019 = e.EMP_NUM2019
inner join JOB2019 j
on e.JOB_CODE2019 = j.JOB_CODE2019
group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019)t
group by t.JOB_CODE2019) t2
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
from ASSIGNMENT2019 a inner join EMPLOYEE2019 e
on a.EMP_NUM2019 = e.EMP_NUM2019
inner join JOB2019 j
on e.JOB_CODE2019 = j.JOB_CODE2019
group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t1
ON t2.JOB_CODE2019 = t1.JOB_CODE2019 AND t2.maxcharge = t1.SUM_CHARGE
INNER JOIN (select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
from ASSIGNMENT2019 a inner join EMPLOYEE2019 e
on a.EMP_NUM2019 = e.EMP_NUM2019
inner join JOB2019 j
on e.JOB_CODE2019 = j.JOB_CODE2019
group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019)t3
ON t2.JOB_CODE2019 = t3.JOB_CODE2019 AND t2.mincharge = t3.SUM_CHARGE
GROUP BY t2.JOB_CODE2019;

JOB_CODE2019 maxcharge empnamemax mincharge empnamemin
503 633.75 June Arbough 633.75 June Arbough
510 62.42 James Frommer 62.42 James Frommer
501 406.35 Ralph Washington 251.55 Anne Ramoras
502 829.50 Alice Johnson 178.50 John News
508 529.10 Annelise Jones 120.25 elbert Joenbrood
506 107.48 Geoff Wabash 107.48 Geoff Wabash
507 55.14 arlene Smithson 55.14 arlene Smithson
500 85.80 William Smithfield 85.80 William Smithfield
select DISTINCT e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019, a.PROJ_NUM2019,SUM(a.ASSIGN_CHARGE2019) as SUM_CHARGE
from ASSIGNMENT2019 a inner join EMPLOYEE2019 e
on a.EMP_NUM2019 = e.EMP_NUM2019
inner join JOB2019 j
on e.JOB_CODE2019 = j.JOB_CODE2019
group by a.PROJ_NUM2019,e.JOB_CODE2019,a.EMP_NUM2019, EMP_LNAME2019 , EMP_FNAME2019
ORDER BY a.PROJ_NUM2019, SUM_CHARGE
JOB_CODE2019 EMP_NUM2019 EMP_LNAME2019 EMP_FNAME2019 PROJ_NUM2019 SUM_CHARGE
502 105 Johnson Alice 15 210.00
501 102 Senior avid 15 367.65
502 101 News John 15 619.50
503 103 Arbough June 15 633.75
507 112 Smithson arlene 18 55.14
510 118 Frommer James 18 62.42
501 104 Ramoras Anne 18 251.55
500 106 Smithfield William 22 85.80
506 111 Wabash Geoff 22 107.48
508 113 Joenbrood elbert 22 120.25
501 104 Ramoras Anne 22 251.55
502 105 Johnson Alice 22 829.50
502 101 News John 25 178.50
501 115 Bawangi Travis 25 406.35
501 108 Washington Ralph 25 406.35
508 114 Jones Annelise 25 529.10