SQL Basics Every Data Scientist Needs

sql basics data scientist work actually requires isn't database administration — it's SELECT/WHERE/GROUP BY/JOIN, applied directly to extract and pre-filter data before it ever reaches pandas. This article covers sql for data science through the lens of an actual data science workflow, bridging every concept back to the equivalent pandas operations already covered throughout this series.

Why Data Scientists Need SQL, Not Just Python

A realistic framing

Most data scientists use SQL to perform basic data transformations before importing data into a Python environment at all — SQL is often the very first step in a workflow, not a separate skill sitting in competition with pandas. A typical real-world sequence: write a SQL query against a production database, pull back exactly the subset of data actually needed, and then hand that result off to pandas for the heavier exploratory analysis, cleaning, and visualization covered throughout the rest of this series.

An honest scope-setting note

A data scientist doesn't need to be a SQL expert. Being able to extract, filter, and aggregate data using SQL is genuinely enough for the majority of day-to-day tasks — pandas takes over for the heavier, more exploratory analysis once the relevant data is actually loaded into memory. This article covers exactly that practical core, not the considerably deeper territory a dedicated database administrator or backend engineer would need.

What this article covers

SELECT/WHERE/filtering, aggregation with GROUP BY, joins across multiple tables, and — throughout — how each SQL concept maps directly onto the equivalent pandas operation already covered earlier in this series.

The Foundation: SELECT, FROM, and WHERE

SELECT: choosing columns

SELECT name, department, salary
FROM employees;

SELECT defines which columns to retrieve from the table named in FROM — either naming columns individually, as shown here, or using SELECT * to retrieve every column at once.

WHERE: filtering rows

SELECT name, department, salary
FROM employees
WHERE salary > 60000;

WHERE filters rows based on comparison and logical operators — directly analogous to the pandas boolean filtering covered in the earlier data selection article, just expressed in SQL's declarative syntax rather than df.loc[condition]. Where pandas has you write df.loc[df["salary"] > 60000], SQL expresses the exact same filtering logic as WHERE salary > 60000 — genuinely the same underlying operation, described in a different language's syntax.

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering' AND salary > 60000;

Combining conditions with AND/OR in SQL works exactly the way &/| combine boolean masks in pandas, covered in the earlier pandas filtering article — the underlying logic is identical, even though the operators themselves look different.

DISTINCT, ORDER BY, and LIMIT

SELECT DISTINCT department
FROM employees;

DISTINCT returns only unique rows, dropping duplicates that would otherwise appear in the result — directly comparable to pandas' .drop_duplicates(), covered in the earlier data cleaning article.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

ORDER BY sorts the result (here, by salary, descending), and LIMIT caps the number of rows returned — together, the basic toolkit for answering simple analytical questions like "who are the five highest-paid employees" directly against a database, without needing to pull the entire table back first.

Aggregating Data: GROUP BY, HAVING, and Aggregate Functions

Core aggregate functions

SELECT department, COUNT(*), AVG(salary), MAX(salary)
FROM employees
GROUP BY department;

COUNT, SUM, AVG, MIN, and MAX, paired with GROUP BY, summarize data by category — this is genuinely the SQL equivalent of the pandas .groupby().agg() pattern covered in the earlier groupby and aggregation article, and it's worth explicitly recognizing as the same underlying concept, expressed in two different languages. GROUP BY department splits the table into groups by department, exactly as df.groupby("department") would in pandas; COUNT(*), AVG(salary), and MAX(salary) are the SQL equivalents of .count(), .mean(), and .max() applied to each of those groups.

HAVING: filtering on an aggregated result

SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 500000;

This is a genuinely common point of confusion worth being precise about: WHERE filters rows, before any aggregation happens. HAVING filters groups, after aggregation has already been computed. You can't write WHERE SUM(salary) > 500000 — at the point WHERE runs, individual rows haven't been grouped or summed yet at all, so there's no aggregated value for WHERE to compare against. HAVING exists specifically to filter on the result of an aggregation, which is exactly why it's a separate clause, applied after GROUP BY.

A practical example: a sales report

SELECT region, month, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region, month
ORDER BY region, month;

