SQL Essentials¶

The language of data. SQL is how you ask questions of tabular data at scale — the same questions pandas answers in memory, answered where the data lives. Syntax below is broadly standard (dialect notes point to the database sheets).

Table of Contents¶

Installation / Setup
Core Syntax
Common Operations
Practical Examples
Common Patterns
Best Practices
Common Mistakes
Troubleshooting
Related Cheat Sheets
Official Documentation

Installation / Setup¶

SQL runs inside a database. For practice without installing a server:

import sqlite3, pandas as pd
con = sqlite3.connect(":memory:")
pd.read_sql("SELECT ...", con)        # SQL straight into a DataFrame

-- Quick schema tour
SELECT * FROM customers LIMIT 5;
SELECT COUNT(*) FROM orders;
DESCRIBE customers;      -- MySQL    |  \d customers  (Postgres)
PRAGMA table_info(customers);         -- SQLite

Core Syntax¶

Query order of operations (logical, not written)¶

Step	Clause	Purpose
1	`FROM / JOIN`	Source tables, row set built
2	`WHERE`	Filter rows (before grouping)
3	`GROUP BY`	Form groups
4	`HAVING`	Filter groups
5	`SELECT`	Choose/aggregate columns
6	`DISTINCT`	Deduplicate
7	`ORDER BY`	Sort
8	`LIMIT / OFFSET`	Paginate

This is why you cannot use aliases or aggregates in WHERE, but can in HAVING/ORDER BY.

SELECT, WHERE, ORDER BY, LIMIT¶

SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 100000
ORDER BY salary DESC          -- ASC default
LIMIT 10 OFFSET 0;            -- rows 1-10; OFFSET 10 -> rows 11-20

DISTINCT & LIMIT/OFFSET¶

SELECT DISTINCT department FROM employees;     -- unique values
SELECT DISTINCT department, job_title FROM employees;   -- distinct combos
SELECT * FROM logs ORDER BY ts DESC LIMIT 100;          -- newest 100
SELECT * FROM logs ORDER BY ts DESC LIMIT 10 OFFSET 20; -- page 3 of 10

WHERE filtering toolbox¶

Operator	Example	Meaning
Comparison	`age >= 18`, `price <> 0`	`<>` = not equal
`BETWEEN`	`age BETWEEN 18 AND 35`	inclusive range
`IN`	`city IN ('NYC','LA','SF')`	membership
`LIKE`	`name LIKE 'A%'`	pattern: `%` any, `_` one
`ILIKE`	Postgres case-insensitive	`name ILIKE 'a%'`
`IS NULL`	`email IS NULL`	missing (never `= NULL`!)
Logical	`AND OR NOT`	combine
`EXISTS`	`EXISTS (subquery)`	row exists in subquery

SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND status IN ('paid', 'shipped')
  AND coupon_code IS NOT NULL;

GROUP BY, HAVING, aggregations¶

SELECT department,
       COUNT(*)          AS n_employees,
       COUNT(DISTINCT job_title) AS n_roles,
       AVG(salary)       AS avg_salary,
       MIN(salary), MAX(salary),
       SUM(bonus),       -- skips NULLs by default
       ROUND(AVG(salary), 2)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5                 -- filter GROUPS (WHERE can't)
ORDER BY avg_salary DESC;

Aggregate functions: COUNT(*), COUNT(col) (non-null), COUNT(DISTINCT col), SUM, AVG, MIN, MAX, STDDEV/STDDEV_POP, VAR_POP, PERCENTILE_CONT, plus GROUPING SETS/ROLLUP/CUBE for multi-level totals.

Joins¶

-- INNER: only matching rows on both sides
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT: all orders, customers where they exist (NULL otherwise)
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

-- RIGHT: all customers, orders where they exist
SELECT o.order_id, c.name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;

-- FULL: everything from both sides (NULL-filled)
SELECT o.order_id, c.name
FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.id;

-- CROSS: every row of a × every row of b (careful: n*m rows)
SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;

Join	Rows kept
`INNER`	intersection of keys
`LEFT`	all left + matches
`RIGHT`	all right + matches
`FULL`	union of keys
`CROSS`	every combination

UNION¶

-- Combine result sets vertically (deduplicates); UNION ALL keeps duplicates
SELECT user_id, 'signup' AS event FROM signups
UNION ALL
SELECT user_id, 'purchase' FROM purchases;
-- Column count & types must match. Use UNION ALL unless you truly need dedup.

Subqueries¶

-- Scalar subquery (one value)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- IN subquery
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Correlated subquery (references outer row)
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

CTEs (`WITH`)¶

WITH high_value AS (
    SELECT customer_id, SUM(total) AS lifetime
    FROM orders GROUP BY customer_id
    HAVING SUM(total) > 5000
),
vip AS (
    SELECT c.id, c.name, h.lifetime
    FROM customers c JOIN high_value h ON c.id = h.customer_id
)
SELECT * FROM vip ORDER BY lifetime DESC;

CTEs make queries readable, reusable, and chainable — the default structure for data-science SQL.

Window functions¶

Window functions compute over a frame of rows without collapsing them (unlike GROUP BY).

SELECT customer_id, order_date, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn,
       RANK()       OVER (ORDER BY amount DESC)                  AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC)                  AS drnk,
       SUM(amount)  OVER (PARTITION BY customer_id)              AS cust_total,
       AVG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3,
       LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev,
       LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next
