PostgreSQL

Using PostgreSQL to Manage Business Inventory Data and Visualize It

Author: Bonnie
Length: 7 MINS
Type: Guide
Date: 2023-01-23
Learn how to connect Postgres to DbVisualizer, import inventory dataset into Postgres, run SQL queries on Business Inventory data stored in a Postgres database using DbVisualizer to get important analytics and convert them to charts for visualization.

The function of every business is to create products and sell them to customers. The goods produced make up the inventory of the business. When the business is small, ensuring that the goods produced are sold is not a headache.

However, keeping up with the inventory as the business grows might become daunting. To streamline the production process, you might need to employ inventory management.

In this article, you will learn how to use SQL to manage business inventory data held in a Postgres database to come up with meaningful analytics. You will then learn how to convert the analytics into charts for visualisation.

What is Inventory Management?

Inventory management is the process of ordering, storing and selling a business’s inventory where inventory is one of the most valuable assets of a company. Employing inventory management will help the business minimise overstocking and improve the efficiency of the production process.

Some inventory management benefits include,

  • Availability of supplies to keep production running.
  • Preventing loss or theft of supplies and produced goods.
  • Improving production and sales process.
  • Dealing with market changes that affect the demand and supply of goods.

Prerequisites

To follow through with this tutorial, you need a database, an inventory dataset and a SQL client. In this case, we will use Postgres as the database, the Kaggle inventory dataset, and DbVisualizer as the database SQL client.

First, we will connect Postgres to DbVisualizer. Next, we will import the inventory dataset into Postgres using DbVisualizer. Then we will run SQL queries using DbVisualizer on the dataset to come up with important analytics. Finally, we will use DbVisualizer to create charts from the dataset.

What is Postgres and DbVisualizer?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system. Postgres is available for Windows, macOS and Linux.

DbVisualizer is a SQL client or a database management and analysis tool for all major databases. The SQL client provides SQL tools such as SQL editor with support for auto-completion, parameterized SQLs, SQL formatter, visual query builder, command-line based interface and more.

Installing Postgres

To install Postgres, navigate to the Postgres download page and download it for your operating system. You can follow this guide to install Postgres on Windows, this guide to install on Linux and this guide to install on macOS.

Installing DbVisualizer

To install DbVisualizer, navigate to the DbVisualizer download page. Then download the recommended installer for your operating system, as shown below.

Installing DbVisualizer

After that, execute the installer you have downloaded and follow the instructions that follow. When you start DbVisualizer, you are prompted to choose the DbVisualizer plan to proceed. You can proceed with the DbVisualizer free version with limited features or get a a 21-day free trial.

Connecting Postgres To DbVisualizer

Step 1: Open Postgres using the command line or SQL shell as shown below.

Using SQL shell in Postgres.

Step 2: Create a database called inventory in Postgres by running the command below on the command line.

$
create database inventory;

You can check if the database has been created by typing forward slash together with L and hitting enter. After that, you should see the inventory database we created on the list of databases in Postgres, as shown below.

Connecting Postgres to DbVisualizer.

Step 3: Start DbVisualizer and click create a connection button as shown below.

Creating a database connection in DbVisualizer.

Step 4: Search and select Postgres driver from the popup menu on the left side of your screen, as shown below.

Selecting Postgres driver in DbVisualizer.

An object view tab for the Postgres connection is opened.

Step 5: Fill in the empty fields as shown below

Name:InventoryManagement
Database: inventory
Database Userid: postgres
Database Password: Enter Your Postgres password

Once you have filled all the empty fields, click the connect button at the bottom. If the connection is succesful, your screen should look as shown below.

Database connection for Inventory Management.

Importing Data Table Into Postgres Using DbVisualizer

Step 1: Navigate to this Kaggle web page and download the inventory dataset.

Importing data table Into Postgres using DbVisualizer.

Step 2: Next, Open the database connection Inventory Management tab tree as shown below.

Opening the database connection Inventory Management tab tree.

Inside the connection tab tree, open the Databases tab tree.

Opening the database tab tree.

Inside the Databases tab tree, open the inventory database tab tree.

Opening the inventory database tab tree.

Inside the inventory database tab tree, open the schemas tab tree.

Opening the schemas tab tree.

Inside the schemas tab tree, open the public tab tree.

Opening the public tab tree inside the schemas tab tree.

Step 3: Right-click on the Tables tab tree and select Import Table Data.

Right-click on the Tables tab tree to Import Table Data.

Step 4: When you reach the popup window, navigate to the directory you downloaded the inventory dataset CSV file into and click the Open button.

Managing Inventory Management with Postgres and DbVisualizer.

Step 5: Keep clicking the Next button below the popup window until you reach the window shown below.

Window to Import Table Data.

The first thing to do on this popup window is to give the table that will hold the inventory dataset a name. In this case, I have named my table InventoryData as shown below.

New database table for InventoryData.

The second thing to do is change all bit data types to boolean data types by clicking the data type as shown below. This is because the database table accepts boolean data types instead of bit data types.

Changing all bit data types to boolean data types.

Next check the Use Delimited Identifiers checkbox as shown below. This is because in our dataset we have a column called Order. Order is a reserved word in Postgres and checking Use Delimited Identifiers will help Postgres to differentiate column name order from the reserved name Order.

Checking the Use Delimited Identifiers checkbox in Postgres.

Click next and then import the dataset into Postgres. Once the dataset is imported, right-click on the table tab and refresh to see the table data as shown below.

Refresh Objects Tree in Postgres.

Open the table and navigate to the Data tab. You should be able to see the dataset imported, as shown below.

