SQL basics: from your first table to a result you can explain

When I learn a new tool, I want an early example of what it helps me do. With SQL, that might be a simple question: how many orders were completed, and what were they worth? A few lines return an answer. Then I notice that cancelled orders were included in the total. That is where the useful part of learning begins.

SQL lets me read and transform data in relational databases, among other things. I describe the result I need, and the database engine handles execution. My first priority is understanding which rows I select, what I calculate and why. A long catalogue of commands can wait until I have a question and a way to check the answer.

This guide uses SQLite and six synthetic orders. Run the examples in sequence in one empty practice database. You do not need company data or a separate server. I also include a small update inside a transaction that ends with a rollback, so that experiment leaves the starting data unchanged.

A database, a table and a query result

A database stores data. A table organises it into rows and columns. A query describes an operation. The result of SELECT looks like another table, but displaying it does not automatically create a new permanent table. I can ask the same data several questions without manually copying it into a separate spreadsheet for each analysis.

In our table, one row represents one order. The order_id identifies it, channel describes the sales channel, status records its state and amount_cents stores an amount in minor currency units. All amounts use the same currency. These assumptions matter as much as remembering the SELECT keyword.

If each row represented an order item, counting rows would count items. If the table stored status history, the same order could appear several times. Before querying unfamiliar data, I write down what one row represents. That small habit prevents an impressive amount of confusion later, especially when a perfectly valid query returns an unexpectedly large number.

How to run the examples

Use an application that supports SQLite or its command line shell. The shell can open a local database file or an in-memory database for a temporary exercise. The SQLite shell documentation explains these options. Run the table creation block first, followed by the remaining examples in order.

I do not start with CREATE DATABASE in SQLite. Some server databases support that command, but SQLite has a different way of opening databases. SQL has a shared foundation, while individual products differ in their handling of types, dates, database administration and additional functions. Every executable example here targets SQLite specifically.

If you see a message saying the table already exists, you probably ran the setup twice in the same database. Open a new practice database. Avoid automatically adding a command that deletes the existing table, particularly when you are unsure which environment the application is connected to. Checking the connection name belongs in the workflow too.

Create a small dataset

-- example: fixture
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    channel TEXT NOT NULL,
    status TEXT NOT NULL,
    amount_cents INTEGER
);
INSERT INTO orders (order_id, channel, status, amount_cents)
VALUES
    (1, 'web', 'completed', 10000),
    (2, 'web', 'cancelled', 5000),
    (3, 'store', 'completed', 20000),
    (4, 'web', 'completed', NULL),
    (5, 'store', 'completed', 0),
    (6, 'web', 'completed', 10000);

CREATE TABLE defines the structure, and INSERT INTO adds the sample rows. I name the INSERT columns explicitly so the order of the supplied values is visible. Text uses single quotes. NULL has no quotes because it represents a missing value, rather than a four-letter string.

The primary key prevents duplicate order identifiers here. NOT NULL requires a value for channel and status. I deliberately allow an unknown amount to show how missing values affect calculations. The SQLite CREATE TABLE reference describes these declarations. A complete database design needs more decisions, but this small structure is enough for our question.

SQLite has a distinctive type system. A type name in an ordinary SQLite table does not provide exactly the same guarantees as in every other database. I control the input values in this exercise. In a real process, I would also check whether amounts are actually numeric and whether their units are consistent.

SELECT chooses columns

-- example: selected
SELECT order_id, channel, amount_cents
FROM orders
ORDER BY order_id;

The result contains six rows and three columns. SELECT specifies what I want to see, FROM identifies the source and ORDER BY sorts the result. A semicolon ends the statement. I spread the query across several lines because missing commas and misplaced conditions are easier to spot that way.

SELECT * can be useful when exploring a table because it selects every column. For a finished report, I prefer an explicit column list. It makes the output clear and avoids silently including new fields after the source structure changes. A useful output column name should also help the recipient understand the value without reading my private notes.

Without ORDER BY, I do not rely on the order I happen to observe. Rows appearing in identifier order today do not establish a promise about tomorrow’s output. That assumption often becomes visible when someone compares two exports and reports differences that are only changes in row order.

WHERE selects rows before aggregation

-- example: filtered
SELECT order_id, amount_cents
FROM orders
WHERE status = 'completed'
  AND channel = 'web'
