Keentune

SQL curriculum

32 chapters
·
323 concepts
·
free
Everything the adaptive question bank can teach and test in SQL, from foundations through advanced practice. Work through it in order, or start practising and let the questions find your level.
New here? Read the SQL guide
A free 17-minute primer — the mental model, the mistakes beginners make, and what to practise first.
A. The relational model and what a query is
•
a table is a bag of rows; order is never implied
•
a primary key identifies a row; position never does
•
SQL keeps duplicates unless you ask it not to
•
you state the result, the planner chooses the path
•
FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
•
the SELECT list chooses output columns while FROM chooses the row source
•
why a SELECT alias is unusable in WHERE
•
TRUE, FALSE and UNKNOWN, and what UNKNOWN does to a filter
•
unquoted identifiers fold case; quoted ones do not
•
search_path, and why an unqualified name is ambiguous
•
which statements change data, structure, or neither
B. NULL and the three-valued logic that follows
•
= NULL is never true; only IS NULL tests it
•
any arithmetic with NULL yields NULL
•
aggregates skip NULLs, and COUNT(*) does not
•
the difference the NULLs make
•
a NULL inside NOT IN silently empties the result
•
substituting a value, and manufacturing a NULL
•
NULLS FIRST and NULLS LAST, and each engine's default
•
several NULLs can coexist in a UNIQUE column
•
NULLs form one group, unlike in a join
•
and the Oracle exception that trips people
C. Filtering with WHERE
•
and why <> and != are the same
•
AND binds tighter than OR, and the parenthesis bug
•
both endpoints are included
•
semantics, and when each is the clearer statement
•
percent and underscore, and escaping them
•
case-insensitive matching, and its portability
•
the tilde operator and SIMILAR TO versus LIKE
•
wrapping a column in a function defeats its index
•
comparing text to a number, and what the planner does
•
testing a boolean without comparing it to TRUE
D. Sorting, paging and DISTINCT
•
ASC is the default; sorting is the last logical step
•
sorting by a computed value or an ordinal
•
how ties are broken, in written order
•
a LIMIT with no ORDER BY returns arbitrary rows
•
OFFSET reads and discards; it does not skip
•
paging by the last seen key instead
•
DISTINCT is not a function of one column
•
PostgreSQL's per-group first row, and its ORDER BY requirement
•
text ordering depends on collation, not on byte order
•
why the same query can return a different order
•
an operating-system collation update silently invalidates a text index
•
case-insensitive and accent-insensitive ordering, and what it forbids
E. Aggregation and grouping
•
every non-aggregated column must be grouped
•
WHERE filters rows, HAVING filters groups
•
collapses the whole table to one row
•
and why it is more expensive than COUNT
•
SUM returns NULL, COUNT returns zero
•
the integer-truncation trap
•
ordering rules apply, not length
•
conditional aggregation without CASE
•
string_agg, array_agg, and ordering inside them
•
several groupings in one pass
•
grouping by a primary key makes the table's other columns unambiguous
F. Joins
•
the matched intersection
•
keeping unmatched rows, and which side
•
and how to find rows unique to each side
•
deliberate, and accidental
•
a WHERE on the outer side silently makes it inner
•
a table joined to itself, and why aliases become mandatory
•
NOT EXISTS versus LEFT JOIN with an IS NULL test
•
EXISTS, and why it does not duplicate rows
•
a one-to-many join inflates aggregates
•
the shorthand, and why NATURAL is dangerous
•
a subquery that can reference the row to its left
•
written order is not execution order
G. Subqueries and CTEs
•
must return one row and one column
•
re-evaluated per row, and the cost
•
quantified comparison, and the NULL trap in each
•
why SELECT 1 inside EXISTS is idiomatic
•
a subquery in FROM, and its mandatory alias
•
WITH, for naming a step rather than nesting it
•
when a CTE is an optimization fence
•
the anchor, the recursive term, and termination
•
how a graph query avoids looping forever
•
scope, reuse and visibility
H. Window functions
•
aggregating without collapsing rows
•
restarting the calculation per group
•
why it changes a SUM into a running total
•
how each treats a tie
•
reaching to the previous or next row
•
and the frame that makes LAST_VALUE surprising
•
counting rows versus counting peer values
•
the default is RANGE UNBOUNDED PRECEDING, not the whole partition
•
EXCLUDE removes the current row, its peer group, or its ties from an otherwise-defined frame
•
bucketing, and its uneven remainder
•
when each is the right tool
•
reusing one definition across several functions
•
you cannot filter on a window result in WHERE
I. Set operations
•
the deduplication, and what it costs
•
and their duplicate-handling rules
•
what makes two queries union-compatible
•
one ORDER BY, at the end
•
combining rows versus combining columns
•
INTERSECT binds tighter than UNION and EXCEPT
•
the difference that survives a NULL on either side
•
UNION pairs NULL with NULL, unlike =
•
a literal table, usable anywhere a SELECT is
•
why a recursive CTE rarely deduplicates on every pass
J. Data types
•
smallint, integer, bigint, and what overflows
•
exact decimal versus binary approximation
•
why float is the wrong type for currency
•
padding, limits, and which to reach for
•
and the strings each engine accepts
•
what each stores, and what it does not
•
one stores an instant, the other a wall clock
•
adding time, and month-length ambiguity
•
generated keys, and the sequence behind them
•
randomness, index locality, and ordering
•
the migration cost of each
•
explicit CAST, the double-colon form, and implicit promotion
•
NUMERIC(p,s) rounds on write, and errors when it cannot
•
why = on a float is a bug, and where NaN sorts
•
a span stored as one value, with inclusive and exclusive bounds
•
a row type held in one column, and the parentheses its fields need
K. Expressions and functions
•
searched and simple forms, and the ELSE default
•
length, substring, position, trim
•
concatenating with a NULL operand yields NULL
•
date_trunc and EXTRACT
•
statement time versus clock time
•
round, trunc, ceil, floor, and half-even
•
and how to force a fractional result
•
the sign of the result across engines
•
row-wise, unlike MAX and MIN
•
producing rows from nothing
L. Modifying data
•
VALUES, multi-row, and INSERT from a SELECT
•
reading back what you just wrote
•
the FROM clause, and the dialect differences
•
the whole-table accident
•
logging, triggers, rollback and speed
•
DO NOTHING versus DO UPDATE, and the arbiter
•
the standard form, and its version gate
•
one statement versus many round trips
•
what DEFAULT means at insert time
•
stored versus virtual
M. Constraints and integrity
•
uniqueness plus NOT NULL, and one per table
•
and its NULL behavior
•
referential integrity, and what it prevents
•
CASCADE, RESTRICT, SET NULL, NO ACTION
•
a row-level invariant enforced by the database
•
the same rule, two costs
•
checking at commit instead of per statement
•
and the column order that matters
•
stability versus meaning
•
the optimizer reads them
•
a named type that carries its own constraint into every table
•
"no two bookings overlap", enforced by an index
•
a CHECK that UNKNOWN quietly passes
•
adding a constraint without locking out writers
N. Indexes
•
what an index actually stores
•
selectivity, and the sequential-scan choice
•
the leftmost-prefix rule
•
INCLUDE, and the index-only scan
•
indexing the rows you actually query
•
indexing a lowercased column so the predicate is sargable
•
the same enforcement, different declaration
•
every index is paid for on INSERT and UPDATE
•
full-text, arrays and ranges
•
why an index degrades
•
equality only, and the narrow case where it beats a B-tree
•
tiny, and useful only when physical order tracks the values
•
an index serves only the operators its class declares
•
building without blocking writes, and the invalid index it can leave
•
DESC and NULLS FIRST in the definition, so the sort disappears
O. Transactions and concurrency
•
what each letter actually guarantees
•
and what an implicit transaction is
•
read committed, repeatable read, serializable
•
and why no PostgreSQL level permits it
•
the same row changing mid-transaction
•
the same predicate matching new rows
•
and the read-modify-write that causes it
•
disjoint writes can jointly break an invariant under snapshot isolation
•
PostgreSQL tracks rw-antidependencies and aborts a transaction when they can form a serialization cycle
•
pessimistic locking, and its scope
•
version columns instead of locks
•
how two transactions trap each other, and lock ordering
•
readers do not block writers
•
bloat, and blocked vacuum
•
partial rollback inside a transaction
•
which engines allow it
P. Query planning and performance
•
an estimate versus a measurement
•
nodes, costs, rows, and the actual-versus-estimate gap
•
and when the sequential scan is correct
•
the three join strategies and their conditions
•
the planner's row estimates come from somewhere
•
the application-side query multiplication
•
bandwidth, and the lost index-only scan
•
the sargability failure, seen in a plan
•
why a fast LIMIT query slows without it
•
trading freshness for query cost
•
EXPLAIN (ANALYZE, BUFFERS), and the cache hit that hides the cost
•
actual rows are per loop; multiply before you panic
•
a wrong estimate low in the tree picks the wrong join above it
•
correlated columns, and the CREATE STATISTICS that repairs the estimate
•
workers and the gather node, and what silently disables them
•
a prepared statement that stops re-planning for your parameter
Q. Security
•
the mechanism, in terms of parsing
•
why binding is not escaping
•
GRANT, REVOKE, and role separation
•
a policy the database enforces, not the app
•
and where that boundary leaks
•
the privilege they run with
•
building identifiers safely
•
what a statement log does and does not capture
•
a role is both a user and a group; INHERIT and SET ROLE
•
ALTER DEFAULT PRIVILEGES, for the tables that do not exist yet
•
what every role can already do before you grant anything
•
the rows a policy lets you read, and the rows it lets you write
•
policies OR together unless they are declared restrictive
•
the owner is exempt until FORCE ROW LEVEL SECURITY says otherwise
•
the pinned path that stops the function being hijacked
•
data stored safely today, concatenated into SQL tomorrow
R. Schema design and normalization
•
atomic values, and the comma-separated-list smell
•
partial and transitive dependencies
•
read speed against write complexity
•
the foreign key goes on the many side
•
and its composite key
•
absent, unknown, or not applicable
•
and what it does to every subsequent query
•
created_at, updated_at, and who set them
•
why it defeats the type system and the planner
•
range and list, and the pruning it enables
S. JSON and semi-structured data
•
text fidelity versus a parsed binary form
•
the arrow operators, and the type each returns
•
jsonpath, and containment
•
GIN, and indexing one extracted expression
•
when a schema is the better answer
•
@> and <@, and the index that can serve them
•
?, ?| and ?&, and why they only see the top level
•
jsonb_array_elements and jsonb_each, called in FROM
•
jsonb_build_object, jsonb_agg and row_to_json
•
jsonb_set, concatenation, and removing a key
•
key order, duplicate keys and whitespace do not survive the parse
•
a JSON null is a value; a missing key is something else again
•
@? and @@, and a predicate written inside the path
T. Views, functions and procedural extensions
•
a stored query, not stored data
•
the conditions, and INSTEAD OF triggers
•
and the concurrent option
•
volatility categories and why they matter
•
BEFORE, AFTER, and the row-versus-statement distinction
•
the debugging cost of invisible logic
•
DROP ... CASCADE, and the column you can no longer alter
•
you may append columns, never retype or reorder them
•
WITH CHECK OPTION, so a write cannot escape the view it went through
•
a view costs exactly what its query costs, and nesting hides that
•
a matview carries indexes; a plain view cannot
U. Dialect differences and portability
•
the standard form and the common one
•
the double-pipe operator versus plus versus CONCAT
•
double quotes, backticks and brackets
•
SERIAL, IDENTITY, AUTOINCREMENT
•
ON CONFLICT versus ON DUPLICATE KEY
•
dynamic typing, and what it accepts
•
engines without a native boolean
•
the same operation, different spellings
•
an engine whose default collation ignores case, and one that does not
•
an engine that once invented a value for an ungrouped column
•
reading back a write, and the last-insert-id fallback where it is missing
•
CTEs and window functions arrived late outside PostgreSQL
•
the portable information_schema against each engine's own catalog
•
one engine coerces silently where another raises
V. Full-text search
•
a document reduced to lexemes, and a query written over them
•
the language configuration decides stemming and stop words
•
turning a raw search box into a query without a syntax error
•
a match is a boolean; relevance is a separate computation
•
GIN against GiST, and the generated column that stores the vector
•
fuzzy and misspelled matching, where stemming cannot help
W. Arrays
•
the constructor, the braced literal, and one-based indexing
•
the array form of IN, and why it takes a single parameter
•
contains, is-contained-by, and shares-any-element
•
an array turned back into rows, keeping its position
•
when a normalised table is the better answer
X. Dates, times and time zones
•
converting an instant to a wall clock, and a wall clock to an instant
•
one stored value rendered differently per connection
•
the local time that never happened, and the one that happened twice
•
date minus date is a number; timestamp minus timestamp is an interval
•
>= start AND < end beats BETWEEN once a time component exists
•
a daily report belongs to someone's midnight, not to UTC
•
to_char and to_timestamp patterns, and their locale exposure
Y. Sequences, identity and generated keys
•
a rollback does not hand the value back
•
session scope, and the reset a bulk load has to do
•
GENERATED ALWAYS, and the insert it refuses
•
per-session blocks, and why the gaps are not a defect
•
an invoice number needs a different mechanism entirely
Z. Partitioning and inheritance
•
one logical table, many physical ones
•
a query that cannot prune reads every partition
•
the three strategies, and the data each one suits
•
adding next month, and dropping last year in constant time
•
a unique constraint has to contain the partition key
•
the older mechanism, ONLY, and what it does not enforce
AA. Locking, blocking and retry
•
what a plain UPDATE takes, and who ends up waiting behind it
•
the lock levels, and which pairs actually conflict
•
one ALTER TABLE queued behind a long query stops everything
•
a work queue that does not stampede
•
application-level mutual exclusion, session or transaction scoped
•
deadlock and serialization failures are the caller's to handle
AB. MVCC, physical storage and vacuum
•
an UPDATE writes a new version and hides the old one
•
an update touching no indexed column can stay on its page
•
why an index-only scan can still have to visit the heap
•
space marked reusable, not space handed back to the filesystem
•
the exclusive lock it takes, and the alternative
•
why the defaults never keep up with a hot table
•
the freeze that must happen, and the shutdown if it does not
AC. Server-side routines and error handling
•
CALL, and the transaction control only a procedure has
•
RETURNS TABLE, and calling one in FROM
•
DECLARE, BEGIN, EXCEPTION, END, and nesting them
•
the loop that is slow because it catches
•
catching one class of error instead of everything
•
the return type, the row variables, and the operation name
•
a BEFORE trigger returning NULL cancels the row
AD. Sessions, connections and server settings
•
a setting that outlives your statement, and one that does not
•
a query with a deadline, and where the deadline belongs
•
session against transaction pooling, and what each one breaks
•
invisible to everyone else, and dropped at the end
•
streaming a large result instead of buffering all of it
AE. Backup, restore and replication
•
a dump of statements against a copy of the files
•
every change written twice, and why that is both faster and safer
•
a base backup plus the log replayed to a chosen moment
•
a byte-identical replica, and the lag you can actually measure
•
per-table and cross-version, and what it does not carry
AF. Extensions and foreign data
•
what it adds, the schema it lands in, and the upgrade path
•
another database queried as if it were a local table
•
the filter that travels, and the join that does not
•
comparison folded by the type, not at every call site
Keentune is not affiliated with or endorsed by the organizations whose documentation informs these maps.
All about SQL practice
Also on your phone
App StoreGoogle Play
All exam, test, and product names and trademarks are the property of their respective owners and are used here for identification and reference only. Keentune is independent study practice — not affiliated with, authorized, or endorsed by any of these organizations.
© 2026 SportaApp LLC