SQL Best Practices and Where to Go Next

In the SQL for Beginners series you have come a long way — from your first SELECT to joins, CTEs, window functions, indexes, views, transactions, a normalized schema and SQL running behind an application. This final chapter of the 24-chapter course introduces no new syntax. It does two jobs instead: collecting the habits that separate SQL that merely runs from SQL you still trust a year from now, and pointing at where to go once this series ends.

The arc of the series

Everything you have learned fits five verbs, and each one earned its own band of chapters:

1. Read — SELECT, WHERE, sorting and grouping: ask precise questions of one table (chapter 3 onward).
2. Change — inserts, updates, deletes, then the types, constraints and keys that make the changes trustworthy (chapter 7).
3. Combine — joins, subqueries, set operations, CTEs and window functions: answers that span tables (chapter 10 onward).
4. Reshape — string, date and CASE expressions: the last mile from stored values to something a human can read (chapter 17).
5. Operate — indexes, views, transactions, design and delivery: keeping the database fast, correct and reproducible (chapter 19 onward).

When a new problem arrives, name the band first. “This report is wrong” is a Combine or Reshape question; “this report is slow” is Operate; “we stored the same thing twice” is Design. Half of consulting is knowing which shelf to pull from.

A pattern shortlist of defaults

Most day-to-day style advice is a default you can choose once and stop thinking about. These are the seven worth hard-wiring:

The first row deserves a worked example, because SELECT * is the habit that breaks earliest. Both statements below answer “the two customers in India”; only the second one says what it wants:

SELECT * FROM customers WHERE country = 'India';
-- drags email and signup_date along for the ride

SELECT id, first_name, last_name, city
FROM customers
WHERE country = 'India'
ORDER BY id;

Expected output: (the second statement)

1|Aarav|Sharma|Mumbai
2|Priya|Nair|Delhi

What to avoid

Two failures account for most of the damage a beginner does with SQL. The first is asking for a column that is neither grouped nor aggregated. It looks harmless, and SQLite will even answer you — by picking one row from each group and inventing the rest:

SELECT country, first_name, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY country;

Expected output: (SQLite’s pick from each group)

Canada|Marc|1
Germany|Lena|1
India|Aarav|2
Kenya|Amara|1
Singapore|Wei|1

Every count is right and every name is a guess: India says Aarav although Priya is from India too, and reading a value SQLite never promised to choose for you is how dashboards quietly start lying. MySQL, in its default ONLY_FULL_GROUP_BY mode, refuses to run the statement at all. Fix it by grouping the column or aggregating it on purpose:

SELECT country,
       GROUP_CONCAT(first_name, ', ') AS members,
       COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY country;

Expected output:

Canada|Marc|1
Germany|Lena|1
India|Aarav, Priya|2
Kenya|Amara|1
Singapore|Wei|1

The second failure is a write without a filter: UPDATE customers SET city = 'Pune'; or DELETE FROM orders; committed in autocommit mode takes the whole table in one keystroke, with no undo. The habit from chapter 21 survives every engine: restate the same WHERE as a SELECT COUNT(*) first, wrap the write in a transaction, read the affected row count, and only then commit.

Measure, then optimise

The fastest way to ruin a working query is to optimise it from a feeling. The playbook is the one from chapter 19, in order: read the plan, add the index the plan asks for, shorten the transaction, and only then consider changing the design. Start by letting the engine disagree with you — here a full scan is simply the correct answer for six rows ordered by rowid:

sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM customers ORDER BY id;"

Expected output:

