Question:

What needs to be answered:

Table

Data: Hi if you could please list the SQL queries needed to complete 1-21 that would be amazing please and thank! Ive included screenshots of all the information you should need

The Queen Anne Curiosity Shop sells both antiques and current-production household items that complement or are useful with the antiques. For example, the store sells antique dining room tables and new tablecloths. The antiques are purchased from vendors, including individuals, and distributors. The store's customers include individuals, owners of bed-and-breakfast operations, and interior designers. Assume that the shop designs a database with the following tables. CUSTOMER (CustomerID, LastName, FirstName, City, Phone) VENDOR(VendorID, CompanyName, ContactLastName, ContactFirst Name, City, Email) ITEM(ItemID, ItemDescription, PurchaseDate, ItemCost, VendorID) SALE(SaleID, CustomerID, ItemID, SaleDate, SubTotal, Tax, Total) The referential integrity constraints are: - VendorID in ITEM must exist in VendorID in VENDOR - CustomerID in SALE must exist in CustomerID in CUSTOMER - ItemId in SALe must exist in ItemId in ITEM Prepare tables and data: 1. Download assignment4-SQLScripts.zip 2. Delete the table you have created in your database using assign-4-delete-tables.sq1. 3. Create tables using assign-4-create-tables.sql. 4. Insert data using assign-4-insert-data. sq1. Then, write SQL statements and answer questions for this database as follows. Answer the questions in order. Please use comment /*** Question X/ before each query to separate your answers (X should be question number). When we say show all items in a table, we mean show all columns for that table. You must use the statements learned during the class. (5 points for each question unless otherwise specified) 1. Show ItemID and ItemDescription for all items that cost more than $1000. (3 points) 2. Count how many items cost more than $1000. (3 points) 3. Count how many distinct ItemDescription in ITEM table. (4 points) 4. Show ItemDescription and ItemCost for the item with maximum cost Note: using subquery. 5. Show items with ItemDescription containing word "Table" and the ItemCost is more than $1000. 6. Show all customers NOT from city "Seattle" or "Bellevue" 7. Show all the items in descending order of their ItemCost 8. Show all the vendors with CompanyName as NULL 9. Show the sum of Total in SALE 10. For ITEM table, group by VendorID. Show VendorID and corresponding sum of ItemCost 11. Add one constraint to question above: only show the vendors with sum of ItemCost more than $2000. 12. List ItemDescription and ItemCost of items that were purchased from company with CompanyName as "Linens and Things". Use subquery. Note: you will need two tables ITEM and VENDOR to answer this query 13. Answer the question above using join. 14. List LastName, FirstName, and Phone of customers who have purchased an item with Total more than \$1000. Use subquery. Note: you will need two tables CUSTOMER and SALE to answer this query 15. Answer the question above using join. 16. We want to show two more columns from the question above: also show the SaleDate and Total. You can only use join to answer this question. 17. Show ItemDescription for items sold to customer from Seattle. You can choose to use join or subquery. Note: you will need three tables CUSTOMER, SALE, and ITEM to answer this query 18. We want to show 3 more columns from the question above. Besides ItemDescription, also show customer name (FirstName and LastName) and Total in SALE. 19. Show the customers who purchased the most expensive items (highest Total value in SALE). Note: use join 20. Sort customers by the total amount of their purchases (sum of total in SALE) 21. Show the customer name (in CUSTOMER), Total (in SALE), ItemDescription (in ITEM), and CompanyName (in VENDOR) for Total (in SALE) is more than 2000. Note: use join INSERT INTO CUSTOMER VALUES ( NULL, 'Shire', 'Robert', 'Seattle', '206-524-2433'); INSERT INTO CUSTOMER VALUES( NULL, 'Goodyear', 'Katherine', 'Seattle', '206-524-3544'); INSERT INTO CUSTOMER VALUES ( NULL, 'Bancroft', 'Chris','Bellevue','425-635-9788'); INSERT INTO CUSTOMER VALUES ( NULL, 'Griffith', 'John', 'Seattle', '206-524-4655'); INSERT INTO CUSTOMER VALUES ( NULL, 'Tierney', 'Doris', 'Bellevue', '425-635-8677'); INSERT INTO CUSTOMER VALUES( NULL, 'Anderson', 'Donna', 'Mt. Vernon', '360-538-7566'); INSERT INTO CUSTOMER VALUES ( NULL, 'Svane', 'Jack', 'Seattle', '206-524-5766'); INSERT INTO CUSTOMER VALUES ( NULL, 'Walsh', 'Denesha', 'Redmond', '425-635-7566'); INSERT INTO CUSTOMER VALUES ( NULL, 'Enquist', 'Craig', 'Bellingham', '360-538-6455'); INSERT INTO CUSTOMER VALUES ( NULL, 'Anderson', 'Rose', 'Seattle', '206-524-6877'); INSERT INTO VENDOR VALUES( NULL, 'Linens and Things', 'Huntington', 'Anne', 'Seattle', 'LAT@business. com'); INSERT INTO VENDOR VALUES( NULL, 'European Specialties', 'Tadema', 'Ken', 'Seattle', 'ES@business.com'); INSERT INTO VENDOR VALUES( NULL, 'Lamps and Lighting', 'Swanson', 'Sally', 'Seattle', 'LALQbusiness, com'); INSERT INTO VENDOR (ContactlastName, ContactFirstName, City, Email) VALUES ('Lee', 'Andrew', 'Kirkland', 'Andrew. Lee@somewhere. com'); INSERT INTO VENDOR (ContactLastName, ContactFirstName, City, Email) VALUES('Harrison', 'Denise', 'Kirkland', 'Denise. Harrisonesomewhere. Com'); INSERT INTO VENDOR VALUES( NULL, 'New York Brokerage', 'Smith', 'Mark', 'Seattle', 'NYB@business. com'); INSERT INTO VENDOR (ContactLastName, ContactFirstName, City, Email) VALUES ('Walsh', 'Denesha', 'Redmond', 'Denesha. Walsh@somewhere. com'); INSERT INTO VENDOR (ContactLastName, ContactFirstName, City, Email) VALUES('Bancroft', 'Chris', 'Bellevue', 'Chris.Bancroft@somewhere.com'); INSERT INTO VENDOR VALUES( NULL, 'Specialty Antiques', 'Nelson', 'Fred', 'San Francisco', 'SA@business.com'); INSERT INTO VENDOR VALUES( NULL, 'General Antiques', 'Garner', 'Patty', 'San Francisco', 'GA@business. com'); INSERT INTO ITEM VALUES( NULL, 'Antique Desk', '2012-11-07', 1800.00, 2); INSERT INTO ITEM VALUES( NULL, 'Antique Desk Chair', '2012-11-10', 300.00, 4); INSERT INTO ITEM VALUES( NULL, 'Dining Table Linens', '2012-11-14', 600.00, 1); INSERT INT0 ITEM VALUES( NULL, 'Candles', '2012-11-14', 30.00, 1); INSERT INT0 ITEM VALUES( NULL, 'Candles', '2012-11-14', 27.00, 1); INSERT INTO ITEM VALUES( NULL, 'Desk Lamp', '2012-11-14', 150.00, 3); INSERT INTO ITEM VALUES( NULL, 'Dining Table Linens', '2012-11-14', 450.00, 1); INSERT INTO ITEM VALUES( NULL, 'Book Shelf', '2012-11-21', 150.00, 5); INSERT INTO ITEM VALUES( NULL, 'Antique Chair', '2012-11-21', 750.00, 6); INSERT INTO ITEM VALUES( NULL, 'Antique Chair', '2012-11-21', 1050.00, 6); INSERT INTO ITEM VALUES( NULL, 'Antique Candle Holders', '2012-11-28', 210.00, 2); INSERT INTO ITEM VALUES( NULL, 'Antique Desk', '2013-01-05', 1920.00, 2); INSERT INTO ITEM VALUES( NULL, 'Antique Desk', '2013-01-05', 2100.00, 2); INSERT INTO ITEM VALUES( NULL, 'Antique Desk Chair', '2013-01-06', 285.00, 9); INSERT INTO ITEM VALUES( NULL, 'Antique Desk Chair', '2013-01-06', 339.00, 9); INSERT INTO ITEM VALUES( NULL, 'Desk Lamp', '2013-06-06', 150.00, 10); INSERT INTO ITEM VALUES( NULL, 'Desk Lamp', '2013-06-06', 150.00, 10); INSERT INTO ITEM VALUES( NULL, 'Desk Lamp', '2013-06-06', 144.00, 3); INSERT INTO ITEM VALUES( NULL, 'Antique Dining Table', '2013-01-10', 3000.00, 7); INSERT INTO ITEM VALUES( NULL, 'Antique Sideboard', '2013-01-11', 2700.00, 8); INSERT INTO ITEM VALUES( NULL, 'Dining Table Chairs', '2013-01-11', 5100.00, 9); INSERT INTO ITEM VALUES( NULL, 'Dining Table Linens', '2013-01-12', 450.00, 1); INSERT INTO ITEM VALUES( NULL, 'Dining Table Linens', '2013-01-12', 480.00, 1); INSERT INT0 ITEM VALUES( NULL, 'Candles', '2013-01-17', 30.00, 1); INSERT INTO ITEM VALUES( NULL, 'Candles', '2013-01-17', 36.00, 1); INSERT INTO SALE VALUES(NULL, 1,2,20121214,3500.00,290.50,3790.50 ); INSERT INTO SALE VALUES(NULL, 2,3,20121215,1000.00,83.00,1083.00 ); INSERT INTO SALE VALUES(NULL, 3, 1,20121215,50.00,4.15,54.15 ); INSERT INTO SALE VALUES(NULL, 4, 4, '2012-12-23', 45.00, 3.74, 48.74); INSERT INTO SALE VALUES(NULL, 1,5,20130105 ', 250.00,20.75,270.75 ); INSERT INTO SALE VALUES(NULL, 5,18,20130110 ', 750.00,62.25,812.25 ); INSERT INTO SALE VALUES(NULL, 6,11,20130112,250.00,20.75,270.75 ); INSERT INTO SALE VALUES(NULL, 2,15,20130115 ', 3000.00,249.00,3249.00 ); INSERT INTO SALE VALUES(NULL, 5,20,20130125,350.00,29.05,379.05 ); INSERT INTO SALE VALUES(NULL, 7, 9, '2013-02-04' , 14250.00, 1182.75, 15432.75); INSERT INTO SALE VALUES(NULL, 8, 10, '2013-02-04', 250.00,20.75,270.75 ); INSERT INTO SALE VALUES(NULL, 5, 12, '2013-02-07', 50.00, 4.15, 54.15); INSERT INTO SALE VALUES(NULL, 9,21, '2013-02-07', 4500.00, 373.50, 4873.50); INSERT INTO SALE VALUES(NULL, 10,6,20130211,3675.00,305.03,3980.03 ); INSERT INTO SALE VALUES(NULL, 2,8,20130211,800.00,66.40,866.40 )