Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Can someone please help me write this sql query with a select statement? (Database code is located beneith the questions) .Write a query to list

Can someone please help me write this sql query with a select statement? (Database code is located beneith the questions)

.Write a query to list all jobs that have work entered for them. Include the job ID, job description, and job status description. List the total hours worked for each job with the lowest, highest, and average hourly rate. The average hourly rate should be weighted based on the number of hours worked at that rate. Make sure that your data includes at least one job that does not have hours logged. This job should not be included in the query. Order by highest to lowest average hourly rate. 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)

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

Step: 3

blur-text-image

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

OCA Oracle Database SQL Exam Guide Exam 1Z0-071

Authors: Steve O'Hearn

1st Edition

1259585492, 978-1259585494

More Books

Students also viewed these Databases questions

Question

U11 Informing Industry: Publicizing Contract Actions 317

Answered: 1 week ago