Top 50 DBMS Interview Questions with Answers (2026): Fresher to Database Architect

💡 Why Interviewers Ask This: The foundational baseline test. A strong candidate defines it by emphasizing “data management” and “interface/intermediary” rather than just saying “it stores data.”

💡 Why Interviewers Ask This: Tests historical and architectural knowledge. You must know that almost all modern SQL databases are specifically Relational databases.

💡 Why Interviewers Ask This: They want to know if you understand why we use databases instead of just writing data to a .txt or .csv file.

💡 Why Interviewers Ask This: A core concept of the Three-Schema Architecture. It proves you understand how databases abstract physical storage from application logic.

💡 Why Interviewers Ask This: Highly tested. You must instantly know that TRUNCATE is DDL (not DML) and therefore cannot be rolled back in most databases.

A Database Schema is the logical blueprint defining the structure of the database — tables, columns, data types, relationships, views, and constraints. The schema rarely changes once deployed. It must be distinguished from a database instance (the actual data stored at a specific moment).

💡 Why Interviewers Ask This: You must distinguish between the empty skeletal structure (schema) and the live populated data (instance).

A Data Dictionary is a centralized repository of metadata — data about the data. It stores information about table definitions, column types, constraints, relationships, indexes, and user permissions. In PostgreSQL it is accessed via information_schema; in Oracle via ALL_TABLES system views.

💡 Why Interviewers Ask This: Shows administrative awareness. Advanced developers query the data dictionary to automate database introspection and documentation tasks.

The ER Model is a high-level conceptual data model used to define the data elements and relationships for a specified system. It represents real-world objects as Entities and their connections as Relationships, visualized in an ER Diagram.

💡 Why Interviewers Ask This: System design interviews heavily rely on ER modeling. It proves you can translate a business requirement into a database architecture.

💡 Why Interviewers Ask This: Foundational vocabulary. Use these exact terms when discussing database design with a senior architect.

A NULL value represents a missing, unknown, or inapplicable value. It is not the same as zero, an empty string, or a blank space. Critically, NULL = NULL evaluates to UNKNOWN (not TRUE) in SQL — you must use IS NULL to check for NULLs.

💡 Why Interviewers Ask This: NULLs cause massive bugs in production query logic. The NULL = NULL → UNKNOWN behavior is the most commonly misunderstood SQL fact.

A Primary Key is a column (or set of columns) that uniquely identifies every row in a table. It enforces two rules: all values must be unique, and it cannot contain NULL values. Each table can have at most one Primary Key.

💡 Why Interviewers Ask This: The most fundamental constraint in relational databases. You must explicitly mention the NULL prohibition — that is the differentiator from a Unique Key.

A Foreign Key is a column in one table that references the Primary Key of another table, establishing a relational link. It enforces Referential Integrity — you cannot insert a Foreign Key value that does not exist in the referenced table. On deletion, common actions are CASCADE (delete children) or RESTRICT (block deletion).

💡 Why Interviewers Ask This: Tests relational mapping knowledge. The follow-up about CASCADE vs. RESTRICT is almost guaranteed.

A Candidate Key is any column or minimal set of columns that can uniquely identify every row in a table. A table can have multiple Candidate Keys. The database designer selects one to act as the Primary Key — the Candidate Key that “won the election.”

💡 Why Interviewers Ask This: Differentiates intermediate candidates from beginners — the Primary Key being a chosen Candidate Key is a key insight.

A Super Key is any set of attributes that can uniquely identify a row. A Candidate Key is a minimal Super Key — a Super Key with no redundant attributes. Example: if EmployeeID is unique, then {EmployeeID, EmployeeName} is a Super Key but not a Candidate Key because EmployeeName is redundant.

💡 Why Interviewers Ask This: Deep theoretical knowledge test. The minimal property of Candidate Keys vs. the superset nature of Super Keys is the key distinction.

An Alternate Key is any Candidate Key that was not selected as the Primary Key. Example: if both Email and UserID are unique and UserID is chosen as the Primary Key, then Email is the Alternate Key.

💡 Why Interviewers Ask This: Vocabulary test. Interviewers want to see if you can correctly classify all key types in a schema.

A Composite Key is a Primary Key made up of two or more columns. Used when no single column uniquely identifies a record. Common in junction tables for many-to-many relationships. Example: in an Enrollments table, neither StudentID nor CourseID alone is unique, but the combination {StudentID, CourseID} is.

💡 Why Interviewers Ask This: Essential for modeling many-to-many relationships, which appear in every system design interview.

A Unique Key ensures all values in a column are distinct. Unlike a Primary Key: a table can have multiple Unique Keys, and a Unique Key can accept one NULL value (in most SQL engines). Example: Email column constrained as UNIQUE but not Primary Key.

💡 Why Interviewers Ask This: The classic Primary Key vs. Unique Key comparison. The ability to accept NULL is the critical differentiator.

