-- 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'
Expert's Answer
Chat with our Experts
Want to contact us directly? No Problem. We are always here for you
Your future, our responsibilty submit your task on time.
Order NowGet Online
Assignment Help Services