The problems for this chapter use a database for a simple department store that
ID: 3723697 • 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:
Fill in the blanks (remember a blank, drop "blank" as answer)
SELECT , , ,
FROM ( SELECT || ', ' || Customer_FirstName AS Name,
Customer_City , Customer_State AS State, Customer_Phone AS Phone
FROM Customer
SELECT Vendor_CompanyName AS Name,
Vendor_City AS City, Vendor_State AS State, Vendor_Phone AS Phone
FROM Vendor )
State, Name;
Fill in the blanks with words that would best complete the passage.
State
City
blank
Name
ORDER
Customer_LastName
Phone
blank
UNION
AS
BY
city
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
Filled Query:
----------------
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
UNION
SELECT Vendor_CompanyName AS Name,
Vendor_City AS City, Vendor_State AS State, Vendor_Phone AS Phone
FROM Vendor
)
ORDER BY State, Name;
-----------------------------------------------------
|| ', ' || -> This symbol used to concat the First name and Last name as a name.
The two sub queries giving same column values (Name, City, State, Phone).
ORDER BY -> These keywords used to sort the result by State and name.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.