This question is from The Queen Anne Curiosity Case questions from Database Processing: Fundamentals, Design, and Implementation, 16th edition textbook.

We have executed the following SQL CREATE TABLE statements and obtains the QACS tables by a set of INSERT statements for each of the created tables. Now give the required answers to the questions below the tables.

a. Using the above QACS database, write a an UPDATE statement to change values of ITEM.ItemDescription from Desk Lamp to Desk Lamps.

b. Write an SQL statement to create a view called SaleSummaryView that contains SALE.SaleID, SALE.SaleDate, SALE_ITEM.SaleItemID, SALE_ITEM.ItemID, ITEM.ItemDescription, and ITEM.ItemPrice.

c. Write an SQL statement to create a view called CustomerSaleSummaryView that contains SALE.SaleID, SALE.SaleDate, CUSTOMER.LastName, CUSTOMER,FirstName, SALE_ITEM.SaleItemID, SALE_ITEM.ItemID, ITEM.ItemDescription, and ITEM.ItemPrice.

d. Show a table resulted by running the following SQL statement:

SELECT *

FROM CustomerSaleSummaryView

WHERE ItemID = 3;

e. Write an SQL statement to create a view called CustomerSaleHistoryView that:

(1) Includes all columns of CustomerSaleSummaryView except SALE_ITEM.SaleItemID, ITEM.ItemID, and SALE_ITEM.ItemDescription.

(2) Groups by SALE.SaleID, CUSTOMER.LastName, and CUSTOMER.FirstName, and SALE.SaleDate.

(3) Sums and averages SALE_ITEM.ItemPrice for each order for each customer.

f. Show a table resulted by running the following SQL statement:

SELECT *

FROM CustomerSaleHistoryView

WHERE LastName = 'Shire';