Referential Integrity ensures relationships between tables remain consistent. A Foreign Key value must always point to an existing Primary Key value in the referenced table. It prevents orphan records — e.g., an order row belonging to a user that was deleted.

💡 Why Interviewers Ask This: This is the primary reason RDBMS systems exist over flat files. Data integrity at the relational level is a core engineering concern.

💡 Why Interviewers Ask This: Tests whether you can distinguish between the conceptual design phase and the physical implementation phase.

A Surrogate Key is an artificially generated, system-assigned Primary Key (e.g., an auto-incrementing integer or UUID) with no business meaning, used purely for row identification. Preferred over natural keys (e.g., SSN, email) because natural data can change — a surrogate ID never does.

💡 Why Interviewers Ask This: Senior engineers consistently prefer surrogate keys in production. Understanding this trade-off shows architectural maturity.

Normalization is the process of organizing a database to reduce data redundancy and eliminate update, insertion, and deletion anomalies. It involves dividing large tables into smaller, related tables linked by foreign keys. Optimizes for write-heavy OLTP workloads.

💡 Why Interviewers Ask This: The most critical database design question. Every database engineer must be able to explain and apply this from 1NF through 3NF.

💡 Why Interviewers Ask This: You must prove you understand why we normalize. Normalization specifically cures these three anomalies.

A table is in 1NF if every cell contains only atomic (indivisible) values — no repeating groups, no arrays, no comma-separated lists in a single column. Example violation: storing “555-1234, 555-5678” in a PhoneNumbers column. Fix: move each phone number to a separate row in a child table.

💡 Why Interviewers Ask This: The first rule of relational databases. Storing lists in a single cell is the most common beginner mistake.

A table is in 2NF if it is in 1NF and has no Partial Dependencies — every non-key attribute must depend on the entire Composite Primary Key, not just part of it. Example violation: CourseName depends only on CourseID in a table with Primary Key {StudentID, CourseID}.

💡 Why Interviewers Ask This: Only applies to tables with composite keys. This concept separates memorizers from those who truly understand normalization.

A table is in 3NF if it is in 2NF and has no Transitive Dependencies — a non-key attribute must not depend on another non-key attribute. The rule: “every non-key attribute must depend on the key, the whole key, and nothing but the key.”

💡 Why Interviewers Ask This: 3NF is the industry standard target. Most enterprise databases normalize to 3NF and stop there, balancing integrity and performance.

BCNF is a stricter version of 3NF (often called 3.5NF). A table is in BCNF if for every functional dependency X → Y, X must be a Super Key. It resolves anomalies that 3NF cannot catch when there are multiple overlapping candidate keys.

💡 Why Interviewers Ask This: A highly academic question that separates standard developers from database architects.

Denormalization is the intentional addition of redundancy back into a normalized database to improve read performance. It reduces expensive JOIN operations at query time. Used extensively in Data Warehousing (OLAP), where read speed matters more than write efficiency.

💡 Why Interviewers Ask This: Tests real-world engineering trade-offs. While normalization optimizes writes, denormalization optimizes reads.

A Functional Dependency (A → B) means the value of attribute A uniquely determines the value of attribute B. Example: StudentID → StudentName (knowing the ID tells you the name). It is the mathematical foundation of all normalization rules.

💡 Why Interviewers Ask This: You cannot explain 2NF or 3NF without understanding functional dependencies. This is the prerequisite concept.

A Multivalued Dependency occurs when the presence of one row implies the presence of another row in the same table, independent of all other attributes. Requires at least three attributes and is resolved by moving to Fourth Normal Form (4NF).

💡 Why Interviewers Ask This: Usually only asked in senior or DBA interviews to test advanced normalization theory beyond 3NF.

An ERD is a visual blueprint of a database's logical structure using standard symbols: rectangles (entities), ovals (attributes), diamonds (relationships), and lines with cardinality notation (1:1, 1:N, M:N). It is the tool used to translate business requirements into a database design.

💡 Why Interviewers Ask This: Interviewers will often hand you a whiteboard marker and ask you to draw an ERD for a system (library, e-commerce, hospital).

A JOIN clause combines rows from two or more tables based on a related column (usually a Foreign Key). It is the primary mechanism for querying normalized, relational data spread across multiple tables.

💡 Why Interviewers Ask This: The bread and butter of database querying. You must be able to visually explain which rows each join type includes or excludes.

An INNER JOIN returns only rows that have matching values in both tables. If a row in Table A has no corresponding match in Table B, it is completely excluded from the result. Writing just JOIN in SQL defaults to INNER JOIN.

💡 Why Interviewers Ask This: The most common join type. Understand that unmatched rows from either side are silently dropped.

💡 Why Interviewers Ask This: A classic practical scenario: “List all customers, including those with zero orders” requires a LEFT JOIN from customers to orders.

A FULL OUTER JOIN returns all rows from both tables. Where no match exists on either side, NULLs fill the gaps. It is the combination of LEFT and RIGHT JOIN. Note: MySQL does not natively support FULL OUTER JOIN — it must be simulated with UNION of LEFT and RIGHT JOINs.

