Write the SQL queries that accomplish the following tasks in the ZAGI Retail Company Sales Department Database:
Question:
Write the SQL queries that accomplish the following tasks in the ZAGI Retail Company Sales Department Database:
a. Display the VendorID and VendorName for all vendors.
b. Display the CustomerName and CustomerZip for all customers.
c. Display the ProductID, ProductName, and ProductPrice for products with a ProductPrice of $100 or higher.
d. Display the ProductID, ProductName, ProductPrice, and VendorName for all products. Sort the results by ProductID.
e. Display the ProductID, ProductName, ProductPrice, VendorName, and CategoryName for all products. Sort the results by ProductID.
f. Display the ProductID, ProductName, and ProductPrice for products in the category whose CategoryName value is Camping. Sort the results by ProductID.
g. Display the TID, CustomerName, and TDate for sales a transaction involving a customer buying a product whose ProductName is Dura Boot
h. Display the RegionID, RegionName, and number of stores in the region for all regions.
i. For each product category, display the CategoryID, CategoryName, and average price of a product in the category
j. For each product category, display the CategoryID and the total number of items purchased in the category.
k. Display the TID and the total number of items (of all products) sold within the transaction for all sales transactions whose total number of items (of all products) sold within the transaction is greater than five.
l. Display the ProductID and ProductName of the cheapest product.
m. Display the ProductID, ProductName, and VendorName for products whose price is below the average price of all products.
n. Display the ProductID for the product that has been sold the most (i.e., that has been sold in the highest quantity).
o. Rewrite Query 29 using a JOIN statement (no nested queries).
p. Rewrite Query 30 using a JOIN statement (no nested queries).
Step by Step Answer:
Database Systems Introduction to Databases and Data Warehouses
ISBN: 978-0132575676
1st edition
Authors: Nenad Jukic, Susan Vrbsky, Svetlozar Nestorov