Mastering Inventory Management with SQL: A Dive into the AdventureWorks Database

Using DBeaver and MySQL Server

Introduction

In the dynamic world of commerce and business, effective inventory management is often the linchpin of success. As businesses strive to optimize their operations and meet customer demands, having a robust system in place to manage inventory efficiently becomes paramount. In this article, we embark on a journey through the intricate realm of inventory management using SQL, leveraging the versatile AdventureWorks database as our canvas.

The AdventureWorks

Before we embark on this adventure, let’s set the stage. You’ll need access to the AdventureWorks database, a fictitious database created by Microsoft to emulate the operations of a bicycle company. Ensure that it’s installed and configured in your MySQL environment. If you don’t have the AdventureWorks database in your MySQL environment, don’t worry. You can create it using the following steps:

1. Creating the Database and Tables
Let’s create a simplified database structure with six tables: Product, ProductSubCategory, ProductCategory, ProductInventory, Location, SalesOrderHeader, and SalesOrderDetail. To get started, I will provide you with an example code script to create the “Product” table. You can find the complete set of scripts for all these tables on my GitHub repository. Here’s the link to the repository: GitHub Repository

CREATE database AdventureWorks;

use AdventureWorks;

-- Table: Product
CREATE TABLE Product (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    ProductNumber VARCHAR(25) NOT NULL,
    MakeFlag BOOLEAN NOT NULL DEFAULT 1,
    FinishedGoodsFlag BOOLEAN NOT NULL DEFAULT 1,
    Color VARCHAR(15),
    SafetyStockLevel SMALLINT NOT NULL,
    ReorderPoint SMALLINT NOT NULL,
    StandardCost DECIMAL(19, 4) NOT NULL,
    ListPrice DECIMAL(19, 4) NOT NULL,
    Size VARCHAR(5),
    SizeUnitMeasureCode CHAR(3),
    WeightUnitMeasureCode CHAR(3),
    Weight DECIMAL(8, 2),
    DaysToManufacture INT NOT NULL,
    ProductLine CHAR(2),
    Class CHAR(2),
    Style CHAR(2),
    ProductSubcategoryID INT,
    ProductModelID INT,
    SellStartDate DATETIME NOT NULL,
    SellEndDate DATETIME,
    DiscontinuedDate DATETIME,
    rowguid VARCHAR(50) NOT NULL,
    ModifiedDate DATETIME NOT NULL
);

-- Add any additional constraints or indexes as needed

Results From Create Database Script

ER Diagram For AdventureWorks Database

2. Importing Data from CSV Files
You can now proceed to import data into these tables from your CSV files. Ensure that you have CSV files containing data that matches the structure of these tables. Feel free to adapt the following example to import data into the ‘Product’ table, and remember to repeat this process for any other tables you intend to populate. Here’s the link to the data that I'm using in this article: GitHub Repository

Tutorial Import Data CSV

Product Catalog Management

Our inventory management odyssey begins with organizing the product catalog. This is the foundation upon which all inventory activities are built.

Listing All Products

The purpose of listing all products is to provide an overview of the entire product catalog, enabling users, administrators, or analysts to gain insights into the full range of products available within a given context, whether it’s for inventory management, analysis, or decision-making purposes.

SELECT ProductID, Name, Color, ListPrice
FROM Product;

Result Listing All Products

Filtering Products by Category

The purpose of Filtering Products by Category is to sort and display products based on specific categories, making it easier to find and manage items within a particular group.

-- Filtering Products by Product Category (using ProductSubcategory)
SELECT p.ProductID, p.Name, p.Color, p.ListPrice, psc.Name AS SubCategory, pc.Name AS Category
FROM Product AS p
JOIN ProductSubcategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
JOIN ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
WHERE pc.Name = 'Bikes';

Result Filtering Products by Category

Tracking Product Availability

Knowing the availability of each product is crucial to meeting customer orders efficiently.

Checking Product Availability

The purpose of Checking Product Availability is to determine whether a product is in stock and how many units are currently available. This helps customers and businesses ensure that products are on hand and can be promptly delivered or purchased when needed.

-- Checking Product Availability
SELECT p.Name, s.Name AS Location, pi.Quantity
FROM Product AS p
JOIN ProductInventory AS pi ON p.ProductID = pi.ProductID
JOIN Location AS s ON pi.LocationID = s.LocationID
WHERE p.Name = 'Mountain-500 Black 48';

Result Checking Product Availability

Managing Stock Levels

Maintaining accurate stock levels ensures products are always available when customers need them.

Updating Stock Levels

The purpose of Updating Stock Levels is to ensure that the quantity of products in inventory accurately reflects the current stock on hand. This helps businesses maintain accurate records and meet customer demand efficiently.

-- Decrease stock for a product at a specific location
UPDATE Production.ProductInventory
SET Quantity = Quantity - 10
WHERE ProductID = 701 AND LocationID = 5;

Result Decrease stock for a product at a specific location

Inserting New Stock Records

The purpose of Inserting New Stock Records is to add information about newly acquired products to the inventory database. This ensures that the system is aware of the latest stock additions, helping businesses track and manage their inventory effectively.

-- Add stock record for a new product at a new location
INSERT INTO ProductInventory (ProductID, LocationID, Quantity)
VALUES (900, 10, 100);

Result Inserting New Stock Records

Analyzing Sales Data

Lastly, to make informed decisions and optimize inventory, we need to analyze sales data.

Listing Top Selling Products

-- Top Selling Product
SELECT p.Name, COUNT(soh.SalesOrderID) AS SalesCount
FROM SalesOrderDetail AS sod
JOIN Product AS p ON sod.ProductID = p.ProductID
JOIN SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY p.Name
ORDER BY SalesCount DESC
LIMIT 10;

Result Top Selling Products

Calculating Total Sales Revenue

-- Total Sales Revenue
SELECT SUM(sod.LineTotal) AS TotalRevenue
FROM SalesOrderDetail AS sod
JOIN SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID;

Result Total Sales Revenue

-- Total Sales Revenue by Year
SELECT YEAR(soh.OrderDate) AS SalesYear, SUM(sod.LineTotal) AS TotalSalesRevenue
FROM SalesOrderDetail AS sod
JOIN SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY YEAR(soh.OrderDate)
ORDER BY SalesYear;

Result Total Sales Revenue by Year

-- Monthly Sales Revenue Trends
SELECT DATE_FORMAT(soh.OrderDate, '%Y-%m') AS SalesMonth, SUM(sod.LineTotal) AS TotalSalesRevenue
FROM SalesOrderDetail AS sod
JOIN SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY DATE_FORMAT(soh.OrderDate, '%Y-%m')
ORDER BY SalesMonth;

Result Monthly Sales Revenue Trends

Conclusion

In the ever-evolving landscape of commerce, inventory management is a pivotal element that can make or break a business. By harnessing the power of SQL and leveraging the AdventureWorks database, we’ve embarked on a journey to streamline product catalog management, track product availability, manage stock levels, and analyze sales data.

These SQL operations provide a foundation for efficient inventory management, empowering businesses to make data-driven decisions, optimize their operations, and deliver exceptional customer experiences.

As you delve deeper into the world of inventory management, remember that SQL is your trusty companion, enabling you to navigate the complexities of inventory with precision and grace. Tailor these queries to suit your specific needs, and may your inventory management endeavors be ever prosperous.