This generates a simple sales report grouped by region and month — mirroring exactly the kind of task a data scientist would otherwise do with pd.groupby(["region", "month"])["sales_amount"].sum() after loading a CSV, covered in the earlier pandas groupby article. The genuine difference is where the aggregation happens: this SQL query computes the summary inside the database itself, before any data ever reaches Python — a distinction covered further in Section 5.

Joining Tables: Combining Data Across Sources

The four core join types

SELECT o.order_id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

INNER JOIN — only rows with a match in both tables. LEFT JOIN — all rows from the left table, plus matches from the right where they exist. RIGHT JOIN — the mirror image, all rows from the right table. FULL JOIN — all rows from both tables, with gaps (NULLs) where no match exists on either side.

This maps directly onto the pd.merge() join types covered in the earlier pandas merging article: SQL's INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN correspond exactly to how="inner", how="left", how="right", and how="outer" in pd.merge() — genuinely the same four relational join concepts, whether expressed in SQL or in pandas.

SELECT o.order_id, c.name, o.amount
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

Table aliases for readability

Notice orders o and customers c above — these are table aliases, keeping multi-table queries readable by letting you refer to orders.customer_id as simply o.customer_id instead. This matters genuinely in practice, since real analytical queries rarely involve just one table — joining three, four, or more tables together without aliases would produce genuinely unreadable, repetitive column references.

Subqueries

SELECT name, total_spend
FROM (
    SELECT customer_id, SUM(amount) AS total_spend
    FROM orders
    GROUP BY customer_id
) AS customer_totals
JOIN customers ON customer_totals.customer_id = customers.id
WHERE total_spend > (SELECT AVG(total_spend) FROM (
    SELECT SUM(amount) AS total_spend FROM orders GROUP BY customer_id
) AS avg_calc);

A subquery uses the result of one query as an input to another — here, filtering for customers whose total spend exceeds the average total spend across all customers, all in a single combined query, without needing a separate manual calculation step in between. This is a genuinely more advanced pattern, but worth recognizing: it's SQL's way of expressing "compute this intermediate result, then filter against it," conceptually similar to computing df["total_spend"].mean() in pandas and then filtering against that computed value in a second step.

SQL, Pandas, and Where to Go from Here

The practical workflow, tying this article directly to the rest of the series

A typical, realistic sequence: a data scientist writes a SQL query to extract and pre-filter exactly the data actually needed — not the entire table, just the relevant subset — loads that result directly into a pandas DataFrame (tying back to the earlier articles on reading CSVs and other data sources), and then does the heavier exploratory analysis, cleaning, and visualization entirely in Python, using everything covered throughout the rest of this series.

import pandas as pd
import sqlite3

c sqlite3.connect("company.db")
query = """
    SELECT region, month, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region, month
"""
df = pd.read_sql(query, conn)

pd.read_sql() runs a SQL query directly against a database connection and returns the result as a pandas DataFrame in one step — genuinely the most direct bridge between the SQL covered throughout this article and everything covered in the earlier pandas articles in this series.

A quick decision guide for where each tool does its best work

Use SQL for filtering, joining, and aggregating data that's still living in a database — this lets the database engine, which is specifically optimized for exactly this kind of set-based operation on large tables, do the heavy lifting, rather than pulling an entire massive table into Python memory first and then filtering it down afterward. Use pandas for iterative, exploratory work once a genuinely manageable-sized dataset is already in memory — the kind of back-and-forth, trial-and-error data exploration that's considerably more natural in a Python/pandas environment than in repeated, separately-run SQL queries.

What's next beyond the basics

Window functions, CTEs (Common Table Expressions), and query optimization are the next tier of SQL skills worth learning once the fundamentals covered in this article feel genuinely comfortable — window functions let you compute values across a set of rows related to the current row (a running total, a rank within a group) without collapsing them via GROUP BY; CTEs let you name and reuse intermediate query results cleanly, considerably more readable than deeply nested subqueries like the one shown in Section 4. Both are genuinely useful for more complex reporting and data pipeline work — but they're not required to start being immediately productive with SQL day to day, and everything covered in this article is enough to handle the large majority of everyday data-extraction tasks a data scientist actually encounters.