Below is a database I've created about:

Fixinyerleaks is a residential service company that does Plumbing. They want you to design a database that tracks all jobs for their customers. A Job is a single service call to a customer to fix some sort of plumbing issue. Over the years, a customer can use Fixinyerleaks many times. For each job, the plumber will record their hours and also record all of the materials used for the job.

Need help with a few updates/deletes, and some subqueries. Thanks!

Update and Deletes

1. Create SQL statements to update the address for a specific customer. Include a select statement before and after the update.

2. Create SQL statements to increase the hourly rate by $2 for each worker that has been an employee for at least 1 year. Include a select before and after the update. Make sure that you have data so that some rows are updated and others are not.

3. Create SQL statements to delete a specific job that has associated work hours and materials assigned to it. Include a select before and after the statement(s).

Queries

1. Write a query to list all jobs that are in process. Include the Job ID and Description, Customer ID and name, and the start date. Order by the Job ID.

2. Write a query to list all complete jobs for a specific customer and the materials used on each job. Include the quantity, unit cost, and total cost for each material on each job. Order by Job ID and material ID. Note: Select a customer that has at least 3 complete jobs and at least 1 open job and 1 in process job. At least one of the complete jobs should have multiple materials. If needed, go back to your inserts and add data.

3. This step should use the same customer as in step 2. Write a query to list the total cost for all materials for each completed job for the customer. Use the data returned in step 4.2 to validate your results.

4. 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.

5. Write a query that lists all materials that have not been used on any jobs. Include Material ID and Description. Order by Material ID.

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.

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.

8. Write a query that lists all customers who are located on 'Main Street'. Include the customer Id and full address. Order by Customer ID. Make sure that you have at least three customers on 'Main Street' each with different house numbers. Make sure that you also have customers that are not on 'Main Street'.

9. Write a query to list completed jobs that started and ended in the same month. List Job, Job Status, Start Date and End Date.

10. Create a query to list workers that worked on three or more jobs for the same customer.

11. Create a query to list all workers and their total # of skills. Make sure that you have workers that have multiple skills and that you have at least 1 worker with no skills. The worker with no skills should be included with a total number of skills = 0. Order by Worker ID.

12. Write a query to list the total Charge to the customer for each job. Calculate the total charge to the customer as the total cost of materials + total Labor costs + 30% Profit.

13. Write a query that totals what is owed to each vendor for a particular job.

CREATE TABLE TJobs

(

intJobID INTEGER NOT NULL

,intCustomerID INTEGER NOT NULL

,intStatusID INTEGER NOT NULL

,dtmStartDate DATETIME NOT NULL

,dtmEndDate DATETIME NOT NULL

,strJobDesc VARCHAR(8000) NOT NULL

,CONSTRAINT TJobs_PK PRIMARY KEY ( intJobID )

)

CREATE TABLE TCustomers

(

intCustomerID INTEGER NOT NULL

,strFirstName VARCHAR(255) NOT NULL

,strLastName VARCHAR(255) NOT NULL

,strAddress VARCHAR(255) NOT NULL

,strCity VARCHAR(255) NOT NULL

,intStateID INTEGER NOT NULL

,strZip VARCHAR(255) NOT NULL

,strPhoneNumber VARCHAR(255) NOT NULL

,CONSTRAINT TCustomer_PK PRIMARY KEY ( intCustomerID )

)

CREATE TABLE TStatuses

(

intStatusID INTEGER NOT NULL

,strStatus VARCHAR(255) NOT NULL

,CONSTRAINT TStatuses_PK PRIMARY KEY ( intStatusID )

)

CREATE TABLE TJobMaterials

(

intJobMaterialID INTEGER NOT NULL

,intJobID INTEGER NOT NULL

,intMaterialID INTEGER NOT NULL

,intQuantity INTEGER NOT NULL

,CONSTRAINT TCustomerJobMaterials_PK PRIMARY KEY ( intJobMaterialID )

)

CREATE TABLE TMaterials

(

intMaterialID INTEGER NOT NULL

,strDescription VARCHAR(255) NOT NULL

,monCost MONEY NOT NULL

,intVendorID INTEGER NOT NULL

,CONSTRAINT TMaterials_PK PRIMARY KEY ( intMaterialID )

)

CREATE TABLE TVendors

(

intVendorID INTEGER NOT NULL

,strVendorName VARCHAR(255) NOT NULL

,strAddress VARCHAR(255) NOT NULL

,strCity VARCHAR(255) NOT NULL

,intStateID INTEGER NOT NULL

,strZip VARCHAR(255) NOT NULL

,strPhoneNumber VARCHAR(255) NOT NULL

,CONSTRAINT TVendors_PK PRIMARY KEY ( intVendorID )

)

CREATE TABLE TJobWorkers

