CREATE TABLE TJobs ( intJobID INTEGER NOT NULL ,dtmStartDate DATETIME NOT NULL ,dtmEndDate DATETIME DEFAULT NULL ,strJobDesc VARCHAR(225) 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 ,strEmail VARCHAR(255) NOT NULL ,strPhoneNumber VARCHAR(255) NOT NULL ,CONSTRAINT TCustomer_PK PRIMARY KEY ( intCustomerID ) )

CREATE TABLE TCustomerJobs ( intCustomerJobsID INTEGER NOT NULL ,intCustomerID INTEGER NOT NULL , intJobID INTEGER NOT NULL ,CONSTRAINT TStatuses_PK PRIMARY KEY ( intCustomerJobsID ) )

-- -------------------------------------------------------------------------------- -- Step #1.2: Identify and Create Foreign Keys -- -------------------------------------------------------------------------------- -- # Child Parent Column -- - ----- --------- --------- -- 1 TCustomerJobs TCustomers intCustomerID

-- 2 TCustomerJobs TJobs intJobID

-- 1 ALTER TABLE TTCustomerJobs ADD CONSTRAINT TTCustomerJobs_TCustomers_FK FOREIGN KEY ( intCustomerID ) REFERENCES TCustomers ( intCustomerID )

-- 2 ALTER TABLE TTCustomerJobs ADD CONSTRAINT TTCustomerJobs_TJobs_FK FOREIGN KEY ( intJobID ) REFERENCES TJobs ( intJobID )

INSERT INTO TCustomers(intCustomerID, strFirstName,strLastName,strPhoneNumber,strEmail)

VALUES ( 1, 'Bob', 'Jones', '8597602063','Johny@mail.com') ,( 2, 'Jay', 'Gray', '8597602222','jay@mail.com') ,( 3, 'Molly', 'Blue', '8597603333','2Blue@hotmail.com') ,( 5, 'Rosanne', 'Neff', '8592222063','Rosa25@hotmail.com') ,( 6, 'Bob', 'Hartland', '8592222063','bob3@mail.com')

INSERT INTO TJobs ( intJobID, strJobDesc ,dtmStartDate,dtmEndDate)

VALUES (1,'Build house','09/02/2021','11/01/2021') ,(2,'Room Addition','01/02/2019','02/09/2019') ,(3,' Cinema Remodel','12/22/2020','00/00/00') ,(4,'Finish Basement','03/01/2017','06/11/2017') ,(5,'Paint Room','04/01/2021','00/00/00') INSERT INTO TCustomerJobs ( intCustomerJobsID, intCustomerID, intJobID) VALUES ( 1 , 2 , 3 ) ,( 2, 1, 2 ) ,( 3 , 2 , 1 ) ,( 4 , 5 , 0)

1. Create a view called VCustomers that will show all customers, their name (last, first) and their email address.
2. Create a view called VCustomerJobs that will show all customers with jobs.
3. Create a view called VCustomerNoJob that will show all customers without a job.
4. Create a view tcalled VCustomerJobCount to show the count of jobs for each customer.
5. Create the stored procedure uspAddCustomer that will add a record to TCustomers. Call the stored procedure after you create it to make sure it works correctly. Comment your test code out prior to submitting.
6. Create the stored procedure uspAddJob that will add a record to TJobs. Call the stored procedure after you create it to make sure it works correctly. Comment your test code out prior to submitting.