FROM orders;

Function	Result
`ROW_NUMBER()`	1,2,3… unique per partition
`RANK()` / `DENSE_RANK()`	ties share a rank (1,1,3 vs 1,1,2)
`NTILE(n)`	bucket into n groups (deciles!)
`LAG(col, k)` / `LEAD(col, k)`	previous / next row value
`SUM/AVG/MIN/MAX(...) OVER (...)`	running or framed aggregate
`FIRST_VALUE / LAST_VALUE`	frame edges
`PERCENT_RANK()`, `CUME_DIST()`	distribution stats

Top-N per group (classic):

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

CASE (if/then in SQL)¶

SELECT order_id, total,
       CASE WHEN total >= 1000 THEN 'high'
            WHEN total >= 100  THEN 'medium'
            ELSE 'low'
       END AS tier
FROM orders;

Works anywhere an expression does — in SELECT, WHERE, GROUP BY, ORDER BY.

DDL: CREATE / ALTER / DROP¶

CREATE TABLE customers (
    id          INTEGER PRIMARY KEY,           -- PK (see below)
    email       TEXT    UNIQUE NOT NULL,
    name        TEXT    NOT NULL,
    age         INTEGER CHECK (age >= 0),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE customers ADD COLUMN phone TEXT;         -- add column
ALTER TABLE customers RENAME COLUMN phone TO mobile; -- rename (dialect-specific)
ALTER TABLE customers DROP COLUMN mobile;
DROP TABLE customers;                                -- deletes table + data (careful!)
DROP VIEW IF EXISTS v_active;                        -- views too

DML: INSERT / UPDATE / DELETE¶

INSERT INTO customers (email, name, age) VALUES ('a@x.com', 'Ada', 36);

-- Bulk insert from a SELECT
INSERT INTO archive SELECT * FROM orders WHERE created_at < '2023-01-01';

UPDATE customers SET age = 37 WHERE email = 'a@x.com';   -- always WHERE!

DELETE FROM customers WHERE email = 'a@x.com';           -- always WHERE!
TRUNCATE TABLE customers;   -- delete all rows fast (no per-row WHERE)

Never run UPDATE/DELETE without a WHERE — inside a transaction, or SELECT-first to preview.

Constraints & keys¶

Constraint	Meaning
`PRIMARY KEY`	Unique + not null; identifies the row
`FOREIGN KEY`	References another table's PK; enforces referential integrity
`UNIQUE`	No duplicate values
`NOT NULL`	Value required
`CHECK`	Row-level rule (`CHECK (price >= 0)`)
`DEFAULT`	Value when none given

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),  -- FK shorthand
    total       NUMERIC(10,2) CHECK (total >= 0),
    status      TEXT DEFAULT 'pending'
);

Indexes¶

CREATE INDEX idx_orders_customer ON orders(customer_id);   -- speed WHERE/JOIN keys
CREATE UNIQUE INDEX idx_customers_email ON customers(email);
DROP INDEX idx_orders_customer;

Indexes accelerate lookups but slow writes and cost storage — index join keys and hot WHERE columns only.

