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

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

Sample Data for the tables follow:

Customer

ItemType

ItemSize

Item

Invoice

InvoiceDetail

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:

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

Explanation / Answer

select Item.UPC, Item.Item_Description, Item.ItemType_Id,

Item.ItemSize_Id,COUNT(Invoice.Invoice_Number) AS Invoice_Count,

SUM(InvoiceDetail.Detai_Quantity) AS Total_Sales

blank FROM item LEFT JOIN InvoiceDetail ON item.UPC = InvoiceDetail.UPC

LEFT JOIN Invoice ON Invoice.Invoice_Number = InvoiceDetail.Invoice_Number

GROUP BY Item.UPC

ORDER BY Item.ItemType_id, Item.Item_Description, Item.ItemSize_ID;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.