25 Most Asked DBMS Interview Questions (TCS, Infosys & Accenture 2026)

DBMS stores data as files and does not establish relationships between tables or enforce tabular constraints. RDBMS (Relational DBMS) stores data in structured tables (relations) with rows and columns, enforcing primary/foreign keys, relational algebra, and ACID constraints.

The 3-Schema architecture separates the user application view from the physical database storage to achieve Data Independence:

1. External Level (View Level): Describes the portion of the database relevant to a specific user group.
2. Conceptual Level (Logical Level): Describes what data is stored, including entities, data types, and relationships (the whole database design).
3. Internal Level (Physical Level): Describes physical storage structures, indexing, file organization, and compression on disk.

Data Independence is the ability to modify a schema definition at one level without affecting the schema definition at the next higher level.

• Logical Data Independence: Ability to alter the conceptual schema (e.g. adding a new attribute or table) without having to rewrite external views or user queries.
• Physical Data Independence: Ability to alter internal storage structures (e.g. switching from HDD to SSD, adding B-Tree indexes) without changing the conceptual schema.

SQL is categorized into 4 sub-languages depending on operation type:

This is one of the top 3 most frequently asked comparison questions in mass hiring technical assessments:

Database keys uniquely identify rows and enforce referential integrity:

• Super Key: Any set of one or more attributes that can uniquely identify a tuple in a relation.
• Candidate Key: A minimal super key with no redundant attributes. A table can have multiple candidate keys.
• Primary Key: The chosen candidate key by the DBA. It MUST NOT contain NULL values and must be unique.
• Alternate Key: All candidate keys that were not chosen as the primary key.
• Foreign Key: An attribute in a table that references the Primary Key of another table, establishing a parent-child relation.

Referential Integrity guarantees that a foreign key value in a child table must always correspond to an existing primary key in the parent table.

When a parent row is deleted, foreign key constraints define the cascade behavior:

• ON DELETE CASCADE: Automatically deletes all associated child rows.
• ON DELETE SET NULL: Sets foreign key values in the child table to NULL.
• ON DELETE RESTRICT / NO ACTION: Rejects the deletion of the parent row if child rows exist.

WHERE filters individual records before grouping occurs. HAVING filters aggregated groups after the GROUP BY clause is applied.

• INNER JOIN: Returns rows that have matching values in both tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table, and matched rows from the right table (unmatched right columns are NULL).
• RIGHT (OUTER) JOIN: Returns all rows from the right table, and matched rows from the left table.
• FULL (OUTER) JOIN: Returns all rows when there is a match in either table.
• CROSS JOIN: Returns the Cartesian product of rows from both tables ($N \times M$).

Two standard approaches asked in Accenture and TCS Digital interviews:

Normalization is the systematic process of decomposing relations to eliminate data redundancy and prevent data anomalies:

1. Insertion Anomaly: Inability to insert certain data without inserting unrelated data.
2. Deletion Anomaly: Loss of unintended data when deleting another piece of information.
3. Update Anomaly: Data inconsistency caused by updating data in some rows but not all copies.

Remember this progressive hierarchy for your interviews:

Denormalization is an optimization strategy where redundant data is deliberately added back to normalized tables to reduce costly JOIN operations in read-heavy analytics (OLAP) workloads and data warehouses.

A transaction is a logical unit of database work. It must satisfy ACID:

• Atomicity (All or Nothing): The entire transaction succeeds or rolls back completely. Managed by the Recovery Manager.
• Consistency: Database must transition from one valid state to another, preserving all integrity constraints.
• Isolation: Concurrent transactions execute independently without interference. Managed by the Concurrency Control Manager.
• Durability: Once committed, changes persist permanently in non-volatile storage, even during power loss. Managed by the Log Manager / WAL.

A transaction goes through 6 lifecycle states:

1. Active: Initial state; transaction operations are executing.
2. Partially Committed: Final statement has executed, but changes are not yet flushed to disk.
3. Committed: Successfully executed and permanent.
4. Failed: Normal execution can no longer proceed due to hardware/software check failure.
5. Aborted: Transaction rolled back and database restored to prior state.
6. Terminated: Transaction leaves the system.

When multiple transactions execute concurrently without proper isolation levels:

• Dirty Read (Uncommitted Dependency): Transaction $T_2$ reads data modified by $T_1$ which is subsequently rolled back.
• Non-Repeatable Read: $T_1$ reads a row twice, but between reads, $T_2$ updates or deletes that row, giving conflicting data.
• Phantom Read: $T_1$ queries a range of rows twice, but $T_2$ inserts a new row matching that range between queries.

An Index is an auxiliary data structure (usually a B+ Tree) that speeds up data retrieval without scanning every row in a table.

• Clustered Index: Dictates the physical order of data rows on disk. A table can have only ONE clustered index (usually the Primary Key).
• Non-Clustered Index: Stored separately from the data rows. Contains key values and pointers (Row IDs) to the physical rows. A table can have multiple non-clustered indexes.

B+ Trees have high fan-out (wide branching factor), which minimizes disk I/O operations. In B+ Trees, all actual data pointers are located strictly in leaf nodes, and leaf nodes are linked as a doubly-linked list, making sequential range scans extremely fast.

A Deadlock is a condition where two or more transactions are waiting indefinitely for locks held by each other (e.g. $T_1$ holds Lock A and waits for B, while $T_2$ holds Lock B and waits for A). It is detected using a Wait-For Graph (WFG) where a cycle indicates a deadlock.

2PL guarantees serializability by dividing lock acquisition into two distinct phases:

1. Growing Phase: Transaction may acquire locks, but cannot release any.
2. Shrinking Phase: Transaction may release locks, but cannot acquire any new locks.

A standard View is a virtual table containing no physical data (the underlying query runs every time the view is referenced). A Materialized View physically stores the query result on disk and must be refreshed periodically, enabling high-performance reads on expensive calculations.

• Stored Procedure: Precompiled set of SQL statements stored on the database server that can be explicitly invoked with parameters.
• Trigger: Special stored procedure that automatically executes (fires) in response to specific DML events (BEFORE/AFTER INSERT, UPDATE, DELETE) on a specified table.

WAL is a core durability protocol where any modification is first recorded in a persistent append-only transaction log before being written to the actual database data pages on disk. This ensures crash recovery can replay committed transactions.