You already know how to pull basic records from a database. Now, we'll focus on the real work of a data analyst: transforming raw data into structured, meaningful insights. This is where SQL shifts from a simple retrieval tool to a powerful engine for analysis. The entire process happens within a Relational Database Management System (RDBMS), an environment designed to manage interconnected data tables efficiently.

Think of it this way: raw data is like a jumbled pile of LEGO bricks. Your job as an analyst is to use SQL to sort, connect, and assemble those bricks into a coherent model that tells a story or answers a specific business question. Let's start by connecting the pieces.

Data rarely lives in one giant table. It's usually split across multiple tables to stay organized and efficient. For example, a company might have one table for customer information and another for their orders. To find out which customer placed which order, you need to join them.

Imagine we have two tables:

An INNER JOIN finds the matching records in both tables. It only returns rows where the CustomerID exists in both the Customers and Orders tables. In this case, Charlie (CustomerID 3) has no orders, so he won't appear in the result.

A LEFT JOIN is different. It returns all records from the left table (Customers) and any matching records from the right table (Orders). If there's no match, the columns from the right table will be NULL. This is perfect for finding customers who haven't placed any orders.

RIGHT JOIN is the opposite of LEFT JOIN, returning all records from the right table. A FULL OUTER JOIN returns all records when there is a match in either the left or the right table. It's essentially a combination of LEFT and RIGHT joins.

Getting a list of transactions is useful, but businesses run on metrics. How many orders were placed? What was the average sale amount? This is where aggregate functions come in. They take many values and return a single summary value.

The most common aggregate functions are COUNT(), SUM(), AVG(), MIN(), and MAX().

By themselves, they summarize the entire table. But their real power is unlocked when paired with the GROUP BY clause. This lets you calculate metrics for specific segments. For example, we can count the number of orders each customer placed.

This query first joins the tables, then groups the rows by customer name, and finally calculates the order count and total spend for each group.

But what if you want to filter based on the result of an aggregation? For example, you only want to see customers who have spent more than $100. A WHERE clause won't work here, because it filters rows before the aggregation happens. For this, you need HAVING.

Real-world analysis often involves multiple steps. You might need to calculate an intermediate value and then perform another calculation on that result. Two common ways to handle this are subqueries and Common Table Expressions (CTEs).

A subquery is a query nested inside another query. The database executes the inner query first, and its result is used by the outer query. They can get difficult to read if you nest too many.

For example, to find all orders with an amount greater than the overall average order amount:

A cleaner, more modern approach is using a CTE. A CTE lets you define a temporary, named result set that you can reference within your main query. You define it using a WITH clause. They make long queries much easier to read and debug because you can break the logic into sequential steps.

Here's the same problem solved with a CTE:

Your analysis is only as good as your data. Inconsistent formats, missing values, and incorrect data types can ruin your results. While you can clean data in tools like Python or Excel, doing it directly in SQL is often more efficient, ensuring that anyone querying the database gets clean data from the start.

Missing values, or NULLs, are a common problem. The COALESCE() function is a great tool for this. It returns the first non-null value in a list of arguments. You can use it to replace NULLs with a sensible default, like 0 for a numerical column or 'N/A' for a text column.

Another frequent task is changing a column's data type. Maybe a date is stored as text, or a number is stored as a string. The CAST() function converts a value from one data type to another.

For more complex conditional logic, the CASE statement is your best friend. It functions like an if-then-else statement, allowing you to create new categories or values based on existing data. This is incredibly useful for bucketing data, like grouping customers by their spending habits.

Finally, SQL has a rich library of functions for manipulating text and dates. Functions like LOWER(), UPPER(), TRIM(), and SUBSTRING() help standardize text fields. Date functions like DATE_TRUNC() or EXTRACT() allow you to group data by month, year, or day of the week, which is essential for time-series analysis.

Mastering these techniques will allow you to handle the messy, real-world data you'll encounter as an analyst.

Now you have the core tools to transform, aggregate, and clean data for robust analysis. Let's test your understanding.