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

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:

Fill in the blanks (remember a blank, drop “blank” as answer)

SELECT Item.UPC, Item.Item_Description, Item.ItemType_Id,
Item.ItemSize_Id, COUNT(Invoice.Invoice_Number) AS Invoice_Count,
SUM(InvoiceDetail.Detail_Quantity) AS Total_Sales
FROM item
InvoiceDetail ON item. = .UPC
Invoice ON .Invoice_Number = InvoiceDetail.
GROUP BY Item.UPC
ORDER BY Item.ItemType_id, Item.Item_Description, Item.ItemSize_Id;

Fill in the blanks with words that would best complete the passage.