Please help me write these 2 queries? (Database code is located beneith the ques
ID: 3868750 • Letter: P
Question
Please help me write these 2 queries? (Database code is located beneith the questions)
4.6. Create a query that lists all workers with a specific skill, their hire date, and the total number of jobs that they worked on. List the Skill ID and description with each row. Order by Worker ID.
4.7. Create a query that lists all workers that worked greater than 20 hours for all jobs that they worked on. Include the Worker ID and name, number of hours worked, and number of jobs that they worked on. Order by Worker ID.
CREATE TABLE TCustomers
(
intCustomerID INTEGER NOT NULL
,strFirstName VARCHAR(30) NOT NULL
,strLastName VARCHAR(30) NOT NULL
,strAddress VARCHAR(30) NOT NULL
,strCity VARCHAR(30) NOT NULL
,intStateID INTEGER NOT NULL
,strZipcode VARCHAR(30) NOT NULL
,CONSTRAINT TCustomers_PK PRIMARY KEY( intCustomerID )
)
CREATE TABLE TStates
(
intStateID INTEGER NOT NULL
,strState VARCHAR(30) NOT NULL
,CONSTRAINT TStates_PK PRIMARY KEY( intStateID )
)
CREATE TABLE TJobStatus
(
intJobStatusID INTEGER NOT NULL
,strJobStatus VARCHAR(30) NOT NULL
,CONSTRAINT TJobStatus_PK PRIMARY KEY( intJobStatusID )
)
CREATE TABLE TJobs
(
intJobID INTEGER NOT NULL
,intCustomerID INTEGER NOT NULL
,intJobStatusID INTEGER NOT NULL
,strJobDescription VARCHAR(2000) NOT NULL
,dteStartDate DATE NOT NULL
,dteEndDate DATE NOT NULL
,CONSTRAINT TJobs_PK PRIMARY KEY( intJobID )
)
CREATE TABLE TWorkers
(
intWorkerID INTEGER NOT NULL
,strFirstName VARCHAR(30) NOT NULL
,strLastName VARCHAR(30) NOT NULL
,dteDateOfHire DATE NOT NULL
,monHourlyRate MONEY NOT NULL
,CONSTRAINT TWorkers_prk PRIMARY KEY( intWorkerID )
)
CREATE TABLE TJobWorkers
(
intJobID INTEGER NOT NULL
,intWorkerID INTEGER NOT NULL
,intTotalHours INTEGER NOT NULL
,CONSTRAINT TJobWorkers_PK PRIMARY KEY( intJobID,intWorkerID )
)
CREATE TABLE TSkills
(
intSkillId INTEGER NOT NULL
,strSkillDescription VARCHAR(30) NOT NULL
,CONSTRAINT TSkills_PK PRIMARY KEY( intSkillId )
)
CREATE TABLE TWorkerSkills
(
intWorkerID INTEGER NOT NULL
,intSkillId INTEGER NOT NULL
,CONSTRAINT TWorkerSkills_PK PRIMARY KEY( intWorkerID,intSkillID )
)
CREATE TABLE TMaterials
(
intMaterialID INTEGER NOT NULL
,strMaterialName VARCHAR(30) NOT NULL
,monMaterialCost MONEY NOT NULL
,CONSTRAINT TMaterials_PK PRIMARY KEY( intMaterialID)
)
CREATE TABLE TJobMaterials
(
intMaterialID INTEGER NOT NULL
, intJobID INTEGER NOT NULL
,intQuantity INTEGER NOT NULL
,CONSTRAINT TJobMaterials_PK PRIMARY KEY( intJobID, intMaterialID)
)
ALTER TABLE TWorkerSkills ADD CONSTRAINT TWorkerSkills_TSkills_FK
FOREIGN KEY ( intSkillId ) REFERENCES TSkills ( intSkillId )
ALTER TABLE TWorkerSkills ADD CONSTRAINT TWorkerSkills_TWorkers_FK
FOREIGN KEY ( intWorkerID) REFERENCES TWorkers ( intWorkerID )
ALTER TABLE TJobs ADD CONSTRAINT TJobs_TJobStatus_FK
FOREIGN KEY ( intJobStatusID ) REFERENCES TJobStatus ( intJobStatusID )
ALTER TABLE TJobs ADD CONSTRAINT TJobs_TCustomers_FK
FOREIGN KEY ( intCustomerID ) REFERENCES TCustomers ( intCustomerID )
ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TMaterials_FK
FOREIGN KEY ( intMaterialID) REFERENCES TMaterials ( intMaterialID)
ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TJobs_FK
FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )
ALTER TABLE TJobWorkers ADD CONSTRAINT TJobWorkers_TJobs_FK
FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID)
INSERT INTO TStates (intStateID,strState)
VALUES (1,'Ohio')
,(2,'Alaska')
,(3,'Iowa')
,(4,'Florida')
,(5,'Kentucky')
,(6,'Indiana')
,(7,'New York')
INSERT INTO TCustomers(intCustomerID, strFirstName,strLastName,strAddress,strCity,intStateID,strZipcode)
VALUES (1,'Jav','Nee','1 Main Street','Cincinnati',1,'45212')
,(2,'Umye','Onn','2 Main Street','Harper',3,'66223')
,(3,'Ansel','Adams','3 Main Street','KEn',4,'88812')
,(4,'Jenny','Smith','4 Hopple Street','Hamilton',2,'78582')
,(5,'Woodrow','Wilson','5 New Street','Butler',5,'85236')
INSERT INTO TWorkers (intWorkerID,strFirstName,strLastName,dteDateOfHire, monHourlyRate)
VALUES (1,'Richard','Jefferson','05/01/2014',12.00)
,(2,'Top', 'Gun','01/01/2015',15.00)
,(3,'Joe', 'Joe','02/01/2011',11.00)
,(4,'KEvin', 'Smith','03/01/2017',82.00)
,(5,'Leroy', 'Brown','04/01/2011',51.00)
,(6,'Sue', 'ME','05/01/2010',22.00)
INSERT INTO TJobStatus (intJobStatusID,strJobStatus)
VALUES (1,'Open ')
,(2,'In Process')
,(3,'Complete')
INSERT INTO TSkills (intSkillId,strSkillDescription)
VALUES (1,'Carpenter')
,(2,'Painter')
,(3,'Electrician')
,(4,'Plumber')
INSERT INTO TMaterials (intMaterialID,strMaterialName ,monMaterialCost)
VALUES (1,'Studs ',15)
,(2,'Paint',100)
,(3,'Pipe',50)
,(4,'Wire',200)
,(5,'Dry Wall',20)
INSERT INTO TJobs ( intJobID,intCustomerID,intJobStatusID, strJobDescription ,dteStartDate,dteEndDate)
VALUES (1,1,1,'Build house','09/02/2013','11/01/2013')
,(2,2,2,'Room Addition','01/02/2015','02/09/2015')
,(3,3,3,' Cinema Remodel','12/22/2016','02/10/2017')
,(4,2,1,'Finish Basement','03/01/2017','06/11/2017')
,(6,5,2,'Paint Room','04/01/2017','07/11/2017')
,(7,1,3,'Build Room','09/02/2012','11/01/2012')
,(8,1,2,'Kitchen Addition','01/02/2016','02/09/2016')
,(9,3,3,' Roof Remodel','12/22/2016','02/10/2017')
,(10,2,1,'Paint Basement','03/01/2017','06/11/2017')
,(11,5,2,'Wire Room','04/01/2017','07/11/2017')
INSERT INTO TJobMaterials ( intJobID, intMaterialID, intQuantity)
VALUES ( 1 , 1 , 1 )
,( 2, 2, 12 )
,( 3 , 3 , 42 )
,( 4 , 4 , 51 )
,( 1, 2, 12 )
,( 1 , 3 , 42 )
,( 6 , 4 , 51 )
,( 7, 2, 12 )
,( 8 , 3 , 42 )
,( 9 , 4 , 51 )
INSERT INTO TWorkerSkills (intWorkerID,intSkillId)
VALUES ( 1 , 1 )
,( 2 , 2 )
,( 3 , 3 )
INSERT INTO TJobWorkers ( intJobID,intWorkerID, intTotalHours)
VALUES ( 1 , 1 , 20)
,( 2, 2 , 40)
,( 3 , 3 , 70)
,( 4, 4, 50)
Explanation / Answer
/*********************** Solution to 4.6******************************/
" NOTES: List all workers with a specific skill, here skill is assumed as "Carpenter" "
Query:
SELECT
a.intSkillId, a.strSkillDescription, b.intWorkerID, c.dteDateOfHire, count(d.intJobID) TotalNoOfJobs
FROM TSkills a
INNER JOIN TWorkerSkills b ON a.intSkillId = b.intSkillId
INNER JOIN TWorkers c ON b.intWorkerID = c.intWorkerID
INNER JOIN TJobWorkers d ON c.intWorkerID = d.intWorkerID
WHERE (a.strSkillDescription = "Carpenter")
GROUP BY
b.intWorkerID, c.dteDateOfHire, a.intSkillId, a.strSkillDescription
ORDER BY
b.intWorkerID
/*********************************************************************************************/
/*************************Solution to 4.7******************************************************/
" NOTE : the logic to solve this is select those workers where the number of jobs the worker done is equal to the number of jobs they have done with total number of working hours greater than 20 "
Query:
SELECT
a.intWorkerID, a.strFirstName, count(b.intJobID) TotalNoOfJobs, sum (b.intTotalHour) TotalNoOfHours
FROM
TWorkers a
WHERE
(SELECT COUNT(b.intJobID)
FROM TJobWorkers b
WHERE a.intWorkerID = b.intWorkerID )
=
(SELECT COUNT(b2.intJobID)
FROM TJobWorkers b2
WHERE b2.intWorkerID = a.intWorkerID AND b2.intTotalHour > 20 )
GROUP BY
a.intWorkerID, a.strFirstName
ORDER BY
a.intWorkerID
/*******************************************************************************************/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.