# SQL for Real Life: From First Query to Data-Driven Answers
Learn SQL from the ground up by writing real queries against realistic datasets, ending with the ability to independently retrieve, join, summarize, and analyze data to answer practical business questions.
## Module 1: Module 1: Getting Comfortable with SQL Basics
### What Is a Relational Database? Tables, Rows, and Keys Explained
# What Is a Relational Database? Tables, Rows, and Keys Explained
Before you write a single line of SQL, it helps to understand what you're actually querying: a **relational database**. At its core, a relational database is a way of organizing data into **tables** — grid-like structures of rows and columns — that can be linked together based on shared values. This "relating" of tables is where the term comes from, and it's the reason SQL (Structured Query Language) is so powerful: it lets you pull related pieces of information together on demand, instead of storing everything in one giant, repetitive spreadsheet.
## Tables: The Building Blocks
A table represents one type of "thing" — customers, orders, products, employees, and so on. Each table is made up of:
- **Columns** (also called fields or attributes): these define *what kind* of data is stored, and each column has a specific data type (text, number, date, etc.).
- **Rows** (also called records): each row represents *one instance* of that thing — one specific customer, one specific order.
Here's a simple `customers` table:
| customer_id | first_name | last_name | email | signup_date |
|-------------|-----------|-----------|-----------------------|-------------|
| 1 | Maria | Chen | maria.c@example.com | 2023-01-15 |
| 2 | James | Ortiz | james.o@example.com | 2023-02-02 |
| 3 | Priya | Nair | priya.n@example.com | 2023-02-20 |
Each **column** (`customer_id`, `first_name`, etc.) describes an attribute of a customer. Each **row** is a complete record for one customer. This structure is intentional: it keeps data consistent (every customer has the same set of fields) and easy to search, filter, and sort — all things you'll do constantly with SQL.
## Keys: How Rows Are Identified and Connected
Keys are what make tables *relational* — they let you uniquely identify rows and link tables to one another.
### Primary Keys
A **primary key** is a column (or combination of columns) that uniquely identifies each row in a table. No two rows can share the same primary key value, and it can't be blank. In the table above, `customer_id` is the primary key — even if two customers happen to have the same name, their `customer_id` values will always be different, so you can always pinpoint exactly the right row.
### Foreign Keys
A **foreign key** is a column in one table that refers to the primary key of another table. This is how relational databases avoid duplicating data. Consider an `orders` table:
| order_id | customer_id | order_date | total_amount |
|----------|-------------|------------|---------------|
| 101 | 1 | 2023-03-01 | 45.00 |
| 102 | 1 | 2023-03-10 | 20.50 |
| 103 | 3 | 2023-03-12 | 78.25 |
Here, `customer_id` in the `orders` table is a **foreign key** pointing back to `customer_id` in the `customers` table. Notice that customer 1 (Maria Chen) placed two orders, but her name and email are stored only *once*, in the `customers` table — not repeated in every order row. This is a core relational database principle: **store each fact once, and connect tables through keys** rather than duplicating information everywhere. It keeps data accurate (update Maria's email once, and it's correct everywhere) and storage efficient.
## Why This Matters for Writing SQL
Understanding this structure explains *why* SQL queries work the way they do:
- When you write a `SELECT`, you're pulling specific columns from a specific table (or combination of tables).
- When you eventually learn `JOIN` operations (covered in a later module), you'll be matching foreign keys to primary keys to combine data from `customers` and `orders` — for example, to answer "What is Maria Chen's total spending?"
- Primary keys are also what you'll frequently filter on with `WHERE` clauses, since they guarantee you're targeting exactly one row (e.g., `WHERE customer_id = 1`).
**Quick check:** In the tables above, if you wanted to find all orders placed by James Ortiz, you'd need his `customer_id` (2) from the `customers` table, then look for rows in `orders` where `customer_id = 2`. That two-step logic — find the key, then match the key — is the mental model behind nearly every multi-table query you'll write in SQL.
### Diagram: Relational databases link tables together through shared key columns: a primary key uniquely identifies each row in one table, and a foreign key in another table references that primary key to establish the relationship.
```mermaid
erDiagram
CUSTOMERS ||--o{ ORDERS : "places"
CUSTOMERS {
int customer_id PK
string first_name
string last_name
string email
date signup_date
}
ORDERS {
int order_id PK
int customer_id FK
date order_date
decimal total_amount
}
```
### Your First Query: SELECT and FROM
# Your First Query: SELECT and FROM
Every SQL query you write to retrieve data starts with the same two ingredients: **SELECT**, which tells the database *what columns you want to see*, and **FROM**, which tells it *which table to look in*. Once you understand this pair, you have the foundation for almost everything else you'll do in SQL.
## The Basic Syntax
```sql
SELECT column1, column2
FROM table_name;
```
Read this in plain English: "Give me `column1` and `column2` from `table_name`." The semicolon at the end marks the statement as complete — most database tools require it, or at least won't complain if it's there.
Let's ground this in a real example. Imagine a table called `employees` that stores basic information about everyone who works at a company:
| employee_id | first_name | last_name | department | salary | hire_date |
|-------------|------------|-----------|------------|--------|------------|
| 1 | Maria | Chen | Sales | 62000 | 2019-03-14 |
| 2 | James | Osei | IT | 78000 | 2021-07-01 |
| 3 | Priya | Nair | Sales | 59000 | 2020-11-23 |
| 4 | Diego | Ramirez | Marketing | 65000 | 2018-05-30 |
Each **row** represents one employee, and each **column** represents one attribute of that employee — their name, department, salary, and so on. This is the essence of a relational table: consistent columns describing many individual records.
## Selecting Specific Columns
Suppose you only care about who works where — you don't need salary or hire date cluttering your results. You'd write:
```sql
SELECT first_name, last_name, department
FROM employees;
```
This returns:
| first_name | last_name | department |
|------------|-----------|------------|
| Maria | Chen | Sales |
| James | Osei | IT |
| Priya | Nair | Sales |
| Diego | Ramirez | Marketing |
Notice that the order of columns in your `SELECT` list determines the order they appear in the results — SQL doesn't force you to match the table's original column order.
## Selecting All Columns with `*`
If you want every column without typing each name, use the asterisk wildcard:
```sql
SELECT *
FROM employees;
```
This is convenient for quickly exploring a table you're unfamiliar with, but it's considered poor practice in production code or reports. Here's why: if someone later adds a new column to the table (say, `email`), every query using `SELECT *` will suddenly return that new data too — which can break applications, slow down queries unnecessarily, or expose information you didn't intend to show. As a habit, name the columns you actually need.
## A Few Practical Notes
- **Column order in `SELECT` is your choice.** `SELECT last_name, first_name` and `SELECT first_name, last_name` pull from the same table but display differently.
- **Capitalization of keywords is a convention, not a requirement.** `select first_name from employees;` works identically to `SELECT first_name FROM employees;` in most database systems. Writing keywords in uppercase (`SELECT`, `FROM`) is a widely followed style choice that makes queries easier to scan — not a syntax rule.
- **Whitespace and line breaks don't matter to the database.** You could write your entire query on one line, but breaking `SELECT` and `FROM` onto separate lines (as shown above) makes longer queries far easier to read once you start adding more clauses later in this module.
- **Table and column names are case-sensitive in some systems** (like PostgreSQL, in certain configurations) and not in others (like MySQL on Windows by default). When in doubt, match the exact capitalization used when the table was created.
## Try It Yourself
Using the `employees` table above, write a query that returns only each employee's `department` and `hire_date`. Then try writing one that returns all columns using `*`. Compare the two outputs — this contrast is a useful gut-check for understanding exactly what `SELECT` controls versus what `FROM` controls: `SELECT` picks the *columns*, `FROM` picks the *source table* — and every query you write from here on builds on that distinction.
### Diagram: The basic anatomy of a SQL query: SELECT specifies which columns to retrieve, while FROM specifies the source table, together producing a result set.
```mermaid
flowchart LR
subgraph Query["SQL Statement"]
S["SELECT first_name, last_name, department"]
F["FROM employees"]
end
S -->|"picks columns"| R
F -->|"picks table"| R
T["employees table\n(employee_id, first_name, last_name,\ndepartment, salary, hire_date)"] --> F
R["Result Set\n\nfirst_name | last_name | department\nMaria | Chen | Sales\nJames | Osei | IT\nPriya | Nair | Sales\nDiego | Ramirez | Marketing"]
style S fill:#cde4ff,stroke:#3366cc
style F fill:#ffe0b3,stroke:#cc7a00
style T fill:#f0f0f0,stroke:#999
style R fill:#d9f2d9,stroke:#339933
```
### Filtering Rows with WHERE, AND, OR, and NOT
## Filtering Rows with WHERE, AND, OR, and NOT
So far you've learned how to pull specific *columns* out of a table with `SELECT`. But most of the time, you don't want every row — you want the rows that matter for your question. That's the job of the `WHERE` clause: it filters rows based on a condition, so only the ones that satisfy that condition make it into your result.
### The Basic Syntax
```sql
SELECT column1, column2
FROM table_name
WHERE condition;
```
The `WHERE` clause runs *after* the table is read but *before* the results are displayed — think of it as a bouncer checking each row against a rule before letting it into the results list.
### Comparison Operators
These are the building blocks of any condition:
| Operator | Meaning |
|----------|---------|
| `=` | equal to |
| `<>` or `!=` | not equal to |
| `>` | greater than |
| `<` | less than |
| `>=` | greater than or equal to |
| `<=` | less than or equal to |
**Example:** Suppose you have a table called `employees`:
| id | name | department | salary | years_employed |
|----|------|------------|--------|-----------------|
| 1 | Ana | Sales | 52000 | 3 |
| 2 | Ben | Engineering | 71000 | 5 |
| 3 | Cho | Sales | 48000 | 1 |
| 4 | Dev | Engineering | 65000 | 2 |
| 5 | Eve | Marketing | 58000 | 4 |
To find everyone earning more than $60,000:
```sql
SELECT name, salary
FROM employees
WHERE salary > 60000;
```
**Result:**
| name | salary |
|------|--------|
| Ben | 71000 |
| Dev | 65000 |
Only Ben and Dev satisfy the condition — every other row is filtered out before it ever reaches your screen.
### Combining Conditions with AND and OR
Real questions are rarely that simple. What if you want employees in Sales *and* earning under $50,000? That's where `AND` comes in — **every** condition joined by `AND` must be true for the row to appear.
```sql
SELECT name, department, salary
FROM employees
WHERE department = 'Sales' AND salary < 50000;
```
**Result:**
| name | department | salary |
|------|------------|--------|
| Cho | Sales | 48000 |
Ana is in Sales but earns 52000, so she's excluded — she fails the second condition even though she passes the first.
`OR` is more permissive: the row appears if **at least one** condition is true.
```sql
SELECT name, department, salary
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';
```
This returns Ana, Cho, and Eve — anyone in either department.
**A common pitfall:** mixing `AND` and `OR` without parentheses can produce surprising results, because SQL evaluates `AND` before `OR` by default. For example:
```sql
WHERE department = 'Sales' OR department = 'Marketing' AND salary > 55000
```
This is actually interpreted as `department = 'Sales' OR (department = 'Marketing' AND salary > 55000)` — so *every* Sales employee is included regardless of salary, which may not be what you intended. Always use parentheses to make your intent explicit:
```sql
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 55000
```
### Excluding Rows with NOT
`NOT` reverses a condition, keeping rows that *don't* match. To find everyone outside of Engineering:
```sql
SELECT name, department
FROM employees
WHERE NOT department = 'Engineering';
```
This is equivalent to writing `department <> 'Engineering'`, but `NOT` is especially useful with more complex conditions or with operators like `IN` and `BETWEEN` (covered later), for example:
```sql
WHERE NOT (department = 'Sales' AND salary < 50000)
```
### Practical Tips
- Text values need quotes (`'Sales'`); numbers don't.
- Comparisons are case-sensitive in many databases — `'sales'` may not match `'Sales'`.
- When conditions get long, format them on separate lines and use parentheses liberally — it costs nothing and prevents logic errors.
Mastering `WHERE`, `AND`, `OR`, and `NOT` gives you precise control over exactly which rows you see, which is the foundation for nearly every real-world query you'll write.
### Diagram: The WHERE clause acts as a bouncer, checking each row from the table against a condition (using operators like =, >, AND, OR, NOT) before deciding whether it passes into the final result set.
```mermaid
flowchart LR
A[employees table
5 rows: Ana, Ben, Cho, Dev, Eve] --> B{WHERE clause
bouncer checks condition}
B -->|"salary > 60000
evaluates TRUE"| C[Row passes]
B -->|"salary > 60000
evaluates FALSE"| D[Row rejected]
C --> E[Ben - 71000]
C --> F[Dev - 65000]
D --> G[Ana - 52000 rejected]
D --> H[Cho - 48000 rejected]
D --> I[Eve - 58000 rejected]
subgraph Logic["Combining Conditions"]
J["AND: ALL conditions must be TRUE
e.g. department='Sales' AND salary<50000"]
K["OR: AT LEAST ONE condition TRUE
e.g. department='Sales' OR department='Marketing'"]
L["NOT: reverses the condition
e.g. NOT department='Engineering'"]
end
B -.uses.-> Logic
```
### Sorting and Limiting Results with ORDER BY and LIMIT
# Sorting and Limiting Results with ORDER BY and LIMIT
Once you can filter rows with `WHERE`, the next question is usually: *in what order should the results appear, and how many of them do I actually need?* By default, a relational database does not guarantee any particular order for the rows it returns — it may store and retrieve them in whatever order is most efficient internally. If you want a meaningful order (alphabetical, newest first, highest value first, etc.), you have to ask for it explicitly with `ORDER BY`. And if you only need a handful of rows — say, the top 5 results — `LIMIT` lets you cut the result set down instead of scrolling through everything.
## Sorting with ORDER BY
The `ORDER BY` clause goes near the end of your query, after `WHERE`, and specifies one or more columns to sort by.
```sql
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date;
```
This returns every employee, sorted from the earliest hire date to the most recent. By default, `ORDER BY` sorts in **ascending order** (`ASC`) — smallest to largest, earliest to latest, or A to Z. To reverse that, add `DESC`:
```sql
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC;
```
Now the most recently hired employees appear first.
### Sorting by multiple columns
You can sort by more than one column, and the order you list them in matters — the first column is the primary sort key, and each subsequent column only breaks ties within the previous one.
```sql
SELECT department, last_name, salary
FROM employees
ORDER BY department ASC, salary DESC;
```
This groups employees by department alphabetically, and *within* each department, lists the highest earners first. Note that `ASC` and `DESC` apply individually to each column — you're not forced to sort every column the same direction.
## Limiting Results with LIMIT
`LIMIT` restricts how many rows the query returns. It's especially useful for previewing data, avoiding overwhelming result sets, or answering "top N" questions.
```sql
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
```
This returns only the five highest-paid employees. Note the order of clauses here — `LIMIT` always comes *after* `ORDER BY` in the query, even though you read the effect as "sort, then limit." SQL processes them in that logical sequence: filter with `WHERE`, sort with `ORDER BY`, then cut down with `LIMIT`.
> **Common pitfall:** Using `LIMIT` without `ORDER BY` gives you an arbitrary set of rows — not necessarily the "first" or "top" ones in any meaningful sense, since the database has no defined order to begin with. If you want a reliable "top N," always pair `LIMIT` with `ORDER BY`.
## Worked Example: Combining WHERE, ORDER BY, and LIMIT
Suppose you have an `orders` table with columns `order_id`, `customer_name`, `order_total`, and `order_date`. You want to find the three largest orders placed in 2023.
```sql
SELECT order_id, customer_name, order_total, order_date
FROM orders
WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31'
ORDER BY order_total DESC
LIMIT 3;
```
Reading this step by step:
1. `WHERE` filters the table down to only rows where `order_date` falls within 2023.
2. `ORDER BY order_total DESC` sorts those filtered rows from the largest total to the smallest.
3. `LIMIT 3` keeps only the first three rows after sorting — the three biggest orders of the year.
If you wanted the three *smallest* orders instead, you'd simply change `DESC` to `ASC`. If you wanted the top 3 orders *per customer* rather than overall, that requires more advanced grouping techniques covered in a later module — but for straightforward "top N results" questions, `ORDER BY` plus `LIMIT` is the pattern you'll use constantly, from quick data checks to building reports.
## Quick Reference
| Goal | Clause |
|---|---|
| Sort smallest/earliest first | `ORDER BY column ASC` (default) |
| Sort largest/latest first | `ORDER BY column DESC` |
| Break ties with a second column | `ORDER BY col1, col2 DESC` |
| Return only N rows | `LIMIT N` |
| Get "top N" reliably | Always combine `ORDER BY` with `LIMIT` |
### Diagram: A query is processed in stages: rows are filtered (WHERE), then sorted (ORDER BY, with optional multi-column tie-breaking and ASC/DESC direction), and finally trimmed to a set number of rows (LIMIT).
```mermaid
flowchart TD
A["Raw Table Rows\n(unordered by default)"] --> B{"WHERE clause?"}
B -->|Filters rows| C["Filtered Rows"]
B -->|No filter| C
C --> D["ORDER BY column1 [ASC|DESC]"]
D --> E{"Tie on column1?"}
E -->|Yes| F["ORDER BY column2 [ASC|DESC]\n(breaks ties)"]
E -->|No| G["Sorted Result Set"]
F --> G
G --> H{"LIMIT n specified?"}
H -->|Yes| I["Return only first n rows"]
H -->|No| J["Return all sorted rows"]
style A fill:#eee,stroke:#333
style G fill:#dff0d8,stroke:#333
style I fill:#d9edf7,stroke:#333
style J fill:#d9edf7,stroke:#333
```
#### Module check
1. In a basic SQL query, what is the difference between the SELECT and FROM clauses?
- SELECT specifies the table; FROM specifies the columns
- SELECT filters rows; FROM sorts them
- SELECT specifies the columns to return; FROM specifies the table to query
- SELECT and FROM do the same thing and are interchangeable
2. True or False: A relational database guarantees that rows will be returned in the order they were inserted unless you specify otherwise.
- True
- False
3. The ____ clause filters rows based on a condition, allowing only rows that satisfy that condition to appear in the query results.
## Module 2: Module 2: Summarizing and Analyzing Data
### Summarizing Data with Aggregate Functions
# Summarizing Data with Aggregate Functions
So far you've worked with queries that return individual rows — one row per customer, one row per order, one row per product. But many of the most important business questions aren't about individual rows at all. They're about totals, averages, and counts: *How many orders did we get last month? What's our average order value? What's the highest sale we've ever made?*
To answer these, SQL gives you **aggregate functions** — functions that take many rows as input and collapse them into a single summary value.
## The Five Core Aggregate Functions
| Function | What it does | Example use case |
|---|---|---|
| `COUNT()` | Counts rows | How many orders were placed? |
| `SUM()` | Adds up numeric values | Total revenue |
| `AVG()` | Calculates the average | Average order value |
| `MIN()` | Finds the smallest value | Cheapest product |
| `MAX()` | Finds the largest value | Largest single sale |
Each of these takes a column name as an argument and returns one value calculated across all the rows that match your query.
## A Worked Example
Suppose we have an `orders` table:
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 1 | 101 | 2024-01-05 | 250.00 |
| 2 | 102 | 2024-01-06 | 175.50 |
| 3 | 101 | 2024-01-09 | 320.00 |
| 4 | 103 | 2024-01-12 | NULL |
| 5 | 102 | 2024-01-15 | 90.00 |
Let's ask a few basic questions:
```sql
SELECT
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue,
AVG(amount) AS average_order,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders;
```
Result:
| total_orders | total_revenue | average_order | smallest_order | largest_order |
|---|---|---|---|---|
| 5 | 835.50 | 208.875 | 90.00 | 320.00 |
Notice a few things here that trip people up the first time they see them.
## COUNT(*) vs. COUNT(column)
`COUNT(*)` counts **rows**, regardless of whether any column contains `NULL`. That's why it returned 5 — there are five order records, even though one has no amount.
`COUNT(amount)`, on the other hand, only counts rows where `amount` is **not NULL**. If you ran:
```sql
SELECT COUNT(amount) AS orders_with_amount
FROM orders;
```
you'd get `4`, not `5`, because order 4's `NULL` amount is excluded.
This distinction matters constantly in real analysis. If someone asks "how many orders do we have?" you almost always want `COUNT(*)`. If someone asks "how many orders have a recorded amount?" you want `COUNT(amount)`.
## Why AVG Isn't What You Might Expect
Look again at the average: `208.875`. It was calculated as:
```
(250.00 + 175.50 + 320.00 + 90.00) / 4 = 835.50 / 4 = 208.875
```
Not divided by 5. `AVG()`, like `SUM()`, `MIN()`, and `MAX()`, **ignores NULL values entirely** — it doesn't treat them as zero. This is usually the correct behavior (a missing amount isn't the same as a $0 order), but it's a common source of subtle bugs. If you want NULLs treated as zero, you have to say so explicitly, typically with `COALESCE`:
```sql
SELECT AVG(COALESCE(amount, 0)) AS average_including_nulls_as_zero
FROM orders;
```
That version divides by 5 instead of 4, giving `167.10` — a meaningfully different number. Always ask yourself: *should missing data be excluded, or treated as zero?* The answer depends on the business question, not on SQL's default behavior.
## Filtering Before You Aggregate
Aggregate functions operate on whatever rows survive your `WHERE` clause. To get total revenue for just January:
```sql
SELECT SUM(amount) AS january_revenue
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
```
`WHERE` filters rows *before* aggregation happens — this distinction becomes essential once you start using `GROUP BY` and `HAVING` in the next section, where filtering *before* versus *after* grouping produces very different results.
**Practice tip:** before running any aggregate query, predict the answer by scanning the raw data yourself. If your `SUM` or `AVG` doesn't match your prediction, check for NULLs first — that's the most common culprit.
### Chart: The five core aggregate functions applied to the orders table collapse five rows of data into single summary values, illustrating how each function answers a different business question.
### Grouping Results with GROUP BY and HAVING
# Grouping Results with GROUP BY and HAVING
Aggregate functions like `SUM` and `COUNT` become far more powerful when you don't just summarize an entire table, but summarize it *by category*. That's the job of `GROUP BY`: it splits your rows into buckets based on shared values in one or more columns, then applies your aggregate function separately to each bucket.
## How GROUP BY Works
Imagine an `orders` table:
| order_id | customer | region | amount |
|----------|----------|--------|--------|
| 1 | Ada | East | 120 |
| 2 | Ben | West | 85 |
| 3 | Ada | East | 200 |
| 4 | Cy | West | 150 |
| 5 | Ben | West | 60 |
If you want total sales per region, you group by `region`:
```sql
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region;
```
Result:
| region | total_sales |
|--------|-------------|
| East | 320 |
| West | 295 |
Conceptually, the database first partitions rows into groups where `region` is identical (`East` rows together, `West` rows together), then computes `SUM(amount)` within each partition, collapsing each group into a single output row.
**The golden rule:** every column in your `SELECT` list must either appear in the `GROUP BY` clause or be wrapped in an aggregate function. If you try to also select `customer` without grouping by it or aggregating it, most databases will raise an error (or, in more permissive systems, return an arbitrary, unreliable value). This rule exists because the database wouldn't know *which* customer to display for a group that contains multiple customers.
## Grouping by Multiple Columns
You can group by more than one column to get finer-grained buckets. To see sales per customer *within* each region:
```sql
SELECT region, customer, SUM(amount) AS total_sales, COUNT(*) AS order_count
FROM orders
GROUP BY region, customer;
```
This produces one row for every unique `region`/`customer` combination, useful when a single grouping column would hide meaningful subdivisions.
## Filtering Groups with HAVING
`WHERE` filters individual rows *before* grouping happens. But what if you want to filter based on the result of an aggregate — like "only show regions with total sales over 300"? You can't use `WHERE` for this, because at the time `WHERE` is evaluated, the aggregate hasn't been calculated yet. This is exactly what `HAVING` is for: it filters groups *after* aggregation.
```sql
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
HAVING SUM(amount) > 300;
```
Result:
| region | total_sales |
|--------|-------------|
| East | 320 |
West is excluded because its total (295) doesn't clear the threshold — but note that West's individual rows never violated any condition on their own; it's only the *group total* that failed the test.
## WHERE vs. HAVING: A Combined Example
You can use both clauses together. `WHERE` trims rows early (for efficiency and row-level logic), and `HAVING` trims groups afterward (for aggregate-level logic). Suppose you only want orders from this year, and only want customers whose total spend exceeds $150:
```sql
SELECT customer, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer
HAVING SUM(amount) > 150;
```
Order of operations here matters conceptually:
1. `WHERE` removes rows with old order dates.
2. `GROUP BY` buckets the remaining rows by customer.
3. `SUM(amount)` is calculated per bucket.
4. `HAVING` removes buckets whose sum doesn't exceed 150.
5. `SELECT` and any `ORDER BY` produce the final display.
## Practical Tips
- You can reference an aggregate by its expression (`HAVING SUM(amount) > 300`) even if you haven't aliased it; some databases also let you reuse the `SELECT` alias in `HAVING`, but relying on the full expression is more portable.
- `HAVING` isn't limited to sums — you can filter on `COUNT(*) > 5` to find groups with more than five records, or `AVG(amount) < 100` to find underperforming groups.
- If a query returns unexpected duplicate-looking rows, check whether you've grouped by too many columns — extra grouping columns create more, smaller buckets than you intended.
### Chart: GROUP BY partitions the orders table into region-based buckets, and SUM(amount) is computed independently within each bucket to produce one summarized row per region.
### Working with NULLs: Pitfalls and Best Practices
## Working with NULLs: Pitfalls and Best Practices
NULL means "unknown" or "not applicable" — it is not zero, not an empty string, and not "false." This distinction trips up more analysts than any other SQL concept, because NULL doesn't behave like a normal value in comparisons, calculations, or aggregations. Understanding its quirks will save you from silently wrong reports.
### Pitfall 1: NULL breaks equality checks
You cannot test for NULL using `= NULL`. Because NULL represents an unknown value, SQL can't determine whether one unknown equals another — so the comparison itself evaluates to NULL, not TRUE or FALSE. A row with a NULL in the compared column is simply excluded from your results, with no error to warn you.
```sql
-- This returns ZERO rows, even if discount_pct is NULL for many orders
SELECT * FROM orders WHERE discount_pct = NULL;
-- Correct approach
SELECT * FROM orders WHERE discount_pct IS NULL;
SELECT * FROM orders WHERE discount_pct IS NOT NULL;
```
### Pitfall 2: Aggregate functions quietly skip NULLs
Every aggregate function except `COUNT(*)` ignores NULLs in its calculation. This is usually what you want, but only if you know it's happening.
**Worked example.** Suppose a `customer_feedback` table stores a 1–5 satisfaction score, but customers who didn't respond have a NULL:
| customer_id | score |
|---|---|
| 1 | 5 |
| 2 | NULL |
| 3 | 3 |
| 4 | NULL |
| 5 | 4 |
```sql
SELECT
COUNT(*) AS total_customers,
COUNT(score) AS responses_received,
AVG(score) AS avg_score,
SUM(score) AS score_sum
FROM customer_feedback;
```
Result:
| total_customers | responses_received | avg_score | score_sum |
|---|---|---|---|
| 5 | 3 | 4.0 | 12 |
Notice `AVG(score)` returns 4.0 — the average of the **3 non-null scores** (5+3+4=12, divided by 3) — not divided by 5. If you wanted "average satisfaction across all customers, treating non-responses as 0," you'd need to convert NULLs explicitly:
```sql
SELECT AVG(COALESCE(score, 0)) AS avg_including_nonresponders
FROM customer_feedback;
```
This returns 2.4 (12 ÷ 5) — a very different, and often more misleading, number. The lesson: always ask *what should a NULL mean in this calculation* before choosing whether to exclude it or substitute a value.
### Pitfall 3: NULLs and arithmetic
Any arithmetic expression involving NULL returns NULL. `price * NULL` is NULL, not `price` or `0`. This can silently zero out rows in calculated columns:
```sql
SELECT order_id, price, discount_pct,
price * (1 - discount_pct) AS final_price
FROM orders;
```
If `discount_pct` is NULL for undiscounted orders (instead of 0), `final_price` becomes NULL for every one of them. Fix it with `COALESCE`:
```sql
SELECT order_id, price, discount_pct,
price * (1 - COALESCE(discount_pct, 0)) AS final_price
FROM orders;
```
### Pitfall 4: NULLs in GROUP BY
`GROUP BY` treats all NULLs as a single group, which is usually fine but worth naming clearly:
```sql
SELECT COALESCE(region, 'Unknown Region') AS region, COUNT(*) AS num_orders
FROM orders
GROUP BY region;
```
### Best Practices Checklist
- Use `IS NULL` / `IS NOT NULL`, never `= NULL`.
- Use `COALESCE(column, default_value)` to substitute a value when NULL should be treated as zero, blank, or a labeled category.
- Before writing `AVG`, `SUM`, or `COUNT(column)`, ask whether NULLs should be **excluded** (the default) or **counted as zero/absent** — these give different, sometimes contradictory, answers.
- Use `COUNT(*)` vs `COUNT(column)` deliberately: the difference between them tells you how many NULLs exist in that column.
- Test logic with `CASE WHEN column IS NULL THEN ... END` when you need custom handling beyond simple substitution.
- Document your NULL-handling choice in comments — future readers (including you) need to know whether "average" means "average of responders" or "average of everyone."
### Diagram: NULL is "unknown," not zero or false — this flowchart shows how that changes the outcome of common SQL operations, from equality checks to aggregations and arithmetic.
```mermaid
flowchart TD
A["NULL encountered
(unknown / not applicable)"] --> B{"How is it used?"}
B --> C["Equality check
col = NULL"]
C --> C1["Evaluates to NULL
(not TRUE or FALSE)"]
C1 --> C2["Row silently excluded
NO ERROR shown"]
C2 --> C3["✅ Fix: use IS NULL / IS NOT NULL"]
B --> D["Aggregate function
AVG, SUM, MIN, MAX, COUNT(col)"]
D --> D1["NULLs are skipped
(not treated as 0)"]
D1 --> D2["Denominator/count shrinks
e.g. AVG uses only non-null rows"]
D2 --> D3["✅ Fix: use COALESCE(col, 0)
if 0 is the intended meaning"]
B --> E["COUNT(*)"]
E --> E1["Counts ALL rows
including NULLs"]
B --> F["Arithmetic / concatenation
col + 5, col || 'x'"]
F --> F1["Result is NULL
(contagious)"]
F1 --> F2["✅ Fix: wrap with COALESCE"]
style A fill:#fdf6e3,stroke:#b58900
style C2 fill:#f8d7da,stroke:#c0392b
style D2 fill:#f8d7da,stroke:#c0392b
style F1 fill:#f8d7da,stroke:#c0392b
style C3 fill:#d4edda,stroke:#27ae60
style D3 fill:#d4edda,stroke:#27ae60
style F2 fill:#d4edda,stroke:#27ae60
```
### Conditional Logic with CASE and Simple Subqueries
# Conditional Logic with CASE and Simple Subqueries
So far you've learned to summarize data using aggregate functions and grouping. But many real-world questions require you to first *classify* or *filter* data based on conditions before summarizing it. This is where `CASE` statements and subqueries come in — they let you embed decision-making directly into your SQL.
## The CASE Statement: If/Then Logic in SQL
A `CASE` statement evaluates conditions in order and returns a value when a condition is true. Think of it as SQL's version of an `IF...ELSE IF...ELSE` block.
**Basic syntax:**
```sql
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
```
### Example: Categorizing Orders by Size
Suppose you have an `orders` table with columns `order_id`, `customer_id`, and `total_amount`. You want to label each order as "Small," "Medium," or "Large."
```sql
SELECT
order_id,
total_amount,
CASE
WHEN total_amount < 50 THEN 'Small'
WHEN total_amount BETWEEN 50 AND 199.99 THEN 'Medium'
ELSE 'Large'
END AS order_size
FROM orders;
```
This adds a new column, `order_size`, computed row by row. Notice that `CASE` conditions are checked top to bottom — the first match wins, so order matters. If `total_amount` is 75, it skips the "Small" check, matches "Medium," and stops there.
### Combining CASE with Aggregate Functions
The real power of `CASE` shows up when you pair it with `SUM` or `COUNT` to build **conditional aggregates** — essentially counting or summing only rows that meet a condition, without needing multiple queries.
**Example:** Count how many orders fall into each size category, all in one row of output:
```sql
SELECT
COUNT(CASE WHEN total_amount < 50 THEN 1 END) AS small_orders,
COUNT(CASE WHEN total_amount BETWEEN 50 AND 199.99 THEN 1 END) AS medium_orders,
COUNT(CASE WHEN total_amount >= 200 THEN 1 END) AS large_orders
FROM orders;
```
Here's the trick: `COUNT` only counts non-NULL values. When the `CASE` condition is false and there's no `ELSE`, it returns `NULL` by default — so that row simply isn't counted. This pattern (`COUNT(CASE WHEN ... THEN 1 END)`) is one of the most useful idioms in analytical SQL, letting you turn one table scan into a mini pivot table.
## Simple Subqueries: A Query Inside a Query
A **subquery** is a query nested inside another query, usually inside parentheses. It runs first, and its result is used by the outer query. Subqueries are especially useful when you need to compare a row against a summary value — something an aggregate function alone can't do in the same step.
### Example: Finding Above-Average Orders
Suppose you want to find every order that's larger than the *average* order amount. You can't write `WHERE total_amount > AVG(total_amount)` directly — aggregate functions aren't allowed in a `WHERE` clause. Instead, use a subquery:
```sql
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (
SELECT AVG(total_amount)
FROM orders
);
```
The inner query calculates a single value (say, 82.50), and the outer query then filters using that value — as if you'd typed `WHERE total_amount > 82.50`. This is called a **scalar subquery** because it returns exactly one value.
### Example: Filtering with a List from a Subquery
Subqueries can also return multiple values for use with `IN`:
```sql
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE total_amount > 500
);
```
This finds every customer who has placed at least one order over $500. The inner query returns a list of matching `customer_id` values; the outer query checks membership in that list.
## Practical Tips
- Always alias your `CASE` expressions (`AS order_size`) — otherwise the output column has no readable name.
- When nesting `CASE` inside `SUM` or `COUNT`, double-check whether you need `THEN 1` (for counting) or `THEN total_amount` (for summing actual values).
- Test subqueries independently first — run the inner query alone to confirm it returns what you expect before wrapping it in the outer query.
- Scalar subqueries (used with `=`, `>`, `<`) must return exactly one row and one column, or the query will error.
### Diagram: A CASE statement evaluates conditions top-to-bottom, returning the result for the first true condition (or the ELSE default), which then flows into row labeling or conditional aggregation.
```mermaid
flowchart TD
A[Row: total_amount value] --> B{total_amount < 50?}
B -- Yes --> C[Result: 'Small']
B -- No --> D{total_amount BETWEEN 50 AND 199.99?}
D -- Yes --> E[Result: 'Medium']
D -- No --> F[ELSE: Result 'Large']
C --> G[order_size column]
E --> G
F --> G
G --> H[Used directly as SELECT column]
G --> I[Used inside COUNT/SUM for conditional aggregates]
I --> J[COUNT CASE WHEN total_amount < 50 THEN 1 END AS small_orders]
I --> K[COUNT CASE WHEN total_amount BETWEEN 50 AND 199.99 THEN 1 END AS medium_orders]
I --> L[COUNT CASE WHEN total_amount >= 200 THEN 1 END AS large_orders]
```
#### Module check
1. In SQL, the expression `WHERE discount_pct = NULL` will correctly return all rows where discount_pct is NULL.
- True
- False
2. What does the GROUP BY clause do when used with an aggregate function like SUM()?
- It removes duplicate rows from the result set
- It filters rows before any aggregate functions are applied
- It splits rows into buckets based on shared column values so aggregate functions can be applied separately to each bucket
- It sorts the final result set alphabetically by the grouped column
3. The ____ statement lets you embed if/then/else decision-making logic directly into a SQL query to classify rows based on conditions.
## Module 3: Module 3: Connecting Tables with Joins
### Why We Need Joins: Splitting Data Across Tables
# Why We Need Joins: Splitting Data Across Tables
If you've only ever worked with a single spreadsheet, the idea of storing your data across multiple tables might seem like an unnecessary complication. Why not just keep everything — customer names, order details, product info — in one giant table? The answer lies in a database design principle called **normalization**, and understanding it is the key to understanding why joins exist at all.
## The Problem with One Big Table
Imagine you run an online store and you keep all your data in a single table called `orders_flat`:
| order_id | customer_name | customer_email | product_name | product_price | quantity |
|---|---|---|---|---|---|
| 1001 | Maria Chen | maria@email.com | Wireless Mouse | 24.99 | 2 |
| 1002 | Maria Chen | maria@email.com | USB-C Cable | 9.99 | 1 |
| 1003 | James Okafor | james@email.com | Wireless Mouse | 24.99 | 1 |
At first glance this looks fine. But look closer, and you'll spot the problems:
- **Redundancy**: Maria Chen's name, email, and other details are repeated every time she places an order. If she has 50 orders, that's 50 copies of the same information.
- **Update anomalies**: If Maria changes her email address, you now have to update every single row where her email appears. Miss one, and your data is inconsistent.
- **Wasted storage and risk of error**: The product name "Wireless Mouse" and its price are repeated too. If the price changes, do you update every historical row, or just new ones? Typos become likely with repeated manual entry.
- **Limited flexibility**: What if a customer hasn't placed any orders yet? There's no row for them at all, so you can't even see they exist as a customer.
## The Solution: Splitting Data into Related Tables
Instead, a well-designed database splits this information into separate tables, each focused on one type of entity:
**`customers`**
| customer_id | customer_name | customer_email |
|---|---|---|
| 1 | Maria Chen | maria@email.com |
| 2 | James Okafor | james@email.com |
**`products`**
| product_id | product_name | product_price |
|---|---|---|
| 501 | Wireless Mouse | 24.99 |
| 502 | USB-C Cable | 9.99 |
**`orders`**
| order_id | customer_id | product_id | quantity |
|---|---|---|---|
| 1001 | 1 | 501 | 2 |
| 1002 | 1 | 502 | 1 |
| 1003 | 2 | 501 | 1 |
Now each piece of information lives in exactly one place. Maria's email is stored once, in the `customers` table. The price of a Wireless Mouse is stored once, in the `products` table. If either changes, you update a single row, and every order automatically reflects the correct, current information.
Notice how the tables connect to each other: `orders.customer_id` refers back to `customers.customer_id`, and `orders.product_id` refers back to `products.product_id`. These connecting columns are called **keys** — specifically, `customer_id` is a *primary key* in the `customers` table and a *foreign key* when it appears in `orders`.
## So Where Do Joins Come In?
This design solves our redundancy problem, but it creates a new challenge: the information you actually want to see — "Maria Chen ordered 2 Wireless Mice for $24.99 each" — no longer lives in a single table. The customer's name is in one table, the product name and price are in another, and the order details are in a third.
A **join** is the SQL tool that reassembles this scattered information at query time. It says, in effect: "For each row in `orders`, go find the matching row in `customers` (where the `customer_id` values line up), and the matching row in `products` (where the `product_id` values line up), and stitch them together into one combined result."
For example, this query reconnects all three tables to answer a real business question — "What did each customer order, and how much did it cost?":
```sql
SELECT
c.customer_name,
p.product_name,
p.product_price,
o.quantity
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN products AS p ON o.product_id = p.product_id;
```
This is the fundamental trade-off joins exist to manage: **normalization keeps your data clean, accurate, and efficient to store; joins let you bring it back together whenever you need the full picture.** Every join type you'll learn in this module — INNER, LEFT, and RIGHT — is really just a different strategy for handling that reassembly, especially when matches don't line up perfectly on both sides.
### Diagram: Normalization splits one redundant flat table into separate related tables (customers, products, orders) linked by keys, which joins later reassemble.
```mermaid
erDiagram
ORDERS_FLAT {
int order_id
string customer_name
string customer_email
string product_name
float product_price
int quantity
}
CUSTOMERS {
int customer_id PK
string customer_name
string customer_email
}
PRODUCTS {
int product_id PK
string product_name
float product_price
}
ORDERS {
int order_id PK
int customer_id FK
int product_id FK
int quantity
}
ORDERS_FLAT ||--o{ CUSTOMERS : "split into"
ORDERS_FLAT ||--o{ PRODUCTS : "split into"
ORDERS_FLAT ||--o{ ORDERS : "split into"
CUSTOMERS ||--o{ ORDERS : "referenced by customer_id"
PRODUCTS ||--o{ ORDERS : "referenced by product_id"
```
### INNER JOIN vs. LEFT JOIN vs. RIGHT JOIN
# INNER JOIN vs. LEFT JOIN vs. RIGHT JOIN
When you combine two tables, the join type you choose determines which rows survive the match — and which get dropped. Picking the wrong one is one of the most common sources of "missing data" bugs in SQL, so it's worth understanding exactly what each join does at the row level, not just memorizing syntax.
Let's use two small tables to make this concrete.
**customers**
| customer_id | name |
|-------------|---------|
| 1 | Ana |
| 2 | Ben |
| 3 | Carla |
**orders**
| order_id | customer_id | amount |
|----------|-------------|--------|
| 101 | 1 | 50 |
| 102 | 1 | 20 |
| 103 | 2 | 75 |
Notice Carla (customer_id 3) has never placed an order, and there's no order with a customer_id of 4. This mismatch is exactly what exposes the difference between join types.
## INNER JOIN: only the matches
An `INNER JOIN` returns rows only when there's a match in *both* tables. If a row in one table has no corresponding row in the other, it's excluded entirely.
```sql
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
```
Result:
| name | order_id | amount |
|------|----------|--------|
| Ana | 101 | 50 |
| Ana | 102 | 20 |
| Ben | 103 | 75 |
Carla disappears completely — she has no orders, so there's nothing to match. **Use INNER JOIN when you only care about records that exist in both tables**, e.g., "show me orders along with the customer who placed them." If a customer has no orders, that question doesn't apply to them anyway.
## LEFT JOIN: keep everything from the left, matched or not
A `LEFT JOIN` (or `LEFT OUTER JOIN`) keeps *every* row from the left table, filling in `NULL` for columns from the right table when there's no match.
```sql
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
```
Result:
| name | order_id | amount |
|-------|----------|--------|
| Ana | 101 | 50 |
| Ana | 102 | 20 |
| Ben | 103 | 75 |
| Carla | NULL | NULL |
Now Carla shows up with `NULL`s. **Use LEFT JOIN when the business question is about the table on the left, regardless of whether a match exists** — for example, "list all customers and how much each has spent, including customers who haven't ordered yet." This is the join you reach for most often when auditing for missing activity, like finding customers with zero orders (`WHERE o.order_id IS NULL`).
## RIGHT JOIN: keep everything from the right, matched or not
A `RIGHT JOIN` is the mirror image: it keeps every row from the right table, filling in `NULL`s for the left table when unmatched.
```sql
SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
```
With this data, the result looks identical to the INNER JOIN, because every order does have a matching customer. RIGHT JOIN's usefulness shows up when the *right* table has rows without a match on the left — for instance, an `orders` table containing a stray order with `customer_id = 4` that doesn't exist in `customers`. That row would appear with `name = NULL`, flagging a data integrity problem.
In practice, RIGHT JOIN is used far less often than LEFT JOIN, simply because most people write the table they care most about first and use LEFT JOIN — a RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping table order. Some teams ban RIGHT JOIN from their style guide entirely, just to keep queries easier to read at a glance.
## Choosing the right join: a quick mental checklist
- **"Only show records that exist in both"** → INNER JOIN
- **"Show all of Table A, even if there's nothing matching in Table B"** → LEFT JOIN (Table A first)
- **"Show all of Table B, even if there's nothing matching in Table A"** → RIGHT JOIN, or just LEFT JOIN with tables swapped
- **"Find things in A with no match in B"** → LEFT JOIN + `WHERE B.key IS NULL`
Before writing a join, say the business question out loud and ask: *"Do I want to lose rows that don't match, or keep them?"* That single question resolves nearly every INNER vs. LEFT vs. RIGHT decision you'll face.
### Diagram: Each join type includes a different set of customer-order row combinations based on whether matches exist in the left (customers) and/or right (orders) table.
```mermaid
flowchart LR
subgraph Data["Source Tables"]
direction LR
C["customers\nAna(1), Ben(2), Carla(3)"]
O["orders\n101→1, 102→1, 103→2"]
end
C --> INNER
O --> INNER
C --> LEFTJ
O --> LEFTJ
C --> RIGHTJ
O --> RIGHTJ
subgraph INNER["INNER JOIN"]
I1["Ana - 101 - 50"]
I2["Ana - 102 - 20"]
I3["Ben - 103 - 75"]
I4["❌ Carla dropped (no order match)"]
end
subgraph LEFTJ["LEFT JOIN"]
L1["Ana - 101 - 50"]
L2["Ana - 102 - 20"]
L3["Ben - 103 - 75"]
L4["Carla - NULL - NULL (kept, no match)"]
end
subgraph RIGHTJ["RIGHT JOIN"]
R1["Ana - 101 - 50"]
R2["Ana - 102 - 20"]
R3["Ben - 103 - 75"]
R4["❌ Carla dropped (not referenced by any order)"]
R5["(any order with unmatched customer_id would show NULL name)"]
end
INNER -->|"Only rows matched in BOTH tables"| Summary1["Use when: record must exist on both sides"]
LEFTJ -->|"ALL left rows, matched or not"| Summary2["Use when: keep every left-side record, even unmatched"]
RIGHTJ -->|"ALL right rows, matched or not"| Summary3["Use when: keep every right-side record, even unmatched"]
```
### Aliasing Tables and Joining Three or More Tables
# Aliasing Tables and Joining Three or More Tables
As soon as you join more than two tables, your queries get harder to read and easier to break. Two habits will save you here: **using table aliases consistently** and **building multi-table joins one step at a time**. Let's cover both.
## Why Alias Your Tables
A table alias is a short nickname you assign to a table for the rest of the query. Instead of typing `customers.customer_id` every time, you write `c.customer_id`. This matters for three practical reasons:
1. **Readability** — Short aliases make long `SELECT` and `ON` clauses easier to scan.
2. **Disambiguation** — When two tables share a column name (like `id` or `created_at`), the database needs to know which table you mean. Aliases make this explicit.
3. **Self-joins** — If you ever join a table to itself (e.g., comparing employees to their managers in the same `employees` table), aliases are *required* because you can't reference the same table name twice.
Aliases are declared right after the table name, with or without the optional `AS` keyword:
```sql
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
```
A good convention: use the first letter (or first few letters) of the table name, and keep it consistent throughout the query. Avoid single letters like `a`, `b`, `c` for unrelated tables — six months from now, you won't remember what `b` stood for. `ord`, `cust`, `prod` are easier to maintain than `a`, `b`, `c`, especially once you're joining four or five tables.
## Joining Three or More Tables
The mechanics don't change when you add a third table — you just add another `JOIN` clause with its own `ON` condition. The key mental model: **join two tables first, then treat that combined result as the thing you're joining the next table to.**
### Worked Example
Suppose you're analyzing a sales database with three tables:
- `orders` (order_id, customer_id, order_date)
- `customers` (customer_id, customer_name, region)
- `order_items` (order_item_id, order_id, product_id, quantity)
- `products` (product_id, product_name, category)
**Business question:** "For every order placed in the West region, list the customer name, order date, product name, and quantity."
This requires four tables. Build it incrementally:
```sql
SELECT
c.customer_name,
o.order_date,
p.product_name,
oi.quantity
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.customer_id
JOIN order_items AS oi
ON o.order_id = oi.order_id
JOIN products AS p
ON oi.product_id = p.product_id
WHERE c.region = 'West';
```
Notice the pattern: each `JOIN` connects a *new* table to a table already in the query — not necessarily to the first one. `order_items` joins to `orders` (via `order_id`), and `products` joins to `order_items` (via `product_id`), not to `orders` or `customers` directly, because that's where the shared key actually lives.
### Building It Step by Step
When writing (or debugging) a query like this, don't try to write all four tables at once. Instead:
1. Start with `SELECT * FROM orders o JOIN customers c ON ...` and run it. Confirm the row count and content look right.
2. Add `JOIN order_items oi ON o.order_id = oi.order_id` and run again. Check whether the row count jumped — it should, since one order can have many items. That's expected here.
3. Add `JOIN products p ON oi.product_id = p.product_id` and run again.
4. Only once all joins are confirmed do you narrow the `SELECT` list down to the columns you actually need and add the `WHERE` filter.
This incremental approach catches problems early. If row counts explode unexpectedly at step 2, you know the issue is in the `orders`-to-`order_items` join, not somewhere buried in a four-table query.
## A Note on Mixing Join Types
You aren't limited to one join type per query. It's common to write something like:
```sql
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
```
Here, a `LEFT JOIN` preserves orders even if customer data is missing, while an `INNER JOIN` to `order_items` assumes every order must have at least one item. Choose each join type based on what that specific relationship guarantees — don't default to `LEFT JOIN` everywhere "just to be safe," since that can silently reintroduce rows with missing data you meant to exclude.
### Diagram: Joining three tables step by step: combine orders and customers first using aliases, then join that result to order_items — each step narrowing/expanding columns via a clear ON condition.
```mermaid
flowchart LR
subgraph T1["orders (alias: o)"]
A1["order_id\ncustomer_id\norder_date"]
end
subgraph T2["customers (alias: c)"]
A2["customer_id\ncustomer_name\nregion"]
end
subgraph T3["order_items (alias: oi)"]
A3["order_id\nproduct_id\nquantity"]
end
T1 -- "JOIN ON o.customer_id = c.customer_id" --> J1["Step 1 Result:\norders + customers"]
T2 -- "JOIN ON o.customer_id = c.customer_id" --> J1
J1 -- "treat as one combined table" --> J2["Step 2:\nJOIN order_items oi\nON o.order_id = oi.order_id"]
T3 -- "JOIN ON o.order_id = oi.order_id" --> J2
J2 --> FINAL["Final Result:\norder_id, order_date,\ncustomer_name, region,\nproduct_id, quantity"]
style T1 fill:#dbeafe,stroke:#1e40af
style T2 fill:#dcfce7,stroke:#166534
style T3 fill:#fef9c3,stroke:#92400e
style J1 fill:#e0e7ff,stroke:#3730a3
style J2 fill:#fde68a,stroke:#92400e
style FINAL fill:#fca5a5,stroke:#991b1b
```
### Debugging Joins: Duplicates, Missing Rows, and Fan-Out Traps
## Debugging Joins: Duplicates, Missing Rows, and Fan-Out Traps
Once you can write a JOIN that runs without errors, the next skill is learning to trust its output. Joins fail silently far more often than they throw errors — the query executes fine, but the numbers are wrong. This section covers the three most common failure patterns and how to catch them before they reach a report.
### Trap 1: The Fan-Out (Row Multiplication)
A "fan-out" happens when a join matches one row on the left to *multiple* rows on the right, silently inflating your row count — and often your totals.
**Example scenario:** You want total revenue per customer, joining `customers` to `orders`.
```sql
SELECT c.customer_id, c.name, SUM(o.order_total) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
```
This looks correct — and usually is, because one customer legitimately has many orders, and `SUM` correctly adds them up. The fan-out becomes a *bug* when you add a second join that introduces multiplicity you didn't intend:
```sql
SELECT c.customer_id, c.name, SUM(o.order_total) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name;
```
Now each order row is duplicated once per line item. If an order has 4 items, `order_total` gets summed 4 times, wildly overstating revenue. The join itself isn't wrong — `order_items` correctly has one row per item — but it doesn't match your intended grain (one row per order).
**How to catch it:**
- Before trusting a SUM or COUNT after a join, run `SELECT COUNT(*)` on the joined result and compare it to the row count of your "base" table alone. A jump from 10,000 orders to 34,000 joined rows is your signal.
- Ask: "What does one row in this result represent?" If you can't answer clearly, the grain has shifted.
- Fix by aggregating the "many" side first in a subquery/CTE before joining, e.g., summing `order_items` to one row per order before joining to `customers`.
### Trap 2: Missing Rows from INNER JOIN
INNER JOIN silently drops any row without a match. This is often invisible until someone asks, "Why does this report only show 900 of our 1,200 customers?"
**Example:** A marketing report joins `customers` to `orders` to find total spend, using INNER JOIN. Customers who haven't ordered yet vanish entirely — not shown as zero, just absent. If the business question is "spend per customer, including new customers with $0," INNER JOIN gives a wrong (and misleadingly clean) answer.
**Fix:** Use LEFT JOIN from the table that must be fully preserved, and wrap the aggregate in `COALESCE` to convert nulls to zero:
```sql
SELECT c.customer_id, c.name, COALESCE(SUM(o.order_total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
```
**How to catch it:** Compare `COUNT(DISTINCT c.customer_id)` before and after the join. If it drops, rows are being excluded — decide if that's intentional.
### Trap 3: Duplicate Matches from Loose Join Conditions
Joining on a key that isn't actually unique in one of the tables — even unintentionally — produces duplicate rows that look plausible but are wrong. This happens often with date-based joins (e.g., joining on `order_date = signup_date` instead of an ID) or when a "lookup" table secretly has duplicate keys due to a data entry error.
**How to catch it:**
- Check uniqueness of your join key first: `SELECT key_column, COUNT(*) FROM table GROUP BY key_column HAVING COUNT(*) > 1;`
- If duplicates exist and are unintended, resolve them (dedupe, pick the latest record, or aggregate) *before* joining, not after.
### A General Debugging Habit
Before trusting any joined aggregate, run three quick checks: row count before vs. after the join, uniqueness of each join key, and a manual spot-check on one known record (e.g., "Customer #4521 should have 3 orders totaling $412 — does the query show that?"). This three-step habit catches the vast majority of join errors before they become bad business decisions.
### Diagram: A flowchart showing how a fan-out join silently inflates row counts and revenue totals, and how to catch and fix it by checking row counts and pre-aggregating before joining.
```mermaid
flowchart TD
A["customers JOIN orders\n10,000 base order rows"] -->|"looks correct: SUM(order_total) per customer"| B["Add JOIN order_items\n(one row per line item)"]
B --> C["Result: 34,000 joined rows\n(orders duplicated per item)"]
C --> D["SUM(order_total) now adds\neach order 1x per item\n= inflated revenue"]
D --> E{"Debug Check"}
E -->|"COUNT(*) on joined result\nvs base table row count"| F["10,000 vs 34,000\n= red flag: grain has shifted"]
E -->|"Ask: what does one row\nrepresent now?"| G["Answer unclear\n= fan-out confirmed"]
F --> H["Fix: Aggregate the 'many' side\nfirst in a CTE/subquery"]
G --> H
H --> I["Join pre-aggregated orders\nto customers\n= correct revenue per customer"]
style C fill:#f8d7da,stroke:#c0392b
style D fill:#f8d7da,stroke:#c0392b
style F fill:#f8d7da,stroke:#c0392b
style I fill:#d4edda,stroke:#2e7d32
```
#### Module check
1. Using the customers and orders tables from the module (Carla has never placed an order), what happens to Carla's row if you run a LEFT JOIN from customers to orders?
- It causes an error because Carla has no matching orders
- Carla is silently dropped from the results
- Carla still appears, with NULL values for the order columns
- Carla appears once for every row in the orders table
2. True or False: One of the main reasons databases split data into multiple tables instead of one giant flat table is to avoid repeating the same information (like a customer's name and email) on every related row.
- True
- False
3. When a JOIN matches one row on the left table to multiple rows on the right table, silently inflating row counts and totals, this problem is called a ____.
## Module 4: Module 4: Building and Modifying Your Own Database
### Creating Tables: Data Types, Primary Keys, and Constraints
# Creating Tables: Data Types, Primary Keys, and Constraints
Before you can insert a single row of data, you need to design the container that will hold it. A well-designed table saves you from data corruption, confusing bugs, and painful cleanup work later. This section walks through the `CREATE TABLE` statement piece by piece, using a real example: a table to track customer orders for a small online store.
## Choosing Data Types
Every column needs a data type that matches the kind of value it will store. Picking the right type isn't just pedantic—it prevents invalid data from ever entering your table and helps the database store and search data efficiently.
Common types you'll use constantly:
- **`INTEGER`** — whole numbers (order counts, quantities, IDs)
- **`DECIMAL(precision, scale)`** — exact numbers with decimals, essential for money (e.g., `DECIMAL(10,2)` allows values like `1234.56`)
- **`VARCHAR(n)`** — variable-length text with a maximum length (e.g., `VARCHAR(100)` for a customer name)
- **`TEXT`** — long-form text with no practical length limit (e.g., order notes)
- **`DATE`** / **`TIMESTAMP`** — calendar dates or full date-and-time values
- **`BOOLEAN`** — true/false flags (e.g., `is_paid`)
A common beginner mistake is using `FLOAT` or `DOUBLE` for money. These types can introduce tiny rounding errors (e.g., storing `19.99` might actually save as `19.990000001`). Always use `DECIMAL` for currency.
## Defining Primary Keys
A **primary key** uniquely identifies each row in a table. No two rows can share the same primary key value, and it can never be `NULL`. Most tables should have a single-column primary key, often an auto-incrementing integer, so the database can generate unique IDs for you without your application having to track them.
## Adding Constraints
Constraints are rules the database enforces automatically, so bad data never gets in, no matter how many places your application inserts from:
- **`NOT NULL`** — the column must always have a value
- **`UNIQUE`** — no duplicate values allowed (e.g., email addresses)
- **`CHECK`** — restricts values to a valid range or set (e.g., quantity must be positive)
- **`DEFAULT`** — automatically fills in a value when none is provided
- **`FOREIGN KEY`** — ensures a value in one table matches a real value in another table, preserving relationships
## Worked Example
Here's a complete table definition for storing orders, with each design decision reflecting the concepts above:
```sql
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_email VARCHAR(255) NOT NULL,
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
customer_id INTEGER,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
```
Walking through this line by line:
- `order_id` is the primary key, auto-generated so you never have to invent unique IDs yourself.
- `customer_email` is required (`NOT NULL`) — an order without a contact email is not valid.
- `order_date` defaults to today's date if the application doesn't supply one, saving repetitive application code.
- `quantity` uses a `CHECK` constraint so the database physically rejects an order for zero or negative items, catching bugs at the source rather than downstream.
- `unit_price` uses `DECIMAL` (never `FLOAT`) and a `CHECK` to prevent negative prices.
- `status` defaults to `'pending'`, giving every new order a sensible starting state.
- `customer_id` is a **foreign key** referencing a `customers` table, guaranteeing you can never record an order for a customer that doesn't exist.
## Why This Matters in Practice
If you skip constraints, your database will happily store an order with `quantity = -5` or a blank email—and you'll only discover the problem weeks later when a report breaks or an email fails to send. Constraints push validation as close to the data as possible, which is the most reliable place to enforce it, since it can't be bypassed by a forgotten check in your application code. As you move into the next sections on inserting and updating data, keep this table in mind—you'll see exactly how these constraints protect you when a query tries to violate them.
### Diagram: Anatomy of a CREATE TABLE statement: each column needs a data type, one column becomes the primary key, and constraints enforce data quality rules.
```mermaid
flowchart TD
A["CREATE TABLE orders"] --> B["Choose Data Types"]
A --> C["Define Primary Key"]
A --> D["Add Constraints"]
B --> B1["INTEGER — whole numbers\ne.g., quantity"]
B --> B2["DECIMAL(10,2) — exact money values\ne.g., order_total (never FLOAT!)"]
B --> B3["VARCHAR(100) — short text\ne.g., customer_name"]
B --> B4["TEXT — long text\ne.g., order_notes"]
B --> B5["DATE / TIMESTAMP\ne.g., order_date"]
B --> B6["BOOLEAN\ne.g., is_paid"]
C --> C1["order_id INTEGER"]
C1 --> C2["Auto-incrementing"]
C1 --> C3["Unique per row"]
C1 --> C4["Never NULL"]
D --> D1["NOT NULL\nvalue must be present"]
D --> D2["UNIQUE\nno duplicate values"]
D --> D3["CHECK\nvalue must meet a rule"]
D --> D4["DEFAULT\nfallback value if none given"]
B --> E["Well-Designed Table"]
C --> E
D --> E
E --> F["Prevents corruption,\nbugs, and cleanup later"]
```
### Modifying Data: INSERT, UPDATE, and DELETE Safely
# Modifying Data: INSERT, UPDATE, and DELETE Safely
Once your tables exist, the next skill is changing the data inside them without breaking anything. `INSERT`, `UPDATE`, and `DELETE` are simple to write but dangerous to run carelessly — an `UPDATE` or `DELETE` without a proper `WHERE` clause can silently rewrite or wipe out an entire table. This section walks through each statement and the habits that keep you safe.
## INSERT: Adding New Rows
Always list the columns explicitly rather than relying on column order. This protects your code if the table structure changes later.
```sql
INSERT INTO customers (first_name, last_name, email, signup_date)
VALUES ('Maria', 'Chen', 'maria.chen@example.com', '2024-03-01');
```
To insert multiple rows in one statement (faster and more atomic than separate inserts):
```sql
INSERT INTO customers (first_name, last_name, email, signup_date)
VALUES
('Sam', 'Okoro', 'sam.okoro@example.com', '2024-03-02'),
('Priya', 'Nair', 'priya.nair@example.com', '2024-03-02');
```
If your table has a `NOT NULL` or `UNIQUE` constraint, the database will reject bad data automatically — treat that rejection as a helpful guardrail, not an obstacle to work around.
## UPDATE: Changing Existing Rows
The golden rule: **write and test the `WHERE` clause before you trust the `UPDATE`.**
A safe workflow:
1. Write the filter as a `SELECT` first, and check the results.
2. Convert the `SELECT` to an `UPDATE` once you're confident it targets the right rows.
```sql
-- Step 1: Check what you're about to change
SELECT id, email, status
FROM customers
WHERE signup_date < '2023-01-01' AND status = 'active';
-- Step 2: Run the actual update
UPDATE customers
SET status = 'inactive'
WHERE signup_date < '2023-01-01' AND status = 'active';
```
Without the `WHERE` clause, that `UPDATE` would mark *every* customer inactive. This is the single most common cause of accidental data loss for new SQL users.
If you need to update multiple columns, separate them with commas:
```sql
UPDATE products
SET price = price * 1.10, last_updated = CURRENT_DATE
WHERE category = 'electronics';
```
Notice `price = price * 1.10` — you can reference a column's current value on the right-hand side of the assignment.
## DELETE: Removing Rows
`DELETE` follows the same logic as `UPDATE` and carries the same risk.
```sql
-- Preview first
SELECT * FROM orders WHERE status = 'cancelled' AND order_date < '2022-01-01';
-- Then delete
DELETE FROM orders
WHERE status = 'cancelled' AND order_date < '2022-01-01';
```
Running `DELETE FROM orders;` with no `WHERE` clause deletes every row in the table. The table still exists, but it's now empty — and unlike a spreadsheet, there's no undo button once the change is committed.
## Practical Safety Habits
- **Preview with SELECT first.** Convert only after confirming the row count and contents look right.
- **Check the row count returned.** Most database clients report how many rows were affected — if you expected 12 and see 12,000, stop and investigate before it's too late.
- **Use transactions for anything risky.** Wrapping changes in `BEGIN` / `COMMIT` lets you inspect results and `ROLLBACK` if something looks wrong:
```sql
BEGIN;
UPDATE customers
SET status = 'inactive'
WHERE signup_date < '2023-01-01' AND status = 'active';
-- Check the result
SELECT status, COUNT(*) FROM customers GROUP BY status;
-- If it looks correct:
COMMIT;
-- If not:
ROLLBACK;
```
- **Avoid relying on `LIMIT` to make DELETE "safer"** unless your database guarantees which rows it applies to — it's better to make the `WHERE` clause precise.
- **Back up before bulk changes** in any real production environment, even when you're confident in your query.
Mastering `INSERT`, `UPDATE`, and `DELETE` is really about mastering the discipline around them: know exactly which rows you're touching before you touch them, and use transactions as your safety net when the stakes are high. The next section builds on this by showing how transactions protect you across multi-step changes, not just single statements.
### Diagram: The safe workflow for modifying data: always preview changes with a SELECT before committing to UPDATE or DELETE, so a missing or wrong WHERE clause can't silently damage the whole table.
```mermaid
flowchart TD
A[Want to change data] --> B{Which operation?}
B -->|INSERT| C[List columns explicitly]
C --> D[Use single or multi-row VALUES]
D --> E[Let NOT NULL / UNIQUE constraints guard bad data]
B -->|UPDATE or DELETE| F[Write the WHERE clause first]
F --> G[Run it as a SELECT to preview affected rows]
G --> H{Rows look correct?}
H -->|No| F
H -->|Yes| I[Convert SELECT to UPDATE or DELETE]
I --> J[Run inside a transaction if possible]
J --> K[Verify results, then COMMIT]
E --> L[Data safely modified]
K --> L
style F fill:#ffe6e6
style G fill:#fff3cd
style I fill:#d4edda
style K fill:#d4edda
```
### Protecting Data with Transactions (COMMIT and ROLLBACK)
# Protecting Data with Transactions (COMMIT and ROLLBACK)
When you insert, update, or delete data, you're rarely making just one change. Real-world tasks often involve several related steps — transfer money between two accounts, cancel an order and restock inventory, move an employee to a new department and update their salary. If your database applies some of those steps but not others (because of a crash, a bug, or an error midway through), your data ends up in a broken, inconsistent state. **Transactions** exist to prevent exactly this problem.
## What a Transaction Is
A transaction is a group of one or more SQL statements that the database treats as a single, indivisible unit of work. Either **all** the statements succeed and their effects are saved permanently (`COMMIT`), or **none** of them take effect and the database is restored to how it looked before you started (`ROLLBACK`). This "all-or-nothing" behavior is often summarized by the acronym **ACID** (Atomicity, Consistency, Isolation, Durability) — for this section, focus on the *Atomicity* piece: partial success is not allowed.
## The Basic Syntax
Most SQL databases (PostgreSQL, MySQL with InnoDB, SQL Server, SQLite) support this pattern:
```sql
BEGIN; -- or START TRANSACTION;
-- your statements go here
COMMIT; -- save everything permanently
-- or
ROLLBACK; -- undo everything back to BEGIN
```
Until you `COMMIT`, your changes are typically visible only within your own session — other users querying the table won't see them yet, and you can still back out.
## Worked Example: A Bank Transfer
Imagine two accounts in a table called `accounts`:
| id | owner | balance |
|----|-------|---------|
| 1 | Alice | 500 |
| 2 | Bob | 100 |
To transfer $200 from Alice to Bob, you need two updates. If only the first one runs, Alice loses money that Bob never receives — a serious data integrity bug. Wrapping both in a transaction prevents that:
```sql
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;
UPDATE accounts
SET balance = balance + 200
WHERE id = 2;
COMMIT;
```
If both `UPDATE` statements succeed, `COMMIT` makes the new balances (Alice: 300, Bob: 300) permanent. If something goes wrong after the first `UPDATE` — say, the second one fails because of a typo in the column name, or the application crashes — you can issue:
```sql
ROLLBACK;
```
and the database reverts Alice's balance back to 500, as if neither statement had run. No money vanishes.
## A Practical Habit: Check Before You Commit
Because `UPDATE` and `DELETE` can affect more rows than you intend, it's good practice to inspect the results before committing:
```sql
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;
SELECT * FROM accounts WHERE id IN (1, 2);
-- Review the output. Does it look right?
UPDATE accounts
SET balance = balance + 200
WHERE id = 2;
SELECT * FROM accounts WHERE id IN (1, 2);
-- Confirm both balances are correct
COMMIT;
```
If the intermediate `SELECT` shows something unexpected — wrong row count, wrong value — you can `ROLLBACK` instead of `COMMIT` and try again with no harm done.
## Common Pitfalls to Avoid
- **Forgetting to commit or rollback.** Leaving a transaction open can hold locks on rows, blocking other users from updating them. Always finish what you start.
- **Assuming autocommit is off.** Many tools (like default settings in MySQL Workbench or psql) run every statement in its own automatic transaction unless you explicitly `BEGIN`. Know your client's default behavior.
- **Running DDL inside transactions.** Statements like `CREATE TABLE` or `ALTER TABLE` are transactional in some databases (PostgreSQL) but not others (MySQL auto-commits them immediately). Don't assume you can roll back a structural change everywhere.
- **Using transactions as a substitute for testing.** A transaction protects against partial failure, but it won't stop a logically wrong `UPDATE ... WHERE` clause from committing bad data if you don't check it first.
## Key Takeaway
Whenever a task requires more than one `INSERT`, `UPDATE`, or `DELETE` that must succeed or fail together, wrap it in `BEGIN ... COMMIT`, and keep `ROLLBACK` ready as your safety net. This single habit will save you from some of the most damaging and hard-to-fix mistakes in database work.
### Diagram: A transaction treats multiple SQL statements as one all-or-nothing unit: if every step succeeds it's saved with COMMIT, but if any step fails, ROLLBACK undoes all changes back to the original state.
```mermaid
sequenceDiagram
participant App as Application
participant DB as Database
App->>DB: BEGIN;
Note over DB: Transaction started
(changes not yet permanent)
App->>DB: UPDATE accounts SET balance = balance - 200 WHERE owner = 'Alice';
Note over DB: Alice: 500 → 300 (pending)
App->>DB: UPDATE accounts SET balance = balance + 200 WHERE owner = 'Bob';
Note over DB: Bob: 100 → 300 (pending)
alt All statements succeeded
App->>DB: COMMIT;
Note over DB: Changes saved permanently
Alice = 300, Bob = 300
else Error or crash mid-transaction
App->>DB: ROLLBACK;
Note over DB: All changes undone
Alice = 500, Bob = 100
(back to original state)
end
```
### Organizing Complex Queries with CTEs and Subqueries
# Organizing Complex Queries with CTEs and Subqueries
As your database grows and your questions become more sophisticated, you'll often find that a single `SELECT` statement isn't enough to express what you want. You might need to calculate an intermediate result before filtering on it, or break a tangled piece of logic into readable steps. This is where subqueries and Common Table Expressions (CTEs) come in.
## Subqueries: Queries Inside Queries
A subquery is simply a `SELECT` statement nested inside another query. It runs first (conceptually), and its result is used by the outer query.
**Example scenario:** Suppose you have an `orders` table and want to find every customer whose total order value is above the average order value across all customers.
```sql
SELECT customer_id, order_total
FROM orders
WHERE order_total > (
SELECT AVG(order_total)
FROM orders
);
```
Here, the inner query `(SELECT AVG(order_total) FROM orders)` calculates a single number, and the outer query uses it as a comparison value. This is called a **scalar subquery** because it returns exactly one value.
Subqueries can also return a list of values, used with `IN`:
```sql
SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date >= '2024-01-01'
);
```
This finds all customers who placed at least one order this year, without needing a `JOIN`.
**A caution:** subqueries nested several levels deep become hard to read and debug. If you find yourself indenting three or four layers of `SELECT` statements, it's usually a sign to switch to a CTE.
## CTEs: Naming Your Intermediate Steps
A Common Table Expression, written with the `WITH` keyword, lets you define a temporary named result set that you can reference later in the same query. Think of it as giving a subquery a label and pulling it up to the top so the logic reads top-to-bottom instead of inside-out.
**Basic syntax:**
```sql
WITH high_value_customers AS (
SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 1000
)
SELECT c.customer_name, h.total_spent
FROM high_value_customers h
JOIN customers c ON c.customer_id = h.customer_id
ORDER BY h.total_spent DESC;
```
Compare this to writing the same logic as a nested subquery in the `FROM` clause — it would work, but the CTE version clearly separates "step 1: calculate spending per customer" from "step 2: join that to customer names." Each piece can be understood on its own.
## Chaining Multiple CTEs
One of the biggest advantages of CTEs is that you can chain several together, each building on the last, which mirrors how you'd naturally break down a problem.
```sql
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS sales_month,
SUM(order_total) AS monthly_total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
),
avg_monthly_sales AS (
SELECT AVG(monthly_total) AS avg_total
FROM monthly_sales
)
SELECT m.sales_month, m.monthly_total
FROM monthly_sales m, avg_monthly_sales a
WHERE m.monthly_total > a.avg_total;
```
This finds months where sales exceeded the average monthly total. Notice how each CTE has a single, clear responsibility — `monthly_sales` aggregates, `avg_monthly_sales` summarizes further, and the final `SELECT` filters. If a result looks wrong, you can run each CTE independently (just replace the final `SELECT` temporarily) to check its output before trusting the whole chain.
## When to Choose Which
- **Use a subquery** for a quick, one-off calculation embedded directly where it's needed — especially scalar comparisons or `IN` filters.
- **Use a CTE** when the logic has multiple steps, when you'll reference the same intermediate result more than once, or when naming the steps would make the query easier for your future self (or a teammate) to understand.
- Both are ultimately processed by the database in similar ways, so the choice is mostly about **readability and maintainability**, not performance in most everyday cases.
As a habit, whenever a query starts to feel like a run-on sentence, try rewriting it as a short sequence of named CTEs. Clear structure now saves debugging time later.
### Diagram: Subqueries nest logic inside-out (evaluated innermost-first), while CTEs pull that same logic into named, top-to-bottom steps that are easier to read, debug, and reuse.
```mermaid
flowchart TB
subgraph SUB["Subquery Approach: Inside-Out"]
direction TB
S1["Outer SELECT customer_name FROM customers WHERE customer_id IN (...)"] --> S2["Inner SELECT customer_id FROM orders WHERE order_date >= 2024-01-01"]
S2 -.evaluated first, feeds result into.-> S1
S3["⚠️ Nest 3-4 levels deep → hard to read/debug"]
end
subgraph CTE["CTE Approach: Top-to-Bottom"]
direction TB
C1["WITH high_value_customers AS (\nSELECT customer_id, AVG(order_total)\nFROM orders GROUP BY customer_id\nHAVING AVG(order_total) > overall_avg\n)"] --> C2["Named result set: high_value_customers"]
C2 --> C3["Final SELECT * FROM high_value_customers"]
C4["✅ Reads like a story: define step, then use it"]
end
SUB ==refactor when nesting gets messy==> CTE
```
#### Module check
1. Running an UPDATE or DELETE statement without a WHERE clause is safe as long as the table has a primary key.
- True
- False
2. In the context of database transactions, what is the difference between COMMIT and ROLLBACK?
- COMMIT saves all changes in the transaction permanently; ROLLBACK undoes all changes made since the transaction began
- COMMIT undoes all changes in the transaction; ROLLBACK saves them permanently
- COMMIT and ROLLBACK both permanently save changes, but ROLLBACK is faster
- COMMIT only works on INSERT statements; ROLLBACK only works on DELETE statements
3. The acronym CTE, used to organize complex SQL queries into readable steps, stands for ____.
## Module 5: Module 5: Capstone – Real-World Data Analysis Project
### From Question to Query: Planning Your Analysis
# From Question to Query: Planning Your Analysis
The most common mistake analysts make isn't writing bad SQL — it's writing *correct* SQL that answers the wrong question. Before you touch a keyboard, you need a plan that translates a vague business ask into a precise, testable query strategy. This section walks through that translation process using a realistic scenario you'll carry through the rest of the capstone.
## The Scenario
Imagine a stakeholder from the marketing team asks:
> "Which customers should we target for a re-engagement campaign?"
This is a real business question, but it's not yet an analysis. It's missing definitions, scope, and success criteria. Your job as the analyst is to interrogate the question before writing a single `SELECT`.
## Step 1: Clarify the Question in Writing
Turn the vague ask into a specific, answerable statement. Ask yourself:
- **Who** counts as a "customer"? (Registered users? Paying customers only?)
- **What does "re-engagement" mean?** Usually it implies customers who *used to* be active but have gone quiet.
- **What's the time window?** "Quiet" needs a number attached — 60 days? 90 days?
- **What does success look like?** A list of customer IDs? Segmented by value? Ranked by likelihood to return?
After clarifying (through a quick conversation or reasonable assumptions you document), you might restate the question as:
> "List customers who placed at least 3 orders in the past 12 months, but have made **no purchase in the last 90 days**, along with their total historical spend, so marketing can prioritize high-value lapsed customers."
This restated version is now something you can actually query.
## Step 2: Identify the Data You Need
Before writing SQL, sketch out — on paper or in comments — what tables and columns are involved:
- `orders` (order_id, customer_id, order_date, order_total)
- `customers` (customer_id, name, signup_date)
Ask: do I need every table connected to "orders," or just these two? Resist the urge to join tables "just in case" — this is the seed of the performance problems you'll fix later in this module.
## Step 3: Break the Logic into Stages
Rather than writing one dense query, decompose the problem into logical steps, which map naturally to CTEs:
1. **Filter**: orders from the last 12 months.
2. **Aggregate**: per customer, count orders and sum spend; find their most recent order date.
3. **Filter again**: keep only customers with 3+ orders AND last order more than 90 days ago.
4. **Present**: order by total spend descending.
## Step 4: Draft the Query Plan (Before the SQL)
Write this out in plain language or pseudocode first:
```
WITH recent_orders AS (
-- orders from the last 12 months
),
customer_summary AS (
-- per customer: order count, total spend, last order date
)
SELECT ...
FROM customer_summary
WHERE order_count >= 3
AND last_order_date < (today - 90 days)
ORDER BY total_spend DESC;
```
Only now do you translate this into actual SQL:
```sql
WITH recent_orders AS (
SELECT customer_id, order_date, order_total
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
),
customer_summary AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(order_total) AS total_spend,
MAX(order_date) AS last_order_date
FROM recent_orders
GROUP BY customer_id
)
SELECT
c.customer_id,
c.name,
cs.order_count,
cs.total_spend,
cs.last_order_date
FROM customer_summary cs
JOIN customers c ON c.customer_id = cs.customer_id
WHERE cs.order_count >= 3
AND cs.last_order_date < CURRENT_DATE - INTERVAL '90 days'
ORDER BY cs.total_spend DESC;
```
Notice the query reads almost exactly like the plan. That's the goal: when your plan is solid, the SQL becomes a mechanical translation rather than a puzzle you solve at the keyboard.
## Why This Matters
Planning first catches errors that are expensive to find later — like realizing halfway through a 40-line query that you never defined what "active" means, or that your join is silently duplicating rows. In the next sections, you'll build on this exact query, add real-world messiness (returns, refunds, multiple order tables), and learn to spot when a join or filter is quietly dragging down performance.
### Diagram: The analysis planning process turns a vague business question into a precise, testable query strategy through a sequence of clarifying steps.
```mermaid
flowchart TD
A["Vague business ask:\n'Which customers should we target\nfor a re-engagement campaign?'"] --> B["Step 1: Clarify the question\n- Who counts as 'customer'?\n- What does 're-engagement' mean?\n- What's the time window?\n- What does success look like?"]
B --> C["Restated, answerable question:\n'List customers with 3+ orders in past 12 months\nbut no purchase in last 90 days,\nplus total historical spend'"]
C --> D["Step 2: Identify the data needed\n- orders (order_id, customer_id, order_date, order_total)\n- customers (customer_id, name, signup_date)"]
D --> E["Step 3: Sketch query logic\n(joins, filters, aggregations)\nbefore writing SQL"]
E --> F["Write & test the SQL query"]
F --> G["Deliver answer that matches\noriginal business intent"]
style A fill:#fdd,stroke:#900
style C fill:#dfd,stroke:#090
style G fill:#dfd,stroke:#090
```
### Capstone Project: Analyzing a Multi-Table Sales Dataset
# Capstone Project: Analyzing a Multi-Table Sales Dataset
## The Business Question
Every good analysis starts with a question a stakeholder actually cares about, not a query someone wants to write. For this capstone, imagine the VP of Sales asks:
> "Which product categories are driving revenue growth in our top regions, and are any high-performing sales reps concentrated in underperforming categories?"
This is deliberately messy — it bundles together *ranking*, *time comparison*, and *segmentation*. Your job is to decompose it into steps a SQL query plan can actually execute.
## Step 1: Translate the Question into a Query Plan
Before writing SQL, sketch the logic in plain English:
1. Define "top regions" — likely regions ranked by total revenue in the current period.
2. Define "growth" — revenue this period vs. same period last year, by category.
3. Join `orders`, `order_items`, `products`, `customers`, and `sales_reps` to connect revenue to region, category, and rep.
4. Aggregate revenue by region + category + period.
5. Calculate growth as a percentage change.
6. Layer in rep performance only for categories flagged as underperforming.
Writing this out first prevents the classic capstone mistake: joining every table "just in case" and then debugging a slow, bloated query.
## Step 2: Build the Query with CTEs
Common Table Expressions let you build this in readable, testable stages rather than one nested monster query.
```sql
WITH regional_revenue AS (
SELECT
c.region,
p.category,
EXTRACT(YEAR FROM o.order_date) AS order_year,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY c.region, p.category, EXTRACT(YEAR FROM o.order_date)
),
growth_calc AS (
SELECT
curr.region,
curr.category,
curr.revenue AS revenue_2024,
prev.revenue AS revenue_2023,
ROUND(((curr.revenue - prev.revenue) / prev.revenue) * 100, 1) AS pct_growth
FROM regional_revenue curr
JOIN regional_revenue prev
ON curr.region = prev.region
AND curr.category = prev.category
AND curr.order_year = 2024
AND prev.order_year = 2023
),
top_regions AS (
SELECT region, SUM(revenue_2024) AS total_2024
FROM growth_calc
GROUP BY region
ORDER BY total_2024 DESC
LIMIT 5
)
SELECT g.*
FROM growth_calc g
JOIN top_regions t ON g.region = t.region
ORDER BY g.region, g.pct_growth DESC;
```
Notice each CTE answers one sub-question: raw revenue, then growth, then "who's actually top." This mirrors your plain-English plan and makes each layer easy to test independently — run `regional_revenue` alone first and sanity-check the numbers before building on top of it.
## Step 3: Optimize Before You Present
Once the query returns correct results, check for waste:
- **Unnecessary joins**: If you're not using `sales_reps` in the growth calculation, don't join it there — save it for a separate, targeted query on underperforming categories.
- **Missing filters early**: The `WHERE o.order_date >= '2023-01-01'` filter should sit in the base CTE, not applied after joining five tables. Filtering early shrinks every downstream join.
- **Self-joins on large tables**: The `growth_calc` self-join is on pre-aggregated data (small), not raw `orders` (large) — this is intentional and keeps the join cheap.
- **Check execution plan**: Run `EXPLAIN` on the full query. If you see a sequential scan on `orders` where you expected an index scan, confirm `order_date` and `customer_id` are indexed.
## Step 4: Summarize for the Stakeholder
Raw output isn't a deliverable. Convert `growth_calc` results into a short narrative table:
| Region | Category | 2024 Revenue | Growth % |
|---|---|---|---|
| West | Electronics | $482,000 | +18.4% |
| West | Home Goods | $210,000 | -6.2% |
Follow with 2–3 sentences: which categories are declining in top regions, and a flagged next step (e.g., "Home Goods in West is shrinking — worth checking rep coverage there next"). This closes the loop from business question to actionable insight.
### Diagram: The capstone workflow decomposes a messy business question into a sequential query-building process: from defining metrics, through joining tables and staging logic in CTEs, to layering in rep-level segmentation for underperforming categories.
```mermaid
flowchart TD
A["Business Question:\nWhich categories drive growth in top regions,\nand are top reps stuck in underperforming categories?"] --> B["Step 1: Decompose into a Query Plan"]
B --> B1["Define 'top regions'\n(rank by total revenue)"]
B --> B2["Define 'growth'\n(this period vs. same period last year)"]
B --> B3["Identify joins needed:\norders, order_items, products,\ncustomers, sales_reps"]
B1 --> C["Step 2: Build Query with CTEs"]
B2 --> C
B3 --> C
C --> C1["CTE 1: regional_revenue\nJoin tables, aggregate revenue\nby region + category + year"]
C1 --> C2["CTE 2: growth_calc\nCompare current vs prior year\nrevenue by category"]
C2 --> C3["Flag underperforming categories\n(negative or low growth)"]
C3 --> C4["Join sales_reps\nonly for flagged categories"]
C4 --> D["Final Output:\nTop regions + growth by category\n+ high performers in weak categories"]
D --> E["Answer delivered to VP of Sales"]
```
### Improving Query Performance and Readability
# Improving Query Performance and Readability
Once your capstone query returns correct results, the next job is making it *fast* and *maintainable*. In real business settings, a query that takes 45 seconds to run on a stakeholder's laptop or times out in a dashboard tool is a query that doesn't get used, no matter how correct the logic is. This section walks through how to diagnose slowness, apply targeted fixes, and restructure messy SQL into something the next analyst (or you, in six months) can actually read.
## Step 1: Find the Bottleneck Before You Fix Anything
Don't guess. Use `EXPLAIN` (or `EXPLAIN ANALYZE` in Postgres, `EXPLAIN PLAN` in Oracle, execution plans in SQL Server) to see how the database is actually executing your query. Look for three red flags:
- **Full table scans** on large tables where you expected an index lookup
- **Nested loop joins** on tables with millions of rows (often a sign of a missing index on the join key)
- **Row estimates wildly off from actual rows returned** (suggests stale statistics or a poor filter)
## Step 2: Common Culprits and Fixes
**Unnecessary joins.** A frequent capstone mistake is joining a table you only need for one lookup column, then forgetting to check whether that join multiplies rows (a "fan-out"). If you joined `orders` to `order_items` just to check whether an order *had* any items, you don't need the join — an `EXISTS` subquery is cheaper and avoids duplicating the `orders` row for every item.
**Missing filters pushed too late.** If you filter on `order_date >= '2024-01-01'` in the outer query after joining five tables, the database may still scan years of data in the intermediate joins. Push date and status filters as early as possible — ideally in the first CTE that touches the raw table.
**SELECT \*** in CTEs. Pulling every column through three chained CTEs when you only need four columns downstream wastes memory and can prevent the optimizer from using narrower indexes.
**Functions on indexed columns.** Writing `WHERE YEAR(order_date) = 2024` prevents the database from using an index on `order_date`. Rewrite as `WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'`.
## Worked Example: Optimizing a Slow Revenue Report
Suppose your capstone query calculates total revenue per customer segment, but joins in `products`, `shipping`, and `returns` tables — even though the question only asks about *completed* orders and revenue, not shipping or returns.
**Before (slow, ~38 seconds on 10M rows):**
```sql
SELECT c.segment, SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN shipping s ON s.order_id = o.order_id
LEFT JOIN returns r ON r.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY c.segment;
```
The `shipping` join adds nothing (no shipping columns are used), and `returns` is joined but never filtered or aggregated — it's pure dead weight that risks fan-out if any order has multiple return rows.
**After (optimized, ~4 seconds):**
```sql
WITH completed_orders AS (
SELECT o.customer_id, o.order_id
FROM orders o
WHERE o.status = 'completed'
AND o.order_date >= '2024-01-01'
)
SELECT c.segment, SUM(oi.quantity * oi.unit_price) AS revenue
FROM completed_orders co
JOIN customers c ON c.customer_id = co.customer_id
JOIN order_items oi ON oi.order_id = co.order_id
GROUP BY c.segment;
```
The fix removed two unnecessary joins, filtered orders down to a manageable CTE before joining, and eliminated the unused `products` join entirely (product details weren't needed for the revenue calculation).
## Readability Checklist
Before submitting your capstone query, review it against these habits:
- **Name CTEs for what they represent**, not `cte1`, `cte2` (e.g., `active_customers`, `monthly_revenue`)
- **One clear transformation per CTE** — filtering, then joining, then aggregating, rather than everything in one block
- **Consistent formatting**: keywords capitalized, one join per line, indentation for subqueries
- **Comment the "why," not the "what"** — e.g., `-- excluding test accounts (id < 1000)` rather than `-- filter customer_id`
A query that's both fast and readable signals to reviewers that you understand not just *how* to get an answer, but how to deliver one that survives contact with production data and other people's eyes.
### Diagram: A diagnostic workflow for improving query performance: identify bottlenecks with EXPLAIN before applying targeted fixes, then refactor for readability.
```mermaid
flowchart TD
A[Capstone query returns correct results] --> B[Run EXPLAIN / EXPLAIN ANALYZE]
B --> C{Red flags found?}
C -->|Full table scan on large table| D[Add or check index on filtered/joined column]
C -->|Nested loop join on millions of rows| E[Add index on join key]
C -->|Row estimates way off actual rows| F[Update stats / rewrite filter]
C -->|No red flags| G[Move to readability pass]
D --> H[Re-run EXPLAIN to confirm improvement]
E --> H
F --> H
H --> C
G --> I[Check for unnecessary joins]
I --> I1[Join only for existence check?]
I1 -->|Yes| I2[Replace with EXISTS subquery]
I1 -->|No| J[Check filter placement]
I2 --> J
J --> J1[Filters applied after multi-table joins?]
J1 -->|Yes| J2[Push filters into earliest CTE]
J1 -->|No| K[Check column selection]
J2 --> K
K --> K1[SELECT * used in chained CTEs?]
K1 -->|Yes| K2[Select only needed columns]
K1 -->|No| L[Check functions on indexed columns]
K2 --> L
L --> M[Fast, readable, maintainable query]
```
### Sharing Your Results: Exporting and Explaining Findings
# Sharing Your Results: Exporting and Explaining Findings
Writing a correct, optimized query is only half the job. If a stakeholder can't understand what you found or can't act on it, the analysis has no business value. This section covers how to package your capstone query results into something a non-technical decision-maker will actually read, trust, and use.
## Step 1: Export Clean, Presentation-Ready Data
Never hand someone a raw SQL result grid. Before exporting to CSV, Excel, or a BI tool, do a final pass on your `SELECT` statement:
- **Rename columns** so they read like a report, not a schema. `cust_ltv_90d` becomes `Customer LTV (90 Days)`.
- **Round and format numbers.** Currency should have two decimals; percentages should be pre-multiplied and labeled.
- **Order rows meaningfully** — usually by the metric that matters most (highest revenue first, largest drop-off first), not by an internal ID.
- **Limit to what's needed.** If your working query returns 40 columns for debugging, your final export should have 5–8.
**Example — before and after:**
```sql
-- Working version (fine for you, bad for a report)
SELECT region, cust_id, SUM(order_total) AS rev, COUNT(*) AS n
FROM orders
GROUP BY region, cust_id;
-- Presentation version
SELECT
region AS "Region",
COUNT(DISTINCT customer_id) AS "Active Customers",
ROUND(SUM(order_total), 2) AS "Total Revenue",
ROUND(SUM(order_total) / COUNT(DISTINCT customer_id), 2) AS "Revenue per Customer"
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY region
ORDER BY "Total Revenue" DESC;
```
The second version answers "how is each region performing?" at a glance — no mental translation required.
## Step 2: Write a Short Narrative Summary
Every result table needs 3–5 sentences of plain-English framing. Use this structure:
1. **The question** you set out to answer.
2. **The headline finding** (the one number or trend that matters most).
3. **The supporting detail** (what the table shows, one level down).
4. **The caveat** (any data limitation, time window, or assumption).
**Worked example**, based on the query above:
> *Question:* Which regions are driving revenue, and how efficiently are we monetizing our customer base?
> *Finding:* The West region generated the highest total revenue ($482,900) in Q1 2024, but the Northeast had the highest revenue per customer ($312), suggesting a smaller but higher-value customer base.
> *Detail:* The full breakdown by region is in the table below; West and Midwest together account for 61% of total revenue.
> *Caveat:* This excludes refunds and covers only orders with a completed status, so true regional profitability may differ.
This four-sentence pattern takes under two minutes to write and instantly upgrades a spreadsheet into a decision-ready brief.
## Step 3: Show Your Work — Briefly
Stakeholders don't need your SQL, but they benefit from knowing your logic held up. Add a short "Methodology" note:
- Data source and table(s) used
- Date range and any filters applied (e.g., "excludes cancelled orders")
- Any grouping or deduplication logic that affects the numbers
This single paragraph preempts the most common pushback: *"Are you sure this number is right?"*
## Step 4: Match the Format to the Audience
- **Executives** want one chart and one sentence — lead with the headline finding, not the table.
- **Analysts/managers** want the table plus the narrative summary and methodology note.
- **Engineers** may want the actual query and execution plan if they'll maintain or automate the report.
A practical habit: export the same result set as (1) a CSV for the data team, (2) a formatted table with narrative in a doc or slide for managers, and (3) a one-line takeaway for a Slack or email update. Building all three from one query is fast once the SQL is solid — and it ensures your analysis gets read at every level, not just filed away.
### Diagram: From raw query to actionable insight: the workflow for transforming SQL results into a report stakeholders can understand, trust, and act on.
```mermaid
flowchart TD
A[Working SQL Query
40 columns, raw names, debug order] --> B[Step 1: Clean the Export]
B --> B1[Rename columns
cust_ltv_90d becomes Customer LTV 90 Days]
B --> B2[Round & format numbers
currency, percentages]
B --> B3[Order rows meaningfully
highest revenue first]
B --> B4[Limit to 5-8 key columns]
B1 --> C[Presentation-Ready Table]
B2 --> C
B3 --> C
B4 --> C
C --> D[Step 2: Write Narrative Summary]
D --> D1[1. The Question
What were we trying to find?]
D --> D2[2. The Headline Finding
The one number that matters]
D --> D3[3. The Supporting Detail
Context and caveats]
D1 --> E[Stakeholder Understands,
Trusts, and Acts on Findings]
D2 --> E
D3 --> E
style A fill:#f8d7da,stroke:#c0392b
style C fill:#d4edda,stroke:#27ae60
style E fill:#d1ecf1,stroke:#2980b9
```
#### Module check
1. According to the module, what should an analyst do first when a stakeholder asks a vague question like "Which customers should we target for a re-engagement campaign?"
- Clarifying and defining the vague terms in the business question (e.g., what counts as a target customer)
- Writing the SELECT statement first, then checking with the stakeholder afterward
- Immediately optimizing the query for performance before checking correctness
- Exporting a sample result set to see what data is available
2. True or False: According to the module, it's acceptable to hand a stakeholder the raw SQL result grid as long as the underlying query logic is correct.
- True
- False
3. Before attempting to fix a slow query, you should use the ____ command (or its database-specific equivalent, like EXPLAIN ANALYZE in Postgres) to see how the database is actually executing the query.
Source: https://learnvoro.com/courses/course-92637fc91fba165223dfe5e5b8c645d65130e2d0893b9aeeb48ebaca9b3285b9-838ed99975fde8ac0307a1b57b3d2050
AI-generated learning material from Learnvoro. Review important claims independently.