(

intJobWorkerID INTEGER NOT NULL

,intJobID INTEGER NOT NULL

,intWorkerID INTEGER NOT NULL

,intHoursWorked INTEGER NOT NULL

,CONSTRAINT TCustomerJobWorkers_PK PRIMARY KEY ( intJobWorkerID )

)

CREATE TABLE TWorkers

(

intWorkerID INTEGER NOT NULL

,strFirstName VARCHAR(255) NOT NULL

,strLastName VARCHAR(255) NOT NULL

,strAddress VARCHAR(255) NOT NULL

,strCity VARCHAR(255) NOT NULL

,intStateID INTEGER NOT NULL

,strZip VARCHAR(255) NOT NULL

,strPhoneNumber VARCHAR(255) NOT NULL

,dtmHireDate DATETIME NOT NULL

,monHourlyRate MONEY NOT NULL

,CONSTRAINT TWorkers_PK PRIMARY KEY ( intWorkerID )

)

CREATE TABLE TWorkerSkills

(

intWorkerSkillID INTEGER NOT NULL

,intWorkerID INTEGER NOT NULL

,intSkillID INTEGER NOT NULL

,CONSTRAINT TWorkerSkills_PK PRIMARY KEY ( intWorkerSkillID )

)

CREATE TABLE TSkills

(

intSkillID INTEGER NOT NULL

,strSkill VARCHAR(255) NOT NULL

,strDescription VARCHAR(255) NOT NULL

,CONSTRAINT TSkills_PK PRIMARY KEY ( intSkillID )

)

CREATE TABLE TStates

(

intStateID INTEGER NOT NULL

,strState VARCHAR(255) NOT NULL

,CONSTRAINT TStates_PK PRIMARY KEY ( intStateID )

)

-- --------------------------------------------------------------------------------

-- Establish Referential Integrity

-- --------------------------------------------------------------------------------

--

-- # Child Parent Column

-- - ----- ------ ---------

-- 1 TJobs TCustomers intCustomerID

-- 2 TJobs TStatuses intStatusID

-- 3 TCustomers TStates intStateID

-- 4 TJobMaterials TJobs intJobID

-- 5 TJobMaterials TMaterials intMaterialID

-- 6 TMaterials TVendors intVendorID

-- 7 TVendors TStates intStateID

-- 8 TJobWorkers TJobs intJobID

-- 9 TJobWorkers TWorkers intWorkerID

-- 10 TWorkers TStates intStateID

-- 11 TWorkerSkills TWorkers intWorkerID

-- 12 TWorkerSkills TSkills intSkillID

-- 1

ALTER TABLE TJobs ADD CONSTRAINT TJobs_TStatuses_FK

FOREIGN KEY ( intCustomerID ) REFERENCES TStatuses ( intCustomerID )

-- 2

ALTER TABLE TJobs ADD CONSTRAINT TJobs_TCustomers_FK

FOREIGN KEY ( intStatusID ) REFERENCES TCustomers ( intStatusID )

-- 3

ALTER TABLE TCustomers ADD CONSTRAINT TCustomers_TStates_FK

FOREIGN KEY ( intStateID ) REFERENCES TStates ( intStateID )

-- 4

ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TJobs_FK

FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )

-- 5

ALTER TABLE TJobMaterials ADD CONSTRAINT TJobMaterials_TMaterials_FK

FOREIGN KEY ( intMaterialID ) REFERENCES TJobs ( intMaterialID )

-- 6

ALTER TABLE TMaterials ADD CONSTRAINT TMaterials_TVendors_FK

FOREIGN KEY ( intVendorID ) REFERENCES TJobs ( intVendorID )

-- 7

ALTER TABLE TVendors ADD CONSTRAINT TVendors_TStates_FK

FOREIGN KEY ( intStateID ) REFERENCES TStates ( intStateID )

-- 8

ALTER TABLE TJobWorkers ADD CONSTRAINT TJobWorkers_TJobs_FK

FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )

-- 9

ALTER TABLE TJobWorkers ADD CONSTRAINT TJobWorkers_TWorkers_FK

FOREIGN KEY ( intWorkerID ) REFERENCES TJobs ( intWorkerID )

-- 10

ALTER TABLE TWorkers ADD CONSTRAINT TWorkers_TStates_FK

FOREIGN KEY ( intStateID ) REFERENCES TStates ( intStateID )

-- 11

ALTER TABLE TWorkerSkills ADD CONSTRAINT TWorkerskills_TWorkers_FK

FOREIGN KEY ( intWorkerID ) REFERENCES TStates ( intWorkerID )

-- 12

ALTER TABLE TWorkerSkills ADD CONSTRAINT TWorkerskills_TSkills_FK

FOREIGN KEY ( intSkillID ) REFERENCES TStates ( intSkillID )