See the imported dataset in the Data tab.

Running SQL Queries

The dataset contains historical sales data identified with the column titled File_Type together with current active inventory that needs evaluation. The historical data shows sales for the past 6 months.

The SoldFlag column shows products that have been sold in the last 6 months as true and those that have not sold in that period as false. The SKU_number column has the unique identifier for each product, while the PriceReg column has the price of each product.

As a data analyst, you can come up with stats from the dataset, such as the following:

  • Sales revenue each product made in the last 6 months.
  • Products not sold in the last 6 months.
  • Ten best selling products in the last 6 months.

Sales revenue each product made in the last 6 months.

To get sales revenue each product made in the last months, create a SQL query that multiplies the PriceReg column with the ItemCount column where the SoldFlag column is true.

The SQL query you can create to get the TotalSales of the products sold in the last 6 months should look as shown below.

1
SELECT "SKU_number", "PriceReg" * "ItemCount" AS “TotalSales”
2
FROM inventorydata WHERE "SoldFlag"= 'True';

To run the SQL query on DbVisualizer, click the SQL Commander tab at the top of your screen and select New SQL Commander as shown below.

How to run the SQL query on DbVisualizer.

Once the SQL Commander opens, type the SQL query and press the (Ctrl+Enter) keys to run the query. You should now see a new table with columns SKU_number and TotalSales, as shown below.

A new table with columns SKU_number and TotalSales.

Products not sold in the last 6 months?

To get the number of products that were not sold in the last 6 months, create a SQL query that counts the SKU_number column where the SoldFlag column is false.

The SQL query should look as shown below.

1
SELECT COUNT("SKU_number") FROM inventorydata WHERE "SoldFlag"='False';

Run the SQL query on DbVisualizer using (Ctrl+Enter) keys, and you should see that 63000 products were not sold in the last 6 months as shown below.

SQL query on DbVisualizer to see products not sold in the last 6 months.

Ten best selling products in the last 6 months.

To get the top 10 selling products in the last 6 months, create a SQL query that finds products' total sales in the last 6 months. Next, build a SQL query that groups the total sales by columns SKU_number and TotalSales.

After that, the SQL query should order the sales by the TotalSales column, where they are limited to only the first 10 products in a descending order. The SQL query should look as shown below.

1
SELECT "SKU_number", "PriceReg" * "ItemCount" AS "TotalSales"
2
FROM inventorydata WHERE "SoldFlag"= 'True'
3
GROUP BY "SKU_number", "TotalSales"
4
ORDER BY "TotalSales"
5
DESC LIMIT 10;

Run the SQL query on DbVisualizer using (Ctrl+Enter) keys and you should see a table of top 10 selling products in the last 6 months as shown below.

SQL query to see a table of top 10 selling products in the last 6 months.

Visualizing Data Using Charts on DbVisualizer

On DbVisualizer, you can present data from any table in a configurable chart displayed in a line, bar, area or pie chart. The charting support is similar to that of MsExcel. The charts can be exported as an image to file, printed and copied to the system clipboard for easy sharing.

To activate the chart view, click the rightmost button in the result toolbar as shown below.

Activating the chart view to visualize data.

When switching to the chart view, DbVisualizer automatically picks the first date or text column as the X-axis for the chart and the first numeric column as the Y-axis. In the following example, the SKU_number is the X-axis and TotalSales as Y-axis as shown below.

How to visualize data using charts on DbVisualizer.

Visualizing Top 10 Selling Products

First, Activate the chart view on the best selling products.

Activating the chart view on the best selling products.

Once that’s done, you should see a chart where SKU_number is on X-axis and TotalSales on Y-axis as shown below.

How to visualize the top 10 selling products on DbVisualizer.

The chart shows that the highest sold product has made over 200,000 in revenue compared to the second highest selling product that has made less than 125,000 in revenue.

Conclusion

In this article, we have walked you through PostgreSQL and DbVisualizer while helping you to work with a data set related to the inventory of a business. Doing so can help you to come up with exciting insights that can help the business management to make the right decisions that can lead to its growth.

I challenge you to create different SQL queries and see what insights you can come up with using DbVisualizer - come back to our blog to learn what’s new on the database front after a while, and until next time!

/* About the author */
Bonnie
Bonnie
Bonnie is a web developer and technical writer creating easy-to-understand technical articles.
/* SIGN UP TO RECEIVE THE TABLE'S ROUNDUP */
/* More from the table */
TITLE
AUTHOR
Antonello Zanini
TAGS
Stored procedure
MySQL
TITLE
AUTHOR
Antonello Zanini
TAGS
Triggers
TITLE
AUTHOR
Igor Bobriakov
TAGS
Microsoft SQL Server
Optimization
TITLE
AUTHOR
Bonnie
TAGS
PostreSQL
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
Reusable queries
TITLE
AUTHOR
Antonello Zanini
TAGS
BIG Data
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Security
TITLE
AUTHOR
TheTable
TAGS
Beginner
SQL
TITLE
AUTHOR
Ochuko Onojakpor
TAGS
CRUD
SQL Transactions
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
Security
TITLE
AUTHOR
Antonello Zanini
TAGS
PostgreSQL
JSON
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
InnoDB
ibdata1
TITLE
AUTHOR
Lukas Vileikis
TAGS
MySQL
TITLE
AUTHOR
Scott A. Adams
TAGS
Filter
TITLE
AUTHOR
Scott A. Adams
TAGS
SQLite
TITLE
AUTHOR
Scott A. Adams
TAGS
Excel
Export
TITLE
AUTHOR
Scott A. Adams
TAGS
ERD
Join