SQL Next Steps

If you can already write SELECT, JOIN, GROUP BY, and subqueries comfortably, you know enough SQL to be dangerous in a good way — but there is a whole layer of the language that separates someone who can query a database from someone who can design, optimize, and reason about one at scale. This lesson is a map of what to learn next, why each topic matters, and runnable examples that give you a taste of each one so you know exactly where to go deeper.

Overview: What Comes After the Basics

Core SQL (SELECT/WHERE/JOIN/GROUP BY/aggregate functions) lets you answer “what happened.” The next tier of SQL lets you answer harder questions efficiently and safely: “what happened relative to each row’s neighbors” (window functions), “how do I build a multi-step, readable query” (CTEs), “how do I make several statements succeed or fail together” (transactions), “how do I make this fast on a million-row table” (indexing and query plans), and “how should this schema be shaped so it doesn’t corrupt itself” (normalization and constraints). Under the hood, the database engine treats all of these as extensions of the same query-processing pipeline you already know: it still resolves FROM, filters with WHERE, groups, and orders — window functions and CTEs just insert new stages into that pipeline, while indexes change how the engine’s query planner chooses to walk the table, and transactions wrap the whole thing in a guarantee about atomicity and durability.

None of these topics require learning a new language — they are all standard SQL (defined in the SQL:2003 and later standards) and are supported, with minor syntax differences, by SQLite, PostgreSQL, MySQL 8+, and SQL Server. Learning them here transfers directly.

Syntax

Here is the general shape of each next-step topic, so you recognize it when you see it in documentation:

Topic	General form	What it’s for
Window function	func(...) OVER (PARTITION BY col ORDER BY col)	Rank, rolling totals, and comparisons across rows without collapsing them with GROUP BY
CTE (Common Table Expression)	WITH name AS (SELECT ...) SELECT ... FROM name	Name an intermediate result set to build multi-step queries that read top to bottom
Recursive CTE	WITH RECURSIVE name AS (base SELECT UNION ALL recursive SELECT) SELECT ...	Walk hierarchies and graphs (org charts, category trees)
Transaction	BEGIN; ...statements...; COMMIT; or ROLLBACK;	Make multiple writes succeed or fail as one atomic unit
Index	CREATE INDEX idx_name ON table(column)	Let the query planner find rows without scanning the whole table

Examples

Example 1: Window functions for ranking within groups

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary REAL);
INSERT INTO employees VALUES
  (1, 'Alice', 'Engineering', 95000),
  (2, 'Bob',   'Engineering', 105000),
  (3, 'Carol', 'Sales', 72000),
  (4, 'Dave',  'Sales', 80000),
  (5, 'Eve',   'Marketing', 68000);

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
ORDER BY department, dept_rank;

Result:

name	department	salary	dept_rank
Bob	Engineering	105000	1
Alice	Engineering	95000	2
Eve	Marketing	68000	1
Dave	Sales	80000	1
Carol	Sales	72000	2

Unlike GROUP BY, PARTITION BY does not collapse rows — every employee still appears, but RANK() restarts at 1 for each new department. This is the key idea behind window functions: they compute a value across a group of related rows while keeping every row visible in the output.

Example 2: CTEs for readable multi-step queries

CREATE TABLE customers (customer_id INTEGER, customer_name TEXT);
CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, amount REAL);
INSERT INTO customers VALUES (1, 'Acme Corp'), (2, 'Globex'), (3, 'Initech');
INSERT INTO orders VALUES
  (1, 1, 250.00),
  (2, 1, 125.50),
  (3, 2, 90.00),
  (4, 3, 500.00),
  (5, 3, 75.25);

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.customer_name, ct.total_spent
FROM customer_totals ct
JOIN customers c ON c.customer_id = ct.customer_id
WHERE ct.total_spent > 100
ORDER BY ct.total_spent DESC;

Result:

customer_name	total_spent
Initech	575.25
Acme Corp	375.5

The WITH clause computes customer_totals once, gives it a name, and lets the outer query treat it like any other table. Globex is excluded because its total (90.00) doesn’t pass the outer WHERE filter. Without a CTE you would have needed a nested subquery in the FROM clause, which reads bottom-up instead of top-down.

Example 3: Transactions for atomic multi-statement writes

CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);
INSERT INTO accounts VALUES (1, 'Checking', 500.00), (2, 'Savings', 1000.00);

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

SELECT id, name, balance FROM accounts ORDER BY id;

Result:

id	name	balance
1	Checking	300
2	Savings	1200

Both UPDATE statements happen inside one transaction. If the second update failed for any reason, ROLLBACK would undo the first one too, so the $200 could never vanish or be duplicated. This all-or-nothing guarantee is one quarter of what’s called ACID (Atomicity, Consistency, Isolation, Durability), a property every serious production database relies on.

How It Works Step by Step

These next-step features all plug into the same logical query-processing order you already learned for basic SELECTs — they just add new stages:

