How to learn SQL: a plan from SELECT to independent analysis

A useful SQL learning plan should lead to an independent answer about data. A list of keywords does not guarantee that. You can recognise JOIN, GROUP BY and CTE while counting one order three times. I therefore organise learning around questions and checks. Each stage includes a small task that shows whether the tool you used makes sense to you.

This plan is aimed at someone moving into data analysis. It begins with reading and filtering, then adds aggregation, joins, breaking a problem into steps and window functions. The examples use PostgreSQL. I checked its documentation during the November 2026 revision; behaviour in another database engine needs its own confirmation, particularly for functions and dialect-specific syntax.

Understand the environment and the meaning of a row

SQL is a language, the database engine executes a query, and a client lets you enter it and inspect the response. You can begin in a browser environment or in your own database. Local installation is useful to learn, but it does not have to prevent your first lesson. Establish which engine you are connected to and where the practice data lives.

Avoid mixing PostgreSQL, MySQL and SQL Server examples immediately. Their common foundations are substantial, but date functions, result limits and some syntax differ. When a copied query fails, check the dialect before hunting for a typo. Record the engine beside examples in your notes, especially when adapting a solution found online to a different environment.

Two small synthetic tables are enough for this plan. In customers, one row represents one customer and customer_id is unique. In orders, one row represents an order and order_id is unique. Amounts are illustrative numbers in one unit, without taxes or refunds. These assumptions let us focus on how a query changes the records available to the next step.

NULL on the last order means that its amount is unknown. I am not replacing it with zero at the start. Ada has two orders, Ben has two, and Celina has none. Known amounts total 230; completed orders total 180. Keep these numbers beside the exercise so that you can return to the meaning of a result when a query becomes more complicated.

Stage one: SELECT, columns and ordering

Start by selecting the necessary columns and giving them readable aliases. The PostgreSQL SELECT tutorial introduces columns, expressions and filters. Our first task can be as simple as this:

SELECT order_id, amount AS order_amount
FROM orders
ORDER BY order_id;

There are four rows, ordered by their identifiers. An alias changes the result column’s name rather than renaming a field in the source table. SELECT * helps you inspect an unfamiliar table, but I prefer explicit columns in a report. They make the information being selected visible to the next person reading the query.

Distinguish selecting records from arranging them. To return the two smallest order identifiers, add LIMIT 2 after the ordering. The LIMIT documentation emphasises a unique order when you need a predictable subset. “The first two” without a defined ordering leaves an important part of the request unspecified.

You have understood this stage when you can predict the row count, column names and presentation order. Swap the positions of amount and order_id and notice that the underlying information is unchanged. Add one idea at a time after that. This makes it much easier to identify which change caused a new result, rather than debugging several unfamiliar clauses together.

Stage two: WHERE, logic and missing values

Select completed orders with an amount of at least 90:

SELECT order_id, amount
FROM orders
WHERE status = 'completed'
  AND amount >= 90
ORDER BY order_id;

Only order 101 remains, with an amount of 100. The status removes 102, and the threshold removes 103. Change each condition separately and explain which records return. When combining AND with OR, use parentheses to express your intended rule. Writing the rule in ordinary language first can help you notice a missing alternative or an unintended exception.

To find an unknown amount, use amount IS NULL. Writing amount = NULL does not perform this check. The PostgreSQL comparison reference explains the unknown result of ordinary comparisons involving NULL. In this dataset, a separate query looking for a missing amount should identify order 104.

When adding dates, define the boundaries of the period deliberately. For timestamps, an inclusive start and an exclusive start of the next period are often convenient. Record the relevant time zone too. A filter labelled “November” is incomplete if the source stores moments in one zone and the report groups them into local business days in another.

Finish this stage by explaining every rejected record. An empty result does not automatically mean that the source contains no data. A condition, a type mismatch or a different spelling of a status may be responsible. Reduce the query to one criterion when investigating, instead of adding more functions before you understand why the current filter rejects everything.

Stage three: aggregates, GROUP BY and HAVING

First count orders and known amounts separately:

SELECT COUNT(*) AS orders_count,
       COUNT(amount) AS known_amounts,
       SUM(amount) AS total_amount,
       AVG(amount) AS average_amount
FROM orders;

The values are 4, 3, 230 and an average of 230 divided by 3. The PostgreSQL aggregate reference distinguishes counting rows from counting non-null inputs. The average function is AVG; names such as MEAN and AVERAGE from other tools are not the aggregate names used here.

Next calculate completed amounts per customer and keep groups exceeding 90:

SELECT customer_id, SUM(amount) AS completed_amount
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 90
ORDER BY customer_id;

Customer 1 remains with 100. WHERE selects orders before grouping, while HAVING evaluates the resulting groups. The aggregation tutorial explains this distinction. Describe which two orders enter the grouping before considering their totals. That intermediate explanation is often where a mistaken assumption becomes visible.

State the meaning of an output row every time. After grouping by customer_id, a row represents a customer group rather than an individual order. Joining that summary to order lines later could repeat its values. Recording the level of detail at each stage is one of the most useful habits you can develop while learning analytical SQL.

