Data Analytics 101: All You Need to Know about SQL

Imagine a company that stores its data in a smart warehouse — dozens and dozens of boxes on thousands of shelves.

But the warehouse is so huge that employees simply can’t manage it manually. So the warehouse comes with high-tech robots. They specialize in processing, finding, and retrieving data. Each time a person requires a box, a robot finds it and brings it over.

Here’s the thing: robots don’t speak “human” ― they only understand a special language, used for commands. The company’s employees need to learn this language in order to communicate with the robots.

This language is called SQL. And the warehouse is a database. Here’s all you need to know about how these two work together!

Introduction to data analytics

Nowadays, every website and piece of software is driven by data. Businesses need databases to store customer information, track inventory, and keep financial records — the list goes on.

Databases are the backbone of the world, which makes data analytics an immensely valuable field. Based on informed insights gathered by data analysts, companies gather information about their customers’ needs.

For example, Amazon’s product recommendations generate 35% of its annual sales. This is because recommendations are supported by data on customers’ browsing and purchasing behavior. By analyzing that data, Amazon learns what products to show a specific customer if it wants them to shop more.

(If you’d like to know more about data analytics, check our previous article: Data Analysis Career Guide for 2023: Tasks, Skills, and How to Become a Data Analyst!)

Q. I’ve heard two terms that sound alike — data analytics and data science. Is there any difference between them?
A. Data scientists design algorithms and methods for information processing. Data analysts apply these methods to existing sets of data and search for meaningful patterns.
In short,

• Data scientists are people who manage the warehouse and upgrade the robots.
• Data analysts are those who operate the robots in order to retrieve info.

What is SQL?

SQL stands for Structured Query Language. It’s a programming language used to talk to databases. SQL is a common tool in web development, data analysis, and other fields where businesses need to manage large amounts of data.

SQL allows users to:

• Set up a structured database
• Add new data records to it
• Modify or delete the existing records
• Retrieve filtered data with specific queries

However, it also has a number of standout features that truly make it special.

SQL allows you to draw data from several connected databases

Imagine that you have a couple of Excel-style spreadsheets. We’ll call them “tables.” Each table has information about one type of item, for example:

• Table 1. Companies
• Table 2. Employees
• Table 3. Smartphone brands

Items from one table can be linked with items in another table. For example, you can link “John” from Table 1 to “iPhone” from Table 2. This creates the connection that says: “John owns an iPhone.”

SQL makes it easy to manipulate the data in those interlinked tables. For example, you can get a list of all the people who own an iPhone.

In other words, SQL works with relational databases. These databases are called “relational” because they have some sort of connection, or relation, to one another. Here’s a picture that represents this:

SQL has different versions tailored for different needs

SQL is a standard language, but it has dialects that often tweak or customize how it works. For example, one SQL dialect may prioritize security, while another is fine-tuned for distributed computing. These dialects compete with each other for market share.

Once you’ve learned the basics of SQL, it’s not too hard to understand another SQL dialect like MySQL or PostgreSQL. It’s because the standard core of SQL works the same across all dialects. So they operate on the same fundamental principles. There might be some differences, but they only come up for less frequent use cases.

Using SQL for analytics

Businesses use SQL to work with databases. This way, they get a better understanding of:

• Internal operations
• Customer behavior
• Emerging market trends
• Key performance indicators, etc.

It leads to better business decisions and increased profitability. Besides, SQL is considered an accessible language that is pretty easy to master. Many professionals use it — from software engineers to MBAs.

To understand it better, let’s take an eCommerce company that sells coffee beans — Majestic. This business needs a database to store information about its products, orders, and customers. Here’s what it can do with SQL:

1. Retrieve a list of products that are currently out of stock, so they can be restocked.
2. Calculate the total sales revenue for Q4 2022 to evaluate performance.
3. Identify the most frequently bought coffee beans to focus on promoting them.
4. Analyze customers’ average order value to improve marketing campaigns.

Now, let’s dive deeper into some of the SQL functions.

Businesses need SQL to assess performance

Companies define their goals with key performance indicators (KPIs). KPIs are measurements that help businesses understand how well they are doing.

Examples of KPIs:

• Customer retention
• Product usage
• Feature usage
• Profit per region
• Lifetime customer value, etc.

But there’s a problem with KPIs — they can’t be measured directly.

You can’t see your lifetime value just from the number of sold items or acquired clients. Instead, you need to calculate it. So companies evaluate KPIs using so-called metrics — or figures that are already there. These are usually customer information, product details, financial records, and other things stored in databases.

Basically, SQL retrieves metrics from a database and puts them in formulas written by a data analyst. For example, this SQL query will give you the total price of all items in the inventory:

SELECT SUM(price) AS total_price FROM inventory;

The SUM function adds up the prices of all items in the inventory. The AS keyword renames the result as total_price.

With SQL, a data analyst is only supposed to describe what they want. The machine does all the calculations on its own, figuring out how to do them. In programming, languages like this are called “declarative languages.”

They are different from imperative languages (e.g., C, JavaScript, Python). With the latter, it’s the user’s task to come up with a solution and give the machine step-by-step instructions.

SQL is used to group data by a chosen parameter

