This SQL script analyzes the Mint Classics database to identify sales patterns, optimize inventory management, and provide data-driven recommendations to improve business performance. The analysis focuses on product demand, pricing strategies, and warehouse utilization.
The Mint Classics database is assumed to be already created and
populated with the necessary tables (customers, employees, offices,
orderdetails, orders, payments, productlines, products, warehouses). The
USE Mintclassics; statement selects the database for use in
subsequent queries.
USE Mintclassics;Initial exploration of the database schema and data is crucial. While
the original script listed SELECT * for each table, this is
generally excessive for a portfolio. Instead, a concise description of
the database schema and key relationships would be more appropriate. An
EER diagram (created using a database design tool) would be a valuable
addition here.
The Mintclassics schema contains information about a classic model car business.: The database contains information about customers, orders, products, employees, and warehouses. Key tables include: Products: Stores details about each product (name, code, price, quantity in stock, product line). OrderDetails: Records the details of each order, linking products to orders and specifying quantities and prices. Orders: Contains information about customer orders (order date, customer ID, etc.). Customers: Stores customer information. Warehouses: Details about warehouse locations and capacity.
The primary business problem addressed is optimizing inventory management to meet customer demand while minimizing storage costs and potential stockouts. This involves understanding product popularity, the impact of pricing, and efficient warehouse utilization.
SELECT
P.productLine,
P.productName,
SUM(OD.quantityOrdered) AS TotalSold
FROM orderDetails OD
JOIN products P ON OD.productCode = P.productCode
GROUP BY
P.productLine,
P.productName
ORDER BY TotalSold DESC;– Analysis: This query reveals which specific products and product lines are most popular, allowing for focused inventory management and marketing efforts.
SELECT
P.productLine,
OD.priceEach,
SUM(OD.quantityOrdered) AS TotalSold
FROM orderDetails OD
JOIN products P ON OD.productCode = P.productCode
GROUP BY
P.productLine,
OD.priceEach
ORDER BY OD.priceEach DESC;– Analysis: Analyzing the relationship between price and sales helps identify optimal price points for maximizing revenue. Further analysis, perhaps using correlation or regression techniques, would strengthen this insight.
UPDATE Products
SET quantityInStock = quantityInStock * 0.95;SELECT
productName,
productLine,
quantityInStock
FROM Products;– Analysis: This simulation helps assess the potential risk of stockouts if inventory levels are reduced. It’s crucial to compare the new stock levels with historical sales data to determine if the reduced inventory can still meet demand. This should be followed by reverting the change (or using a temporary table) so it doesn’t affect further analysis.
Focus on maintaining adequate stock levels for high-demand products. The following query identifies products with sales exceeding the average quantity ordered, suggesting these are likely high-demand items.
SELECT
P.productName,
P.productLine,
SUM(OD.quantityOrdered) AS TotalSold,
p.quantityInStock
FROM orderDetails OD
JOIN products P ON OD.productCode = P.productCode
GROUP BY
P.productName,
P.productLine,
p.quantityInStock
HAVING TotalSold > (SELECT AVG(quantityOrdered) FROM orderdetails)
ORDER BY TotalSold DESC;– Recommendation: Prioritize these products for inventory replenishment. Consider increasing safety stock levels to avoid stockouts.
Evaluate the utilization of each warehouse. Identify warehouses with low stock levels and consider consolidating inventory to optimize warehouse space and reduce operational costs.
SELECT
W.warehouseCode,
W.warehouseName,
W.warehousePctCap,
SUM(p.quantityInStock) AS TotalStock
FROM warehouses W
JOIN products P ON W.warehouseCode = P.warehouseCode
GROUP BY
W.warehouseCode,
W.warehouseName,
W.warehousePctCap;– Recommendation: Consolidate inventory in warehouses with higher capacity utilization and close or downsize underutilized warehouses.
SELECT
P.productName,
P.productLine,
OD.priceEach,
SUM(OD.quantityOrdered) AS TotalSold
FROM orderDetails OD
JOIN products P ON OD.productCode = P.productCode
GROUP BY
P.productName,
P.productLine,
OD.priceEach
HAVING TotalSold < (SELECT AVG(quantityOrdered) FROM orderdetails)
ORDER BY OD.priceEach DESC;