Stage four: JOIN and the number of matches

Now I want every customer alongside their orders:

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Ada occupies two rows, Ben occupies two, and Celina occupies one with NULL on the order side. There are five rows overall. LEFT JOIN preserves a customer without a match; it does not promise one output row per customer. The PostgreSQL join tutorial explains matched pairs and the preservation behaviour of an outer join.

Compare the result with INNER JOIN. Celina disappears, leaving four matched orders. Learn these cases properly before working through every other join type. I would rather understand the number of matches than reproduce diagrams from memory. The LEFT JOIN guide provides more detail, including what happens when you filter the right-hand table.

For another exercise, imagine a second customer 1 record in the lookup table. Each of that customer’s orders gets another match. Do not immediately add DISTINCT as a repair. Establish why the key stopped being unique and which record should apply. Removing similar-looking rows can hide the problem or discard legitimate records, particularly after selecting fewer columns than the source contains.

Stage five: subqueries and CTEs

When a question needs several steps, give those steps names. Calculate completed amounts per customer first, then select totals above the threshold:

WITH completed_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM completed_totals
WHERE total_amount > 90
ORDER BY customer_id;

The result is unchanged: customer 1 with an amount of 100. The benefit here is naming an intermediate step clearly. The WITH documentation also describes materialisation rules. A CTE does not give a universal guarantee that its result will be stored separately or that the query will run faster.

Learn subqueries and CTEs as ways to organise a problem. Record the input and output of each step. If a subquery is expected to supply one value, consider what happens when it supplies several rows. If it produces a customer list, check whether duplicates matter to the join that follows. These questions connect syntax to the shape of your data.

You do not need recursion or the shortest possible expression immediately. You need to reopen the query a week later and explain why each part exists. A name such as completed_totals helps more than cte1, because it reminds you of both the status condition and the aggregation. Clear names reduce the amount of context you must reconstruct from memory.

Stage six: window functions

Window calculations help when you want to retain orders and add a customer summary beside them:

SELECT order_id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_amount
FROM orders
ORDER BY order_id;

Orders 101 and 102 show 150 beside them; orders 103 and 104 show 80. Four rows remain. The window function tutorial explains retaining individual rows while calculating across a related set. I have omitted ORDER BY inside OVER here, so that running totals and frame boundaries can be introduced as a separate subject.

Next choose one specific topic: ranking, a previous value or a running total. Define ties and missing periods before choosing a function. A previous row may mean the previous order without meaning the previous calendar day. The question should establish that distinction. Otherwise a technically valid calculation can end up labelled as a comparison it never actually performed.

I do not consider window functions a condition for beginning a first project. Correct filtering, grouping and joining already support useful analysis. Add windows when you can compare their output with a simpler summary and explain the difference in row counts. That gives you a way to inspect the new calculation instead of accepting it because the database returned no error.

Modifying data and reading execution plans

An analyst should also recognise commands that change data. INSERT adds rows, UPDATE changes values, DELETE removes selected rows, and CREATE and DROP concern objects. Practise these in a separate training database. The PostgreSQL UPDATE tutorial demonstrates selecting records with a condition; omitting an appropriate condition can extend a change across the entire table.

I would reserve a distinct stage for these operations. Reading practice lets you concentrate on the meaning of the output. Modification also requires understanding the effect on the source. Use data you can recreate easily, and describe the intended change before executing it. Keeping those responsibilities separate makes both the exercise and the review easier to follow.

Approach performance after establishing that a query answers the right question. EXPLAIN displays a plan; EXPLAIN ANALYZE executes the command and gathers measurements, as the documentation explains. Begin by recognising row counts and expensive operations. An index is not an automatic improvement, and shorter SQL does not automatically execute faster.

Choose a course and check your independence

Inspect the course syllabus and the exercises. You need tasks where you choose the filter, join key and grouping level yourself. Following every click is helpful for initial contact. Later there should be a point where you have only data, a question and a requirement to justify the answer. Without that transition, it is difficult to discover which decisions you can make independently.

Check whether the material names its engine and version, covers NULL and shows row multiplication after joins. Recording length tells you little about how much SQL you will write yourself. Look for explanations of results too. A query without interpretation is easy to copy and difficult to assess in a new context.

A final project can remain small. Identify customers with completed orders, calculate their amounts and find customers who have not ordered. For each output, state the meaning of one row, the treatment of missing values and the expected record count. Then add a customer without orders or a cancelled order. Predict which answer should change before inspecting the new result.

If you get stuck, describe the difference between expectation and observation. Five rows instead of three may be a completely correct consequence of a one-to-many relationship. Naming that difference is the beginning of an investigation. The guide to logical query order can help when you cannot identify the stage at which a record disappears.

Documentation remains available throughout learning, just as it does at work. The important ability is choosing an approach, explaining its limits and recognising an output that disagrees with the data. Your course plan then becomes a map of practical skills that you can revisit for the next project, rather than a list of functions you once saw on a slide.