CREATE TABLE TJobs

$($

intJobID INTEGER NOT NULL

$,$intCustomerID INTEGER NOT NULL

$,$intStatusID INTEGER NOT NULL

$,$intServiceID INTEGER NOT NULL

$,$dtmStartDate DATE NOT NULL

$,$dtmEndDate DATE 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

$,$strEmailAddress 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

$,$monRetailCost MONEY NOT NULL

$,$monWholeSaleCost 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 DATE NOT NULL

$,$dtmTerminationDate DATE NOT NULL

$,$monPayRate MONEY NOT NULL

$,$monBillingRate 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

$,$CONSTRAINT TSkills$\_$PK PRIMARY KEY $($ intSkillID $)$

$)$

CREATE TABLE TStates

$($

intStateID INTEGER NOT NULL

$,$strState VARCHAR$(255)$ NOT NULL

$,$CONSTRAINT TStates$\_$PK PRIMARY KEY $($ intStateID $)$

$)$

CREATE TABLE TServices

$($

intServiceID INTEGER NOT NULL

$,$strService VARCHAR$(255)$ NOT NULL

$,$CONSTRAINT TServices$\_$PK PRIMARY KEY $($ intServiceID $)$

$)$

$----------------------------------------------------------------------------------$

$--$ 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

$--13$ TJobs TServices intServiceID

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$.($Difficulty Level: $\backslash$#$1)$

$2.$ Write a query to list all complete jobs for Sydney Nye and the materials used on each job. Include the quantity, the individual material cost, and total cost for each material on each job. Order by Job ID and material ID$.($Difficulty Level: $\backslash$#$2)$

$3.$ Write a query to list the total cost for all materials for each completed job for all customers. $($Difficulty Level: $\backslash$#$1)$

$4.$ Write a query to list First Name, Last Name, and Termination Date of all workers whose employment ended in the $2021$ and the total jobs they worked on during that year. $($Difficulty Level: $\backslash$#$2)$

$5.$ 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 billing rate. The average hourly billing rate should be weighted based on the number of hours worked at that rate. $($Difficulty Level: $\backslash$#$3)$

$6.$ Write a query that lists all materials that have not been used on any jobs. Include Material ID and Description. Order by Material ID$.($Difficulty Level: $\backslash$#$1)$

$79.$ Create a query that lists a $1$