The problems for this chapter use a database for a simple department store that sells items to customers and wants to keep track of the invoices, the selling price (if an item is on sale), and the sales tax (7 ) to be collected on some items Every customer and invoice are assigned unique numbers All items have a Universal Product Code (UPC) number and bar code assigned to each unique item Food and non carbonated beverages are not taxed, but clothing, home goods, and most other items are The structure of the tables are described in the following crows foot ERD Sample Data for the tables follow Customer Customer Id Customer FirstName Customer LastName Customer Address Customer City Customer State Customer ZipCode Customer Phone Customer Email 342 Linda Spangler 2323 Roanoke Pk Floyd VA 24987 555 555 5646 linda5646 nowhere com 505 Rodney Ray 12399 27th Ave New York NY 10097 555 555 0909 rayray7 somewhere com 776 Nancy Reno P O Box 98 Carter City KY 41155 555 555 2342 puppylove8 elsewhere com 987 Gustov Jones 333 East Main St Jamestown VA 23099 555 555 9876 gustov99 somewhere com ItemType ItemType Id ItemType Description W Woman's Clothing M Men's Clothing WA Woman's Accessories MA Men's Accessories A General Accessories O Other ItemSize ItemSize Id ItemSize Description XS Extra Small S Small M Medium L Large XL Extra Large Item UPC Item Description ItemType Id ItemSize Id Item Price Item Taxable 012345234569 Cream Blouse W S 29 95 1 012345234576 Cream Blouse W M 29 95 1 012345234588 Cream Blouse W L 29 95 1 012345234590 Cream Blouse W XL 29 95 1 012345234468 Blue Blouse W S 29 95 1 012345234475 Blue Blouse W M 29 95 1 012345234491 Blue Blouse W XL 29 95 1 012345224889 12 Inch Pearl Necklace WA 345 95 1 012345224126 10 Inch Pearl Necklace WA 298 95 1 012345334678 Explorer Cargo Shorts M S 33 45 1 012345334734 Explorer Cargo Shorts M M 33 45 1 012345334795 Explorer Cargo Shorts M L 33 45 1 012345334889 Explorer Cargo Shorts M XL 33 45 1 012345335101 Pink Silk Tie MA 67 55 1 012345335303 Pink and Green Silk Tie MA 67 55 1 012345999001 Yummy Bottled Water O 1 29 0 Invoice Invoice Number Customer Id Invoice Date Invoice Taxable Invoice NonTaxable Invoice SalesTax Invoice Total 10101 987 2015 07 27 29 95 2 58 2 1 34 63 10102 505 2015 07 27 33 45 0 2 34 35 79 10107 505 2015 07 28 59 99 1 29 4 2 65 48 10111 342 2015 07 28 262 89 0 18 4 281 29 InvoiceDetail Invoice Number UPC Detail Quantity Detail RegularPrice Detail SellingPrice 10101 012345334795 1 33 45 29 95 10101 012345999001 2 1 29 1 29 10102 012345334889 1 33 45 33 45 10107 012345335303 1 67 55 59 99 10107 012345999001 1 1 29 1 29 10111 012345234576 1 29 95 29 95 10111 012345234475 1 29 95 29 95 10111 012345224126 1 298 95 202 99 You have been tasked to generate a report from the database using a single SQL statement to do the following List all items with the number of invoices that the items have been on and the total of the sales of that item Display in order by type, description, and size Your generated output should look like UPC Item Description ItemType Id ItemSize Id Invoice Count Total Sales 012345334795 Explorer Cargo Shorts M L 1 1 012345334734 Explorer Cargo Shorts M M 0 012345334678 Explorer Cargo Shorts M S 0 012345334889 Explorer Cargo Shorts M XL 1 1 012345335101 Pink Silk Tie MA 0 012345335303 Pink and Green Silk Tie MA 1 1 012345999001 Yummy Bottled Water O 2 3 012345234475 Blue Blouse W M 1 1 012345234468 Blue Blouse W S 0 012345234491 Blue Blouse W XL 0 012345234588 Cream Blouse W L 0 012345234576 Cream Blouse W M 1 1 012345234569 Cream Blouse W S 0 012345234590 Cream Blouse W XL 0 012345224126 10 Inch Pearl Necklace WA 1 1 012345224889 12 Inch Pearl Necklace WA 0

The Answer is in the image, click to view ...

Question: The problems for this chapter use a database for a simple department store that sells items to customers and wants to keep track of the

The problems for this chapter use a database for a simple department store that sells items to customers and wants to keep track of the invoices, the selling price (if an item is on sale), and the sales tax (7%) to be collected on some items. Every customer and invoice are assigned unique numbers. All items have a Universal Product Code (UPC) number and bar-code assigned to each unique item. Food and non-carbonated beverages are not taxed, but clothing, home goods, and most other items are.

The structure of the tables are described in the following crows foot ERD:

Sample Data for the tables follow:

Customer

Customer_Id	Customer_ FirstName	Customer_ LastName	Customer_ Address	Customer_ City	Customer_ State	Customer_ ZipCode	Customer_ Phone	Customer_Email
342	Linda	Spangler	2323 Roanoke Pk	Floyd	VA	24987	555-555-5646	linda5646@nowhere.com
505	Rodney	Ray	12399 27th Ave	New York	NY	10097	555-555-0909	rayray7@somewhere.com
776	Nancy	Reno	P.O.Box 98	Carter City	KY	41155	555-555-2342	puppylove8@elsewhere.com
987	Gustov	Jones	333 East Main St	Jamestown	VA	23099	555-555-9876	gustov99@somewhere.com

ItemType

ItemType_Id	ItemType_Description
W	Woman's Clothing
M	Men's Clothing
WA	Woman's Accessories
MA	Men's Accessories
A	General Accessories
O	Other

