SQL Queries for Retail Database Tasks
Uploaded by
Kristen ButtsSQL Queries for Retail Database Tasks
Uploaded by
Kristen Butts- MODULE 5: SQL In Class Problems
Common questions
Powered by AIThe SQL query to list products in the 'Camping' category, sorted by ProductID, would be: SELECT ProductID, ProductName, ProductPrice FROM ProductTable WHERE CategoryName = 'Camping' ORDER BY ProductID. This command filters products by the Camping category and sorts the results by ProductID .
To find all products with a product price of $100 or higher, you would use the SQL query: SELECT ProductID, ProductName, ProductPrice FROM ProductTable WHERE ProductPrice >= 100. This query selects the ProductID, ProductName, and ProductPrice from the ProductTable where the ProductPrice is greater than or equal to $100 .
To calculate the average product price within each category, you would use: SELECT CategoryID, CategoryName, AVG(ProductPrice) AS AveragePrice FROM ProductTable GROUP BY CategoryID, CategoryName. This query averages the ProductPrice for each category by grouping the results based on CategoryID and CategoryName .
The SQL query to display the ProductID, ProductName, ProductPrice, and VendorName for all products, sorted by ProductID, would be: SELECT ProductID, ProductName, ProductPrice, VendorName FROM ProductTable ORDER BY ProductID. This retrieves the required fields and sorts them in ascending order based on the ProductID .
To display unique RegionIDs from regions with stores using only the STORE table, you would write: SELECT DISTINCT RegionID FROM STORE. This SQL query will ensure that only unique RegionIDs are displayed by using the DISTINCT keyword .
To avoid duplicate entries when listing products sold in the 'Tristate' region by 'Pacifica Gear', use: SELECT DISTINCT ProductID, ProductName, ProductPrice FROM SalesTable JOIN RegionTable ON SalesTable.RegionID = RegionTable.RegionID WHERE VendorName = 'Pacifica Gear' AND RegionName = 'Tristate'. The DISTINCT keyword ensures each product is listed only once .
To find the store count within each region, the SQL query is: SELECT RegionID, RegionName, COUNT(StoreID) AS StoreCount FROM StoreTable GROUP BY RegionID, RegionName. This query groups the results by RegionID and RegionName and uses the COUNT() function to determine the number of stores in each region .
The query to find products priced below the average price would be: SELECT ProductID, ProductName, VendorName FROM ProductTable WHERE ProductPrice < (SELECT AVG(ProductPrice) FROM ProductTable). This leverages a subquery to calculate the average price and then filters products priced below this average .
The SQL query to find the best-selling product by total transactions would be: SELECT ProductID FROM SalesTable GROUP BY ProductID ORDER BY SUM(QuantitySold) DESC LIMIT 1. This groups sales by ProductID, sums their quantities, and sorts them in descending order to select the one sold the most .
To display the cheapest product, the SQL query is: SELECT ProductID, ProductName, ProductPrice FROM ProductTable WHERE ProductPrice = (SELECT MIN(ProductPrice) FROM ProductTable). This uses the MIN() function to identify the lowest product price and then selects the corresponding product details .
