SQL Learning Roadmap

Learn SQL by answering increasingly precise questions about one small database. Start with selecting rows, then filter, group, and join them. The finished project should contain a reproducible setup script, clearly named queries, expected outputs, and a short interpretation. This roadmap uses PostgreSQL-style examples, but the underlying questions transfer to other relational databases. Check your chosen database’s documentation for syntax differences instead of assuming every SQL feature behaves identically.

Design the practice database

Use a fictional library with a Books table and a Loans table. Books contains book_id and title. Loans contains loan_id, book_id, and returned, where returned is a true-or-false value. A book can have several loans over time, so book_id is unique in Books but repeats in Loans. Drawing that relationship before writing a join prevents a common misunderstanding: joined rows represent loans, not unique books.

Create three books: A, B, and C. Add four loans: two for A, one for B, and one for C. Mark one A loan and the C loan returned; leave the other two unreturned. Keep these invented records in your setup file. The official PostgreSQL tutorial introduces tables and queries Source 1; use it to check commands while keeping your exercise small enough to inspect manually.

Learn selection before aggregation

First select every loan, then select only loan_id and book_id. Add a filter for unreturned loans. You should see two rows, belonging to A and B. Add an explicit ordering when the display order matters. Do not rely on rows appearing in insertion order: a result needs its own sorting instruction if the order is part of your output.

Write the question above each query as a comment. For example, “Which loans remain open?” is clearer than “Query three.” Change one returned value in a disposable copy and predict which result changes. This prediction habit connects syntax with meaning. Use read-only queries for analysis and practise changes only in your fictional database, where restoring the setup script is straightforward.

Group rows and join deliberately

Count all loans by book_id. Expected counts are A: two, B: one, C: one. Then count only unreturned loans by book_id; A and B should each have one. PostgreSQL’s aggregate tutorial explains grouping and the distinction between filtering input rows and filtering groups Source 2. Write a separate query for each question before combining several conditions into a dense statement.

Join Loans to Books using book_id to display titles. The join should still contain four loan rows. The PostgreSQL joins tutorial describes matching rows and outer joins Source 3. Add a fourth book, D, with no loan. An inner join will not show D. A left join from Books can preserve it, but counting loan_id is important: counting every joined row would also count the unmatched placeholder row.

Your expected all-book loan counts are now A: two, B: one, C: one, D: zero. The sum must remain four. If the total becomes eight, inspect whether a supposed unique book key is repeated or the join condition is missing. Do not hide the problem with DISTINCT until you understand why the duplication occurred.

Turn queries into a useful analysis

Prepare a short library note: four recorded loans, two currently unreturned, and one catalogue book with no recorded loan. Avoid describing popularity from four invented records. The exercise demonstrates query behaviour, not a representative library trend. Include the exact definition of “open”: returned equals false, with no due-date or overdue interpretation.

Organise your submission into setup.sql, analysis.sql, expected-results.txt, and a short readme. Run setup in a clean practice database, then run every analysis query in order. A reviewer should obtain the stated counts without guessing which manual edits you made. Add a NULL test only after the basic version works: a missing returned status is unknown and should not silently become false.

For a next project, introduce dates and ask about a defined period. Keep the earlier checks as regression checks. More complicated SQL is useful only when it answers a more complicated question correctly; a window function is not an achievement if a simple grouped query would communicate the result more clearly.

Practice sequence

InspectRead the table definitions and sample records.
SelectReturn precisely the rows the question needs.
SummariseGroup only after defining the unit of analysis.
ValidateCompare joined totals with the original tables.

Practice decisions and evidence
Checkpoint	What to produce	How to review it
Filter	Two unreturned loans	A and B appear once each
Group	Counts of 2, 1, 1	Counts sum to four loans
Join	Four loans with titles	No unexplained increase in rows
Outer join	Book D has zero loans	Count loan identifiers, not placeholder rows

Evidence before advancement

Row question: Which individual loans are still open?
Group question: How many loans belong to each book?
Relationship question: Which catalogue books have no loans?

Common questions

Do I need programming knowledge before SQL?

You can begin with basic computer skills and a willingness to inspect tables. SQL has its own concepts, especially sets, joins, and missing values. Learn those through tiny examples instead of first completing an unrelated programming course. Programming becomes useful later when you want to automate repeated database work or build applications.

Which database should I use for this roadmap?

PostgreSQL is a suitable choice because the linked official tutorial supports the examples. Another database can also work, but check its Boolean values, date functions, and setup commands. Keep a note of the engine you used. A project should be reproducible in a specified environment rather than vaguely described as SQL.

Why should I count loan_id instead of every row?

After a left join, a book without loans can still produce a preserved book row with empty loan fields. Counting every row would report one. Counting a non-null loan identifier reports zero for that book. Inspect the joined detail table once so this difference is visible before trusting an aggregate.

Can DISTINCT fix duplicated join results?

It can remove duplicate output values, but it does not explain why the join multiplied records. First inspect key uniqueness and the matching condition. Some repeated titles are legitimate because a book has several loans. Apply DISTINCT only when the question genuinely asks for unique values and you understand what information it removes.

How should I handle a NULL returned status?

Treat it as an unknown status until the data definition says otherwise. A missing value is different from false. Add an explicit query counting unknown statuses and describe how they affect your open-loan result. If the practice exercise later assigns a valid value, record that change rather than silently rewriting the interpretation.

When should I learn subqueries and window functions?

Learn them when a question requires a new comparison, such as ranking books within categories or comparing each loan with a group result. First be comfortable predicting filters, joins, and grouped counts. Adding advanced syntax before you can reconcile totals makes debugging harder and can produce impressive-looking but unreliable analysis.

What makes a SQL portfolio project reviewable?

Include the schema, fictional data, query comments, expected outputs, and a concise business interpretation. Avoid screenshots as the only evidence because they cannot be rerun. A small project that works from a clean setup is stronger evidence of care than a large collection of disconnected queries with unexplained results.

How do I practise without damaging real data?

Use a separate fictional database and keep a setup script that recreates it. Do not connect your first exercises to an employer’s production system. For read-only practice, focus on SELECT queries. When learning changes, inspect the target rows first and use your database’s documented transaction behaviour to understand recovery.