1. Write SELECT INTO statements to create two test tables named VendorCopy and InvoiceCopy that are complete copies of the Vendors and Invoices tables. If VendorCopy and InvoiceCopy already exist, first code two DROP TABLE statements to delete them.
2. Write an INSERT statement that adds a row to the InvoiceCopy table with the
Vendor1D: 32
InvoiceTotal: $434.58
Terms1D: 2
InvoiceNumber: AX-014-027
PaymentTotal:
InvoiceDueDate: 07/8/2016
InvoiceDate: 6/21/2016
CreditT0tal: SO.OO
PaymentDate: null
3. Write an INSERT statement that adds a row to the VendorCopy table for each non-California vendor in the Vendors table. (This will result in duplicate vendors in the VendorCopy table.)
6. Write an UPDATE statement that modifies the InvoiceCopy table. Change TermsID to 2 for each invoice that's from a vendor with a DefaultTermsID of 2. Use a subquery.
7. Solve exercise 6 using a join rather than a subquery.
9. Write a DELETE statement for the VendorCopy table. Delete the vendors that are located in states from which no vendor has ever sent an invoice. Hint: Use a subquery coded with "SELECT DISTINCT Vendorstate" introduced with the NOT IN operator.