SQL for Data Science

Jr Codex Data Science Notes

Level: Intermediate Prerequisites: Chapter 1 Time to complete: ~30 minutes

Table of Contents

1. Why SQL, When You Already Know Pandas?
2. SELECT & WHERE — Filtering Data
3. ORDER BY & LIMIT
4. Aggregations & GROUP BY
5. JOIN — Combining Tables
6. Types of JOIN
7. Subqueries
8. SQL ↔ Pandas — a Direct Comparison
9. Summary & Next Steps

1. Why SQL, When You Already Know Pandas?

Pandas (Python Notes) manipulates data already loaded into memory. SQL queries data at the source — the database only sends back what you actually asked for, which matters enormously once a table has millions of rows that would never fit comfortably in memory as a DataFrame. Most real data science work involves both: SQL to pull a reasonably-sized, already-filtered dataset out of a database, then pandas to analyze it further.

2. SELECT & WHERE — Filtering Data

-- Get all columns for every customer
SELECT * FROM customers;
 
-- Get specific columns only
SELECT customer_id, name, signup_date FROM customers;
 
-- Filter rows with WHERE
SELECT * FROM customers
WHERE signup_date >= '2024-01-01';
 
-- Combine conditions with AND / OR
SELECT * FROM orders
WHERE total_amount > 100 AND status = 'completed';
 
-- Pattern matching with LIKE
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';        -- % is a wildcard, matches anything
 
-- Checking a list of values with IN
SELECT * FROM orders
WHERE status IN ('completed', 'shipped');

SQL WHERE ≈ Pandas Boolean Masking
─────────────────────────────────────────
  SQL:     WHERE total_amount > 100 AND status = 'completed'
  Pandas:  df[(df["total_amount"] > 100) & (df["status"] == "completed")]
                (same logic as Python Notes' NumPy/Pandas masking chapters)
─────────────────────────────────────────

3. ORDER BY & LIMIT

-- Sort results
SELECT * FROM orders
ORDER BY total_amount DESC;         -- descending; use ASC for ascending (the default)
 
-- Limit how many rows come back — critical for exploring a huge table safely
SELECT * FROM orders
ORDER BY total_amount DESC
LIMIT 10;                             -- top 10 highest-value orders

Practical habit: when exploring an unfamiliar, potentially huge table, always add LIMIT while you're still figuring out what you're looking at — pulling millions of rows just to preview the data wastes time and memory.

4. Aggregations & GROUP BY

-- Aggregate functions, applied across an entire table
SELECT COUNT(*) FROM orders;
SELECT AVG(total_amount) FROM orders;
SELECT SUM(total_amount), MAX(total_amount), MIN(total_amount) FROM orders;
 
-- GROUP BY — aggregate WITHIN each group, like pandas' .groupby()
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

GROUP BY ≈ pandas .groupby()
─────────────────────────────────────────
  SQL:
    SELECT department, AVG(salary)
    FROM employees
    GROUP BY department

  Pandas (Python Notes' Pandas Primer):
    df.groupby("department")["salary"].mean()
─────────────────────────────────────────

HAVING — Filtering AFTER Aggregation

-- WHERE filters ROWS before grouping; HAVING filters GROUPS after aggregation
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;      -- only customers who've spent over $1000 total

5. JOIN — Combining Tables

Real databases split data across multiple related tables (to avoid duplication) — JOIN combines them back together for analysis.

-- Two tables:
-- customers(customer_id, name, email)
-- orders(order_id, customer_id, total_amount)
 
SELECT customers.name, orders.total_amount
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

Why Data is Split Across Tables (Normalization)
─────────────────────────────────────────
  customers                    orders
  ┌────┬───────┬─────────┐    ┌────┬─────────────┬────────┐
  │ id │ name  │ email   │    │ id │ customer_id │ amount │
  ├────┼───────┼─────────┤    ├────┼─────────────┼────────┤
  │  1 │ Alice │ a@x.com │    │ 101│      1      │  50.00 │
  │  2 │ Bob   │ b@x.com │    │ 102│      1      │  30.00 │
  └────┴───────┴─────────┘    │ 103│      2      │  75.00 │
                                └────┴─────────────┴────────┘
  Alice's name isn't repeated for every order — JOIN reconnects them when needed.
─────────────────────────────────────────

6. Types of JOIN

-- INNER JOIN (default) — only rows that match in BOTH tables
SELECT c.name, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
 
-- LEFT JOIN — ALL rows from the left table, matched data from the right (NULL if no match)
SELECT c.name, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- includes customers who have NEVER placed an order (total_amount will be NULL for them)
 
-- RIGHT JOIN — the mirror image of LEFT JOIN (less commonly used)
-- FULL OUTER JOIN — all rows from BOTH tables, matched where possible

INNER JOIN                          LEFT JOIN
─────────────────────────────       ─────────────────────────────
  customers ∩ orders                  ALL customers, + orders where they exist
  (only customers WITH orders)         (customers with NO orders still appear,
                                          with NULL order data)
─────────────────────────────       ─────────────────────────────

The most common real-world mistake: using INNER JOIN when you actually needed LEFT JOIN — e.g., "how many customers have never ordered?" requires a LEFT JOIN (to keep customers with no matching order row), since an INNER JOIN would silently drop them entirely.

7. Subqueries

A query nested inside another query — useful for filtering based on an aggregated result.

-- Find customers who spent MORE than the average customer
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > (
    SELECT AVG(customer_total)
    FROM (
        SELECT SUM(total_amount) AS customer_total
        FROM orders
        GROUP BY customer_id
    ) AS customer_totals
);

Subqueries can get complex quickly — in practice, many data scientists find it more readable to pull a slightly broader result with SQL, then finish the logic in pandas (Section 8), especially for anything beyond a simple filter.

8. SQL ↔ Pandas — a Direct Comparison

import pandas as pd
import sqlite3
 
connection = sqlite3.connect("company.db")
 
# The SQL way
query = """
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 1000
    ORDER BY total_spent DESC
"""
result_sql = pd.read_sql(query, connection)
 
# The equivalent, done in pandas AFTER pulling the raw table
orders_df = pd.read_sql("SELECT * FROM orders", connection)
result_pandas = (
    orders_df.groupby("customer_id")["total_amount"]
    .sum()
    .reset_index(name="total_spent")
)
result_pandas = result_pandas[result_pandas["total_spent"] > 1000]
result_pandas = result_pandas.sort_values("total_spent", ascending=False)

Rule of thumb: filter and aggregate as much as reasonably possible in SQL, at the database — only pull the data you'll actually need into pandas for the finer-grained analysis (EDA, Module 3) that's easier to express in Python.

9. Summary & Next Steps

Key Takeaways

• SQL filters/aggregates data at the source, before it ever reaches Python — critical for tables too large to comfortably load entirely into a DataFrame.
• WHERE filters rows before grouping; HAVING filters groups after aggregation — a common source of confusion.
• JOIN recombines normalized (split-apart) tables; choosing INNER vs LEFT matters — LEFT JOIN is required to keep rows with no match on the other side.
• SQL and pandas map onto each other closely (GROUP BY ↔ .groupby(), JOIN ↔ pd.merge()) — use SQL for the heavy initial filtering, pandas for the finer analysis afterward.

Concept Check

1. Why would INNER JOIN silently miss customers who have never placed an order, and what fixes that?
2. What's the difference between WHERE and HAVING?
3. When would you choose to do a GROUP BY in SQL versus in pandas?

Next Chapter

→ Chapter 3: Data Cleaning Techniques

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index