SQL for Beginners — Query Databases Like a Pro

SQL (Structured Query Language) is the universal language for working with relational databases. Whether you're a data analyst, software engineer, product manager, or scientist, SQL is the single most valuable technical skill you can learn — it's been the lingua franca of data since 1974 and shows no signs of being replaced.

This guide takes you from zero to querying like a pro, with runnable examples for PostgreSQL, MySQL, SQLite, and BigQuery.

Why Every Data Person Needs SQL

Almost every company stores its operational data — users, orders, events, payments — in a relational database. Excel breaks at a million rows. Python and pandas work great for in-memory analysis, but the data has to come from somewhere first. That "somewhere" is almost always SQL.

Knowing SQL means you can answer your own questions without filing a ticket with the data team.

Relational Database Basics

A relational database stores data in tables (think spreadsheets). Each table has:

• Columns — fields like id, email, created_at with a type (INTEGER, TEXT, TIMESTAMP)
• Rows — individual records
• Primary key — a column (usually id) that uniquely identifies each row
• Foreign key — a column that references another table's primary key, creating relationships

Example schema:

CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 email TEXT UNIQUE NOT NULL,
 created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
 id SERIAL PRIMARY KEY,
 user_id INTEGER REFERENCES users(id),
 amount NUMERIC(10, 2),
 status TEXT,
 placed_at TIMESTAMP
);

orders.user_id is a foreign key pointing to users.id — one user has many orders.

SELECT — The Heart of SQL

-- Get everything (avoid in production)
SELECT * FROM users;

-- Get specific columns
SELECT email, created_at FROM users;

-- Filter with WHERE
SELECT * FROM orders WHERE status = 'paid' AND amount > 100;

-- Sort
SELECT * FROM orders ORDER BY placed_at DESC;

-- Limit results
SELECT * FROM orders ORDER BY amount DESC LIMIT 10;

Useful WHERE operators: =, !=, <, >, BETWEEN 1 AND 10, IN ('a','b'), LIKE 'foo%', IS NULL, IS NOT NULL.

Aggregations and GROUP BY

Aggregations summarize many rows into one.

-- How many users do we have?
SELECT COUNT(*) FROM users;

-- Revenue by status
SELECT status, SUM(amount) AS revenue, AVG(amount) AS avg_order
FROM orders
GROUP BY status;

-- Top spenders
SELECT user_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING SUM(amount) > 1000
ORDER BY total_spent DESC;

Key rule: every non-aggregated column in SELECT must appear in GROUP BY. Use HAVING (not WHERE) to filter on aggregated values.

Common aggregates: COUNT, SUM, AVG, MIN, MAX, COUNT(DISTINCT x).

JOINs — Combining Tables

JOINs are where SQL becomes powerful. Here's the visual mental model:

JOIN type	Returns	Use case
INNER JOIN	Only matching rows from both tables	"Users who placed orders"
LEFT JOIN	All from left + matches from right (NULL else)	"All users, with order count"
RIGHT JOIN	All from right + matches from left	Rare; flip and use LEFT
FULL OUTER	All rows from both, NULLs where no match	Reconciliation, audit
CROSS JOIN	Cartesian product (every row × every row)	Generate combinations

-- Users with their order totals (INNER — only users who ordered)
SELECT u.email, SUM(o.amount) AS total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