ItemSize

ItemSize_Id	ItemSize_Description
XS	Extra Small
S	Small
M	Medium
L	Large
XL	Extra Large

Item

UPC	Item_Description	ItemType_Id	ItemSize_Id	Item_Price	Item_Taxable
012345234569	Cream Blouse	W	S	29.95	1
012345234576	Cream Blouse	W	M	29.95	1
012345234588	Cream Blouse	W	L	29.95	1
012345234590	Cream Blouse	W	XL	29.95	1
012345234468	Blue Blouse	W	S	29.95	1
012345234475	Blue Blouse	W	M	29.95	1
012345234491	Blue Blouse	W	XL	29.95	1
012345224889	12 Inch Pearl Necklace	WA		345.95	1
012345224126	10 Inch Pearl Necklace	WA		298.95	1
012345334678	Explorer Cargo Shorts	M	S	33.45	1
012345334734	Explorer Cargo Shorts	M	M	33.45	1
012345334795	Explorer Cargo Shorts	M	L	33.45	1
012345334889	Explorer Cargo Shorts	M	XL	33.45	1
012345335101	Pink Silk Tie	MA		67.55	1
012345335303	Pink and Green Silk Tie	MA		67.55	1
012345999001	Yummy Bottled Water	O		1.29	0

Invoice

Invoice_Number	Customer_Id	Invoice_Date	Invoice_Taxable	Invoice_NonTaxable	Invoice_SalesTax	Invoice_Total
10101	987	2015-07-27	29.95	2.58	2.1	34.63
10102	505	2015-07-27	33.45	0	2.34	35.79
10107	505	2015-07-28	59.99	1.29	4.2	65.48
10111	342	2015-07-28	262.89	0	18.4	281.29

InvoiceDetail

Invoice_Number	UPC	Detail_Quantity	Detail_RegularPrice	Detail_SellingPrice
10101	012345334795	1	33.45	29.95
10101	012345999001	2	1.29	1.29
10102	012345334889	1	33.45	33.45
10107	012345335303	1	67.55	59.99
10107	012345999001	1	1.29	1.29
10111	012345234576	1	29.95	29.95
10111	012345234475	1	29.95	29.95
10111	012345224126	1	298.95	202.99

You have been tasked to generate a report from the database using a single SQL statement to do the following:

List all items with the number of invoices that the items have been on and the total of the sales of that item. Display in order by type, description, and size.

Your generated output should look like:

UPC	Item_Description	ItemType_Id	ItemSize_Id	Invoice_Count	Total_Sales
012345334795	Explorer Cargo Shorts	M	L	1	1
012345334734	Explorer Cargo Shorts	M	M	0
012345334678	Explorer Cargo Shorts	M	S	0
012345334889	Explorer Cargo Shorts	M	XL	1	1
012345335101	Pink Silk Tie	MA		0
012345335303	Pink and Green Silk Tie	MA		1	1
012345999001	Yummy Bottled Water	O		2	3
012345234475	Blue Blouse	W	M	1	1
012345234468	Blue Blouse	W	S	0
012345234491	Blue Blouse	W	XL	0
012345234588	Cream Blouse	W	L	0
012345234576	Cream Blouse	W	M	1	1
012345234569	Cream Blouse	W	S	0
012345234590	Cream Blouse	W	XL	0
012345224126	10 Inch Pearl Necklace	WA		1	1
012345224889	12 Inch Pearl Necklace	WA		0

The problems for this chapter use a database for a simple department

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

plz help me with it plz YOUR NAME'S CALIFORNIA CASUAL Your Name's California Casunl is a men's and women's clothing store located in San Luis Obispo, California, that specializes in resort wear. The...

The states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation Select one: O physical data independence NOT NULL constraint O Entity...

Why is it important for us to understand how the global system of financial institutions and markets works? ros57395_ch01.qxd 12/21/04 2:45 PM Page 1 Part 1 The Global Financial System in Perspective...

I need problem E14-11 solved on page 714 of the document attached. JWCL165_c14_674-725.qxd 8/16/09 7:46 AM Page 674 14 Financial Statement Analysis Chapter STUDY OBJECTIVES After studying this...

Hello, I need help with exercise E14-3 in the attached file. (a) Prepare a horizontal analysis of the balance sheet data for Conard Corporation using 2011 as a base. (b) Prepare a vertical analysis...

CMO: How is our ad budget allocation changing this year compared to last? VP of Advertising: We're doubling our allotment to digital channels like social media ads and online search ads and paring...

I require a detailed and comprehensive completion of the attached, so I can utilize it as a study guide. Thank you! Waterways Continuing Problem1 Waterways Continuing Problem (This is a continuation...

Dynamic Medical Solutions Case Questions After reading the Dynamic Medical Solutions Case answer the questions below . Type your answers to the questions below in a Word document and send in through...

See Weygandt_Managerial_6e_Waterways.pdf Can you help me on WCP4, WCP7, WPC1, WPC6? I need it by tomorrow thanks Waterways Continuing Problem Waterways Continuing Problem WCP1 Waterways Corporation...

E-hailing is a service that calls for any form of transportation with the help of a device that can access the internet to transport a person from one point to another (Atkinson, 2019). The industry...

Prepare a Cash Receipts budget for July - October DC's Projected Revenues & Expenses Sales Cost of Goods Sold Gross Profit Admin & Selling Costs Net Income before Taxes June 85,000 52,700 32,300...

OQ 3 . What is the average risk level for Arkansas ( AR ) ?

pps/assessment/take/takejsp?course Remaining Time: 1 hour, 23 minutes, 12 seconds Question Completion Status: brewer Corporauort has the Ohowing momma Orrior August 6,000 units 30,000 units 8,500...