ORDER BY order_id;

This returns orders 1, 4 and 6. Order 2 is excluded because it was cancelled, even though it came through the web channel. Conditions joined with AND must both be satisfied. I have translated “completed online orders” into a precise rule for selecting records.

Before adding an aggregate, I check the selected identifiers. On this tiny dataset, I can inspect every one. On larger data, I inspect a sample and compare counts for relevant groups. If the filter selects the wrong rows, a correctly working SUM will faithfully add the wrong dataset. Arithmetic cannot reconstruct the question I meant to ask.

When combining AND and OR, I use parentheses to make the intention clear. “Completed and from web or store” needs an explicit decision about whether the status condition applies to both channels. SQLite documents operator precedence, but a readable pair of parentheses is usually cheaper than a later argument about a report.

NULL needs a separate question

-- example: missing
SELECT order_id
FROM orders
WHERE amount_cents IS NULL
ORDER BY order_id;

The result is order 4. I use IS NULL to find missing values and IS NOT NULL to find known ones. Comparing amount_cents = NULL does not replace this check. An ordinary comparison with an unknown value does not produce the true condition needed to pass WHERE. This is a common reason for an unexpectedly empty result.

The zero in order 5 remains a valid number. I keep it separate from a missing amount. We can count all five completed orders, but we do not know their complete combined value. We only know the sum of the available amounts. I preserve that distinction in the metric name and its explanation.

COUNT, SUM and AVG answer different questions

-- example: totals
SELECT
    COUNT(*) AS orders_count,
    COUNT(amount_cents) AS known_amounts,
    SUM(amount_cents) AS known_total_cents,
    AVG(amount_cents) AS known_average_cents
FROM orders
WHERE status = 'completed';

The values are 5, 4, 40000 and 10000. There are five orders and four known amounts. The known total is 400 currency units, and the average of the known amounts is 100. COUNT with a column name ignores NULL. SUM and AVG also use the available values here, as described in the SQLite aggregate function reference.

I pay particular attention to the denominator of the average. The values 100, 200, 0 and 100 total 400 across four observations. Dividing by five would introduce a different assumption. I do not make that assumption simply to make the report look complete. Instead, I can display the missing amount count beside the result: one in this example.

GROUP BY changes what one result row represents

-- example: grouped
SELECT channel,
       COUNT(*) AS orders_count,
       COUNT(amount_cents) AS known_amounts,
       SUM(amount_cents) AS known_total_cents
FROM orders
WHERE status = 'completed'
GROUP BY channel
ORDER BY channel;

This produces two rows. Store has values 2, 2 and 20000. Web has 3, 2 and 20000. Each result row now represents a channel, although each source row represented an order. That change in detail is the central idea behind grouping. It also explains why I cannot casually add an order identifier to a channel summary.

In a grouped query, I select grouping columns or aggregate expressions. SQLite also permits some additional non-aggregated columns, but I would not build beginner reports around that behaviour. I want each cell to have an unambiguous meaning. The SELECT documentation explains these engine-specific details as well.

HAVING filters the resulting groups

-- example: having
SELECT channel, COUNT(*) AS orders_count
FROM orders
WHERE status = 'completed'
GROUP BY channel
HAVING COUNT(*) >= 3
ORDER BY channel;

Only web remains, with three orders. I first restrict the input to completed orders, group by channel and keep groups with enough orders. This is a helpful way to reason about the result. It is not a promise about the exact physical sequence of work inside the database engine.

I separate two questions: which records may enter the analysis, and which groups should appear in the report? The first leads to WHERE. The second often leads to HAVING. When I mix them together, I start moving conditions around instead of checking the definition. Writing the requirement in ordinary language is usually the quicker route out.

Sorting, ties and limiting output

-- example: largest
SELECT order_id, amount_cents
FROM orders
WHERE status = 'completed'
  AND amount_cents IS NOT NULL
ORDER BY amount_cents DESC, order_id
LIMIT 2;

The result is order 3 with 20000 and order 1 with 10000. Orders 1 and 6 are tied, so I add the identifier as a second sorting key. LIMIT returns two rows here. It does not return every order at the two highest distinct amount levels. Preserving ties would require a different definition and an appropriate query.

