Introduction to SQL: The Ultimate Guide

Learn practical SQL with PostgreSQL: select, filter, join, aggregate, and keep queries readable and safe.

What you will learn

• SELECT, WHERE, ORDER BY, LIMIT
• Inner and outer joins between tables
• Aggregations with GROUP BY / HAVING
• Indexes at a glance and parameterized queries for safety

Prerequisites

• A PostgreSQL database (local Docker is fine)
• Comfortable with a terminal or any SQL client (psql, pgAdmin, DBeaver)

Start PostgreSQL quickly

docker run --name pg-dev -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16
docker exec -it pg-dev psql -U postgres

Official path: PostgreSQL Tutorial and SQL tutorial.

Sample schema

CREATE TABLE users (
  id bigserial PRIMARY KEY,
  email text NOT NULL UNIQUE,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (email) VALUES
  ('ada@example.com'),
  ('grace@example.com');

INSERT INTO orders (user_id, total_cents) VALUES
  (1, 2500),
  (1, 900),
  (2, 1200);

Core queries

SELECT id, email, created_at
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 20;

SELECT u.email, COUNT(o.id) AS order_count, COALESCE(SUM(o.total_cents), 0) AS spent_cents
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email
HAVING COUNT(o.id) > 0
ORDER BY spent_cents DESC;

• INNER JOIN: only matching rows
• LEFT JOIN: keep left rows even when there is no match
• Filter rows with WHERE; filter groups with HAVING

Indexes (when queries get slow)

CREATE INDEX orders_user_id_idx ON orders (user_id);
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1;

Index columns you filter or join on often. Do not index everything “just in case.”

Safety note

Never concatenate user input into SQL strings. Use parameterized queries in your application driver ($1, ?, or named params depending on the client).

Practice checklist

• Insert three users and five orders
• Write a query that returns users with zero orders
• Add an index and compare EXPLAIN ANALYZE before/after
• Rewrite one unsafe string-built query as a parameterized call in your language of choice

Sources and further reading

• PostgreSQL Tutorial
• PostgreSQL SELECT
• PostgreSQL JOIN
• Source: postgres/postgres

Who this guide is for

• Developers learning relational data through PostgreSQL
• Analysts moving from spreadsheets to repeatable queries
• Backend engineers reviewing schema and query fundamentals

Definition of done

A small relational schema with constraints, representative data, safe CRUD queries, one justified index, transaction examples, and an explain-based performance note.

Finishing the commands is not the same as finishing the guide. Keep the result only when another person can reproduce it, inspect the evidence, and understand the remaining risks.

Professional workflow

Phase 1 — Model rules as data constraints

Goal: Let the database protect invariants.

• Identify entities, keys, and relationships.
• Choose data types from meaning rather than convenience.
• Add not-null, unique, foreign-key, and check constraints where rules are stable.

Verification: Invalid examples fail at the database boundary with understandable errors.

Phase 2 — Query from questions

Goal: Write SQL that answers explicit product or reporting needs.

• Start with the expected result columns and grain.
• Add joins and filters one step at a time.
• Test nulls, duplicates, missing relations, and empty sets.

Verification: A fixture proves the query returns one row per intended business entity.

Phase 3 — Inspect cost and concurrency

Goal: Move beyond correct syntax to operational behavior.

• Use EXPLAIN on important reads.
• Add indexes only for demonstrated access patterns.
• Wrap multi-step changes in a transaction and test rollback.

Verification: The note explains both the query plan and the write/storage cost of each added index.

Review questions before you continue

• What does one result row represent?
• Which invariant belongs in a constraint?
• Can a join multiply rows unexpectedly?
• What isolation behavior does the transaction require?

If an answer is unknown, record it as an explicit follow-up instead of hiding the uncertainty behind a successful demo.

Troubleshooting decision guide

• Counts are too high: inspect join cardinality before adding DISTINCT.
• Filters miss rows: check null semantics and data types.
• An index is ignored: compare selectivity, statistics, expression shape, and table size.
• Concurrent updates conflict: reproduce the transaction order and review locking explicitly.

Change one variable at a time, preserve the first useful error, and write down the command or condition that fixed it. That turns a private debugging session into team knowledge.

Production-readiness checklist

• Use migrations for every schema change.
• Back up and test restore before destructive migrations.
• Use least-privilege roles and parameterized queries.
• Monitor slow queries, locks, connections, and storage growth.
• Review execution plans with production-like data volumes.

These checks are intentionally stricter than a beginner demo. Use them to decide whether to continue learning, run a controlled pilot, or request specialist review.

Your next 30 minutes

• Create a fixture containing edge cases and broken inputs.
• Write one join and explain its result grain.
• Add one transaction test that rolls back.
• Continue through the official PostgreSQL tutorial and EXPLAIN documentation.

CTA: Pick the first unchecked step, complete it now, and save the evidence in your project README or decision log. Then open the official documentation linked below before adopting the result in production.