A. Write SQL CREATE TABLE statements for each of these tables. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading deletions and justify those assumptions.

B. Write SQL statements to insert at least three rows of data into each of these tables using the tables shown below. Assume that all surrogate key column values will be supplied by the DBMS.

C.Write an SQL statement to list ItemID and ItemDescription for all items that cost $1000 or more.

D.Write an SQL statement to list ItemNumber and Description for all items that cost $1000 or more and were purchased from a vendor whose CompanyName starts with the letters New.

E.Write an SQL statement to list LastName, FirstName, and Phone of the customer who made the purchase with SaleID 1. Use a subquery.

F. Write an SQL statement to list LastName, FirstName, and Phone of the customers who made the purchase with SaleIDs 1, 2, and 3. Use a subquery.

G.Write an SQL statement to list LastName, FirstName, and Phone of customers who have made at least one purchase with SubTotal greater than $500. Use a subquery.

H.Write an SQL statement to list LastName, FirstName, and Phone of customers who have purchased an item that has an ItemPrice of $500 or more. Use a subquery.

I. Write an SQL statement to list LastName, FirstName, and Phone of customers who have purchased an item that was supplied by a vendor with a CompanyName that begins with the letter L. Use a subquery.

A.CREATE TABLE CUSTOMER CustomerID Int NOT NULL, LastName Char (25) NOT NULL, First Name Char (25) NOT NULL, Address Char (35) NULL, City Char (35) NULL, State Char (2) NULL, ZIP Char (10) NULL, Phone Char (12) NOT NULL, Email VarChar (100) NULL 1 2 Customer 1 2 4 4 5 Last Name Shire Goodyear Bancroft Griffith Tiemey Anderson Svane Walsh Enquist Anderson First Name Robert Katherine Chris John Doris Donna Jack Denesha Craig Rose Address 6225 Evanston Ave N 7335 11th Ave NE 12605 NE 6th Street 335 Aloha Street 14510 NE 4th Street 1410 Hillcrest Parkway 3211 42nd Street 6712 24th Avenue NE 534 15th Street 6823 17th Ave NE City State Seattle WA Seattle WA Bellevue WA Seattle WA Bellevue WA Mt. Vemon WA Seattle WA Redmond WA Belingham WA Seattle WA ZIP Phone 98103 206-524-2433 98105 206-524-3544 98005 425-635-9788 98109206-524-4655 98005 425-635-8677 98273 360-538-7566 98115 206-524-5766 98053 425-635-7566 98225 360-538-6455 98105 206-524-6877 Email Robert Shire@somewhere.com Katherine Goodyear@somewhere.com Chris. Bancroft @somewhere.com John Griffith@somewhere.com Doris. Tiemey@somewhere.com Donna Anderson@elsewhere.com Jack.Svane@somewhere.com Denesha. Walsh@somewhere.com Craig. Enquist@elsewhere.com Rose. Anderson@elsewhere.com 8 8 10 10 Employeeld Last Name Stuart Stuart Stuart Orange Griffith First Name Anne George Mary William John Phone Email 206-527-0010 Anne. Stuart@QACS.com 206-527-0011 George Stuart@QACS.com 206-527-0012 Mary Stuart @QACS.com 206-527-0013 William Orange@QACS.com 206-527-0014 John Griffith@QACS.com 5 VendorID Company Name Linens and Things European Specialties Lamps and Lighting NULL NULL New York Brokerage NULL NULL Speciaty Antiques General Antiques Contact Last Name Huntington Tadema Swanson Lee Harrison Smith Walsh Bancroft Nelson Gamer Contact First Name Anne Ken Sally Andrew Denise Address 1515 NW Market Street 6123 15th Avenue NW 506 Prospect Street 1102 3rd Street 533 10th Avenue 621 Roy Street 6712 24h Avenue NE 12605 NE Bth Street 2512 Lucky Street 2515 Lucky Street Seattle Seattle Seattle Kirkdand Kirkland Seattle Redmond Belevue San Francisco San Francisco State WA WA WA WA WA WA WA WA CA CA ZIP Phone Far Email 98107 206-325-6755 206-329-9675 LAT@business.com 98107 206-325-7866 206-329-9786 ES@business.com 98109206-325-8977 206-329-9897 LAL@business.com 98033 425-746-5433 NULL Andrew.Lee@somewhere.com 98033 425-746-4322 NULL Denise.Hamison@somewhere.com 98109 206-325-9088 206-329-9908 NYB@business.com 98053 425-635-7566 NULL D enesha. Walsh@somewhere.com 98005 425-635-9788 425-639-9978 Chris Bancroft@somewhere.com 94110 415-422-2121 415-429-9212 SA@business.com 94110 415-422-3232415-429-9323 GA@business.com Mark 7 7 Denesha Chris Fred Patty 9 10 10 T B View 125% Zoom + Add Page ul I Chart Text 2 Shape = Media Comment Insert Table Collaborate Format Document Sales Customer Employeeld 76 SeleDate 2012-12-14... 2012.12.15. 2012-12-15 ... 2012-12-23 .. 2013-01-05 ... 2013-01-10 ... 2013-01-12 ... 2013-01-15 2013-01-25 ... 2013-02-04 ... 2013-02-04 ... 2013-02-07 ... 2013-02-07 ... 2013-02-11 ... 2013-02-11.... Sub Total 3500.00 1000.00 50.00 45.00 250.00 750.00 250.00 300000 350.00 14250.00 250.00 50.00 4500.00 3675.00 800.00 Tax Total 290.50 3790.50 82.00 1083.00 4.15 54.15 3.74 48.74 20.75 270.75 62.25 812 25 20.75 270.75 249.00 3249.00 29.05 379.05 1182.75 15432.75 20.75 270.75 4.15 54.15 373.50 4873.50 305033990 03 66.40 066.40 107 118 12 5 12 14 15 14 15 10 2 Salelo Customer Employeeld 65 SaleDate 2012-12-14 ... 2012-12-15... 2012-12-15... 2012-12-23 ... 2013-01-05 ... 2013-01-10 ... 2013-01-12 ... 2013-01-15.. 2013-01-25 ... 2013-02-04 ... 2013-02-04. 2013-02-07 ... 2013-02-07 ... 2013-02-11 ... 2013-02-11 ... Sub Total 3500.00 1000.00 50.00 45.00 250.00 750.00 250.00 3000.00 350.00 14250.00 25000 50.00 4500.00 3675.00 800.00 Tax 290.50 83.00 4.15 3.74 20.75 62.25 20.75 249.00 29.05 1182.75 20.75 .15 373.50 30503 66.40 Total 3790.50 1083.00 54.15 48.74 270.75 12.25 270.75 3249.00 379.05 15432.75 270.75 54.15 4873.50 3980.03 866.40 7 8 5 4 10 11 12 13 14 15 10 11 12 13 9 14 15 2 2 10 3 2 55 words o B View 125% Zoom + Add Page TuT Insert Table Chart Text Shape Media Comment Collaborate Format Document Sale 1 Sale temID 1 1 ItemID 1 Z temPrice 3000.00 500.00 5000 - 5 6 - - - 45.00 250.00 750.00 250.00 1250.00 1750.00 350.00 5000.00 8500.00 750.00 250.00 50.00 4500.00 3200.00 475.00 300.00 10 - 11 121 131 14 1 14 2 15 1 20 12 14 23 20 temi 1 1 2 3 10 Homescription Antique Desk Dining Table Linens Antique Chair Antique Chair Antique Desk Antique Desk Antique Diring Table Antique deboard Dining Table Chairs 7 8 9 13 19 20 21 Should result in this: 1 9 BerlDtem Description Antique Chair 10 Antique Chair 2 Last Name First Name Se Robert Phone 206524 2438 1 55 words B View 125% Zoom + Add Page T Insert 3 Table T Text W = Media Comment Chart Shape Collaborate Format Document 1 Last Name Shire Goodyear Bancroft First Name Robert Katherine Chria Phone 206.524-2433 206 524 3544 425 635 9788 3 Last Name Shire Goodyear Tiemey Svane Enquist Anderson First Name Robert Katherine Don's Jack Craig Rose Phone 206-524-2433 206-524-3544 425635-8677 206-524-5766 360-538-6455 206-524-6877 6 1 Last Name Shine Goodyear Tiemey Svane Enquist Anderson Ringt Name Robert Katherine Doris Jack Craig Rose Phone 206-524-2433 206-524-3544 425-635-8677 206-524-5766 360-538-6455 206-524-6877 H 6 1 2 3 4 5 6 Last Name Shire Goodyear Bancroft Giffith Tiemney Svane First Name Robert Katherine Chris John Doris Jack Phone 206-524-2433 206-524-3544 425-635-9788 206-524-4655 425-635-8677 206-524-5766 55 words o