The problems for this chapter use a database for a simple department store that
ID: 3723696 • 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
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
Hi Student,
Please find the answer below :
SELECT NAME, CITY, STATE, PHONE FROM ( SELECT CUSTOMER_LASTNAME || ',' || CUSTOMER_FIRSTNAME AS NAME, CUSTOMER_CITY AS CITY, CUSTOMER_STATE AS STATE, CUSTOMER_PHONE AS PHONE FROM CUSTOMER BLANK
UNION
SELECT VENDOR_COMPANY_NAME AS NAME, VENDOR_CITY AS CITY, VENDOR_STATE AS STATE, VENDOR_PHONE AS PHONE FROM VENDOR BLANK )
ORDER BY STATE,NAME;
If you need any clarification please reply . Would be happy to help.
And if you like this question, give a thumbs up! Cheers ! Happy Learning :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.