A limit is useful for inspecting a sample, but it does not make that sample representative. The two largest purchases deliberately show an extreme. I would not use them to describe an average customer. Similarly, a quick preview of a new table helps me understand its structure without proving that the entire dataset is clean.

Practise updates in a controlled transaction

-- example: rollback
BEGIN TRANSACTION;
UPDATE orders
SET amount_cents = 12000
WHERE order_id = 1;
SELECT order_id, amount_cents FROM orders WHERE order_id = 1;
ROLLBACK;

Inside the transaction, the amount becomes 12000. After ROLLBACK, reading order 1 again returns the original 10000. Run this only against the local practice table created above. SQLite transactions can commit changes with COMMIT or undo an active transaction with ROLLBACK.

Before UPDATE, I inspect a SELECT using the same condition. Omitting WHERE can update every row, as the UPDATE reference explains. DELETE removes rows; DROP TABLE removes the table. I do not need to try either on real business data to understand the distinction. Once a change is committed, a later ordinary ROLLBACK cannot magically undo that history.

Check whether the result makes sense

The channel counts add up: two plus three equals five completed orders. Their known totals add up to 40000, matching the overall summary. I also check the cancelled order explicitly. Its 5000 must not appear in these calculations. Several independent checks say much more about correctness than “the query executed successfully”.

Next, I vary the practice data. Adding another completed order with NULL should increase COUNT(*) while leaving the amount count and sum unchanged. With no matching rows, a global COUNT returns zero, while SUM and AVG return NULL. A grouped query may return no groups at all. These outcomes need different handling in a report.

For an exercise, count completed orders with a known amount of at least 10000. The expected identifiers are 1, 3 and 6, giving three orders and a sum of 40000. Then calculate the result by channel and explain why zero and NULL fail the condition for different reasons.

Syntax errors, empty results and wrong answers

I investigate these separately. A syntax error usually points to a fragment the engine could not parse. I return to the smallest working query, with one column and one table, then add elements individually. This reveals whether the problem appeared after a filter, an aggregate or a missing comma. Applying five guesses at once makes the cause harder to understand.

An empty result may be correct. A status absent from the table should produce no matching records. I inspect the actual source values and compare them with the condition, checking spelling, whitespace and what the labels mean. I do not broaden the filter merely because I expected to see something. That expectation is a reason to investigate, not evidence that matching data must exist.

A plausible wrong answer is harder to catch. Including the cancelled order would produce 45000 instead of 40000. Both values are positive and fit the column type. The engine cannot decide which one matches our definition of completed sales. I return to the identifiers and a manual calculation. With six rows, I can justify every contribution to the result.

When asking for help, I provide the query, the error message, the engine name and minimal data that reproduces the problem. I replace private information with synthetic examples. Another person can then run the same case. A screenshot of the final error often leaves out exactly the context needed to explain it.

Two valid reports can measure different things

Suppose another person reports six orders and a total of 45000. Before calling their SQL wrong, I ask about scope. They may intentionally include cancelled records. The disagreement would then concern the definition. I record the included statuses, amount units and handling of missing values next to the result so that people can compare reports without guessing.

Timing also matters. Our local table stays fixed except for the controlled exercise. In a live system, new orders can arrive and statuses can change. Queries executed at different times may disagree even when their logic is identical. To check consistency, I look for a shared reporting period and the same state of the underlying data.

I would not automatically call this sum company revenue. That concept may require rules for refunds, tax, accounting dates and other adjustments. This exercise calculates known amounts for completed orders. A precise, slightly longer label avoids assigning meaning that the table does not establish. A neat chart heading cannot replace a metric definition.

When sharing the result, I also include the number of orders without an amount. The recipient can see that 40000 sums the available values for five orders, one of which is missing its amount. They can decide whether that incompleteness affects their decision. Replacing the unknown with zero would hide useful information, even if it made the chart look tidier.

What to practise after the first working query

Once you can explain these results, move on to joining tables and then more advanced SQL queries. Keep checking row meaning, missing values and counts as you introduce each new feature. More elaborate syntax expands what you can do while making those checks even more useful.

Write down the result definition, the query and the expected numbers for a small sample. Tomorrow, you should be able to run the same material again and explain it to someone else. I find that a more useful demonstration of SQL basics than reciting a list of commands from memory. The aim is an answer that survives the next question.