Views¶

CREATE VIEW v_monthly_revenue AS
    SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
    FROM orders WHERE status = 'paid' GROUP BY 1;

SELECT * FROM v_monthly_revenue;    -- query it like a table (it re-runs)
CREATE MATERIALIZED VIEW ...;       -- Postgres: stores results, refreshable

Transactions¶

BEGIN;                          -- start (also: START TRANSACTION)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- sanity check SELECT ...
COMMIT;                         -- make it permanent
-- or ROLLBACK;                 -- undo everything since BEGIN

ACID: Atomicity, Consistency, Isolation, Durability. One unit of work = all-or-nothing.

Common Operations¶

NULL handling¶

-- NULL is "unknown": =, <>, comparisons all yield NULL (not TRUE)
SELECT * FROM t WHERE col IS NULL;         -- not: col = NULL
SELECT * FROM t WHERE col IS NOT NULL;

SELECT COALESCE(col, 'missing') FROM t;    -- first non-NULL
SELECT NULLIF(a, b) FROM t;                -- NULL when a = b
SELECT col1 || col2 FROM t;                -- concat (NULL -> NULL)
-- Postgres: col1 || COALESCE(col2, '');  MySQL: CONCAT(col1, col2) ignores NULL

-- Aggregates skip NULLs; COUNT(col) counts non-null only
SELECT AVG(col) FROM t;                    -- NULLs excluded, NOT counted as 0

Date/time¶

-- Standard
SELECT CURRENT_DATE, CURRENT_TIMESTAMP;
SELECT DATE('now')          -- SQLite      |  NOW()          (Postgres/MySQL)
SELECT DATE(created_at) AS day FROM orders;       -- cast to date
SELECT created_at + INTERVAL '7 days'    -- Postgres   |  DATE_ADD(created_at, INTERVAL 7 DAY) (MySQL)

-- Truncate/extract
SELECT DATE_TRUNC('month', created_at) FROM orders;      -- Postgres
SELECT DATE_FORMAT(created_at, '%Y-%m') FROM orders;     -- MySQL
SELECT strftime('%Y-%m', created_at) FROM orders;        -- SQLite
SELECT EXTRACT(YEAR FROM created_at) FROM orders;

-- Differences
SELECT AGE('2024-06-01', '2024-01-01');          -- Postgres: 5 mons
SELECT DATEDIFF('2024-06-01', '2024-01-01');     -- MySQL: days
SELECT julianday('2024-06-01') - julianday('2024-01-01');  -- SQLite: days

String functions¶

Function	Result
`UPPER(s)` / `LOWER(s)`	case
`LENGTH(s)` / `CHAR_LENGTH(s)`	length (chars)
`TRIM(s)`, `LTRIM`, `RTRIM`	strip whitespace
`SUBSTRING(s FROM 1 FOR 3)` / `SUBSTR(s,1,3)`	slice
`LEFT(s, 2)`, `RIGHT(s, 2)`	edges
`REPLACE(s, 'a', 'b')`	substitution
`POSITION('@' IN s)` / `STRPOS(s, '@')` / `INSTR(s, '@')`	find
`CONCAT(a, b)` / `a \\|\\| b`	join strings
`SPLIT_PART(s, ',', 1)`	nth piece (Postgres)
`REGEXP_REPLACE(s, pat, rep)`	regex replace (PG/MySQL8)

Practical Examples¶

1. Cohort/retention-style monthly rollup¶

WITH monthly AS (
    SELECT user_id,
           DATE_TRUNC('month', created_at) AS month,
           COUNT(*) AS orders
    FROM orders GROUP BY user_id, 1
)
SELECT month,
       COUNT(DISTINCT user_id)                 AS active_users,
       SUM(orders)                             AS total_orders,
       SUM(orders) / COUNT(DISTINCT user_id)   AS orders_per_user
FROM monthly
WHERE month >= '2024-01-01'
GROUP BY month ORDER BY month;

2. Top 3 products per category (window)¶

WITH ranked AS (
    SELECT category, product_id, revenue,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM product_revenue
)
SELECT * FROM ranked WHERE rn <= 3;

3. New vs returning customers (feature engineering for ML)¶

