SQL Tutorial for Beginners From SELECT to JOINs and Window Functions

Structured Query Language (SQL) is how you read and change data in relational databases such as PostgreSQL, MySQL, SQL Server, and SQLite. Whether you are analyzing product metrics or building a backend API, the same handful of concepts—tables, rows, keys, and joins—show up everywhere. This tutorial walks through those ideas with examples you can run in any SQL client.

Tables, rows, and columns

A table is a collection of rows (records) and columns (fields). Think of a users table:

The schema defines column names and types (INTEGER, VARCHAR, TIMESTAMP, etc.). Primary keys (often id) uniquely identify rows.

SELECT: reading data

Fetch all columns:

SELECT * FROM users;

Fetch specific columns and filter rows:

SELECT email, created_at
FROM users
WHERE created_at >= '2026-01-01';

Common clauses:

• WHERE — filter rows
• ORDER BY created_at DESC — sort
• LIMIT 10 — cap result size (syntax varies slightly on SQL Server: TOP 10)

INSERT, UPDATE, DELETE

Add a row:

INSERT INTO users (email, created_at)
VALUES ('new@example.com', CURRENT_TIMESTAMP);

Change data:

UPDATE users
SET email = 'ada.lovelace@example.com'
WHERE id = 1;

Remove rows (be careful in production):

DELETE FROM users WHERE id = 2;

Always use WHERE unless you intend to touch every row. In transactions, you can ROLLBACK mistakes.

JOINs: combining tables

Relational design splits data to avoid duplication. A orders table might reference users.id:

SELECT users.email, orders.total_cents
FROM orders
INNER JOIN users ON orders.user_id = users.id
WHERE orders.total_cents > 5000;

Join types:

• INNER JOIN — only matching rows on both sides
• LEFT JOIN — all rows from the left table, nulls where no match on the right
• FULL OUTER JOIN — less common; supported on PostgreSQL, not MySQL

Aggregations and GROUP BY

Summarize data:

SELECT user_id, COUNT(*) AS order_count, SUM(total_cents) AS revenue
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 3;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Useful aggregate functions: COUNT, SUM, AVG, MIN, MAX.

Indexes and performance basics

Without indexes, the database may scan every row for WHERE email = '...'. An index on email speeds lookups at the cost of slightly slower writes.

CREATE INDEX idx_users_email ON users (email);

Explain plans (EXPLAIN / EXPLAIN ANALYZE on PostgreSQL) show whether your query uses an index. You do not need to optimize every query on day one—add indexes when real workloads slow down.

Constraints and data integrity

• NOT NULL — required field
• UNIQUE — no duplicate values
• FOREIGN KEY — orders.user_id must exist in users.id
• CHECK — custom rules (e.g., total_cents >= 0)

Constraints catch bugs at write time instead of in your application layer alone.

Transactions

A transaction groups statements that succeed or fail together:

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

If any statement errors, ROLLBACK undoes the batch. ORMs often wrap transactions for you; raw SQL makes the behavior visible.

Practical learning path

1. Install PostgreSQL locally or use a free cloud instance.
2. Create two related tables (users, orders).
3. Practice SELECT with JOIN and GROUP BY.
4. Add indexes and run EXPLAIN on slow queries.
5. Read your framework’s migration tool (Rails, Prisma, Django) to see how schema changes ship.

FAQ

Is SQL the same on every database?
Core syntax is similar; functions, limits, and DDL details differ. PostgreSQL is a strong default for learning.

SQL vs NoSQL?
SQL fits structured data with relationships and ad hoc reporting. Document or key-value stores trade flexible schemas for different scaling patterns.

Do I need to memorize every function?
No. Know SELECT, JOIN, aggregates, and how to read docs for date math and string functions.

How does SQL relate to ORMs?
ORMs generate SQL. Understanding the underlying queries helps you debug N+1 problems and slow endpoints.

Subqueries and CTEs

Sometimes you need an intermediate result before the final SELECT. A subquery in the FROM clause acts like a temporary table:

SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept_avgs
WHERE avg_salary > 120000;

Common Table Expressions (CTEs) with the WITH clause read more clearly for multi-step logic:

WITH dept_avgs AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_avgs WHERE avg_salary > 120000;

PostgreSQL also supports recursive CTEs for hierarchical data (org charts, comment threads). Learn them when adjacency lists outgrow nested application code.

NULL handling

SQL uses three-valued logic: true, false, and unknown (NULL). Comparisons with NULL do not match = NULL; use IS NULL instead:

SELECT * FROM users WHERE phone IS NULL;

COALESCE(phone, 'n/a') substitutes a default display value. NULLIF(a, b) returns NULL when a = b, handy for division-by-zero guards.

Window functions (analytic SQL)

Aggregates collapse rows; window functions compute across related rows without grouping away detail:

SELECT
  user_id,
  order_id,
  total_cents,
  SUM(total_cents) OVER (PARTITION BY user_id) AS user_lifetime_spend
FROM orders;

ROW_NUMBER(), RANK(), and LAG() power funnel analysis and deduplication patterns. They appear in analytics interviews and BI tools alike.

Views and migrations

A view stores a named query:

CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

Application code can read active_users like a table. Schema changes still flow through migration files in your framework—never edit production tables by hand without a rollback plan.

SQL in application code

ORMs generate SQL, but debugging still requires reading the emitted statements. Enable query logging in development, watch for N+1 queries (one query per row in a loop), and use JOIN or eager loading instead.

Parameterized queries ($1, ?, or ORM bindings) prevent SQL injection. Never interpolate user strings into raw SQL.

FAQ addendum

What is normalization?
Splitting data into related tables to reduce redundancy. Denormalization trades storage simplicity for read performance—common in reporting databases.

Star schema vs OLTP schema?
OLTP schemas (many small normalized tables) serve apps. Star schemas (fact + dimension tables) serve warehouses like BigQuery or Snowflake.

Sample dataset to practice joins

Create toy tables and insert a few rows:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  amount NUMERIC(10,2)
);

INSERT INTO customers (name) VALUES ('Ada'), ('Lin');
INSERT INTO orders (customer_id, amount) VALUES (1, 42.50), (1, 18.00), (2, 99.99);

Now practice JOIN, GROUP BY, and a window function on amount. Breaking and fixing these queries on your laptop beats watching tutorials passively.

SQL rewards small, repeatable practice. Run queries against real (or realistic) datasets, break them on purpose, and fix them—that habit transfers directly to production debugging.