How to learn SQL from scratch

Where to practise#

Learning SQL from pictures is pointless — you need a database where you can run queries and make mistakes. Three options, from least to most effort:

• an online sandbox or trainer with ready tables and answer checking;
• SQLite — a database in a single file, nothing to configure;
• PostgreSQL on your own computer — the closest to real working conditions.

For PostgreSQL, take a sample database with a dozen related tables (orders, customers, products) — demo databases like that are easy to find. As a client, use psql in the terminal or any graphical client.

Weeks 1–2: selecting and filtering#

Start by reading data — read queries cannot break anything:

SELECT name, city
FROM customers
WHERE city = 'Boston' AND created_at >= '2025-01-01'
ORDER BY name
LIMIT 10;

Work through comparison operators, IN, BETWEEN, LIKE, IS NULL. The main rule about NULL: it means "unknown", so NULL = NULL is not true, and to test for it you need IS NULL.

Weeks 3–4: aggregates and grouping#

COUNT, SUM, AVG, MIN, MAX, then GROUP BY and HAVING. Remember the logical order in which a query runs: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That explains the difference between WHERE (filters rows before grouping) and HAVING (filters groups after it). Note that COUNT(*) counts all rows while COUNT(column) skips NULLs.

Weeks 5–6: joining tables#

INNER JOIN, LEFT JOIN, less often RIGHT and FULL. Draw two small tables of three rows each on paper and write out the result of every kind of join by hand — after that the confusion goes away. A typical mistake is a join without a complete condition: rows multiply and the total comes out several times larger than it really is.

Weeks 7–8: subqueries, CTEs, window functions#

Subqueries in WHERE and FROM, common table expressions with WITH for readability, then window functions — ROW_NUMBER, RANK, LAG, a running total with SUM(...) OVER (ORDER BY ...). This is the topic data analyst interviews test. The article on SQL queries with examples collects ready query patterns for each of these.

Next: changing data and designing schemas#

INSERT, UPDATE, DELETE, transactions (BEGIN, COMMIT, ROLLBACK), primary and foreign keys, normalisation, indexes and reading a query plan with EXPLAIN. Try queries that change data inside a transaction first and roll them back — that way a mistake ruins nothing.

How to check your queries#

• Before running, write down the result you expect: roughly how many rows and what total. A big difference is a reason to look for a bug.
• After a JOIN, compare count(*) with the number of rows in the source table.
• Solve one task two ways — with a subquery and with a JOIN — and compare the answers.
• Keep your queries in a Git repository with a comment saying which question each one answers. It is both your notes and your portfolio.

A practice project: design a database for a personal library or budget, fill it with data and write ten reports — from a simple select to a ranking with a window function.

Step-by-step plan

1. Weeks 1–2 — SELECT and WHERESelecting, filtering, sorting and handling NULL on a practice database.
2. Weeks 3–4 — aggregatesCOUNT, SUM, AVG, GROUP BY, HAVING and the order a query runs in.
3. Weeks 5–6 — JOINInner and outer joins, checking the row count after a join.
4. Weeks 7–8 — subqueries and windowsWITH, ROW_NUMBER, RANK, LAG, running totals.
5. Weeks 9–10 — your own databaseTables with keys, transactions, indexes and ten reports in a repository.

Check yourself

Sources

• PostgreSQL TutorialThe official introduction to SQL using PostgreSQL
  free
• SQLite DocumentationA lightweight single-file database for your first queries
  free
• SQLBoltFree interactive SQL lessons with exercises in the browser
  free

Was this helpful?

Yes No