💡 Why Interviewers Ask This: Less common in production but heavily tested. The MySQL workaround is a guaranteed follow-up question.

A CROSS JOIN returns every possible combination of rows from both tables — the Cartesian product. If Table A has 100 rows and Table B has 100 rows, the result has 10,000 rows. It has no ON clause. Accidentally omitting the ON clause from a regular JOIN produces a CROSS JOIN.

💡 Why Interviewers Ask This: They want to ensure you know what happens when you forget the ON clause — a common and expensive production mistake.

An Index is a data structure (typically a B-Tree or Hash Table) that speeds up data retrieval on a table, at the cost of additional storage and slower writes (INSERT/UPDATE must update the index too). Like the index at the back of a textbook — it prevents a full table scan.

💡 Why Interviewers Ask This: The #1 performance tuning question. Over-indexing is as dangerous as under-indexing — you must articulate the write-cost trade-off.

💡 Why Interviewers Ask This: Separates junior from senior engineers. Understanding physical disk ordering of Clustered Indexes is critical for query optimization.

A View is a named virtual table based on the result of an SQL query. It does not physically store data (unless it is a Materialized View). Used to simplify complex queries, hide sensitive columns (passwords, salaries) from end-users, and provide a stable API over changing table structures.

💡 Why Interviewers Ask This: Tests security and abstraction awareness. Views as a security layer to restrict column-level access is the key insight.

A Stored Procedure is a precompiled SQL code block stored and executed directly on the database server. It accepts parameters, can contain logic, and is reusable. Benefits: reduced network traffic (one call instead of many), better performance (cached execution plan), and centralized business logic.

💡 Why Interviewers Ask This: Tests enterprise architecture knowledge. Many legacy financial and banking systems run entirely on stored procedures.

💡 Why Interviewers Ask This: A guaranteed interview question. Confusing these three in production can destroy a database — the rollback difference is the critical detail.

A Transaction is a sequence of SQL operations treated as a single indivisible unit of work — it either fully completes (COMMIT) or fully fails (ROLLBACK). Classic example: a bank transfer must debit one account AND credit another as one atomic operation.

💡 Why Interviewers Ask This: The foundation of data integrity. The bank transfer example is the expected illustration — have it ready.

💡 Why Interviewers Ask This: The most famous question in DBMS. You must rattle off all four letters and define each accurately — no exceptions.

Concurrency Control manages simultaneous transactions to prevent conflicts and ensure that concurrent execution produces the same result as sequential execution (serializability). The primary mechanisms are Locking (pessimistic) and Optimistic Concurrency Control (OCC using timestamps or versioning).

💡 Why Interviewers Ask This: Tests high-traffic application design ability. Every heavily-used database must solve this problem correctly.

A DBMS Deadlock occurs when two or more transactions are permanently blocked because each holds a lock that the other needs. The DBMS detects this cycle and kills the “victim” transaction, rolling it back. Prevention: always access tables in the same consistent order across all transactions.

💡 Why Interviewers Ask This: Tests troubleshooting skills for production databases. The consistent-order prevention strategy is the expected practical answer.

A Dirty Read occurs when Transaction A reads data that Transaction B has modified but not yet committed. If B later rolls back, A has read data that never officially existed. Prevented by moving from the READ UNCOMMITTED isolation level to READ COMMITTED or higher.

💡 Why Interviewers Ask This: Leads to the Transaction Isolation Levels question. Know all four levels: Read Uncommitted → Read Committed → Repeatable Read → Serializable.

💡 Why Interviewers Ask This: System design requires choosing the right database. Use SQL for financial integrity; NoSQL for unstructured, rapidly-evolving, high-volume data.

The CAP Theorem states that a distributed database can simultaneously guarantee only 2 of 3 properties: Consistency (all nodes see identical data), Availability (every request gets a response), and Partition Tolerance (system operates despite network failures). Since network partitions (P) are unavoidable, architects must choose between C and A.

💡 Why Interviewers Ask This: The foundational law of distributed systems. Knowing that MongoDB is CP and Cassandra is AP demonstrates real-world database architecture knowledge.

Sharding is horizontal partitioning of a large database into smaller, independent units called shards, each stored on a separate server. Data is distributed by a shard key (e.g., user region). Used by companies like Facebook and Twitter to scale to billions of users beyond what a single server can handle.

💡 Why Interviewers Ask This: Tests scalability knowledge. You must distinguish sharding (splits data) from replication (copies data).

Replication continuously copies data from a primary (master) database to one or more replica (slave) databases. Used to improve read performance (reads routed to replicas), high availability (failover if primary fails), and disaster recovery (geo-redundant replicas).

💡 Why Interviewers Ask This: The key distinction: Sharding splits data across servers; Replication copies the same data across servers.

💡 Why Interviewers Ask This: The Big Data/Data Engineering baseline question. You must know never to run heavy analytical reports on your live production OLTP database.