QUERY PLAN
`--SCAN customers

No temp B-tree, no index, no work worth doing — and that is a conclusion, not a guess. Keep the same discipline about your own time: a query you have never seen run slowly is not a slow query, and a report that runs once a night does not need the index that costs your checkout path on every write.

The tooling is as small as the playbook. Five commands cover most of a working day with SQLite, and each one has a server-side twin:

• EXPLAIN QUERY PLAN — the route the engine will take, before it takes it (chapter 19).
• .schema and sqlite_master — what the database really contains: tables, views and indexes, definitions included.
• .backup file.db — a consistent copy of a live database in one command.
• mysqldump and pg_dump — the same insurance for a server database, in plain SQL you can read.
• Migrations — php artisan migrate in Laravel, or numbered SQL files with a bookkeeping table anywhere else (chapter 23).

One query that uses half the series

Here is a closing example worth keeping: a CTE from chapter 15, an aggregate from chapter 6, a scalar subquery from chapter 13 and rounding from the numeric functions of chapter 18, all answering one question — how is the customer base distributed?

WITH country_totals AS (
    SELECT country, COUNT(*) AS customers
    FROM customers
    GROUP BY country
)
SELECT country,
       customers,
       ROUND(100.0 * customers / (SELECT SUM(customers) FROM country_totals), 1) AS pct
FROM country_totals
ORDER BY customers DESC, country;

Expected output:

India|2|33.3
Canada|1|16.7
Germany|1|16.7
Kenya|1|16.7
Singapore|1|16.7

The percentages add to 100.1 rather than 100.0, because each row rounds on its own — a small reminder that SQL will happily give you precise answers to well-posed questions, and that noticing the last decimal is your job.

Rules worth keeping

If this chapter reduces to a list on the wall, make it this one:

• Name the shape, not the moment — a view for a query you will ask twice, a constraint for a rule you will need on every write, a migration for every structural change.
• Placeholders for values, whitelists for identifiers — no exceptions, no clever escaping, no “it’s only an internal tool”.
• Read the plan before you touch the index — SCAN is a question, not a verdict; answer it with evidence.
• Every multi-statement write is one transaction — autocommit is for single statements and for the statements outside the transaction.
• Store each fact once — denormalize later, deliberately, with a measurement that says it was worth the copies.

Where to go next

The best way to keep learning is to build something that needs a skill you do not have yet: a sales report over a schema you design yourself, a REST API that serves customers and orders with placeholders and migrations, or a copy of this shop data moved into PostgreSQL so you can try its EXPLAIN (ANALYZE, BUFFERS) and its materialized views. Because Scriptly teaches the rest of the stack too, four tracks pair naturally with what you now know. If you want to put a real server in front of your SQL, start Node.js for Beginners — the driver calls, transactions and N+1 warnings from chapter 23 translate line for line. If you want the browser to be type-safe about the data you send it, TypeScript for Beginners will feel familiar. The same goes for the interface in React for Beginners, and for keeping the whole history reviewable in Git and GitHub for Beginners. Everything else — and the full list of tracks — lives on the tutorials index.

Three shapes of project fit what you have just learned:

• A reporting task — a schema you design from scratch, five queries that genuinely need a CTE or a window function, and a plan you read and paste into the commit message.
• A small API — endpoints over customers and orders where every statement is parameterized, every write is transactional and the schema ships as migrations.
• A move — port the shop from SQLite to PostgreSQL and discover exactly which of your habits were engine-specific.

Practice checklist

1. Take a schema you designed before this series and test it: does any column depend on something other than the key?
2. Find your three slowest queries, print their plans, and add only the indexes the plans ask for.
3. Refactor one string-built query into a prepared statement and prove it shrugs off the ' OR '1'='1 payload.
4. Wrap your riskiest multi-statement write in a transaction and deliberately break the last statement to watch it all roll back.
5. Build one feature end to end — migration, indexes, views, parameterized queries, tests — and keep the plan outputs in the commit message.

What's next?

Congratulations — you have finished all 24 chapters of SQL for Beginners, from your first SELECT to indexes, transactions, normalization and application code. Keep the momentum going on the tutorials index, where every other track now plugs into skills you share: placeholders, transactions, versioned changes and the habit of asking the database for evidence.