Hello, everyone, I'm seeking your kind help to solve the below-mentioned questions using PROC SQL. I tried many ways but didn't get correct answers. Here is the queries for create and insert statements. PROC SQL;
create table Aschema.JOB2019(
JOB_CODE2019 num,
JOB_DESCRIPTION2019 varchar(30),
JOB_CHG_HOUR2019 num(4,2),
constraint pkjob1 primary key (JOB_CODE2019)
);
insert into Aschema.job2019
values (500,'Programmer',35.75)
values (501,'Systems Analyst',96.75)
values (502,'Database Designer',105.00)
values (503,'Electrical Engineer',84.50)
values (504,'Mechanical Engineer',67.90)
values (505,'Civil Engineer',55.78)
values (506,'Clerical Support',26.87)
values (507,'DSS Analyst',45.95)
values (508,'Applications Designer',48.10)
values (509,'Bio Technician',34.55)
values (510,'General Support',18.36);
QUIT;
PROC SQL;
create table Aschema.EMPLOYEE2019(
EMP_NUM2019 num,
EMP_LNAME2019 varchar(20),
EMP_FNAME2019 varchar(20),
EMP_INITIAL2019 char(1),
EMP_HIREDATE2019 date,
JOB_CODE2019 int,
constraint pkemployee1 primary key (EMP_NUM2019),
constraint fkemployee1 foreign key (JOB_CODE2019) references Aschema.JOB2019(JOB_CODE2019)
);
insert into Aschema.employee2019
values (101,'News','John','G','08Nov2000'd,502)
values (102,'Senior','David','H','12Jun1989'd,501)
values (103,'Arbough','June','E','01Dec1997'd,503)
values (104,'Ramoras','Anne','K','15Nov1988'd,501)
values (105,'Johnson','Alice','K','01Feb1994'd,502)
values (106,'Smithfield','William',' ','22Jun2005'd,500)
values (107,'Alonzo','Maria','D','10Oct1994'd,500)
values (108,'Washington','Ralph','B','22Aug1889'd,501)
values (109,'Smith','Larry','W','18Jul1999'd,501)
values (110,'Olenko','Gerald','A','11Dec1996'd,505)
values (111,'Wabash','Geoff','B','04Apr1989'd,506)
values (112,'Smithson','Darlene','M','23Oct1995'd,507)
values (113,'Joenbrood','Delbert','K','15Nov1994'd,508)
values (114,'Jones','Annelise',' ','20Aug1991'd,508)
values (115,'Bawangi','Travis','B','25Jan1990'd,501)
values (116,'Pratt','Gerald','L','05Mar1995'd,510)
values (117,'Williamson','Angie','H','19Jun1994'd,509)
values (118,'Frommer','James','J','04Jan2006'd,510);
QUIT;
PROC SQL;
create table Aschema.PROJECT2019(
PROJ_NUM2019 num,
PROJ_NAME2019 varchar(20),
EMP_NUM2019 num,
constraint pkproject1 primary key (PROJ_NUM2019),
constraint pkforiegn1 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);
insert INTO Aschema.project2019
values (15,'Evergreen',105)
values (18,'Amber Wave',104)
values (22,'Rolling Tide',113)
values (25,'Starflight',101);
QUIT;
PROC SQL;
create table Aschema.ASSIGNMENT2019(
ASSIGN_NUM2019 num,
ASSIGN_DATE2019 date,
PROJ_NUM2019 num,
EMP_NUM2019 num,
ASSIGN_HOURS2019 num(5,2),
ASSIGN_CHG_HOUR2019 num(5,2),
ASSIGN_CHARGE2019 num(7,2) ,
constraint pkassignment1 primary key (ASSIGN_NUM2019),
constraint fkassignment1 foreign key (PROJ_NUM2019) references Aschema.project2019(PROJ_NUM2019),
constraint fkassignment2 foreign key (EMP_NUM2019) references Aschema.employee2019(EMP_NUM2019)
);
insert into Aschema.assignment2019(ASSIGN_NUM2019,ASSIGN_DATE2019,PROJ_NUM2019,EMP_NUM2019,ASSIGN_HOURS2019,ASSIGN_CHG_HOUR2019)
values(1001,'04Mar2012'd,15,103,2.6,84.50)
values (1002,'04Mar2012'd,18,118,1.4,18.36)
values (1003,'05Mar2012'd,15,101,3.6,105.00)
values (1004,'05Mar2012'd,22,113,2.5,48.10)
values (1005,'05Mar2012'd,15,103,1.9,84.50)
values (1006,'05Mar2012'd,25,115,4.2,96.75)
values (1007,'05Mar2012'd,22,105,5.2,105.00)
values (1008,'05Mar2012'd,25,101,1.7,105.00)
values (1009,'05Mar2012'd,15,105,2.0,105.00)
values (1010,'06Mar2012'd,15,102,3.8,96.75)
values (1011,'06Mar2012'd,22,104,2.6,96.75)
values (1012,'06Mar2012'd,15,101,2.3,105.00)
values (1013,'06Mar2012'd,25,114,1.8,48.10)
values (1014,'06Mar2012'd,22,111,4.0,26.87)
values (1015,'06Mar2012'd,25,114,3.4,48.10)
values (1016,'06Mar2012'd,18,112,1.2,45.95)
values (1017,'06Mar2012'd,18,118,2.0,18.36)
values (1018,'06Mar2012'd,18,104,2.6,96.75)
values (1019,'06Mar2012'd,15,103,3.0,84.50)
values (1020,'07Mar2012'd,22,105,2.7,105.00)
values (1021,'08Mar2012'd,25,108,4.2,96.75)
values (1022,'07Mar2012'd,25,114,5.8,48.10)
values (1023,'07Mar2012'd,22,106,2.4,35.75);
QUIT;
PROC SQL;
Update Aschema.assignment2019
set ASSIGN_CHARGE2019 = ASSIGN_HOURS2019 * ASSIGN_CHG_HOUR2019;
QUIT;
These are the questions, 1.Show which projects bill hours between 45 and 100 hours 2.What is the smallest and largest individual for Charge Hour for Job Classes? 3.Which Job Class is the second highest Charge Hour in the table? 4.Which Project does not utilize an Application Designer? 5.Identify the employees who are Database Designer and which project have they been associated with? I'm beginner to PROC SQL and your help with these questions will be highly appreciated. Thank you.
... View more
