Complete Copy of Vendors and Invoices Tables: Create VendorCopy

School: Utah Valley University - Course: INFO 2410 - Subject: Accounting

-- 1 -- Write SELECT INTO statements to create two test tables named VendorCopy and InvoiceCopy that are complete copies of the Vendors and Invoices tables. Select * Into accounts_payable.dbo.VendorCopy From accounts_payable.dbo.Vendors ; Drop table accounts_payable.dbo.InvoicesCopy; Select * Into accounts_payable.dbo.InvoicesCopy From accounts_payable.dbo.Invoices ; -- 2 -- Write an INSERT statement that adds a row to the InvoiceCopy table Insert into accounts_payable.dbo.InvoicesCopy (VendorID, InvoiceTotal, TermsID, InvoiceNumber, PaymentTotal, InvoiceDueDate, InvoiceDate, CreditTotal, PaymentDate) Values (32, 434.58, 2, 'AX-014-027', 0.00, '11/8/08', '10/21/08', 0.00, null) ; -- 3 -- Write an INSERT statement that adds a row to the VendorCopy table for each non-California vendor in the Vendors table. SET IDENTITY_INSERT accounts_payable.dbo.VendorCopy ON; Insert into accounts_payable.dbo.VendorCopy Select * From accounts_payable.dbo.Vendors Where VendorState <> 'California' ; SET IDENTITY_INSERT accounts_payable.dbo.VendorCopy OFF; -- 4 -- Write an UPDATE statement that modifies the VendorCopy table. Change the default account number to 403 for each vendor that has a default account number of 400. Update accounts_payable.dbo.VendorCopy Set DefaultAccountNo = '403' Where DefaultAccountNo = '400'

; -- 5 -- Write an UPDATE statement that modifies the InvoiceCopy table. Change the PaymentDate to todays from a vendor with a DefaultTermsID of 2. Use a subquery. Update accounts_payable.dbo.InvoicesCopy Set TermsID = 2 Where VendorID in ( Select VendorID From accounts_payable.dbo.VendorCopy Where DefaultTermsID = 2 ); -- 7 -- Solve exercise 6 using a join rather than a subquery. Update accounts_payable.dbo.InvoicesCopy Set TermsID = 2 From accounts_payable.dbo.InvoicesCopy Join accounts_payable.dbo.VendorCopyon InvoicesCopy.VendorID = VendorCopy.VendorID Where VendorCopy.DefaultTermsID = 2 ; -- 8 -- Write a DELETE statement that deletes all vendors in the state of Minnesota from the VendorCopy table. Delete from accounts_payable.dbo.VendorCopy Where VendorState = 'Minnesota'