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

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

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

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

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

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

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

Assume that The Queen Anne Curiosity Shop designs a database with the following tables. CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email) EMPLOYEE (Employeeld, LastName, FirstName, Phone, Email) VENDOR (VendorID, Company Name, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email) ITEM (ItemID, Item Description, PurchaseDate, ItemCost, ItemPrice, VendorID) SALE (SaleID, CustomerID, Employeeld, SaleDate, SubTotal, Tax, Total) SALE_ITEM (Saleld, SaleltemID, ItemID, ItemPrice) The referential integrity constraints are: CustomerlD in PURCHASE must exist in CustomerID in CUSTOMER Vendorld in ITEM must exist in Vendorld in VENDOR CustomerlD in SALE must exist in CustomerID in CUSTOMER Employeeld in SALE must exist in Employeeld in EMPLOYEE Saleld in SALE_ITEM must exist in SalelD in SALE ItemID in SALE_ITEM must exist in ItemID in ITEM Assume that Customerld of CUSTOMER, Employeeld of EMPLOYEE, ItemID of ITEM, Saleld of SALE, and SaleltemID of SALE_ITEM are all surrogate keys with values as follows: CustomerlD Start at 1 Increment by 1 Employeeld Start at 1 Increment by 1 VendorID Start at 1 Increment by 1 ItemID Start at 1 Increment by 1 SaleID Start at 1 Increment by 1 A vendor may be an individual or a company. If the vendor is an individual, the Company Name field is left blank, while the ContactLastName and ContactFirstName fields must have data values. If the vendor is a company, the company name is recorded in the Company Name field, and the name of the primary contact at the company is recorded in the ContactLastName and ContactFirstName fields. You need to create additional data to populate the tables. (Alternatively, your instructor may provide you with a data set.) These tables, referential integrity constraints, and data are used as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS as appropriate to obtain your results. Name your database QACS. Write SQL statements and answer questions for this database as follows: 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. For Microsoft Access: The Microsoft Access Auto Number data type supports a starting value of 1 and an increment of 1. All surrogate values specified in the original statement of the project will work in the Access versions of these tables. Create CustomerID, Employeeld, VendorID, ItemID, Saleld as NOT NULL, Then set the data type to AutoNumber in the GUI. IMPORTANT: Do this individually for each table immediately after it has been created! Microsoft Access SQL does not support the (m, n) extension of the Numeric data type. (See "Does Not Work with Microsoft Access ANSI-89 SQL" on p. 112.) Create all columns with a Numeric (m, n) data type as Numeric, and then set the appropriate column properties in the GUI. CREATE TABLE CUSTOMER CustomerID Int NOT NULL, LastName Char (25) NOT NULL, FirstName 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, Phone Char (12) NOT NULL, Email VarChar (100) NULL, CONSTRAINT CUSTOMER_PK PRIMARY KEY (CustomerID) CREATE TABLE EMPLOYEE CREATE TABLE VENDOR ( CREATE TABLE ITEM CREATE TABLE SALE CREATE TABLE SALE_ITEM 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. The first one for CUSTOMER is done for you as an example. NOTE: If you are inserting data into MICROSOFT Access tables with an AutoNumber surrogate key. Example of MICROSOFT Access: INSERT INTO CUSTOMER (LastName, FirstName, Address, City, State, Zip, Phone, Email) VALUES Shire', 'Robert', '6225 Evanston Ave N', 'Seattle', 'WA', '98103', 206-524-2433', 'RShire@somewhere.com'); CUSTOMER Table 1 1 2 3 5 6 7 CustomerlDLast Name First Name Shire Robert 2 Goodyear Katherine 3 Bancroft Chris Griffith John 5 Tiemey Doris 6 Anderson Donna 7 Svane Jack Walsh Denesha 9 Enquist Craig 10 Anderson Rose Address 6225 Evanston Ave N 7335 11th Ave NE 1 2605 NE Bh Street 335 Aloha Street 14510 NE 4th Street 1410 Hilcrest Parkway 3211 42nd Street 6712 24th Avenue NE 534 15th Street 6823 17th Ave NE City Seattle Seattle Bellevue Seattle Bellevue Mt. Vemon Seattle Redmond Bellingham Seattle State WA WA WA WA WA WA WA WA WA WA ZIP Phone 98103 206-524-2433 98105 206-524-3544 98005 425 635-9788 98109 206-524-4655 98005 425-635-8677 98273360-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 9 10 INSERT INTO EMPLOYEE EMPLOYEE Table Employeeld 2 3 4 5 2 3 4 5 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 903 words INSERT INTO EMPLOYEE EMPLOYEE Table Employeeld 11 2 2 3 3 4 4 15 5 Last Name Stuart Stuart Stuart Orange Griffith First Name Anne George Mary William John Phone 206-527-0010 206-527-0011 206-527-0012 206-527-0013 206-527-0014 Email Anne Stuart @QACS.com George Stuart@QACS.com Mary Stuart@QACS.com William Orange @QACS.com John Griffith@QACS.com INSERT INTO VENDOR VENDOR Table VendorID Company Name Linens and Things European Specialties Lamps and Lighting NULL NULL New York Brokerage NULL NULL Specialty Antiques General Antiques Contact Last Name Huntington Tadema Swanson Lee Harrison Smith Walsh Bancroft Nelson Gamer Contact First Name Arne Ken Saly Andrew Denise Mark Denesha Chris Address 1515 NW Market Street 6123 15th Avenue NW 506 Prospect Street 1102 3rd Street 533 10th Avenue 621 Ray Street 6712 24th Avenue NE 12605 NE 6th Street 2512 Lucky Street 2515 Lucky Street City Seattle Seattle Seattle Kirkland Kakland Seattle Redmond Bellevue San Francisco San Francisco State WA WA WA WA WA WA WA WA CA CA ZIP Phone Fax 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 98033425-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 Denesha. 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-3232 415-429-9323 GA@business.com 7 7 88 9 9 10 10 Fred Patty INSERT INTO ITEM 903 words ITEM Table ItemID M N OO ENMODENANNN Item Description Antique Desk Antique Desk Chair Dining Table Linens Candles Candles Desk Lamp Dining Table Linens Book Shelf Antique Chair Antique Chair Antique Candle Holders Antique Desk Antique Desk Antique Desk Chair Antique Desk Chair Desk Lamp Desk Lamp Desk Lamp Antique Dining Table Antique Sideboard Dining Table Chairs Dining Table Linens Dining Table Linens Candles Candles Purchase Date 2016-11-07 2016-11-10 2016-11-14 2016-11-14 2016-11-14 2016-11-14 2016-11-14 2016-11-21 2016-11-21 2016-11-21 2016-11-28 2017-01-05 2017-01-05 2017-01-06 2017-01-06 2017-01-06 2017-01-06 2017-01-06 2017-01-10 2017-01-11 2017-01-11 2017-01-12 2017-01-12 2017-01-17 2017-01-17 ItemCost 1800.00 300.00 600.00 30.00 27.00 150.00 450.00 150.00 750.00 1050.00 210.00 1920.00 2100.00 285.00 339.00 150.00 150.00 144.00 3000.00 2700.00 5100.00 450.00 480.00 30.00 36.00 Item Price VendorID 3000.00 2 500.00 4 1000.00 50.00 45.00 250.00 750.00 250.00 1250.00 750.00 350.00 3200.00 3500.00 475.00 565.00 250.00 10 250.00 10 240.00 3 5000.00 7 4500.008 8500.00 9 750.00 800.00 50.00 60.00 WONNNO 15 16 17 18 19 MOND INSERT INTO SALE SALE Table 903 words Customer Employeeld SalelD 111 2 2 2 w umow -N Sale Date 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 ... un covono Sub Total 3500.00 1000.00 50.00 45.00 250.00 750.00 250.00 3000.00 350.00 14250.00 250.00 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 4.15 373.50 305.03 66.40 Total 3790.50 1083.00 54.15 48.74 270.75 812.25 270.75 3249.00 379.05 15432.75 270.75 54.15 4873.50 3980.03 866.40 - 11 12 13 14 15 11 12 13 14 15 5 9 10 2 NWN INSERT INTO SALE_ITEM SALE_ITEM Table Sale SaleItemID ItemID 2 2 3 8 7 98 8 10 ItemPrice 3000.00 500.00 1000.00 50.00 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 800.00 9 19 21 14 10 11 22 17 24 18 14 19 14 2015 20 12 14 23 1 C. Write an SQL statement to list ItemID and Item Description for all items that cost $1000 or more. Assume that The Queen Anne Curiosity Shop designs a database with the following tables. CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email) EMPLOYEE (Employeeld, LastName, FirstName, Phone, Email) VENDOR (VendorID, Company Name, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email) ITEM (ItemID, Item Description, PurchaseDate, ItemCost, ItemPrice, VendorID) SALE (SaleID, CustomerID, Employeeld, SaleDate, SubTotal, Tax, Total) SALE_ITEM (Saleld, SaleltemID, ItemID, ItemPrice) The referential integrity constraints are: CustomerlD in PURCHASE must exist in CustomerID in CUSTOMER Vendorld in ITEM must exist in Vendorld in VENDOR CustomerlD in SALE must exist in CustomerID in CUSTOMER Employeeld in SALE must exist in Employeeld in EMPLOYEE Saleld in SALE_ITEM must exist in SalelD in SALE ItemID in SALE_ITEM must exist in ItemID in ITEM Assume that Customerld of CUSTOMER, Employeeld of EMPLOYEE, ItemID of ITEM, Saleld of SALE, and SaleltemID of SALE_ITEM are all surrogate keys with values as follows: CustomerlD Start at 1 Increment by 1 Employeeld Start at 1 Increment by 1 VendorID Start at 1 Increment by 1 ItemID Start at 1 Increment by 1 SaleID Start at 1 Increment by 1 A vendor may be an individual or a company. If the vendor is an individual, the Company Name field is left blank, while the ContactLastName and ContactFirstName fields must have data values. If the vendor is a company, the company name is recorded in the Company Name field, and the name of the primary contact at the company is recorded in the ContactLastName and ContactFirstName fields. You need to create additional data to populate the tables. (Alternatively, your instructor may provide you with a data set.) These tables, referential integrity constraints, and data are used as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS as appropriate to obtain your results. Name your database QACS. Write SQL statements and answer questions for this database as follows: 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. For Microsoft Access: The Microsoft Access Auto Number data type supports a starting value of 1 and an increment of 1. All surrogate values specified in the original statement of the project will work in the Access versions of these tables. Create CustomerID, Employeeld, VendorID, ItemID, Saleld as NOT NULL, Then set the data type to AutoNumber in the GUI. IMPORTANT: Do this individually for each table immediately after it has been created! Microsoft Access SQL does not support the (m, n) extension of the Numeric data type. (See "Does Not Work with Microsoft Access ANSI-89 SQL" on p. 112.) Create all columns with a Numeric (m, n) data type as Numeric, and then set the appropriate column properties in the GUI. CREATE TABLE CUSTOMER CustomerID Int NOT NULL, LastName Char (25) NOT NULL, FirstName 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, Phone Char (12) NOT NULL, Email VarChar (100) NULL, CONSTRAINT CUSTOMER_PK PRIMARY KEY (CustomerID) CREATE TABLE EMPLOYEE CREATE TABLE VENDOR ( CREATE TABLE ITEM CREATE TABLE SALE CREATE TABLE SALE_ITEM 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. The first one for CUSTOMER is done for you as an example. NOTE: If you are inserting data into MICROSOFT Access tables with an AutoNumber surrogate key. Example of MICROSOFT Access: INSERT INTO CUSTOMER (LastName, FirstName, Address, City, State, Zip, Phone, Email) VALUES Shire', 'Robert', '6225 Evanston Ave N', 'Seattle', 'WA', '98103', 206-524-2433', 'RShire@somewhere.com'); CUSTOMER Table 1 1 2 3 5 6 7 CustomerlDLast Name First Name Shire Robert 2 Goodyear Katherine 3 Bancroft Chris Griffith John 5 Tiemey Doris 6 Anderson Donna 7 Svane Jack Walsh Denesha 9 Enquist Craig 10 Anderson Rose Address 6225 Evanston Ave N 7335 11th Ave NE 1 2605 NE Bh Street 335 Aloha Street 14510 NE 4th Street 1410 Hilcrest Parkway 3211 42nd Street 6712 24th Avenue NE 534 15th Street 6823 17th Ave NE City Seattle Seattle Bellevue Seattle Bellevue Mt. Vemon Seattle Redmond Bellingham Seattle State WA WA WA WA WA WA WA WA WA WA ZIP Phone 98103 206-524-2433 98105 206-524-3544 98005 425 635-9788 98109 206-524-4655 98005 425-635-8677 98273360-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 9 10 INSERT INTO EMPLOYEE EMPLOYEE Table Employeeld 2 3 4 5 2 3 4 5 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 903 words INSERT INTO EMPLOYEE EMPLOYEE Table Employeeld 11 2 2 3 3 4 4 15 5 Last Name Stuart Stuart Stuart Orange Griffith First Name Anne George Mary William John Phone 206-527-0010 206-527-0011 206-527-0012 206-527-0013 206-527-0014 Email Anne Stuart @QACS.com George Stuart@QACS.com Mary Stuart@QACS.com William Orange @QACS.com John Griffith@QACS.com INSERT INTO VENDOR VENDOR Table VendorID Company Name Linens and Things European Specialties Lamps and Lighting NULL NULL New York Brokerage NULL NULL Specialty Antiques General Antiques Contact Last Name Huntington Tadema Swanson Lee Harrison Smith Walsh Bancroft Nelson Gamer Contact First Name Arne Ken Saly Andrew Denise Mark Denesha Chris Address 1515 NW Market Street 6123 15th Avenue NW 506 Prospect Street 1102 3rd Street 533 10th Avenue 621 Ray Street 6712 24th Avenue NE 12605 NE 6th Street 2512 Lucky Street 2515 Lucky Street City Seattle Seattle Seattle Kirkland Kakland Seattle Redmond Bellevue San Francisco San Francisco State WA WA WA WA WA WA WA WA CA CA ZIP Phone Fax 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 98033425-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 Denesha. 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-3232 415-429-9323 GA@business.com 7 7 88 9 9 10 10 Fred Patty INSERT INTO ITEM 903 words ITEM Table ItemID M N OO ENMODENANNN Item Description Antique Desk Antique Desk Chair Dining Table Linens Candles Candles Desk Lamp Dining Table Linens Book Shelf Antique Chair Antique Chair Antique Candle Holders Antique Desk Antique Desk Antique Desk Chair Antique Desk Chair Desk Lamp Desk Lamp Desk Lamp Antique Dining Table Antique Sideboard Dining Table Chairs Dining Table Linens Dining Table Linens Candles Candles Purchase Date 2016-11-07 2016-11-10 2016-11-14 2016-11-14 2016-11-14 2016-11-14 2016-11-14 2016-11-21 2016-11-21 2016-11-21 2016-11-28 2017-01-05 2017-01-05 2017-01-06 2017-01-06 2017-01-06 2017-01-06 2017-01-06 2017-01-10 2017-01-11 2017-01-11 2017-01-12 2017-01-12 2017-01-17 2017-01-17 ItemCost 1800.00 300.00 600.00 30.00 27.00 150.00 450.00 150.00 750.00 1050.00 210.00 1920.00 2100.00 285.00 339.00 150.00 150.00 144.00 3000.00 2700.00 5100.00 450.00 480.00 30.00 36.00 Item Price VendorID 3000.00 2 500.00 4 1000.00 50.00 45.00 250.00 750.00 250.00 1250.00 750.00 350.00 3200.00 3500.00 475.00 565.00 250.00 10 250.00 10 240.00 3 5000.00 7 4500.008 8500.00 9 750.00 800.00 50.00 60.00 WONNNO 15 16 17 18 19 MOND INSERT INTO SALE SALE Table 903 words Customer Employeeld SalelD 111 2 2 2 w umow -N Sale Date 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 ... un covono Sub Total 3500.00 1000.00 50.00 45.00 250.00 750.00 250.00 3000.00 350.00 14250.00 250.00 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 4.15 373.50 305.03 66.40 Total 3790.50 1083.00 54.15 48.74 270.75 812.25 270.75 3249.00 379.05 15432.75 270.75 54.15 4873.50 3980.03 866.40 - 11 12 13 14 15 11 12 13 14 15 5 9 10 2 NWN INSERT INTO SALE_ITEM SALE_ITEM Table Sale SaleItemID ItemID 2 2 3 8 7 98 8 10 ItemPrice 3000.00 500.00 1000.00 50.00 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 800.00 9 19 21 14 10 11 22 17 24 18 14 19 14 2015 20 12 14 23 1 C. Write an SQL statement to list ItemID and Item Description for all items that cost $1000 or more