With every transaction a business makes, a new piece of data is formed. Whether it’s a point-of-sale transaction, a shipment coming from a distributor, or a change in inventory, data is collected by the second by computer systems. While the world of computing seems like a daunting puzzle, we can actually tap into this data by querying, or asking, the computer’s database for the data that we are interested in analyzing. By asking the right questions about our data, we can find the answers and insights for our business needs. To properly ask the computer systems about our data, we will use Structured Query Language, or SQL.
Breaking Down SQL
To understand SQL a little bit better, let’s break it down into its base parts:
Structured
For data to make sense, it should have some sort of organizational structure. Let’s say that we want to analyze our sales data by looking at the point-of-sale system. Instead of stuffing receipts into a box, for example, a database created by a POS would organize each transaction by customer, add a timestamp, and be itemized by price and quantity, and place them into a table.
SQL is the language of Relational Databases, which connect one or more tables of this data. So simple point-of-sale data can be organized by any element of the data, such as by franchise, by geographic area, or by customer.
By utilizing the structure of the data, we can see that structure gives the data power, and we can tap into this power with SQL.
Query
Every time we want to access data in our database, we do it through queries, which means we ask for the specific data that we want. Maybe we’re interested in transactions made in Q3, or rank the performance of each location. If we ask the appropriate questions about our data, by making our query as specific as we need, we get back only the data we need, so that we can make better decisions without any unnecessary information.
These queries are also computationally efficient, so you don’t need a computer science degree to make sure you get the data in a timely manner. Every query processes the code for the data we want behind the scenes, so in the end, you get back just the data with none of the complexity.
Every SQL query formats the data to how you want to see it, as efficiently as possible.
Language
Unlike many computer languages that look intimidating with indentation and semicolons, SQL is a language that looks more like plain English than traditional code. To achieve this, SQL uses keywords so that we can have a unified step-by-step process in asking what we want.
Python via pandas package:
steph_points = game[‘points’][game[‘player’] == ‘Stephen Curry’].sum()
SQL Solution:
SELECT sum(points)
FROM game
WHERE player = ‘Stephen Curry’;
In the above example, we see two ways to extract the same data. The SQL solution is elegant in that you can read it out loud and have an intuitive sense of what data you want and where it comes from.
A common-sense language structure gives SQL the advantage in that it is easy to read, easy to understand, and easy to learn.
The many flavors of SQL
Over the years, there have been many companies that have had both paid and free solutions to their databases and their database management. Microsoft offers MS SQL Server, and there are open-source solutions such as MySQL and PostgreSQL. Since there are so many solutions, one would think that every database had its own language. Luckily, since SQL is so widespread among IT and business, most database solutions have decided that SQL would be the way for analysts to access their data, allowing a bridge to data that’s accessible to anyone willing to learn.