SQL

Problem 1

You are to create a DB for a construction company to show which job is for which customer.

1. Create the following tables

TCustomers

Name

Phone

Email

TJobs

Job description (250 characters)

Start Date

End Date (allow NULLs)

TCustomerJobs

Join the Customer and Job tables together

2. Create FK table showing child, parent and column used to create relationship
3. Create FKs based on step #2.
4. Add at least 3 customers
5. Add at least 3 jobs at least one with a NULL End Date
6. Give 2 customers jobs leaving 1 with no job.
7. Give 1 customer 2 different jobs.
8. Try to give a customer the same job twice. This should fail. (Comment this code out prior to submitting)
9. Write query to show all customers and jobs
10. Write query to show any job not completed
11. Write query to update an End Date from NULL to a date after the Start Date for that job.