1) (3 points) The Murach AP schema contains a table called VENDORS. Use the CREA

Question

1) (3 points) The Murach AP schema contains a table called VENDORS. Use the CREATE TABLE AS syntax with the IN operator to create a table called VENDORS SUBSET that consists just of vendor name, state, and zip code (and no other attributes) for those vendors in the VENDOR table that are based in either California or Pennslyvania and have zip codes of less than 90024. 2) (2 points) Write an UPDATE statement with the IN operator to change all of the line item amount values in the INVOICE LINE ITEM table so that they are $3 more than the current amount for those line items that are NOT "Freight" or "Property Taxes". Be careful not to miss the "NOT 3) (2 points) Write a DELETE statement to remove all rows in the VENDOR CONTACT table that have last nams that either (a) start with either a "D" or end in a "S" and first names that begin with an "M" OR (b) contain the sequence "al" somewhere in the first name. Do not do (a) and (b) as separate queries. You should only have ONE query.

Explanation / Answer

1)
CREATE TABLE VENDORS_SUBSET AS (SELECT name, state, zip FROM VENDOR WHERE state IN("California","Pennslyvania") AND zip<90024 );

2)
UPDATE INVOICE_LINE_ITEM SET line_item_amount=line_item_amount+3 WHERE line_item<>"Freight" OR line_item<>"Property Taxes";

3)
DELETE FROM VENDOR_CONTACT WHERE lastname LIKE 'a%' OR lastname LIKE 'D%' OR lastname LIKE '%S' AND firstname LIKE 'M%' OR firstname LIKE '%al%;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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