--5. Write an UPDATE statement that modifies the InvoiceCopy table. Change the -

Question

--5. Write an UPDATE statement that modifies the InvoiceCopy table. Change the

-- PaymentDate to today's date and the PaymentTotal to the balance due for each

-- invoice with a balance due. Set today's date with a literal date string, or use the

-- GETDATE()function.

--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.

--8. Wirte a DELETE statement that deletes all veendors in the state of Minnesota for the

-- VendorCopy Table.

--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.

Explanation / Answer

5. UPDATE invoicecopy SET PaymentDate = GETDATE() , UPDATE invoicecopy SET PaymentTotal = CreditTotal WHERE (CreditTotal > 0)

6.
UPDATE InvoiceCopy SET TermsID = 2 WHERE VendorID IN ( SELECT VendorID FROM VendorCopy WHERE DefaultTermsID = 2 ) AND TermsID <> 2

7. i will show you how the SELECT with INNER JOIN, try to convert it to the UPDATE statement
SELECT < column list here > FROM InvoiceCopy AS i INNER JOIN VendorCopy AS v ON i.VendorID = v.VendorID WHERE < where condition here >

8. Question does not make sense to me. the NOT IN should be checking from InvoiceCopy table. InvoiceCopy does not contain the VendorState column. So i am not sure how to make use of "SELECT DISTINCT VendorState" Also, your delete statement there will not delete anything. the subquery will return all distinct VendorState value from VendorCopy table and the DELETE is to delete NOT IN from VendorCopy. Try this and see anyting return
DELETE SELECT * FROM VendorCopy WHERE vendorState NOT IN (SELECT DISTINCT VendorState FROM VendorCopy)


Please rate me if it is help full thank you...

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.