skip to content

Relational / SQL

The relational tier, in two halves: `db-relational-concepts` holds everything true of every SQL engine — the model, schema and constraints, transactions and isolation, indexes, the planner, storage and recovery, replication, partitioning and access control — and the engine subtrees beside it hold what is true of one product only. A generic roadmap references the concepts child; a roadmap that names an engine references that engine.

on this pageshow

explore

questions

1 087 · 5 sections

What does a CHECK constraint do in a relational table, and at what point does the database engine evaluate it?

level: juniorimportance: must knowfreq 60%
basics
~20 s

A CHECK constraint is a boolean condition attached to a table. The engine tests it for every row a statement inserts or updates; if the condition comes out false, the statement fails with a constraint-violation error and the row is not stored.

open as a page

If the application already validates every field before saving, why still declare constraints such as NOT NULL, unique keys and foreign keys in the database schema?

level: juniorimportance: must knowfreq 58%
basics
~20 s

Because the application is not the only writer, and its checks are not atomic with the write. Scripts, migrations, other services and future bugs all reach the same tables. Database constraints are the last line of defense that no code path can bypass; app validation exists for fast, friendly feedback.

open as a page

A column in one table references the key of another table under a foreign key constraint. What exactly does that constraint guarantee about the stored data, and what happens if the referencing column holds NULL?

level: juniorimportance: must knowfreq 78%
basics
~20 s

It guarantees every non-NULL value in the referencing (child) column exists in the referenced (parent) key, so there are no orphan rows. A NULL referencing value points at nothing, so the check is skipped and the row is accepted.

open as a page

A table column is declared NOT NULL with a DEFAULT value. Explain the difference between an INSERT that omits that column entirely and one that explicitly supplies NULL for it.

level: juniorimportance: must knowfreq 62%
basics
~20 s

Omitting the column makes the engine apply the DEFAULT, so the insert succeeds. Explicitly writing NULL is a real value being supplied — the DEFAULT is not consulted, and the NOT NULL constraint rejects the row.

open as a page

What does declaring a primary key on a table actually guarantee, and how does a relational engine enforce that guarantee?

level: juniorimportance: must knowfreq 82%
basics
~20 s

A primary key guarantees every row has a value in those columns (no NULLs) and no two rows share the same combination. The engine enforces it by marking the columns NOT NULL and maintaining a unique index it probes on every insert and key update.

open as a page

In PostgreSQL, how do a cluster, a database and a schema relate, and which objects belong to the whole cluster rather than one database?

level: juniorimportance: must knowfreq 72%
basics
~20 s

A PostgreSQL cluster is one server instance and data directory holding many databases; each database holds schemas, which hold tables and functions. Roles, databases and tablespaces are cluster-wide, and every connection is bound to exactly one database.

open as a page

In PostgreSQL, what do initdb, pg_ctl and the postgres server program each do across a cluster's life, from creation to shutdown?

level: juniorimportance: must knowfreq 55%
basics
~20 s

initdb creates a cluster's data directory once; postgres is the server itself, whose first process is the postmaster; pg_ctl is a wrapper that starts, stops, restarts, reloads and reports on that server, tracking it through postmaster.pid.

open as a page

On a PostgreSQL 18 server, ps lists many postgres processes: what is the postmaster, and what do the other processes do?

level: juniorimportance: must knowfreq 72%
basics
~20 s

The postmaster is PostgreSQL's supervisor: it creates shared memory, listens for connections and forks one backend process per client. The other processes are helpers such as the checkpointer, background writer, walwriter and autovacuum launcher, plus background workers.

open as a page

What does PostgreSQL's pg_basebackup copy from a running cluster, and what must the server permit before it can connect and run?

level: juniorimportance: must knowfreq 62%
basics
~20 s

pg_basebackup copies a running PostgreSQL cluster's files: every database and tablespace at once, never a single database. It uses a replication connection, so it needs a REPLICATION or superuser role, a pg_hba.conf replication line and free WAL senders.

open as a page

What are PostgreSQL pg_dump's four output formats, and which tool restores each of them?

level: juniorimportance: must knowfreq 72%
basics
~10 s

pg_dump writes plain SQL (-Fp), replayed with psql, or one of three archives read by pg_restore: custom (-Fc, one compressed file), directory (-Fd, per-table files, the only parallel-dump format) and tar (-Ft, uncompressed).

open as a page

In MySQL, what is the binary log, and how do the ROW, STATEMENT and MIXED binlog formats differ?

level: juniorimportance: must knowfreq 70%
basics
~20 s

MySQL's binary log records committed data and schema changes for replicas and point-in-time recovery. ROW, the 8.4 default, logs changed row images; STATEMENT logs the SQL text; MIXED logs statements but switches to rows for unsafe ones.

open as a page

