Graphic visualization of data using SQL

What is SQL?

Structured query language (SQL) is a programming language for storing and processing information in a relational database. A relational database stores information in tabular form, with rows and columns representing different data attributes and the various relationships between the data values. You can use SQL statements to store, update, remove, search, and retrieve information from the database. You can also use SQL to maintain and optimize database performance.

Graphic visualization

Creating visually stunning charts and graphs is not just about picking the right colors or shapes. The real magic happens behind the scenes, in the data that feeds those visuals. SQL here—will be our key to the realm of data visualization. SQL helps you slice, dice, and prepare your data in a way that makes it shine in whatever visualization tool you are using.

We will start by showing how SQL can be used to prepare data for data visualization. We will then guide you through several types of visualizations and how to prepare data for each

The following are 4 main keys for getting right data

1.     Filtering the data: To filter data WHERE clause is used, where all the unwanted data has been filter. For example, if we need data only for people living in Alpharetta, you could filter them out using the WHERE clause. The following command is used

SELECT customer_first_name,district

FROM dunkin

WHERE district = 'Alpharetta';

The following is the output:

2.     Sorting the data: To sort data ORDER BY clause is used, where all the data are sorted chronologically. For example, if we need to sort data by their district following command is used

SELECT customer_first_name,district

FROM dunkin

ORDER BY district;

The following is the output:

3.     Joining the data: To join the data from more than 1 table we can use the JOIN clause. In this we can choose different columns from different tables and join the data to present in one table, but this can be used only atleast 1 column is common in those tables. For example, if we need data customer’s first and last name from dunkin table and film name and rental rate from film table following command is used

SELECT d.customer_first_name, d.customer_last_name, f.film_name, f.rental_rate

FROM dunkin d

JOIN film f

ON d.film_id = f.film_id;

The following is the output:

4. Grouping the data: If we want to categorize data we have to use GROUP BY clause. GROUP BY clause used mostly with aggregate functions like COUNT(), SUM(), and AVG() to perform calculations on each group. For example, if we need the average of rental rate along with film name the following command is used

SELECT film_name,AVG(rental_rate)

FROM film

GROUP BY film_name;

The following is the output:

 Charts

Charts are important and useful because they are powerful tools that can be used for things like analyzing data, emphazing data and comparing multiple sets of data in a way that it is easy to understand.

You can run SQL queries to get counts, averages, or whatever metric you're interested in, and directly feed this data into your charting tool to create visualizations like bar charts, pie charts, or line charts.

Following are various kinds of graph and steps to get the graphic visualization

Bar Graph

Let’s take the following query

SELECT film_name,AVG(rental_rate)

FROM film

GROUP BY film_name;

The following is the output

Once we get the output, we need to click the ghaphic visualization icon as seen below

Then you will land in the following page

After that we can select what type of graph you need and its X and Y axis

And click on the generate button on the right op corner of the output table

The following is the output

Similarly, we can create different type of charts

Bar graph

Pie chart

Now we will see how we can generate graphs without using JOIN clause. If we need graph for the columns in the same table, we need to do the following steps

We have to right click on the table name and click view/edit data and then all rows as shown below

The following will be the output

After that we need to click on graphic visualization icon as explained above.

In short Data visualization is not just about pretty charts and graphs; it's about helping you prepare, filter, and organize the data.

Happy reading and learning….