Gain insights into advanced SQL data analysis techniques. Explore producing descriptive statistics, handling missing data, and analyzing time series. Discover aggregating results and preparing data for analysis.

SQL databases have been around since the 1970s. Some of the smartest people in the world worked on making it easy to slice, dice, fetch and manipulate data quickly and efficiently. SQL databases have come such a long way that many developers and data scientists have lost track of what they can do with plain SQL.

In this course, you will learn advanced concepts and techniques for analyzing data using SQL. You will obtain hands-on experience in producing descriptive statistics, breaking a large query into multiple steps and samples, and cleaning and preparing data for analysis. You will learn how to produce aggregate results and subtotals, analyze a time series using cumulative and window frames, how to handle and fill missing data, and how to produce buckets and histograms.

After completing this course, you will be able to gain actionable insight from your data using nothing but SQL!

Practical Data Analysis with SQL

Generating data is very handy. Sometimes, we need to generate data for practice. Sometimes, we want to run performance simulations and need a big table to work with. Other times we might need to generate a sequence such as a time dimension in a data warehouse, or an axis table to join to in an anomaly detection system.

There are many reasons to generate data in SQL, and in this chapter we’ll learn several ways to do that.


# The `SELECT` clause

The most straightforward way to to generate data in SQL is using the `SELECT` clause:


This query will produce a single row with the `id` column and the `name` column. Notice that this query is not fetching from any table. In fact, in SQL, the only required clause is `SELECT`.

Generating just one row is useful, but what if we need to generate more than one row?

# The `UNION ALL` command

To combine the results of multiple queries we can use [`UNION ALL`](https://www.postgresql.org/docs/current/queries-union.html):

SELECT 1 AS id, 'haki' AS name
UNION ALL
SELECT 2, 'benita'

We used `UNION ALL` to concatenate the output of two queries.

To use `UNION ALL`, it’s important to keep the types and the number of columns similar in all of the concatenated queries. Let’s consider this example:

Introduction

Basic SQL for Data Analysis

Descriptive Statistics

Grouping and Subtotals

Running and Cumulative Aggregation

Interpolation

Binning

Conclusion

Generating Data

The `SELECT` clause

The `UNION ALL` command

Generating Data

The SELECT clause

The UNION ALL command

The `SELECT` clause

The `UNION ALL` command