1) What Is The Name And Count Of The Manufacturer
In this text, various SQL queries are used to retrieve specific information from a database. These queries involve selecting, listing, and showing information about manufacturers, shoe categories, sales records, and customer names. The use of functions like DISTINCT and CASE are also mentioned, demonstrating the potential complexity of SQL queries.
Here's how to find the answers to both questions using GROUP BY, CASE, and COUNT in SQL:
- To find the name and count of the manufacturer of the most sold boots in April 2015:
SELECT p.ManufacturerName AS Manufacturer, COUNT(*) AS ManuCount,
CASE
WHEN pr.Category = 'Boots' THEN 'Yes'
ELSE 'No'
END AS Is_Boots
FROM Sale s
JOIN SaleItem si ON s.SaleID = si.SaleID
JOIN Product pr ON si.ProductID = pr.ProductID
JOIN Manufacturer p ON pr.ManufacturerID = p.ManufacturerID
WHERE pr.Category = 'Boots'
AND MONTH(s.SaleDate) = 4
AND YEAR(s.SaleDate) = 2015
GROUP BY p.ManufacturerName, pr.Category
ORDER BY Is_Boots DESC, ManuCount DESC;
- To find the name and count of the manufacturer of the most sold non-boots item in April 2015, along with the item category:
SELECT p.ManufacturerName AS Manufacturer, COUNT(*) AS ManuCount,
CASE
WHEN pr.Category = 'Boots' THEN 'Yes'
ELSE 'No'
END AS Is_Boots,
pr.Category
FROM Sale s
JOIN SaleItem si ON s.SaleID = si.SaleID
JOIN Product pr ON si.ProductID = pr.ProductID
JOIN Manufacturer p ON pr.ManufacturerID = p.ManufacturerID
WHERE pr.Category != 'Boots'
AND MONTH(s.SaleDate) = 4
AND YEAR(s.SaleDate) = 2015
GROUP BY p.ManufacturerName, pr.Category
ORDER BY Is_Boots DESC, ManuCount DESC;
These queries should provide you with the answers you need using GROUP BY, CASE, and COUNT. The results will show you the top-selling brand of boots in Row 1 and the top-selling brand of non-boots in Row 10.
Sources
Chapter 2 Database questions Flashcards - QuizletMIS 180 SQL Chapter 2-4 Flashcards - QuizletQuestion: I need help with my hw please. this is all the info i was given, thank you! - CheggSQL database.docx - Show sales information for sneakers whose color is not Black or Blue. Select * From SimplifiedSales Where category = Sneakers AndSolved For each information request below, formulate a | Chegg.com[Solved] Assignment 4 For each information request below, formulate a... - Course Hero
Related Questions
Work fast from anywhere
Stay up to date and move work forward with BrutusAI on macOS/iOS/web & android. Download the app today.