DBMS Interview Guide
50 in-depth DBMS interview questions with easy-to-understand explanations, comparison tables, visual diagrams and practical SQL, covering architecture, ER modeling, the relational model, normalization, transactions, concurrency control, indexing, query processing and NoSQL. Tick each question off as you master it.
DBMS Fundamentals & Architecture
The Three-Level Database Architecture (ANSI/SPARC)
Users see a simplified external view. The conceptual level describes the whole database's logical structure. The internal level describes how data is physically stored. Each level can change without breaking the ones above it — this is called data independence.
001What is a DBMS and how is it different from a plain file system?Beginner+
Easy explanation
A Database Management System (DBMS) is software that lets you create, store, query, update, and manage data in an organized way, with built-in guarantees around consistency, security, and concurrent access. A plain file system just stores raw files — there's no structure enforcement, no query language, and no protection against two programs corrupting the same file at once.
The real advantages a DBMS gives you over files: reduced data redundancy (one fact stored once, referenced everywhere), enforced data integrity (constraints reject bad data before it's saved), controlled concurrent access (many users can safely read/write at once), and a query language (SQL) instead of writing custom code to search through files.
-- A DBMS lets you ask a question directly instead of parsing files yourself:
SELECT name, salary FROM employees WHERE department = 'Engineering';002What is data independence, and what are its two types?Beginner+
Easy explanation
Data independence means you can change one level of the database's structure without being forced to rewrite the applications that use it. This is exactly what the three-level architecture (external, conceptual, internal) is designed to protect.
Logical data independence means you can change the conceptual schema (e.g., add a new column, split a table) without changing the external views/application code that don't use that change. Physical data independence means you can change how data is physically stored (e.g., add an index, move to different disks) without affecting the conceptual schema or the applications at all — this is generally easier to achieve than logical independence.
| Type | Protects against | Example |
|---|---|---|
| Logical independence | Changes to the conceptual schema | Adding a new table doesn't break existing queries |
| Physical independence | Changes to physical storage | Adding an index doesn't change how you write a query |
003What are the different types of DBMS architectures (1-tier, 2-tier, 3-tier)?Beginner+
Easy explanation
A 1-tier architecture has the database and application on the same machine — simple, used mainly for local development or learning, not real production systems. A 2-tier architecture splits things into a client (which has the application logic and UI) talking directly to a database server — common in early client-server desktop applications.
A 3-tier architecture adds a middle 'application/business logic' layer between the client and the database — the client only talks to this middle tier, which then talks to the database. This is the standard for modern web applications: the browser (presentation) talks to a backend API server (application logic), which talks to the database (data layer), giving better security, scalability, and separation of concerns.
| Tier | Structure | Typical use |
|---|---|---|
| 1-tier | App + DB on one machine | Local development, learning |
| 2-tier | Client talks directly to DB server | Legacy desktop client-server apps |
| 3-tier | Client → App server → DB server | Modern web applications |
004What is a data model, and what are the main types?Intermediate+
Easy explanation
A data model is a conceptual framework for how data is organized, related, and manipulated — it's the abstraction layer between 'what the real world looks like' and 'what actually gets stored'. Choosing the right data model shapes how naturally your application logic maps onto your storage.
The relational model (tables of rows and columns, related through keys) is the dominant model for structured data with clear relationships, backed by SQL. The hierarchical model organizes data as a tree (parent-child, one parent per child) — largely historical, though echoes remain in things like file systems. The network model allows more flexible many-to-many linking than hierarchical. The document/NoSQL model (used by MongoDB) stores flexible, JSON-like documents instead of rigid rows, trading structure for schema flexibility.
005What is the difference between DDL, DML, DCL, and TCL in SQL?Intermediate+
Easy explanation
These are categories of SQL commands based on what they do. DDL (Data Definition Language — CREATE, ALTER, DROP) defines and modifies the database's STRUCTURE (tables, schemas, indexes). DML (Data Manipulation Language — SELECT, INSERT, UPDATE, DELETE) works with the actual DATA stored inside those structures.
DCL (Data Control Language — GRANT, REVOKE) manages permissions and access control, deciding who is allowed to do what. TCL (Transaction Control Language — COMMIT, ROLLBACK, SAVEPOINT) manages the boundaries and outcome of a transaction, letting you make a group of changes permanent or undo them together.
| Category | Commands | Purpose |
|---|---|---|
| DDL | CREATE, ALTER, DROP, TRUNCATE | Define/modify structure |
| DML | SELECT, INSERT, UPDATE, DELETE | Work with data |
| DCL | GRANT, REVOKE | Manage permissions |
| TCL | COMMIT, ROLLBACK, SAVEPOINT | Manage transaction outcomes |
006What is a database schema, and what is the difference between schema and instance?Intermediate+
Easy explanation
A schema is the overall DESIGN or blueprint of the database — the table names, column names, data types, and constraints — defined once when the database is designed and changed only occasionally (through migrations). It describes the STRUCTURE, not the actual data sitting in it right now.
An instance is the actual DATA stored in the database at a particular MOMENT in time — it changes constantly as rows are inserted, updated, and deleted, while the schema underneath usually stays the same. The common analogy: the schema is like a class definition in programming, and an instance is like a specific object created from that class, holding its own particular values.
-- Schema: the structure
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
-- Instance: the actual rows that exist right now
-- (1, 'Sara'), (2, 'Ali') -- this changes constantly, the schema doesn't007What is a DBA (Database Administrator) responsible for, and why does data independence make this job feasible?Advanced+
Easy explanation
A DBA is responsible for the health, security, performance, and availability of a production database — this includes managing backups and recovery, tuning performance (indexes, query plans), managing user access/permissions, applying schema migrations safely, and monitoring for issues before they become outages.
Data independence is what makes many of these tasks possible without breaking the applications relying on the database — a DBA can add an index, move data to faster storage, or partition a large table (physical independence) without touching a single line of application code, and can sometimes evolve the schema (logical independence) with minimal application impact if changes are additive.
008What is the difference between a centralized and a distributed database?Advanced+
Easy explanation
A centralized database stores all data in ONE physical location, managed by one DBMS instance — simpler to keep consistent (one source of truth, real ACID transactions are straightforward), but represents a single point of failure and can become a bottleneck for very high traffic or geographically distant users.
A distributed database spreads data across MULTIPLE physical locations (different servers, sometimes different regions), which can improve availability (one node failing doesn't take everything down) and reduce latency for geographically distributed users, but introduces real complexity: keeping data consistent across nodes, handling network partitions, and often trading off some consistency guarantees for availability (this trade-off is formalized in the CAP theorem).
009What is the CAP theorem and what does it actually mean for choosing a database?Advanced+
Easy explanation
The CAP theorem states that a distributed data system can only fully guarantee two of three properties at the same time during a network partition: Consistency (every read gets the most recent write, or an error), Availability (every request gets a non-error response, even if it might not be the latest data), and Partition tolerance (the system keeps working even if network communication between nodes breaks down).
Since real-world networks WILL experience partitions eventually, partition tolerance is essentially non-negotiable for a distributed system — so the real, practical choice most databases make is between prioritizing Consistency (CP systems, like many traditional distributed SQL setups, which may refuse requests during a partition to stay correct) or Availability (AP systems, like many NoSQL databases, which keep answering requests but might briefly return stale data).
010What is metadata in a DBMS, and where is it stored?Advanced+
Easy explanation
Metadata is 'data about data' — information describing the structure and characteristics of the actual data, rather than the data itself: table names, column names and types, constraints, index definitions, and user permissions. Metadata is what lets the DBMS understand and enforce your schema.
This metadata is stored in a special part of the database called the DATA DICTIONARY (or system catalog) — a set of internal system tables the DBMS itself manages and queries constantly (for example, checking whether a column allows NULLs before an INSERT). Most databases expose parts of this catalog to users through views like `information_schema` in MySQL/PostgreSQL, letting you query your own schema's structure using regular SQL.
-- Querying metadata (the data dictionary) directly with SQL:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'users';ER Modeling & Relational Model
From an ER Diagram to Relational Tables
Database design typically starts by identifying real-world entities (like Student, Course) and how they relate, sketching this as an ER diagram, then translating each entity and relationship into actual relational tables connected by keys.
011What is an Entity-Relationship (ER) diagram and what are its basic building blocks?Beginner+
Easy explanation
An ER diagram is a visual blueprint for a database's structure, drawn BEFORE any tables are actually created, to model the real-world things a system needs to track and how they relate to each other. It's a communication tool between whoever is designing the system and whoever needs to review that design.
The three core building blocks: an Entity (a real-world object or concept worth tracking, like Student or Order, usually drawn as a rectangle), an Attribute (a property describing an entity, like a Student's name or ID, drawn as an oval), and a Relationship (how two or more entities are connected, like a Student ENROLLS IN a Course, drawn as a diamond).
012What is the difference between a strong entity and a weak entity?Beginner+
Easy explanation
A strong entity has its own unique identifier (a primary key) and can exist independently in the database without needing another entity to define it — a Student or a Product are typically strong entities, each identifiable purely by their own attributes.
A weak entity does NOT have enough attributes of its own to form a unique primary key, and depends on a related 'owner' (strong) entity for its identity — for example, a Dependent (a family member listed on an employee's insurance) might only be unique in combination with which Employee they belong to. A weak entity's key is a combination of a partial key of its own plus the owner entity's primary key.
-- Weak entity example: dependent_name alone isn't unique, but paired with employee_id it is
CREATE TABLE dependents (
employee_id INT,
dependent_name VARCHAR(100),
PRIMARY KEY (employee_id, dependent_name),
FOREIGN KEY (employee_id) REFERENCES employees(id)
);013What are the types of relationship cardinality (1:1, 1:N, M:N), and how does each map to actual tables?Intermediate+
Easy explanation
Cardinality describes how many instances of one entity can relate to how many instances of another. One-to-one (1:1) means each row in Table A relates to at most one row in Table B and vice versa — often implemented by simply putting a unique foreign key in one of the two tables (like a Person and their Passport).
One-to-many (1:N) means one row in Table A can relate to many rows in Table B, but each row in B relates to only one row in A — implemented by putting a foreign key on the 'many' side (a Customer has many Orders, so `orders` has a `customer_id`). Many-to-many (M:N) means many rows on both sides can relate to many rows on the other — this requires a separate JUNCTION (or bridge/join) table holding a foreign key to each side, since neither original table alone can represent the relationship.
| Cardinality | Example | Implementation |
|---|---|---|
| 1:1 | Person ↔ Passport | Unique foreign key on either table |
| 1:N | Customer → Orders | Foreign key on the 'many' side |
| M:N | Students ↔ Courses | A separate junction/bridge table |
-- M:N junction table for Students and Courses
CREATE TABLE enrollments (
student_id INT REFERENCES students(id),
course_id INT REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);014What is generalization and specialization in ER modeling?Intermediate+
Easy explanation
Specialization is the process of taking a general entity and breaking it down into more specific SUB-entities that share the general entity's attributes but also have their own unique ones — for example, an Employee entity might be specialized into Manager and Developer, each with extra attributes specific to that role.
Generalization works in the opposite direction: you start with several specific entities that share common attributes and combine them into one general SUPER-entity — for example, noticing that Car and Truck share attributes like `wheels` and `max_speed`, you might generalize them into a shared Vehicle entity. Both are essentially the same relationship viewed from different design directions, similar to inheritance in object-oriented programming.
015What is a composite attribute and a derived attribute?Intermediate+
Easy explanation
A composite attribute is one that can be broken down into smaller sub-parts that are each meaningful on their own — like an Address, which is really made up of street, city, state, and zip code, each of which might be queried or displayed independently.
A derived attribute is one whose value can be CALCULATED from other stored attributes, rather than needing to be stored itself — like Age, which can always be computed from a stored date_of_birth and the current date. Storing a derived attribute directly risks it going stale (the person's actual birthday passes but the stored 'age' column doesn't update itself) unless it's deliberately kept in sync.
-- age is a derived attribute — computed, not stored
SELECT name, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM people;016What is the relational model, and what are the basic terms (relation, tuple, attribute, domain)?Intermediate+
Easy explanation
The relational model (proposed by E.F. Codd) represents all data as a collection of RELATIONS — which is just the formal term for a table. Each relation is a set of related facts organized into rows and columns, with mathematical guarantees (like no duplicate rows in a true relation) underpinning the whole model.
A tuple is a single ROW in a relation, representing one specific instance of the entity (one specific student, one specific order). An attribute is a COLUMN in a relation, representing one property (like `name` or `price`). A domain is the SET OF ALLOWED VALUES for an attribute — for example, the domain for a `status` column might be restricted to just `'active'`, `'inactive'`, `'pending'`.
| Relational term | Everyday term |
|---|---|
| Relation | Table |
| Tuple | Row |
| Attribute | Column |
| Degree | Number of columns |
| Cardinality | Number of rows |
017What are the integrity constraints in the relational model (domain, entity, referential)?Advanced+
Easy explanation
Domain integrity ensures every value entered into a column matches that column's defined domain (data type and allowed range) — you can't put text into an integer column, or a negative value into a column defined to require positive numbers via a CHECK constraint.
Entity integrity ensures every table has a primary key, and that primary key value is never NULL and never duplicated — this guarantees every row can always be uniquely identified. Referential integrity ensures a foreign key value in one table must either be NULL or must actually exist as a primary key value in the referenced table — the database rejects an INSERT/UPDATE that would point to a non-existent parent row, preventing 'orphaned' references.
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- referential integrity: can't insert an order for a customer_id that doesn't exist018What is relational algebra, and what are its core operations?Advanced+
Easy explanation
Relational algebra is a formal, mathematical query language that describes HOW to retrieve data from relational tables using a set of well-defined operations — it's the theoretical foundation SQL is actually built on top of, even though you rarely write relational algebra directly in practice.
The core operations: SELECT (σ) filters rows based on a condition (like SQL's WHERE), PROJECT (π) picks specific columns (like SQL's SELECT column list), UNION combines rows from two compatible relations, and JOIN combines rows from two relations based on a related column — each SQL query you write is effectively compiled down into a sequence of these underlying relational algebra operations by the database engine.
| Relational algebra | SQL equivalent |
|---|---|
| σ (Select) | WHERE clause |
| π (Project) | SELECT column list |
| ⋈ (Join) | JOIN |
| ∪ (Union) | UNION |
019What is a surrogate key vs a natural key, and when should you prefer each?Advanced+
Easy explanation
A natural key is a primary key made from an attribute that already has real-world business meaning — like using an email address or a national ID number as a table's primary key. A surrogate key is an artificially generated identifier (typically an auto-incrementing integer or a UUID) that has NO business meaning at all — it exists purely to uniquely identify a row.
Surrogate keys are generally preferred in practice because natural keys can change (an email address gets updated, a business rule changes what counts as 'unique'), and any change to a primary key value cascades painfully through every foreign key referencing it. A surrogate key stays stable forever regardless of what happens to the natural, business-meaningful data — the natural key can still be kept as a regular UNIQUE-constrained column for lookups.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY, -- surrogate key: stable, meaningless
email VARCHAR(255) UNIQUE -- natural key: has business meaning, kept unique but not the PK
);020What is the difference between a view and a materialized view?Advanced+
Easy explanation
A (regular) view is a virtual table defined by a stored SQL query — it doesn't store any data of its own; every time you query the view, the underlying query actually runs fresh against the real tables, so it's always up to date but pays the full cost of that query on every access.
A materialized view actually STORES the query's result physically on disk at the time it's created or refreshed, so reading from it is as fast as reading a regular table — but its data can go stale, since it only reflects the underlying tables as of the last refresh, requiring a manual or scheduled REFRESH to catch up with new changes. Materialized views trade freshness for read speed, useful for expensive aggregate reports that don't need to be perfectly real-time.
-- regular view: always fresh, recomputed every read
CREATE VIEW active_users AS SELECT * FROM users WHERE status='active';
-- materialized view: fast to read, needs manual refresh (PostgreSQL syntax)
CREATE MATERIALIZED VIEW sales_summary AS SELECT region, SUM(total) FROM orders GROUP BY region;
REFRESH MATERIALIZED VIEW sales_summary;Normalization & Keys
The Normalization Ladder
Each normal form fixes a specific kind of redundancy problem left over from the previous one. Most real-world production schemas stop at 3NF, since it removes almost all update anomalies while staying practical to query.
021What is normalization and what problem does it actually solve?Beginner+
Easy explanation
Normalization is the systematic process of organizing a relational database's tables and columns to eliminate data redundancy and prevent certain classes of bugs called 'update anomalies' — situations where the same fact is stored in multiple places, and updating it in one place but not another leaves the database in an inconsistent state.
The three classic anomalies normalization prevents: an INSERTION anomaly (you can't add a new fact without also having unrelated data you don't have yet), an UPDATE anomaly (a fact stored redundantly in many rows only gets updated in some of them), and a DELETION anomaly (deleting one row accidentally destroys an unrelated fact that happened to be stored alongside it).
022What is 1NF (First Normal Form)?Beginner+
Easy explanation
A table is in First Normal Form if every column holds only ATOMIC (indivisible) values — no column should contain a list, a set, or multiple values crammed into one field, like storing `'Math, Science, Art'` as a single string in a `subjects` column.
1NF also requires that each row be uniquely identifiable (a primary key exists) and that there are no repeating groups of columns (like having `phone1`, `phone2`, `phone3` columns to store multiple phone numbers) — the fix for both violations is usually to move the repeating/multi-valued data into its own separate related table.
-- violates 1NF: subjects column holds multiple values
-- id | name | subjects
-- 1 | Sara | 'Math, Science'
-- fixed: atomic values, one row per subject
CREATE TABLE student_subjects (student_id INT, subject VARCHAR(50));023What is 2NF (Second Normal Form) and what is a partial dependency?Intermediate+
Easy explanation
A table is in Second Normal Form if it's already in 1NF, AND every non-key column depends on the ENTIRE primary key, not just part of it. This rule only actually matters for tables with a COMPOSITE (multi-column) primary key — a table with a single-column primary key is automatically in 2NF if it's in 1NF.
A partial dependency happens when a non-key column depends on only PART of a composite key — for example, in a table keyed on `(student_id, course_id)`, if a `student_name` column is included, it only depends on `student_id` alone, not on the combination — that's a partial dependency, and it means student_name gets needlessly repeated for every course that student takes. The fix: move `student_name` into its own `students` table.
-- violates 2NF: student_name only depends on student_id, not the full (student_id, course_id) key
-- fix: split into two tables
CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR(100));
CREATE TABLE enrollments (student_id INT, course_id INT, grade CHAR(1), PRIMARY KEY(student_id, course_id));024What is 3NF (Third Normal Form) and what is a transitive dependency?Intermediate+
Easy explanation
A table is in Third Normal Form if it's already in 2NF, AND it has no transitive dependencies — meaning every non-key column depends DIRECTLY on the primary key, not on another non-key column.
A transitive dependency happens when a non-key column depends on ANOTHER non-key column, which in turn depends on the key — for example, in a table with `(employee_id, department_id, department_name)`, `department_name` really depends on `department_id`, not directly on `employee_id`. This means department_name gets duplicated for every employee in the same department, and updating a department's name requires updating it in every single employee row. The fix: move department info into its own `departments` table.
-- violates 3NF: department_name depends on department_id (transitively on employee_id)
-- fix:
CREATE TABLE departments (department_id INT PRIMARY KEY, department_name VARCHAR(100));
CREATE TABLE employees (employee_id INT PRIMARY KEY, department_id INT REFERENCES departments(department_id));025What is BCNF (Boyce-Codd Normal Form) and how is it stricter than 3NF?Advanced+
Easy explanation
BCNF is a stronger version of 3NF: a table is in BCNF if, for every functional dependency (X determines Y), X is a SUPERKEY — meaning every column that determines another column's value must itself be a candidate key (or part of one). 3NF has a narrow exception that BCNF closes.
The classic edge case where 3NF allows something BCNF doesn't: a table where a non-key attribute determines part of a composite key (a specific, fairly rare situation involving overlapping candidate keys). In practice, most tables that satisfy 3NF also satisfy BCNF, and the difference only matters for schemas with multiple overlapping composite candidate keys — most real-world schema design guides consider 3NF 'good enough' for the vast majority of applications.
026What are 4NF and 5NF, and are they commonly used in practice?Advanced+
Easy explanation
Fourth Normal Form (4NF) deals with MULTI-VALUED DEPENDENCIES — a situation where one key is associated with multiple independent sets of values that have nothing to do with each other (for example, an employee having multiple skills AND multiple languages, unrelated to one another, crammed awkwardly into one table creates a combinatorial mess of redundant rows). Fifth Normal Form (5NF) deals with eliminating redundancy caused by JOIN dependencies that can't be expressed by simpler forms.
In practice, 4NF and 5NF are rarely discussed or deliberately targeted in everyday application development — most production schemas are designed to 3NF (occasionally BCNF) and stop there, because the anomalies 4NF/5NF address are comparatively rare and the added complexity of chasing them usually isn't worth it for typical business applications. They're more commonly discussed in academic database theory courses than in day-to-day engineering work.
027What is denormalization, and when is it a legitimate design decision?Advanced+
Easy explanation
Denormalization is the deliberate process of introducing controlled redundancy back into an already-normalized schema, specifically to improve READ performance — for example, storing a pre-calculated `order_total` directly on an order row instead of recalculating it from line items on every single read.
This is a legitimate, conscious trade-off (not a design mistake) when you have a known, frequent, performance-critical read pattern that would otherwise require expensive joins or aggregations every time — the cost you accept in exchange is that the duplicated data can drift out of sync if every write path that should update it isn't handled carefully, so it's usually paired with either careful application logic or database triggers to keep it consistent.
-- normalized: always accurate, slower to read
SELECT SUM(price*qty) FROM order_items WHERE order_id=42;
-- denormalized: fast to read, must be kept in sync on every write
SELECT total FROM orders WHERE id=42;028What is a candidate key, primary key, and alternate key?Intermediate+
Easy explanation
A candidate key is ANY column (or combination of columns) that COULD uniquely identify a row — a table can have several candidate keys. For a `users` table, both `id` and `email` might independently be able to uniquely identify a row, making both candidate keys.
The primary key is the ONE candidate key that's actually CHOSEN to be the table's official unique identifier — every other candidate key that wasn't chosen becomes an alternate key (also called a secondary key), which is still typically enforced with a UNIQUE constraint even though it's not the primary key.
CREATE TABLE users (
id INT PRIMARY KEY, -- chosen candidate key = primary key
email VARCHAR(255) UNIQUE -- other candidate key = alternate key
);029What is a composite key and a compound key?Intermediate+
Easy explanation
A composite key is a primary key made up of TWO OR MORE columns TOGETHER, where no single one of those columns is unique on its own, but the combination is — a classic example is a junction table for a many-to-many relationship, like `(student_id, course_id)` in an enrollments table.
'Compound key' is sometimes used interchangeably with composite key, though some textbooks reserve 'compound key' specifically for a case where the combined columns are each individually meaningful/unique identifiers from other tables (each part is itself a foreign key referencing a different table) — in casual industry usage, though, the two terms are almost always used to mean the same thing.
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id) -- composite key
);030What is a functional dependency, and how does it drive the entire normalization process?Advanced+
Easy explanation
A functional dependency (written X → Y) means that the value of column X uniquely determines the value of column Y — if you know X, you always know exactly what Y must be, for every row in the table. For example, `student_id → student_name` is a functional dependency, since knowing the ID always tells you exactly which name goes with it.
The entire normalization process (2NF, 3NF, BCNF) is really just systematically finding and eliminating 'bad' functional dependencies — partial dependencies (Y depends on only part of a composite key) and transitive dependencies (Y depends on a non-key column, which itself depends on the key) — until every remaining functional dependency in the table is a 'good' one: directly and fully from the primary key to every other column.
Transactions & Concurrency Control
Transaction States
A transaction moves through these states as it executes. If a failure happens before it reaches Committed, it moves to a Failed state instead and is rolled back — undoing every change made so far, leaving the database exactly as it was before the transaction began.
031What is a transaction, and why does it need to be treated as a single unit?Beginner+
Easy explanation
A transaction is a sequence of one or more database operations (reads and writes) that are executed as a single, indivisible logical unit of work — either every operation in it succeeds and is saved permanently, or none of them are, leaving the database exactly as it was before the transaction started.
This matters whenever a single real-world action genuinely requires multiple separate database writes to stay correct together — the textbook example is transferring money: debiting one account and crediting another are two separate UPDATE statements, but if the process crashed after the debit but before the credit, money would simply disappear unless both statements were wrapped in one transaction.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both succeed together, or ROLLBACK undoes both032What does ACID stand for, and what does each property guarantee?Beginner+
Easy explanation
ACID describes the correctness guarantees a proper transaction provides. Atomicity means a transaction either fully happens or doesn't happen at all — no partial, half-finished updates ever persist. Consistency means a transaction always moves the database from one VALID state to another, never violating defined constraints or rules.
Isolation means concurrent transactions don't interfere with each other or see each other's incomplete, in-progress work. Durability means once a transaction is committed, its changes survive permanently — even a crash immediately afterward won't lose that data, because it's already been written to durable storage.
| Letter | Property | Guarantees |
|---|---|---|
| A | Atomicity | All-or-nothing execution |
| C | Consistency | Moves DB between valid states |
| I | Isolation | Concurrent transactions don't interfere |
| D | Durability | Committed changes survive a crash |
033What is concurrency control, and why is it needed even with ACID guarantees?Intermediate+
Easy explanation
Concurrency control is the set of techniques a DBMS uses to manage multiple transactions running AT THE SAME TIME against the same data, ensuring the end result is as if they'd run one after another in some valid order, even though they're actually interleaved for performance.
Even with ACID guaranteed for each INDIVIDUAL transaction, running many transactions concurrently without coordination can still produce incorrect results — two transactions both reading a value before either writes their update can lose one of the updates entirely (a lost update), which is exactly the kind of problem concurrency control mechanisms like locking and isolation levels are designed to prevent.
034What are the common concurrency problems: dirty read, non-repeatable read, and phantom read?Intermediate+
Easy explanation
A dirty read happens when a transaction reads data that another transaction has WRITTEN BUT NOT YET COMMITTED — if that other transaction then rolls back, the first transaction acted on data that never actually existed. A non-repeatable read happens when a transaction reads the SAME row twice and gets DIFFERENT values, because another transaction updated and committed a change to that row in between the two reads.
A phantom read happens when a transaction re-runs the SAME query twice and gets a DIFFERENT SET OF ROWS (not just different values in the same rows) because another transaction inserted or deleted rows matching that query's condition in between — the 'phantom' is a row that wasn't there before but suddenly appears (or vice versa).
| Problem | What happens |
|---|---|
| Dirty read | Reads another transaction's uncommitted change |
| Non-repeatable read | Same row gives different values on re-read |
| Phantom read | Same query returns a different SET of rows on re-run |
035What are the four SQL isolation levels, and what do they trade off?Intermediate+
Easy explanation
Isolation levels control how much concurrent transactions are allowed to 'see' of each other's in-progress work, trading correctness for performance — stricter isolation prevents more anomalies but requires more locking/blocking, which reduces how many transactions can run concurrently.
READ UNCOMMITTED allows dirty reads (weakest, rarely used). READ COMMITTED (the default in many databases like PostgreSQL) prevents dirty reads but still allows non-repeatable and phantom reads. REPEATABLE READ additionally prevents non-repeatable reads (this is MySQL InnoDB's default) but may still allow phantoms in some implementations. SERIALIZABLE is the strictest — it behaves as if transactions ran one after another with no overlap at all, preventing every anomaly, at the cost of the most blocking/lowest concurrency.
| Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible (implementation-dependent) |
| Serializable | Prevented | Prevented | Prevented |
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- ... queries here see a consistent snapshot ...
COMMIT;036What is a lock, and what is the difference between shared and exclusive locks?Advanced+
Easy explanation
A lock is a mechanism a DBMS uses to control access to a piece of data (a row, a page, or a table) so that concurrent transactions don't corrupt each other's work — before reading or writing data, a transaction acquires the appropriate lock, and other transactions must wait if a conflicting lock is already held.
A shared lock (S-lock) is acquired for READING data — multiple transactions can hold a shared lock on the same data at once, since simply reading doesn't conflict with other reads. An exclusive lock (X-lock) is acquired for WRITING data — only one transaction can hold an exclusive lock on a given piece of data at a time, and no other transaction can hold ANY lock (shared or exclusive) on it simultaneously, since a concurrent read or write during a modification could see an inconsistent state.
| Lock type | Allows concurrent reads? | Allows concurrent writes? |
|---|---|---|
| Shared (S) | Yes, with other shared locks | No |
| Exclusive (X) | No | No |
037What is deadlock, and how does a DBMS detect and resolve it?Advanced+
Easy explanation
A deadlock happens when two (or more) transactions are each waiting for a lock that the OTHER transaction is holding — Transaction A holds a lock Transaction B needs, while B holds a lock A needs, and neither can proceed, creating a cycle of waiting that would continue forever without intervention.
DBMSs detect this by building a 'wait-for graph' representing which transactions are waiting on which others, and periodically checking for CYCLES in that graph — a cycle means a deadlock exists. Once detected, the DBMS resolves it by choosing one transaction as the 'victim' (often the one that's done the least work, to minimize wasted effort) and forcibly rolling it back, releasing its locks so the other transaction(s) can proceed; the victim's application code typically needs to catch this error and retry.
-- Transaction A: locks row 1, then tries to lock row 2
-- Transaction B: locks row 2, then tries to lock row 1
-- => deadlock: DBMS detects the cycle, aborts one transaction automatically038What is Multiversion Concurrency Control (MVCC), and why do many modern databases prefer it over pure locking?Advanced+
Easy explanation
MVCC lets a database maintain MULTIPLE VERSIONS of a row simultaneously, so that a READER can see a consistent snapshot of the data as it existed at the start of their transaction, while a WRITER creates a new version of the row instead of directly overwriting the one readers might currently be looking at.
The huge practical benefit: readers essentially never block writers, and writers don't block readers, since each side is working with (or creating) its own version rather than fighting over the same physical row — this dramatically improves concurrency compared to systems relying purely on locking for read/write conflicts. PostgreSQL and MySQL's InnoDB engine both use MVCC as their core concurrency strategy, which is why a long-running read in these databases doesn't grind writes to a halt.
039What is a savepoint, and how does it let you partially roll back a transaction?Advanced+
Easy explanation
A savepoint is a named marker you can set WITHIN an ongoing transaction, letting you roll back only the changes made AFTER that specific point, rather than being forced to undo the entire transaction from the very beginning.
This is useful for complex, multi-step transactions where one particular step might reasonably fail and you want to gracefully recover just that step (perhaps trying an alternative) without discarding everything successfully done before it — the transaction as a whole is still eventually committed or fully rolled back, but savepoints give you finer-grained recovery control in between.
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT before_risky_step;
UPDATE accounts SET balance = balance + 50 WHERE id = 999; -- this might fail (bad id)
ROLLBACK TO SAVEPOINT before_risky_step; -- undo just this step, keep the first update
COMMIT;040What is two-phase locking (2PL), and how does it guarantee serializability?Advanced+
Easy explanation
Two-phase locking is a protocol that splits a transaction's lifetime into two distinct phases: a GROWING phase, where the transaction can only ACQUIRE new locks (never release any), and a SHRINKING phase, where it can only RELEASE locks (never acquire any new ones) — once a transaction releases its very first lock, it's not allowed to request any more.
This strict separation is what guarantees the resulting execution is SERIALIZABLE — equivalent to some valid sequential ordering of the transactions, even though they actually ran concurrently — because a transaction can't 'sneak in' an additional lock acquisition after it's already started releasing resources, which is exactly the pattern that can otherwise create subtle correctness violations under concurrent execution.
Indexing, Query Processing & NoSQL
How a Query Actually Executes
The optimizer doesn't run your query literally as written — it considers multiple possible execution strategies (which index to use, which join order) and picks the one it estimates will be cheapest, based on statistics it keeps about your data.
041What is an index, and why does it speed up queries?Beginner+
Easy explanation
An index is a separate, ordered data structure the database maintains alongside a table, allowing it to quickly locate rows matching a search condition without scanning every single row in the table — similar to how a book's index lets you jump straight to a page instead of reading the entire book.
Without an index, a query has to perform a FULL TABLE SCAN — checking every single row one by one — which becomes painfully slow as a table grows to millions of rows. With the right index on a frequently-searched column, the database can jump almost directly to matching rows, often turning a query that would take seconds into one that takes milliseconds.
CREATE INDEX idx_users_email ON users(email);
-- speeds up: SELECT * FROM users WHERE email = 'a@b.com';042What is a B-tree, and why is it the most common index structure?Beginner+
Easy explanation
A B-tree (balanced tree) is a self-balancing tree data structure where each node can have multiple children, and all the actual data is stored at a consistent depth from the root — this balance guarantees that finding any value takes roughly the same, small number of steps, regardless of how large the tree grows.
B-trees are the default index type in most relational databases because they efficiently support both EQUALITY lookups (find exactly this value) and RANGE queries (find everything between two values, or sort order), since the tree naturally keeps values in sorted order — a hash-based index, by contrast, is faster for pure equality lookups but can't efficiently support range queries or sorting at all.
043What is the difference between a clustered and a non-clustered index?Intermediate+
Easy explanation
A clustered index determines the PHYSICAL ORDER in which rows are actually stored on disk — the table's data itself is organized according to this index, which means a table can have at most ONE clustered index (data can only be physically sorted one way at a time). In many databases, the primary key automatically becomes the clustered index.
A non-clustered index is a SEPARATE structure that stores the indexed column's values alongside a pointer back to where the actual row lives — a table can have MANY non-clustered indexes, but each lookup through one requires an extra step to follow the pointer back to the real row (unless the query only needs columns already present in the index itself, called a 'covering index').
| Clustered index | Non-clustered index | |
|---|---|---|
| How many per table | At most one | Many |
| Determines physical row order? | Yes | No — separate structure |
| Extra lookup needed? | No — IS the data | Usually yes, back to the table |
044What is a composite index, and why does column ORDER within it matter so much?Intermediate+
Easy explanation
A composite (multi-column) index is built on more than one column together, like `(last_name, first_name)` — it's stored sorted first by the leftmost column, then by the next column within each group of matching values for the first, and so on.
The order matters enormously because a composite index can only be efficiently used for a query if it matches a LEFTMOST PREFIX of the indexed columns — an index on `(last_name, first_name)` speeds up a query filtering only on `last_name`, or on both `last_name` AND `first_name`, but does NOT help a query that filters ONLY on `first_name`, since the index isn't sorted usefully for that access pattern on its own.
CREATE INDEX idx_name ON employees(last_name, first_name);
-- helped: WHERE last_name = 'Khan'
-- helped: WHERE last_name = 'Khan' AND first_name = 'Ali'
-- NOT helped: WHERE first_name = 'Ali' (skips the leftmost column)045What is a query execution plan, and how do you read one (using EXPLAIN)?Advanced+
Easy explanation
A query execution plan is the actual STRATEGY the database's query optimizer decided to use to run your query — which indexes (if any) it will use, what order it will join tables in, and what algorithm it will use for each join or aggregation. The optimizer generates several possible plans and picks the one it estimates will be cheapest based on internal statistics about your table sizes and data distribution.
Running `EXPLAIN` (or `EXPLAIN ANALYZE` to also show actual runtime numbers) in front of a query reveals this plan — look specifically for a 'full table scan' / 'seq scan' on a large table (often a sign a missing index is hurting performance), and check whether the estimated number of rows the optimizer expects roughly matches reality (a big mismatch suggests outdated table statistics, which can cause the optimizer to pick a bad plan).
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
-- look for: Seq Scan (bad, on a large table) vs Index Scan (good)046What is query optimization, and what are some common techniques a database uses?Advanced+
Easy explanation
Query optimization is the process the DBMS goes through to transform your SQL query (which describes WHAT you want) into an efficient execution plan (which describes HOW to actually get it) — since the same logical query can often be executed in many different physical ways with wildly different performance.
Common techniques include predicate pushdown (applying filters as EARLY as possible, before an expensive join, to reduce the number of rows involved), choosing the most efficient join algorithm based on table sizes (nested loop for small tables, hash join or merge join for larger ones), and using indexes instead of full scans whenever a matching index exists and is estimated to be cheaper than scanning everything.
047What is sharding (horizontal partitioning), and what challenge does it introduce?Advanced+
Easy explanation
Sharding splits a single large table's ROWS across multiple separate database servers (shards), typically based on some partitioning key (like `user_id`) — instead of one giant table on one machine, each shard holds a subset of the rows, letting you scale storage and query throughput far beyond what one single server could handle.
The major challenge: any query that needs to combine data ACROSS multiple shards (like a global aggregate, or a join between two entities that live on different shards) becomes significantly more complex and slower than it would be in a single-server database, since it can no longer rely on the DBMS's normal in-process query engine alone — this is a core reason sharding is usually a last resort, adopted only once vertical scaling and simpler optimizations are exhausted.
-- conceptual sharding by user_id range
-- Shard 1: users 1-1,000,000
-- Shard 2: users 1,000,001-2,000,000
-- A query needing data from users on BOTH shards must query both and merge results in the application048What is the difference between horizontal and vertical partitioning of a table?Advanced+
Easy explanation
Horizontal partitioning (a.k.a. sharding when spread across multiple servers) splits a table by ROWS — different rows live in different partitions, but every partition has the SAME columns. This is useful when a table has too many rows for one partition to handle efficiently, and you can split by a logical key like date range or region.
Vertical partitioning splits a table by COLUMNS — different partitions hold different columns for the SAME set of rows, often separating frequently-accessed 'hot' columns from rarely-accessed 'cold' or very large columns (like a big text blob), so common queries only need to touch the smaller, faster partition.
| Horizontal partitioning | Vertical partitioning | |
|---|---|---|
| Splits by | Rows | Columns |
| Example | Orders from 2023 vs 2024 in different partitions | Frequently-used columns separate from a large rarely-read blob column |
049What is a NoSQL database, and what are the main categories?Advanced+
Easy explanation
NoSQL databases relax or abandon the rigid relational model in exchange for flexibility, horizontal scalability, or performance characteristics better suited to specific access patterns — they generally trade some of SQL's strict consistency/structure guarantees for these benefits, though the right trade-off depends heavily on the specific use case.
The main categories: document stores (MongoDB) store flexible, JSON-like documents; key-value stores (Redis, DynamoDB) map simple keys to values, optimized for extremely fast lookups; column-family stores (Cassandra) organize data in column groups optimized for very large-scale writes and specific query patterns; and graph databases (Neo4j) are optimized specifically for traversing complex, deeply nested relationships, like a social network.
| Category | Example | Best for |
|---|---|---|
| Document | MongoDB | Flexible, nested, evolving schemas |
| Key-value | Redis, DynamoDB | Extremely fast simple lookups |
| Column-family | Cassandra | Massive write throughput at scale |
| Graph | Neo4j | Deeply connected relationship data |
050When should you choose a relational (SQL) database over a NoSQL database, and vice versa?Advanced+
Easy explanation
Choose a relational database when your data has clear, stable relationships that benefit from real JOINs, when you need strong ACID transaction guarantees across multiple related pieces of data (like financial transactions), or when your query patterns are varied and not fully known upfront — SQL's flexibility to ask ad-hoc questions across related tables is a major strength.
Choose a NoSQL database when you have a very specific, well-known access pattern that a specialized data model serves better (like extremely low-latency key lookups in Redis, or deeply nested/evolving document structures in MongoDB), when you need to scale writes horizontally beyond what a single relational server can efficiently handle, or when your data genuinely doesn't fit a clean tabular/relational shape. The honest, nuanced answer: this isn't a strict either/or — many real systems use both together, choosing the right tool for each specific data access pattern rather than forcing everything into one model.