Let’s see another use case from the Majestic company. In its database, it stores millions of records divided into columns. They look something like this:

Now, imagine that a data analyst from Majestic wants to calculate total sales. To do that, they need to sum up all the rows in the SalesAmount column. So they select this column and perform the calculation with the SELECT SUM(SalesAmount) query.

The company might want to group total sales by customer (or date, city, zip code, etc.). So the data analyst will use this query: SELECT Customer, SUM(SalesAmount) FROM Table GROUP BY Client. It will “collapse” the entire data set based on the used type of function.

SQL can help with tracking trends

A database keeps records on vastly different events — purchases, account log-ins, refunds, and so on. It’s not easy to sift through them if you want to find peaks, drops, and trends.

That is, unless you implement SQL, which allows you to use linear progression. It’s the most common technique in predictive analysis that models unknown data based on existing data values.

With the help of an SQL query, you can turn this:

Into this:

A guide on trend-tracking can be found here.

Roadmap of learning SQL for beginners

If you want to start learning SQL, there are two key things you need: an SQL editor and a database. An SQL editor is a software program that allows users to write, test, and execute SQL queries. A database is the material that you will test your SQL skills on. 

However, there are useful resources that allow you to start from scratch — no editor needed.

• SQLZoo offers a comprehensive SQL tutorial aimed at beginners. The tutorial is divided into multiple levels. Each level contains a series of exercises that can be completed online.
• SQLBolt also provides an interactive tutorial for learning SQL. Its lessons cover basic SQL commands, filtering data, sorting data, joining tables, and more.
• Schemaverse is an online space-based game coded within a PostgreSQL database. Using SQL commands, players create their own fleets of spaceships, engage in battles with other players, and attempt to conquer the database.
• PostgreSQL Exercises have interactive exercises to train PostgreSQL skills. It makes the learning process way easier.

Now let’s talk about self-education. Here’s a basic step-by-step guide for those who want to master SQL on their own!

Step 1. Familiarize yourself with data

Before diving into SQL you need a basic understanding of the data types you’ll be working with. You should learn how to read the information in datasets when it’s grouped in different ways.

One great resource is Kaggle. Here, you can find a community-driven library of datasets to download and practice on. For example, Top 100 popular movies from 2003 to 2022 (iMDB) is a dataset that includes movie titles, ratings, years, and other information. You can use it to get used to working with data.

Step 2. Find an interesting area to analyze

Mastering SQL can be tedious. If you bury yourself in boring financial or pharmaceutical data, all your enthusiasm might run dry. So it’s important to find a topic that you’re passionate about — whether it’s a particular industry, a social issue, or a hobby. This way your SQL journey will feel a lot more rewarding.

Step 3. Master Excel before moving to SQL

Excel is a great tool for organizing data. It can also serve as a good foundation for learning SQL. By mastering Excel, you’ll be better prepared to work with databases. This step will upgrade your understanding of the principles of data analysis.

The resource that Excel users recommend most frequently is the YouTube channel ExcelIsFun, which publishes both short tutorial videos and hour-long courses for Excel enthusiasts.

Step 4. Learn to describe your statistics

In order to analyze data you should be able to describe it mathematically. This includes knowing answers to questions like:

• What is the median of your dataset?
• What is the distribution of the dataset?
• Is it skewed in any particular direction?
• Is the resulting distribution expected or strange?

This is essential for drawing meaningful conclusions from the data you’re working with.

Step 5. Learn to visualize the results

Data analysts create all sorts of visuals to capture people’s interest and attention. Charts and graphs can help you communicate your findings more clearly. They will also make it easier for others to understand your analysis.

The top three tools for visualizing data are Microsoft Excel, Google Charts, and Tableau. By mastering any of them, you’ll be able to concisely present information.

Step 0. …Or simply ― join an online course!

Learning SQL might seem challenging: if you are a beginner, you’ll most likely not know where to start. Maybe you need to sift through countless YouTube tutorials? Or is it time to buy as many textbooks as you can? It’s easy to get stressed just thinking about it.

For your convenience, TripleTen has launched a Data Analytics Bootcamp. It’s a beginner-friendly program that teaches essential data analysis skills — including SQL. The bootcamp is designed to be a “one-stop-shop” for your data analytics career. In the end, you’ll come out a full-fledged specialist who knows all the ins and outs of this exciting role.

Alternatively, you can check our free SQL program. Thanks to our interactive learning platform, studying almost feels like a game. The lessons are given in small bites, and there’s lots of practice. In just 15 hours, you will master an incredibly useful skill that might land you a promotion!

Final thoughts

SQL (Structured Query Language) is a programming language used to talk to relational databases.

• It helps companies assess their performance by retrieving metrics stored in databases and calculating KPIs.
• Data analysts use SQL to group records in a database by a selected parameter.
• Finally, businesses apply SQL to track trends.

If you are only interested in SQL, check out this free crash course. And if you want to take the first step towards a new profession, join TripleTen’s Data Analytics Bootcamp. You will receive a full set of priceless skills needed to become a data analyst.

Diving into a new field of knowledge might be scary, but don’t worry — we made our programs with this in mind. Our interactive platform makes the learning process more engaging, and our mentors are ready to help you get through any task.