-- ALL users including ones who never ordered (LEFT)
SELECT u.email, COALESCE(SUM(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

COALESCE(x, 0) replaces NULL with 0 — critical when LEFT JOINing.

Subqueries vs CTEs

A subquery is a query inside another query. A CTE (Common Table Expression, the WITH keyword) is the readable modern equivalent.

-- Subquery (works but hard to read)
SELECT email FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 500);

-- Same with a CTE — much clearer
WITH big_spenders AS (
 SELECT DISTINCT user_id FROM orders WHERE amount > 500
)
SELECT u.email
FROM users u
JOIN big_spenders b ON b.user_id = u.id;

CTEs can chain — define multiple WITH blocks separated by commas. Prefer CTEs for anything non-trivial.

Window Functions

Window functions compute across a set of rows without collapsing them like GROUP BY does. They're the "intermediate SQL" skill that separates analysts from power users.

-- Rank each user's orders by amount
SELECT
 user_id,
 amount,
 placed_at,
 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
 RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk,
 LAG(amount) OVER (PARTITION BY user_id ORDER BY placed_at) AS prev_amount
FROM orders;

-- Each user's largest order
WITH ranked AS (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
 FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

• ROW_NUMBER — unique sequential (1,2,3)
• RANK — ties share rank, gap after (1,1,3)
• DENSE_RANK — ties share, no gap (1,1,2)
• LAG/LEAD — previous/next row's value
• SUM() OVER (...) — running totals

Modifying Data

INSERT INTO users (email) VALUES ('alice@example.com');

UPDATE users SET email = 'new@example.com' WHERE id = 42;

DELETE FROM orders WHERE status = 'cancelled' AND placed_at < '2024-01-01';

Always test with SELECT first. A WHERE-less UPDATE or DELETE will rewrite or wipe your whole table. Wrap multi-statement changes in a transaction:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;

Indexes and Query Performance

An index is a sorted lookup structure (usually a B-tree) that makes WHERE / JOIN / ORDER BY fast on a column.

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_placed ON orders (placed_at);

Rules of thumb:

• Primary keys are auto-indexed
• Index columns you frequently filter or JOIN on
• Don't over-index — every index slows down INSERT/UPDATE
• Use EXPLAIN (or EXPLAIN ANALYZE) to see the plan

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

Look for Seq Scan on large tables — that means no index is being used.

SQL Flavors Compared

Database	Best for	Notable quirks
PostgreSQL	General-purpose, analytics, JSON	Most standards-compliant, powerful
MySQL/MariaDB	Web apps, WordPress, OLTP	Looser typing, weaker windows pre-8
SQLite	Embedded, mobile, local dev	Single file, no concurrent writes
SQL Server	Microsoft shops, enterprise	T-SQL dialect, paid
BigQuery	Petabyte analytics, no ops	Standard SQL + ARRAYs/STRUCTs

Core SQL (SELECT, JOIN, GROUP BY) works everywhere. Differences appear in date functions, LIMIT vs TOP, string concatenation (|| vs +), and JSON support.

Real Example Walkthrough

"What was last month's revenue per active user, by signup cohort?"

WITH last_month AS (
 SELECT user_id, SUM(amount) AS revenue
 FROM orders
 WHERE status = 'paid'
 AND placed_at >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')
 AND placed_at < DATE_TRUNC('month', NOW())
 GROUP BY user_id
),
cohorts AS (
 SELECT id AS user_id, DATE_TRUNC('month', created_at) AS cohort
 FROM users
)
SELECT
 c.cohort,
 COUNT(DISTINCT lm.user_id) AS active_users,
 ROUND(AVG(lm.revenue)::numeric, 2) AS arpu
FROM cohorts c
LEFT JOIN last_month lm ON lm.user_id = c.user_id
GROUP BY c.cohort
ORDER BY c.cohort;

That's a real analytics question, answered in 15 readable lines. That's the power of SQL.

FAQ

Q: SQL vs NoSQL — which should I learn? A: SQL first, always. The relational model is older, more standardized, and 90%+ of data jobs require SQL. NoSQL (MongoDB, DynamoDB) is a specialized tool you reach for when you have a specific reason.

Q: How long does it take to learn SQL? A: Basics (SELECT, WHERE, JOIN, GROUP BY) in a weekend. Intermediate (CTEs, window functions, indexes) in a few weeks of practice. Mastery (query tuning, advanced patterns) is years — but you'll be productive long before then.

Q: Should I use ORMs like Prisma or SQLAlchemy instead? A: ORMs are useful for app code, but you still need to read and write raw SQL to debug performance issues and run analytics. Learn SQL first, then ORMs.

Q: Are SQL skills still relevant with AI tools writing queries? A: More than ever — you need to read, verify, and fix what the AI generates. LLMs hallucinate column names and write incorrect JOINs that look plausible. SQL literacy is the verification layer.

Q: What's the best way to practice? A: Install PostgreSQL or SQLite locally, load a real dataset (Kaggle, public BigQuery), and answer real questions. Sites like pgexercises.com and sqlzoo.net are great structured practice.

Q: When do I need window functions vs GROUP BY? A: GROUP BY when you want one row per group. Window functions when you want to keep every row but add aggregated context (ranks, running totals, comparisons to previous row).

See Also

• NumPy Guide — Python's Foundation for Data Science
• Python Pandas Data Analysis Guide

Sources

• PostgreSQL Documentation — postgresql.org/docs
• "SQL for Data Analysis" — Cathy Tanimura, O'Reilly 2021
• Codd, E.F. (1970). "A Relational Model of Data for Large Shared Data Banks"
• Use The Index, Luke — use-the-index-luke.com (Markus Winand)
• BigQuery Standard SQL Reference — cloud.google.com/bigquery/docs