How to Use SQL to Analyze and Visualize Data?

The goal of every business is to perform efficiently, maximize profit and make strategically-guided decisions. In the current business world, the competition is high, and every business needs every edge and advantage they can get to improve its odds of success.

Companies that want to stay in business and thrive in the current business world need to employ data analysis. Data analysis plays a role in making decisions more scientific and helping businesses operate more effectively.

What Is Data Analysis?

Data analysis can be described as the process of cleaning, transforming, and processing raw data to extract actionable and relevant information that helps businesses make informed decisions. The process of data analysis provides useful insights and statistics that can be presented in tables, charts, and graphs.

In this article, you will learn how to get useful information from a collection of data by running SQL queries. Also, you will learn how to present that information using graphs and charts. I will be using a dataset of different stores of a supermarket company, PostgreSQL, and the Arctype SQL client.

What Are PostgreSQL and Arctype?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system. Arctype is a fast and easy-to-use database management tool for writing SQL queries, building dashboards, and sharing data with your team.

Setting Up PostgreSQL

To use Postgres in this tutorial, navigate to the Postgres download page and download it for your operating system. It is available for Linux, macOS, and Windows.

After installing Postgres on your machine, run the command below to confirm the installation.

You should be able to see the version of Postgres installed in your system. Now run the command shown below to open the Postgres prompt.

Once Postgres has opened on the command line, run the command below to set a password for Postgres.

To quit the Postgres prompt, run the command shown below.

\q

Let us now create a database called "supermarket" in PostgreSQL that will be used to store data from the supermarket stores CSV file. We run the command below.

Then run the command shown below to see if the database you created is on the list of databases in Postgres.

\l

As you can see from the screenshot below, the supermarket database was created in PostgreSQL.

The Arctype SQL Client

To use Arctype, navigate to the homepage and download the app on your machine. The application is available on Windows, MacOS, and Linux.

Once the app has been installed, open it, and you will be prompted to create an account, as shown below.

Integrating the Arctype SQL Client With Postgres

Once you have created an account, you will be prompted to select your database. In this case, select Postgres.

The next step is to add Postgres credentials to connect Arctype to your Postgres database.

Fill in the fields according to the example shown below.

• Name: Supermarkets Dataset
• Host: 127.0.0.1
• Port: 5432
• User: postgres
• Password: *postgres password*
• Database: supermarket

Note: Use the credentials you created when creating a database in the Postgres prompt.

Once you have entered the Postgres credentials, click the "Test Connection" button at the bottom to make sure the credentials are correct. If the credentials are correct, click the "save" button to the right of the "Test Connection" button to complete Postgres and Arctype SQL client integration, as shown below.

Importing CSV Files Into Postgres Using Arctype

We will be importing the supermarket's stores dataset CSV file that you can download by navigating to this Kaggle web page, as shown below.

To import the CSV file into Postgres using Arctype, navigate to the "Tables" view. Click on the three dots next to the table search field. Then select "Import CSV to Table," as shown below.

Next, use the file selection window that appears to select the CSV file you downloaded. Select your file and click the "Open" button, as shown below.

Once you have selected and opened the CSV file, Arctype will display the first ten rows of the CSV file data for preview. If everything looks correct, click the "Accept" button, as shown below.

After clicking the "Accept" button, you will be redirected to a configuration window, as shown below. The window allows you to determine where you want the data to be stored and to select the schema.

If everything looks good, click the "Import CSV" button to start importing your CSV file. Open the table data was successfully imported; everything should look as shown below.

Running SQL Queries With Arctype

SQL queries allow you to display or modify data from databases. In this section, I will show you some queries you can run to create tables, charts, or graphs to visualize the supermarket's data.

Let us assume you are the owner of the supermarkets. You would want to know interesting stats such as the top 10 performing stores by sales. To get such insights, you can run a SQL query that creates a table of the top ten performing stores. Then you can visualize the data by creating a graph or a chart.

To run a SQL query using Arctype, click the "Create query" button on the top right of the Arctype window. A screen will then appear where you can run a query, as shown below.

Give your query a descriptive name like "Top 10 Stores By Sales." Then write the following SQL query to determine the top 10 stores by sales from the store's data.

The query selects data in the column store_area and store_sales from the store's table where store_sales is renamed to Sales. The data then gets grouped by the columns named store_id and store_sales (Sales.) After that, the data is ordered by store_sales, where sales are arranged from highest to lowest. The query limits the results to the top 10 selling store areas.

If everything goes well, the query should create a table with two columns named store_area and sales, as shown below.

Creating Charts Using Arctype

Let us now create a chart from the table created by the SQL query. To create a chart using Arctype, select the "Chart" option at the bottom right of the SQL editor, as shown below.

On the right side of the screen, you can select the type of chart you want. In this case, we select "Bar Chart." Then below the "Select Chart Type" option, you have the "Specify Columns For The Chart" option. In this section, drag the store_area column to the X-axis column and the sales column to the Y-axis column. Arctype should help you come up with a chart similar to the one shown below.

Conclusion

In this article, you have learned how to create a database in PostgreSQL, import a CSV file into the DBMS and how run SQL queries to analyze data. Now, I suggest you try to create different SQL queries to come up with some more exciting insights and use Arctype to create charts.