WITH first_order AS (
    SELECT customer_id, MIN(created_at) AS first_at
    FROM orders GROUP BY customer_id
)
SELECT o.customer_id,
       CASE WHEN o.created_at = f.first_at THEN 'new' ELSE 'returning' END AS segment,
       COUNT(*) AS order_count
FROM orders o JOIN first_order f ON o.customer_id = f.customer_id
GROUP BY 1, 2;

4. Pull training data for a model¶

SELECT c.age, c.country,
       COUNT(o.id) AS order_count,
       SUM(o.total) AS lifetime_value,
       AVG(o.total) AS avg_order,
       MAX(o.created_at) AS last_order_date,
       CASE WHEN MAX(o.created_at) > CURRENT_DATE - INTERVAL '90 days'
            THEN 1 ELSE 0 END AS active
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.age, c.country;

Common Patterns¶

-- Deduplicate keeping the latest row (ROW_NUMBER)
WITH d AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) rn
    FROM events
)
SELECT * FROM d WHERE rn = 1;

-- Running total / cumulative
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) AS cumulative
FROM daily_sales;

-- Percentile split for labeling (decile buckets)
SELECT *,
       NTILE(10) OVER (ORDER BY lifetime_value DESC) AS decile
FROM customers;

-- YoY / MoM change via LAG
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS mom_pct
FROM monthly_revenue;

-- Median (Postgres)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

Best Practices¶

Always qualify columns with table aliases in multi-table queries: o.total, not total.
Filter early: WHERE shrinks rows before joins/grouping — do it in the source query, not pandas.
Prefer CTEs over nested subqueries — one idea per CTE, name them meaningfully.
COUNT(*) counts rows; COUNT(col) counts non-NULL — know which you want.
Use UNION ALL unless you specifically need deduplication.
Test with LIMIT 100 before running expensive full queries.
Run writes inside transactions; SELECT first, UPDATE/DELETE after reviewing.
Join only needed tables/columns; every extra row or column costs I/O.
Use window functions instead of self-joins for rankings, running totals, and diffs.
Push aggregation to the database; only pull to pandas what you truly need.

Common Mistakes¶

Mistake	Consequence	Fix
`WHERE col = NULL`	Matches nothing (NULL ≠ NULL)	`IS NULL` / `IS NOT NULL`
Aggregate in `WHERE`	Syntax error / wrong rows	`HAVING` after `GROUP BY`, or subquery
`LEFT JOIN` then `WHERE other.col = x`	Silently becomes INNER JOIN	Move filter into the `ON` clause
Forgetting join condition	Accidental CROSS JOIN (n×m rows)	Always `ON ...`
Counting rows after `LEFT JOIN`	Inflated counts from matched multiple	`COUNT(DISTINCT id)`
`GROUP BY 1,2` magic numbers	Fragile when columns change	Name columns (or use ordinal sparingly)
UPDATE/DELETE without WHERE	Wipes the whole table	Preview with SELECT; transaction
Strings vs dates compared implicitly	Dialect surprises / wrong rows	Cast explicitly; use date literals
Relying on SELECT order without ORDER BY	Non-deterministic rows	Always `ORDER BY` for page/limit logic

Troubleshooting¶

Symptom	Cause	Fix
`column "x" must appear in the GROUP BY clause`	SELECT column not aggregated/grouped	Add to GROUP BY or aggregate it
Double rows after a join	Duplicate keys on one side	Dedupe source; check with `COUNT(DISTINCT)`
`ONLY_FULL_GROUP_BY` error (MySQL)	Non-aggregated column in SELECT	Aggregated properly or adjust sql_mode
Query very slow	Missing index / huge sort	`EXPLAIN`; index join/where columns
`LIMIT` with big `OFFSET` slow	Scans all skipped rows	Keyset pagination (`WHERE id > last`)
Unexpected NULL in calculations	NULL propagation	`COALESCE` or filter NULLs first

PostgreSQL — dialect details, JSON, performance
MySQL — MySQL/MariaDB specifics
SQLite — embedded database, pandas workflows
pandas.md — SQL ↔ pandas translations
Data Engineering — SQL in pipelines and warehouses
Home · Central Navigation Index

Official Documentation¶

Last Updated¶

2026-09-06 — Initial version.