DBMS (Database Management) interview questions & answers
A Database Management System (DBMS) is software that lets you define, store, query, and manage data while enforcing integrity and concurrent access. Relational databases organize data into tables of rows and columns linked by keys and are queried with SQL. Interviews test whether you can design clean schemas, write correct queries, and reason about transactions and performance.
Updated 2026-06-18 · 16 real, commonly-asked questions with answers.
Key takeaways
- A Database Management System (DBMS) is software that lets you define, store, query, and manage data while enforcing integrity and concurrent access.
- Core areas to revise for DBMS (Database Management): Relational model & keys, SQL queries & joins, Normalization, Transactions & ACID, Indexing.
- This guide answers 16 of the most-asked DBMS (Database Management) interview questions — rehearse them in OnJob's free AI mock interview.
Top 16 DBMS (Database Management) interview questions
Q1.What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in a table and cannot be null or duplicated. A foreign key is a column in one table that references the primary key of another, enforcing referential integrity between related tables. A table has at most one primary key but can have several foreign keys.
Q2.What is database normalization?
Normalization is the process of organizing tables to reduce redundancy and avoid update, insert, and delete anomalies. It proceeds through normal forms: 1NF removes repeating groups, 2NF removes partial dependencies on a composite key, and 3NF removes transitive dependencies. The trade-off is more joins, which is why analytical systems sometimes denormalize for speed.
Q3.Explain the ACID properties of a transaction.
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means a transaction fully completes or fully rolls back; Consistency means it leaves the database in a valid state; Isolation means concurrent transactions do not interfere; Durability means committed changes survive crashes. Together they guarantee reliable transaction processing.
Q4.What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes selected rows and can use a WHERE clause, logging each row so it can be rolled back. TRUNCATE removes all rows quickly with minimal logging and resets identity counters but keeps the table structure. DROP removes the entire table, including its structure, indexes, and data.
Q5.What is an index and how does it improve performance?
An index is a separate data structure, usually a B-tree, that lets the database find rows without scanning the whole table. It speeds up SELECT and WHERE lookups but slows down INSERT, UPDATE, and DELETE because the index must also be maintained. Indexes also consume extra storage, so you add them selectively on frequently queried columns.
Q6.What is the difference between WHERE and HAVING?
WHERE filters individual rows before any grouping occurs and cannot use aggregate functions. HAVING filters groups after a GROUP BY has been applied and can reference aggregates like COUNT or SUM. In a query you typically use WHERE to narrow rows first, then GROUP BY, then HAVING to filter the resulting groups.
Q7.Explain the types of SQL joins.
An INNER JOIN returns only rows with matching keys in both tables. A LEFT (or RIGHT) JOIN returns all rows from one side plus matches from the other, filling nulls where there is no match. A FULL OUTER JOIN returns all rows from both sides, and a CROSS JOIN returns the Cartesian product of every combination.
Q8.What is the difference between a clustered and a non-clustered index?
A clustered index determines the physical order of rows in the table, so there can be only one per table, and the leaf level is the data itself. A non-clustered index is a separate structure that stores key values with pointers to the rows, and a table can have many. Clustered indexes are fast for range scans; non-clustered ones suit lookups on secondary columns.
Q9.What is a transaction isolation level?
An isolation level controls how visible one transaction's uncommitted changes are to others, balancing consistency against concurrency. The SQL standard defines Read Uncommitted, Read Committed, Repeatable Read, and Serializable, in increasing strictness. Higher levels prevent anomalies like dirty reads, non-repeatable reads, and phantom reads but reduce parallelism.
Q10.What is the difference between SQL and NoSQL databases?
SQL databases are relational, use fixed schemas and tables, and guarantee strong ACID consistency, making them ideal for structured, related data. NoSQL databases use flexible models like documents, key-value pairs, or graphs and often favor scalability and availability over strict consistency. The choice depends on data structure, query patterns, and scaling needs.
Q11.What is a view in a database?
A view is a virtual table defined by a stored query that does not hold data itself but presents results from one or more underlying tables. It simplifies complex queries, restricts column or row access for security, and provides a stable interface even if base tables change. A materialized view, by contrast, physically stores the result and must be refreshed.
Q12.What is denormalization and when would you use it?
Denormalization deliberately adds redundant data to a normalized schema to reduce the number of joins at query time. It is used in read-heavy or analytical workloads where query speed matters more than storage and write simplicity. The cost is more storage and the burden of keeping duplicated data consistent on writes.
Q13.Explain the difference between UNION and UNION ALL.
UNION combines the result sets of two queries and removes duplicate rows, which requires an extra sorting or hashing step. UNION ALL combines them and keeps all rows including duplicates, so it is faster. You use UNION when distinct results matter and UNION ALL when duplicates are acceptable or impossible.
Q14.What is a deadlock and how can it be prevented?
A deadlock occurs when two or more transactions each hold a lock the other needs, so none can proceed. Databases usually detect deadlocks and roll back one transaction to break the cycle. Prevention strategies include acquiring locks in a consistent order, keeping transactions short, and using lower isolation levels where safe.
Q15.What is the difference between DDL, DML, and DCL?
DDL (Data Definition Language) defines schema with commands like CREATE, ALTER, and DROP. DML (Data Manipulation Language) works with data using SELECT, INSERT, UPDATE, and DELETE. DCL (Data Control Language) manages permissions with GRANT and REVOKE.
Q16.What is a stored procedure?
A stored procedure is a named set of SQL statements precompiled and stored in the database that you can call by name with parameters. It reduces network traffic, centralizes business logic, and can improve performance through reuse of execution plans. The trade-off is that heavy logic in the database can be harder to version and test than application code.
More interview topics
Practise & prepare
Practise DBMS (Database Management) out loud
Reading answers is step one. Rehearse them in OnJob's free AI mock interview, get instant feedback, then apply to AI-matched jobs in one click.