CREATE TABLE CUSTOMER ( CustomerID Int NOT NULL auto_increment, 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, Email VarChar(100) NULL, CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID)); CREATE TABLE EMPLOYEE ( EmployeeID Int NOT NULL auto_increment, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, Phone Char(12) NULL, Email VarChar(100) NOT NULL UNIQUE, CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeID)); CREATE TABLE VENDOR ( VendorID Int NOT NULL auto_increment, CompanyName Char(100) NULL, ContactLastName Char(25) NOT NULL, ContactFirstName 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, Fax Char(12) NULL, Email VarChar(100) NULL, CONSTRAINT VENDOR_PK PRIMARY KEY(VendorID)); CREATE TABLE ITEM( ItemID Int NOT NULL auto_increment, ItemDescription VarChar(255) NOT NULL, PurchaseDate Date NOT NULL, ItemCost Numeric (9,2) NOT NULL, ItemPrice Numeric (9,2) NOT NULL, VendorID Int NOT NULL, CONSTRAINT ITEM PK PRIMARY KEY (ItemID), CONSTRAINT ITEM_VENDOR_FK FOREIGN KEY (VendorID) REFERENCE S VENDOR(VendorID)); CREATE TABLE SALE ( SaleID Int NOT NULL auto_increment, CustomerID Int NOT NULL, EmployeeID Int NOT NULL, SaleDate Date NOT NULL, SubTotal Numeric (15,2) NULL, Tax Numeric (15,2) NULL, Total Numeric (15,2) NULL, CONSTRAINT SALE_PK PRIMARY KEY (SaleID), CONSTRAINT SALE_CUSTOMER_FK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID), CONSTRAINT SALE EMPLOYEE_FK FOREIGN KEY(EmployeeID) REFERENCES EMPLOYEE (EmployeeID)); CUSTOMER TABLE \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline \begin{tabular}{l} Customer \\ ID \end{tabular} & LastName & FirstName & Address & City & State & ZIP & Phone & Email \\ \hline 1 & Shire & Robert & \begin{tabular}{l} 6225 \\ Evanston \\ Ave N \\ \end{tabular} & Seattle & WA & 98103 & \begin{tabular}{l} 206 \\ 524 \\ 2433 \\ \end{tabular} & Robert.Shire@somewhere.com \\ \hline 2 & Goodyear & Katherine & \begin{tabular}{l} 7335 \\ 11th Ave \\ NE \\ \end{tabular} & Seattle & WA & 98105 & \begin{tabular}{l} 206 \\ 524 \\ 3544 \end{tabular} & Katherine.Goodyear@somewhere.com \\ \hline 3 & Bancroft & Chris & \begin{tabular}{l} 12605 \\ NE 6th \\ Street \\ \end{tabular} & Bellevue & WA & 98005 & \begin{tabular}{l} 425 \\ 635 \\ 9788 \end{tabular} & Chris.Bancroft@somewhere.com \\ \hline 4 & Griffith & John & \begin{tabular}{l} 335 \\ Aloha \\ Street \end{tabular} & Seattle & WA & 98109 & \begin{tabular}{l} 206 \\ 524 \\ 4655 \end{tabular} & John.Griffith@somewhere.com \\ \hline 5 & Tierney & Doris & \begin{tabular}{l} 14510 \\ NE 4th \\ Street \\ \end{tabular} & Bellevue & WA & 98005 & \begin{tabular}{l} 425 \\ 635 \\ 8677 \end{tabular} & Doris.Tierney@somewhere.com \\ \hline 6 & Anderson & Donna & \begin{tabular}{l} 1410 \\ Hillcrest \\ Parkway \end{tabular} & Mt. Vernon & WA & 98273 & \begin{tabular}{l} 360 \\ 538 \\ 7566 \end{tabular} & Donna.Anderson@elsewhere.com \\ \hline 7 & Svane & Jack & \begin{tabular}{l} 3211 \\ 42nd \\ Street \end{tabular} & Seattle & WA & 98115 & \begin{tabular}{l} 206 \\ 524 \\ 5766 \end{tabular} & Jack.Svane@somewhere.com \\ \hline 8 & Walsh & Denesha & \begin{tabular}{l} 6712 \\ 24 th \\ Avenue \\ NE \end{tabular} & Redmond & WA & 98053 & \begin{tabular}{l} 425 \\ 635 \\ 7566 \end{tabular} & Denesha.Walsh@somewhere.com \\ \hline 9 & Enquist & Craig & \begin{tabular}{l} 53415 th \\ Street \end{tabular} & Bellingham & WA & 98225 & \begin{tabular}{l} 360 \\ 538 \\ 6455 \end{tabular} & Craig.Enquist@elsewhere.com \\ \hline 10 & Anderson & Rose & \begin{tabular}{l} 6823 \\ 17 th Ave \\ NE \end{tabular} & Seattle & WA & 98105 & \begin{tabular}{l} 206 \\ 524 \\ 6877 \end{tabular} & Rose.Anderson@elsewhere.com \\ \hline \end{tabular} EMPLOYEE TABLE \begin{tabular}{|r|l|l|l|l|} \hline EmployeeID & LastName & FirstName & Phone & Email \\ \hline 1 & Stuart & Anne & 2065270010 & Anne.Stuart@QACS.com \\ \hline 2 & Stuart & George & 2065270011 & George.Stuart@QACS.com \\ \hline 3 & Stuart & Mary & 2065270012 & Mary.Stuart@QACS.com \\ \hline 4 & Orange & William & 2065270013 & William.Orange@QACS.com \\ \hline 5 & Griffith & John & 2065270014 & John.Griffith@QACS.com \\ \hline \end{tabular} ITEM TABLE \begin{tabular}{|c|c|c|c|c|c|} \hline ItemID & ItemDescription & PurchaseDate & ItemCost & ItemPrice & VendorID \\ \hline 1 & Antique Desk & 2014-11-07 & 1800 & 3000 & 2 \\ \hline 2 & Antique Desk Chair & 2014-11-10 & 300 & 500 & 4 \\ \hline 3 & Dining Table Linens & 2014-11-14 & 600 & 1000 & 1 \\ \hline 4 & Candles & 2014-11-14 & 30 & 50 & 1 \\ \hline 5 & Candles & 2014-11-14 & 27 & 45 & 1 \\ \hline 6 & Desk Lamp & 2014-11-14 & 150 & 250 & 3 \\ \hline 7 & Dining Table Linens & 2014-11-14 & 450 & 750 & 1 \\ \hline 8 & Book Shelf & 2014-11-21 & 150 & 250 & 5 \\ \hline 9 & Antique Chair & 2014-11-21 & 750 & 1250 & 6 \\ \hline 10 & Antique Chair & 2014-11-21 & 1050 & 1750 & 6 \\ \hline 11 & Antique Candle Holders & 2014-11-28 & 210 & 350 & 2 \\ \hline 12 & Antique Desk & 2015-01-05 & 1920 & 3200 & 2 \\ \hline 13 & Antique Desk & 2015-01-05 & 2100 & 3500 & 2 \\ \hline 14 & Antique Desk Chair & 2015-01-06 & 285 & 475 & 9 \\ \hline 15 & Antique Desk Chair & 2015-01-06 & 339 & 565 & 9 \\ \hline 16 & Desk Lamp & 2015-01-06 & 150 & 250 & 10 \\ \hline 17 & Desk Lamp & 2015-01-06 & 150 & 250 & 10 \\ \hline 18 & Desk Lamp & 2015-01-06 & 144 & 240 & 3 \\ \hline 19 & Antique Dining Table & 2015-01-10 & 3000 & 5000 & 7 \\ \hline 20 & Antique Sideboard & 2015-01-11 & 2700 & 4500 & 8 \\ \hline 21 & Dining Table Chairs & 2015-01-11 & 5100 & 8500 & 9 \\ \hline 22 & Dining Table Linens & 2015-01-12 & 450 & 750 & 1 \\ \hline 23 & Dining Table Linens & 2015-01-12 & 480 & 800 & 1 \\ \hline 24 & Candles & 2015-01-17 & 30 & 50 & 1 \\ \hline 25 & Candles & 2015-01-17 & 36 & 60 & 1 \\ \hline \end{tabular} SALE TABLE \begin{tabular}{|r|r|r|r|r|r|r|} \hline SaleID & CustomerID & EmployeeID & SaleDate & \multicolumn{1}{l|}{ SubTotal } & Tax & \multicolumn{1}{l|}{ Total } \\ \hline 1 & 1 & 1 & 20141214 & 3500 & 290.5 & 3790.5 \\ \hline 2 & 2 & 2 & 20141215 & 1000 & 83 & 1083 \\ \hline 3 & 3 & 1 & 20141215 & 50 & 4.15 & 54.15 \\ \hline 4 & 4 & 3 & 20141223 & 45 & 3.74 & 48.74 \\ \hline 5 & 1 & 5 & 20150105 & 250 & 20.75 & 270.75 \\ \hline 6 & 5 & 5 & 20150110 & 750 & 62.25 & 812.25 \\ \hline 7 & 6 & 4 & 20150112 & 250 & 20.75 & 270.75 \\ \hline 8 & 2 & 1 & 20150115 & 3000 & 249 & 3249 \\ \hline 9 & 5 & 5 & 20150125 & 350 & 29.05 & 379.05 \\ \hline 10 & 7 & 1 & 20150204 & 14250 & 1182.75 & 15432.75 \\ \hline 11 & 8 & 5 & 20150204 & 250 & 20.75 & 270.75 \\ \hline \end{tabular} \begin{tabular}{|r|r|r|r|r|r|r|} \hline 12 & 5 & 4 & 20150207 & 50 & 4.15 & 54.15 \\ \hline 13 & 9 & 2 & 20150207 & 4500 & 373.5 & 4873.5 \\ \hline 14 & 10 & 3 & 20150211 & 3675 & 305.03 & 3980.03 \\ \hline 15 & 2 & 2 & 20150211 & 800 & 66.4 & 866.4 \\ \hline \end{tabular} SALE ITEM TABLE \begin{tabular}{|r|r|r|r|} \hline SaleID & \multicolumn{1}{|l|}{ SaleltemID } & \multicolumn{1}{l|}{ ItemID } & \multicolumn{1}{l|}{ ItemPrice } \\ \hline 1 & 1 & 1 & 3000 \\ \hline 1 & 2 & 2 & 500 \\ \hline 2 & 1 & 3 & 1000 \\ \hline 3 & 1 & 4 & 50 \\ \hline 4 & 1 & 5 & 45 \\ \hline 5 & 1 & 6 & 250 \\ \hline 6 & 1 & 7 & 750 \\ \hline 7 & 1 & 8 & 250 \\ \hline 8 & 1 & 9 & 1250 \\ \hline 8 & 2 & 10 & 1750 \\ \hline 9 & 1 & 11 & 350 \\ \hline 10 & 1 & 19 & 5000 \\ \hline 10 & 2 & 21 & 8500 \\ \hline 10 & 3 & 22 & 750 \\ \hline 11 & 1 & 17 & 250 \\ \hline 12 & 1 & 24 & 50 \\ \hline 13 & 1 & 20 & 4500 \\ \hline 14 & 1 & 12 & 3200 \\ \hline 14 & 2 & 14 & 475 \\ \hline 15 & 1 & 23 & 800 \\ \hline & & & \\ \hline & 1 & & \\ \hline \end{tabular} VENDOR TABLE \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline VendorID & \begin{tabular}{l} Company \\ Name \\ \end{tabular} & \begin{tabular}{l} Contact \\ LastName \\ \end{tabular} & \begin{tabular}{l} Contact \\ FirstName \\ \end{tabular} & Address & City & State & ZIP & Phone & Fax & Email \\ \hline 1 & \begin{tabular}{l} Linens \\ and \\ Things \end{tabular} & Huntington & Anne & \begin{tabular}{l} 1515NW \\ Market \\ Street \\ \end{tabular} & Seattle & WA & 98107 & \begin{tabular}{l} 206 \\ 325 \\ 6755 \\ \end{tabular} & \begin{tabular}{l} 206 \\ 329 \\ 9675 \\ \end{tabular} & LAT@business.com \\ \hline 2 & \begin{tabular}{l} European \\ Specialties \end{tabular} & Tadema & Ken & \begin{tabular}{l} 6123 \\ 15th \\ Avenue \\ NW \\ \end{tabular} & Seattle & WA & 98107 & \begin{tabular}{l} 206 \\ 325 \\ 7866 \end{tabular} & \begin{tabular}{l} 206 \\ 329 \\ 9786 \end{tabular} & ES@business.com \\ \hline 3 & \begin{tabular}{l} Lamps \\ and \\ Lighting \end{tabular} & Swanson & Sally & \begin{tabular}{l} 506 \\ Prospect \\ Street \end{tabular} & Seattle & WA & 98109 & \begin{tabular}{l} 206 \\ 325 \\ 8977 \end{tabular} & \begin{tabular}{l} 206 \\ 329 \\ 9897 \\ \end{tabular} & LAL@business.com \\ \hline 4 & & Lee & Andrew & \begin{tabular}{l} 11023rd \\ Street \end{tabular} & Kirkland & WA & 98033 & \begin{tabular}{l} 425 \\ 746 \\ 5433 \end{tabular} & & \begin{tabular}{l} Andrew.Lee \\ @somewhere.com \end{tabular} \\ \hline 5 & & Harrison & Denise & \begin{tabular}{l} 53310 th \\ Avenue \end{tabular} & Kirkland & WA & 98033 & \begin{tabular}{l} 425 \\ 746 \\ 4322 \end{tabular} & & \begin{tabular}{l} Denise.Harrison \\ @somewhere.com \end{tabular} \\ \hline 6 & \begin{tabular}{l} New York \\ Brokerage \end{tabular} & Smith & Mark & \begin{tabular}{l} 621 Roy \\ Street \end{tabular} & Seattle & WA & 98109 & \begin{tabular}{l} 206 \\ 325 \\ 9088 \\ \end{tabular} & \begin{tabular}{l} 206 \\ 329 \\ 9908 \\ \end{tabular} & NYB@business.com \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline 7 & & Walsh & Denesha & \begin{tabular}{l} 6712 \\ 24 th \\ Avenue \\ NE \\ \end{tabular} & Redmond & WA & 98053 & \begin{tabular}{l} 425 \\ 635 \\ 7566 \end{tabular} & & \begin{tabular}{l} Denesha.Walsh \\ @somewhere.com \end{tabular} \\ \hline 8 & & Bancroft & Chris & \begin{tabular}{l} 12605 \\ NE 6th \\ Street \end{tabular} & Bellevue & WA & 98005 & \begin{tabular}{l} 425 \\ 635 \\ 9788 \end{tabular} & \begin{tabular}{l} 425 \\ 639 \\ 9978 \end{tabular} & \begin{tabular}{l} Chris.Bancroft \\ @somewhere.com \end{tabular} \\ \hline 9 & \begin{tabular}{l} Specialty \\ Antiques \end{tabular} & Nelson & Fred & \begin{tabular}{l} 2512 \\ Lucky \\ Street \end{tabular} & \begin{tabular}{l} San \\ Francisco \end{tabular} & CA & 94110 & \begin{tabular}{l} 415 \\ 422 \\ 2121 \end{tabular} & \begin{tabular}{l} 415 \\ 429 \\ 9212 \end{tabular} & SA@business.com \\ \hline 10 & \begin{tabular}{l} General \\ Antiques \end{tabular} & Garner & Patty & \begin{tabular}{l} 2515 \\ Lucky \\ Street \end{tabular} & \begin{tabular}{l} San \\ Francisco \end{tabular} & CA & 94110 & \begin{tabular}{l} 415 \\ 422 \\ 3232 \end{tabular} & \begin{tabular}{l} 415 \\ 429 \\ 9323 \end{tabular} & GA@business.com \\ \hline \end{tabular}