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 pageshowhide
explore
- Relational Database Concepts (has its own guide)683 questions
- Relational Model & Theory49 questions
- Schema Design & Normalization94 questions
- Keys & Integrity Constraints48 questions
- Transactions & Concurrency Control108 questions
- Indexes & Access Paths67 questions
- Query Processing & Optimization72 questions
- Views, Procedures & Server-Side Logic46 questions
- Engine Architecture & Storage Internals54 questions
- Replication, Backup & High Availability54 questions
- Partitioning & Scaling46 questions
- Access Control & Data Protection45 questions
- PostgreSQL (has its own guide)297 questions
- Engine Anatomy22 questions
- MVCC & Locking14 questions
- Vacuum & Freezing16 questions
- Indexes & Query Planning33 questions
- Column Types17 questions
- SQL Dialect37 questions
- Multi-Model Workloads23 questions
- Extensibility12 questions
- Server-Side Code21 questions
- Database Security17 questions
- Server Tuning16 questions
- Monitoring & Diagnostics15 questions
- Write-Ahead Log & Replicas28 questions
- Backup & Restore15 questions
- Release Lifecycle11 questions
- MySQL36 questions
- Types & Online DDL6 questions
- InnoDB & Storage Engines6 questions
- Index Types & EXPLAIN6 questions
- InnoDB Locking & Isolation6 questions
- Binlog Replication6 questions
- Query & Server Tuning6 questions
- MariaDBempty
- SQLite35 questions
- Embedded Architecture5 questions
- Type Affinity & Dialect6 questions
- File Format & Journals6 questions
- Locking & Write Concurrency6 questions
- Indexes & Query Planner6 questions
- Limits, Extensions & Corruption6 questions
- Oracle Databaseempty
- SQL Server36 questions
- T-SQL Dialect6 questions
- Clustered & Columnstore Indexes6 questions
- Procedures & User Functions6 questions
- Locking & Row Versioning6 questions
- Plans & Wait Statistics6 questions
- Always On & Backups6 questions
- SAP HANAempty
- Azure SQL Databaseempty
- Google Cloud SQLempty
- AI & Data Scientistrole
- AI Engineerrole
- Android Developerrole
- BI Analystrole
- Backend Developerrole
- BigQuerytool
- Computer Scienceskill
- Cyber Security Expertrole
- Data Analystrole
- Data Engineerrole
- DevOps / SRE Engineerrole
- DevSecOps Engineerrole
- Forward Deployed Engineerrole
- Full Stack Developerrole
- Hibernate ORMtool
- JS Full Stack Developerrole
- Java Backend Developerrole
- Java SDETrole
- Kotlin Backend Developerrole
- MLOps Engineerrole
- Machine Learning Engineerrole
- MongoDBtool
- PostgreSQLtool
- PostgreSQL DBArole
- QA Engineerrole
- SQLskill
- Server-Side Game Developerrole
- Snowflaketool
- Software Architectrole
questions
1 087 · 5 sectionsWhat does a CHECK constraint do in a relational table, and at what point does the database engine evaluate it?
basics
~20 sA 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.
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?
basics
~20 sBecause 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.
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?
basics
~20 sIt 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.
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.
basics
~20 sOmitting 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.
What does declaring a primary key on a table actually guarantee, and how does a relational engine enforce that guarantee?
basics
~20 sA 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.
In PostgreSQL, how do a cluster, a database and a schema relate, and which objects belong to the whole cluster rather than one database?
basics
~20 sA 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.
In PostgreSQL, what do initdb, pg_ctl and the postgres server program each do across a cluster's life, from creation to shutdown?
basics
~20 sinitdb 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.
On a PostgreSQL 18 server, ps lists many postgres processes: what is the postmaster, and what do the other processes do?
basics
~20 sThe 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.
What does PostgreSQL's pg_basebackup copy from a running cluster, and what must the server permit before it can connect and run?
basics
~20 spg_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.
What are PostgreSQL pg_dump's four output formats, and which tool restores each of them?
basics
~10 spg_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).
In MySQL, what is the binary log, and how do the ROW, STATEMENT and MIXED binlog formats differ?
basics
~20 sMySQL'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.
In MySQL, what is the practical difference between a VARCHAR(n) column and a TEXT column, and when do you choose each?
basics
~20 sIn 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.
In MySQL, what are the practical differences between the InnoDB and MyISAM storage engines, and why is InnoDB the default?
basics
~20 sMySQL 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.
In MySQL's EXPLAIN output, what do the type, key, key_len, rows and filtered columns tell you about how a table is read?
basics
~20 stype 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.
In MySQL with InnoDB, what does a secondary index entry contain, and how does the optimizer use the primary key columns stored there?
basics
~20 sEach 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.
What does it mean that SQLite is an in-process, serverless database, and how does an application reach its data?
basics
~20 sSQLite 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.
In SQLite, how do you check whether a query uses an index, and what separates SCAN from SEARCH in EXPLAIN QUERY PLAN?
basics
~20 sPrefix the statement with EXPLAIN QUERY PLAN. A line starting SEARCH means SQLite visits only a subset of a table's rows through an index or the rowid; SCAN means a full pass over the table, even when it walks rows in an index's order.
In SQLite, what are the -wal and -shm files that appear beside a database file, and why must you never delete them by hand?
basics
~20 sIn 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.
In SQLite, what does switching a database to WAL mode change about readers and writers running at the same time?
basics
~20 sIn 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.
In SQLite, what are the five storage classes, and how are booleans and dates stored without dedicated types?
basics
~20 sSQLite 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.
In SQL Server, what do full, differential and transaction log backups each contain, and in what order do you restore them?
basics
~20 sA 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.
In SQL Server, what is the difference between a clustered and a nonclustered index, and how many of each can a table have?
basics
~20 sA 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.
In SQL Server, how does a stored procedure hand values back through OUTPUT parameters and a RETURN code, and what must the caller write?
basics
~20 sA 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.
In SQL Server, how do TOP (n) and OFFSET ... FETCH differ, and what does each require of ORDER BY?
basics
~20 sTOP (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.
What is the difference between a SQL Server failover cluster instance and an Always On availability group, and what does each one fail over?
basics
~20 sA 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.