For an instructor lead, in-depth look at learning SQL click below.
Managing an inventory system efficiently is crucial for the smooth running of any business. In this blog post, we will walk you through how to create a basic inventory management system using SQL (Structured Query Language).
SQL is a highly-efficient, robust language designed specifically for managing data in relational databases. Let’s begin.
Creating Tables
The basis our inventory will be two tables – Products and Orders.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Products ( ProductID int, ProductName varchar(255), QuantityInStock int, Price decimal(5,2), PRIMARY KEY (ProductID) ); |
This creates a table ‘Products’ with four fields.
‘ProductID’ is an integer and is made the primary key. This means that each product must have a unique ID.
‘ProductName’ is a variable character string that can hold up to 255 characters.
‘QuantityInStock’ and ‘Price’ are both integers.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Orders ( OrderID int, ProductID int, Quantity int, OrderDate date, PRIMARY KEY (OrderID), FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ); |
The ‘Orders’ table also has a ‘ProductID’ as a foreign key to link it to the ‘Products’ table. This enables correlation of the data concerning what products are ordered. ‘OrderID’ has been established as the primary key for the Orders table.
Inserting Data
Now, we will populate the tables with data
|
1 2 3 4 5 6 7 |
INSERT INTO Products (ProductID, ProductName, QuantityInStock, Price) VALUES (1, 'Black Tea', 100, 3.99); INSERT INTO Orders (OrderID, ProductID, Quantity, OrderDate) VALUES (1, 1, 3, '2022-04-01'); |
This creates an entry in the Products table for ‘Black Tea’, and also logs an order of 3 units on April 1, 2022.
Running Queries
Let’s now run a query to determine the remaining quantity of a product after an order.
|
1 2 3 4 5 6 |
SELECT p.ProductID, p.ProductName, p.QuantityInStock - SUM(o.Quantity) as 'Remaining Quantity' FROM Products p JOIN Orders o ON p.ProductID = o.ProductID GROUP BY p.ProductID; |
This command gathers the data from both tables, links them on the Product ID, and calculates the remaining stock quantity for each product.
Conclusion
There you have it! With just a handful of commands, we have created a simple inventory management system using SQL. SQL’s power and versatility can handle a lot more complex operations than we included there. Keep exploring!
|
1 |