Write the SQL code to create at least two tables however you

Write the SQL code to create *at least* two tables; however, you might need more tables to accomplish the specific task. Your tables will keep track of:

Owners; the properties of an owner are:

Owner ID…First Name…Last Name…Address…Phone

Note: If you have already created the tables with the same names as the assignment, you will need to drop them before you can recreate them. This is a *very* commonly-used schema!

Vehicles; the properties of a vehicle are:

Vehicle ID
Make
Model
Year
Color

Cause the following relationship to exist: An owner has many cars; a car may have at most one owner (no common-property here).        (This is a one to many)

Write the code to insert at least three records into each table.

Use proper SQL programming style for table creation (e.g.: name all of your constraints and use semicolons.) Choose meaningful identifiers and reasonable data types. For example: a last name might allow 16 characters... perhaps; 50 is too many and two characters is too few. Constrain all last names against NULL.

************************************************************

Part 2 (For part two, use your default database and the existing Vendors-Invoices-Terms schema.) Part two is weighted as indicated.

Write SQL code to accomplish the following tasks:

.

1) Insert the following record into the Terms table:

<TermsID>      <TermsDescription>   <TermsDueDays>

6                      Existing Credit             -1

2) Change the CreditTotal to 50.00 for all of the invoices for VendorID 122.

3) Update the Vendors table to to show (999) 999-9999 for the phones that are currently NULL.

4) Write an SQL SELECT query to show the name and city for Vendors in zip 53707 whose city begins with \'Ma\'; sort the output by city.

5) Write the SQL code to delete VendorID = 113 from the Vendor\'s table; also delete any associated invoices. This will probably require two SQL statements in the correct order.

6) Write an SQL SELECT query that shows the invoiceNumber and invoiceDueDate for Vendors in California (\"CA\").

7) Write an SQL join that will find any unused \"Terms\" in the table; i.e., no invoice has these terms.

8) Write an SQL query that will return the vendorCity, invoiceNumber, and invoiceTotal for all rows with an invoice less than 100 and a termsDueDays less than or equal to 20.

9) You may have noticed that the defaultTermsID field of the vendors table is not constrained as a foreign key. If we tried to add a foreign key constraint, it would fail because there are invalid data in the table... because I placed them there. Write an SQL query that will find all invalid data in that field.

Solution

Part 1 - Creation of tables:

DROP TABLE owners;
CREATE TABLE `owners`(
   `owner_id` bigint(11) NOT NULL AUTO_INCREMENT,
   `first_name` varchar(16) NOT NULL,
   `last_name` varchar(16) NOT NULL,
   `address` varchar(120),
`country_code` varchar(5),
   `phone_number` varchar(15)
);
DROP TABLE vehicles;
CREATE TABLE `vehicles`(
   `vehicle_id` bigint(11) NOT NULL AUTO_INCREMENT,
   `make` varchar(50) NOT NULL,
   `model` varchar(30) NOT NULL,
   `year` int(4) NOT NULL,
`color` varchar(15) NOT NULL,
   `owner_id` bigint(11)
);

For Part 2 We\'ll be needing Vendors-Invoices-Terms schema. field names could be different.

insert into terms(terms_id, terms_description, terms_due_days) values(6, \'Existing Credit\', -1);

update invoices set credit_total = 50 where vendor_id=122;

update vendors set phone=\'(999) 999-9999\' where phone is null;

select name, city from verndors where zip=53707 and city like \'Ma%\' order by city;

delete from invoices where vendor_id=113;

delete from vendors where vendor_id=113;

Others statements would require the table schema as mentioned in the begining.

select invoice_number, invoice_due_date from invoices where vendor_id in (select vendor_id from vendors where country=\'CA\');