In MySQL, what is the practical difference between a VARCHAR(n) column and a TEXT column, and when do you choose each?

level: juniorimportance: must knowfreq 72%
basics
~20 s

In MySQL, VARCHAR(n) holds up to n characters, can be indexed whole and takes a literal DEFAULT; TEXT is a large-object type that needs a prefix length to be indexed and only an expression default. Choose VARCHAR for bounded, searchable strings.

open as a page

In MySQL, what are the practical differences between the InnoDB and MyISAM storage engines, and why is InnoDB the default?

level: juniorimportance: must knowfreq 75%
basics
~20 s

MySQL hands storage to an engine chosen per table. InnoDB, the default, gives transactions, row-level locking, foreign keys and automatic crash recovery; MyISAM has none of these, locks whole tables and can be left marked as crashed.

open as a page

In MySQL's EXPLAIN output, what do the type, key, key_len, rows and filtered columns tell you about how a table is read?

level: middleimportance: must knowfreq 78%
basics
~20 s

type is the access method, from const and eq_ref down through ref, range and index to ALL, a full scan. key is the chosen index, key_len how much of it is used, and rows times filtered estimates rows passed on.

open as a page

In MySQL with InnoDB, what does a secondary index entry contain, and how does the optimizer use the primary key columns stored there?

level: middleimportance: must knowfreq 65%
basics
~20 s

Each InnoDB secondary index entry holds the indexed columns followed by the row's primary key, which is used to find the row. The optimizer treats those appended columns as extra key parts for lookups, sorting and covering.

open as a page

What does it mean that SQLite is an in-process, serverless database, and how does an application reach its data?

level: juniorimportance: must knowfreq 72%
basics
~20 s

SQLite is a C library linked into your program: there is no server process, no port and no network protocol. Each SQL call is a function call that reads and writes the database file directly from the application's own process.

open as a page

In SQLite, what are the -wal and -shm files that appear beside a database file, and why must you never delete them by hand?

level: juniorimportance: must knowfreq 62%
basics
~20 s

In WAL mode the -wal file holds committed changes not yet copied into the main database file, and the -shm file is a shared-memory index over it. Deleting the -wal by hand can lose committed transactions or corrupt the database.

open as a page

In SQLite, what does switching a database to WAL mode change about readers and writers running at the same time?

level: juniorimportance: must knowfreq 70%
basics
~20 s

In WAL mode SQLite readers no longer block the writer and the writer no longer blocks readers: each reader sees a fixed snapshot while one writer appends to the log. Writers still take turns, one at a time.

open as a page

In SQLite, what are the five storage classes, and how are booleans and dates stored without dedicated types?

level: juniorimportance: must knowfreq 70%
basics
~20 s

SQLite stores every value as NULL, INTEGER, REAL, TEXT or BLOB, and the class belongs to the value, not the column. Booleans are the integers 0 and 1; dates are ISO-8601 text, Julian-day reals or Unix-time integers.

open as a page

In SQL Server, what do full, differential and transaction log backups each contain, and in what order do you restore them?

level: juniorimportance: must knowfreq 70%
basics
~20 s

A full backup copies the whole database plus enough log to recover it; a differential, extents changed since the last full; a log backup, log since the previous one. Restore the full, the newest differential, then later log backups in order.

open as a page

In SQL Server, what is the difference between a clustered and a nonclustered index, and how many of each can a table have?

level: juniorimportance: must knowfreq 80%
basics
~20 s

A SQL Server clustered index is the table itself: its leaf pages hold whole rows in key order, so a table has at most one. Nonclustered indexes are separate B+ trees pointing back to those rows, up to 999 per table.

open as a page

In SQL Server, how does a stored procedure hand values back through OUTPUT parameters and a RETURN code, and what must the caller write?

level: juniorimportance: must knowfreq 62%
basics
~20 s

A SQL Server procedure marks a parameter OUTPUT, and the caller must also pass a variable marked OUTPUT or the value is silently lost. RETURN sends back only an integer status, read with EXEC @rc = procedure.

open as a page

In SQL Server, how do TOP (n) and OFFSET ... FETCH differ, and what does each require of ORDER BY?

level: juniorimportance: must knowfreq 70%
basics
~20 s

TOP (n) is T-SQL's own limiter: ORDER BY is optional, but without it the rows chosen are undefined, and only TOP offers WITH TIES and PERCENT. OFFSET ... FETCH is part of ORDER BY, so it needs one, and it is the paging tool.

open as a page

What is the difference between a SQL Server failover cluster instance and an Always On availability group, and what does each one fail over?

level: middleimportance: must knowfreq 68%
basics
~20 s

A failover cluster instance is one SQL Server instance on shared storage that restarts on another node: whole-instance protection, one copy of data. An availability group keeps separate copies of chosen databases on several instances and fails over just those.

open as a page