Most beginners think SQL is a language you learn once: SELECT, JOIN, WHERE, GROUP BY — and you're done. Then they open a real database with a few million rows, write the same query they practised on a toy table, and watch it hang for forty seconds. That gap — between syntax you know and behaviour you can predict — is the whole subject of SQL & Databases, and it's where the interesting work lives.
A relational database is not a filing cabinet that stores what you put in. It's a query planner that decides how to fetch your data, and that decision depends on statistics, indexes, table sizes and the shape of your WHERE clause. You write a declarative statement; the engine compiles it into a plan. Understanding that translation is what separates someone who "knows SQL" from someone who can work with databases.
Below is a walkthrough of how practitioners actually solve a real problem, where the scheme itself breaks, and which learning routes exist — with honest costs.
One query, three plans: a walkthrough
Say you have an orders table with 2,000,000 rows and a customers table with 50,000 rows. The task: list each customer's total spend for the last 90 days, only for customers in a given country, sorted by spend.
A beginner writes this:
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'DE'
AND o.created_at > NOW() - INTERVAL '90 days'
GROUP BY c.name
ORDER BY total DESC;
It works. On a test database with 500 rows it returns instantly. On production it takes 40 seconds. Here is the order in which a practitioner investigates.
Step 1 — read the plan, not the query. EXPLAIN ANALYZE shows what actually happened: which table was scanned, how many rows the planner expected versus how many it got, and where time went. A Seq Scan on 2,000,000 rows is the usual verdict. The planner chose it because either no index exists on orders.created_at, or it exists but the planner estimated the filter is not selective enough to bother.
Step 2 — check the index, then check the statistics. An index on created_at alone helps, but the query filters on date and joins on customer_id. A composite index on (customer_id, created_at) lets the engine fetch only the relevant slice per customer. This is the non-obvious part: index column order matters. (customer_id, created_at) and (created_at, customer_id) are different tools. The first serves lookups by customer within a date range; the second serves a scan across all customers in a date range. If the planner's row estimates are wildly off, ANALYZE refreshes statistics and can flip the plan entirely.
Step 3 — question the shape. Grouping by c.name is a quiet bug: two customers with the same name merge into one row. Group by c.id and select the name alongside. This is exactly the kind of mistake that produces plausible-looking numbers, which is worse than an error.
What breaks if you skip these steps? You add indexes everywhere, the writes slow down because every INSERT now maintains five indexes, and the original query still does a sequential scan because the planner never uses an index it considers unhelpful. Indexes are a trade: you pay on writes and storage to gain on reads, and a careless index is a tax with no benefit.
Where the mechanism itself breaks
These are not beginner mistakes. They are cases where the rules of thumb stop applying.
1. The index exists but the planner ignores it
What goes wrong: you added the index, but the query still scans. Common causes: a function wraps the column (WHERE DATE(created_at) = ... cannot use an index on created_at), an implicit type cast changes the comparison, or the statistics are stale and the planner believes the filter matches most of the table — in which case a scan genuinely is cheaper.
How to spot it early: EXPLAIN before and after any index change. If the plan did not change, the index is not being used, and you should find out why before adding another one.
If it's already too late: rewrite the predicate so the column stands alone (created_at >= ... AND created_at < ... instead of a function on it), then re-run ANALYZE. Dropping unused indexes is a legitimate fix, not an admission of failure.
2. The query is fast but the data is wrong
What goes wrong: a LEFT JOIN with a filter on the right table in the WHERE clause silently becomes an inner join, dropping rows you expected to keep. Aggregates over joined rows double-count when one order has several line items.
How to spot it early: compare row counts at each stage — before the join, after the join, after the group. If the count jumps unexpectedly, the join is fanning out.
If it's already too late: the damage is silent and lives in reports. The fix is a reconciliation query: total from the source table versus total from the aggregated query, run side by side.
3. Transactions that hold locks longer than you think
What goes wrong: a long-running transaction — a migration, a batch update, a forgotten open session — holds locks and blocks writes. Under load, requests queue, and the queue grows faster than it drains.
How to spot it early: monitor lock waits and transaction age. A transaction open for minutes in an OLTP system is a signal, not a curiosity.
If it's already too late: batch large updates into chunks, keep transactions short, and know your isolation level — the default in PostgreSQL is Read Committed, which behaves differently from what many people assume when they reason about concurrent updates.
4. Schema design that was fine at 10,000 rows
What goes wrong: denormalised data that made early queries simple drifts out of sync. A column duplicated across tables updates in one place and not another. Normalisation is not academic tidiness — it is the mechanism that makes a single fact live in a single place.
How to spot it early: if you ever write the same value into two tables by hand, you have a future inconsistency.
If it's already too late: migrations to fix it are costly but mechanical — extract the fact, backfill, add a constraint so the old path cannot recur.
Learning paths and what each one costs
There are three realistic routes, and they differ less in content than in feedback.
| Path | Time | Money | What you actually get |
|---|---|---|---|
| Self-study from docs and tutorials | Months, uneven | Near zero | Syntax and recipes; no one tells you your query is wrong or your design will not scale |
| Practice on real projects | Months, tied to job | Free but slow | Real failure modes — but mistakes are expensive and you learn reactively |
| Structured course | Weeks to a few months | Moderate | A sequence, exercises with instant checking, and someone to ask when the plan surprises you |
The honest trade-off: self-study is cheapest and works if you already have a project with real data and someone to review your decisions. Practice is the best teacher but rarely arrives on schedule. A course buys structure and feedback — the two things self-study cannot supply on its own, because you cannot correct a misconception you do not know you have.
A structured route also forces you through the boring-but-decisive parts: transactions, isolation levels, normalisation, window functions. These are the topics people skip until production teaches them at the worst possible moment. The SQL & Databases course is built around this sequence — PostgreSQL and MySQL, schema design, query optimisation through indexes and window functions — and the lessons adapt to your current level, so you are not re-reading JOIN syntax you already know.
If you are weighing a broader technical direction, our piece on Kotlin prompts for Android development shows a similar principle: tools accelerate you only after you understand what they are doing. The same holds for query planners.
Who this suits and who it does not
This path fits analysts, backend developers, QA engineers and product people who already touch data and want to stop guessing. If you can write a JOIN but cannot explain why a query is slow, you are exactly the target audience.
It does not fit people who want a purely theoretical database course on relational algebra, or those hoping SQL will be a weekend skill. The language takes days; the judgement about plans, indexes and transactions takes weeks of deliberate practice.
Databases reward the person who can reason about what happens underneath. Learn the syntax, then learn the planner, then learn where the plan lies to you. The course walks through these calculations with examples and gives feedback on your query decisions — details and enrolment are on the SQL & Databases page. Start with one slow query from your own work and explain it line by line.