• CTEs run first. The engine evaluates the WITH block (materializing it or inlining it, depending on the optimizer) before the outer query even starts, which is why the outer query can reference it like a table.
• FROM and JOIN build the working row set, same as always.
• WHERE filters rows — and this is exactly why window functions cannot be referenced here (see Common Mistakes below): window functions haven’t been computed yet at this stage.
• GROUP BY and HAVING aggregate and filter groups, if present.
• Window functions evaluate next, after grouping/filtering but before the final column list is fixed — this is why they can “see” the filtered, grouped rows but still return one output row per input row.
• SELECT assembles the final column list, including any window function results.
• ORDER BY and LIMIT run last, sorting and trimming the final result.

Transactions work orthogonally to this pipeline: everything between BEGIN and COMMIT is written to a temporary log first. Only when COMMIT succeeds does the engine make those changes visible and durable; a crash or ROLLBACK before that point leaves the database exactly as it was.

Common Mistakes

Mistake 1: Filtering on a window function in WHERE

SELECT name, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk = 1;

This fails because, per the processing order above, WHERE runs before window functions are computed — the column rnk doesn’t exist yet at that stage, so SQLite raises a “no such column: rnk” error. The fix is to compute the window function in a CTE (or subquery) first, then filter the outer query:

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary REAL);
INSERT INTO employees VALUES (1, 'Alice', 95000), (2, 'Bob', 105000), (3, 'Carol', 72000);

WITH ranked AS (
  SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary FROM ranked WHERE rnk = 1;

Result: one row — Bob, 105000. Wrapping the window function in a CTE lets it finish computing before the outer WHERE filters on its result.

Mistake 2: Nesting transactions with BEGIN instead of SAVEPOINT

BEGIN TRANSACTION;
BEGIN TRANSACTION;
COMMIT;

SQLite (and most databases) only allow one active top-level transaction at a time; the second BEGIN raises “cannot start a transaction within a transaction.” If you need nested, partially-undoable steps inside a larger transaction, use SAVEPOINT instead:

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance REAL);
INSERT INTO accounts VALUES (1, 500.00);

BEGIN TRANSACTION;
SAVEPOINT adjust;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
RELEASE adjust;
COMMIT;

SELECT * FROM accounts;

Result: one row — id 1, balance 450. SAVEPOINT creates a named point you can roll back to with ROLLBACK TO adjust without abandoning the whole outer transaction.

Best Practices

• Learn window functions before you reach for a self-join or a correlated subquery to compute rankings or running totals — they are almost always faster and clearer.
• Prefer CTEs over deeply nested subqueries once a query has more than two logical steps; they read top-to-bottom and are far easier for the next person (including future you) to debug.
• Wrap every multi-statement write (transfers, batch updates, anything that touches more than one table) in an explicit transaction — don’t rely on your database driver’s default autocommit behavior for related writes.
• Add an index on any column you filter or join on frequently, but don’t over-index — every index speeds up reads and slows down writes, so measure before adding one to a write-heavy table.
• Use EXPLAIN QUERY PLAN (SQLite) or EXPLAIN ANALYZE (PostgreSQL/MySQL) to see whether your query is actually using an index or falling back to a full table scan.
• Normalize your schema to at least third normal form (each non-key column depends on the key, the whole key, and nothing but the key) before you optimize anything else — most performance and data-integrity bugs trace back to a bad schema, not a bad query.
• Practice on a real, messier dataset once tutorials feel easy — public datasets (government open data, Kaggle, sample databases like Chinook or Sakila) expose edge cases that clean textbook tables never will.

Practice Exercises

• Exercise 1: Using the employees table from Example 1, write a query that adds a column showing each employee’s salary rank company-wide (no PARTITION BY) using DENSE_RANK() instead of RANK(). What’s the difference between the two when two employees tie on salary?
• Exercise 2: Using the orders and customers tables from Example 2, write a CTE that computes each customer’s average order amount, then select only customers whose average is below 150.
• Exercise 3: Write a transaction that moves 100 from the Savings account to the Checking account in the accounts table from Example 3, then deliberately trigger a ROLLBACK instead of COMMIT. Query the table afterward — what do you expect the balances to be, and why?

Summary

• Window functions (OVER (PARTITION BY ... ORDER BY ...)) compute values across related rows without collapsing them, unlike GROUP BY.
• CTEs (WITH) name intermediate result sets so multi-step queries read top-to-bottom instead of nesting subqueries.
• Window functions run after WHERE/GROUP BY/HAVING in the logical processing order, which is why you can’t filter on one directly in WHERE.
• Transactions (BEGIN/COMMIT/ROLLBACK) make multiple writes succeed or fail together, guaranteeing atomicity; SAVEPOINT handles nested steps inside one transaction.
• Indexes speed up reads by letting the query planner avoid full table scans, at the cost of slower writes — check real query plans, don’t guess.
• A well-normalized schema prevents more bugs than any query optimization ever will — learn schema design alongside advanced querying.
• The fastest way to solidify all of this is practicing on real, imperfect datasets rather than only clean tutorial tables.