The problems for this chapter use a database for a simple department store that
ID: 3757017 • Letter: T
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
Suppose that we have a second table with vendor information (sample is below) in it and that we want to create a single telephone directory with both vendor and customer information in it.
The phone directory should contain the state, a name column with either the customer last name and first name concatenated with a comma or the vendor company name, city, and phone number. Output should be sorted by state then by name. Your results 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
If you have any doubts, please give me comment...
SELECT Item.UPC, Item.Item_Description, Item.ItemType_Id,
Item.ItemSize_Id, COUNT(Invoice.Invoice_Number) AS Invoice_Count,
SUM(InvoiceDetail.Detail_Qunatity) 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
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.