Guides › Database & SQL
DBMS Interview Questions and Answers: Complete Guide
Prepare for DBMS interviews with commonly asked questions on database fundamentals, keys, normalization, ER diagrams, SQL, transactions, ACID properties, indexing, concurrency, and database design.
DBMS Interview Questions and Answers: Complete Guide
Introduction
A Database Management System (DBMS) is software that helps users create, store, retrieve, update, and manage data. DBMS concepts are important for software engineering interviews, backend development, data engineering, and database administration.
This guide covers fundamental and intermediate DBMS interview questions with concise answers and examples.
1. What Is a DBMS?
A DBMS is software used to define, create, maintain, and control access to databases.
Examples include MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server.
2. What Is the Difference Between DBMS and RDBMS?
A DBMS is a broad term for software that manages databases. An RDBMS is a relational DBMS that organizes data into related tables.
Relational systems commonly support keys, constraints, and SQL.
3. What Is a Database?
A database is an organized collection of data that can be accessed and managed efficiently.
Examples include a student database, banking database, and e-commerce database.
4. What Is a Table?
A table stores related data in rows and columns. Each row represents a record, while each column represents an attribute.
5. What Is a Primary Key?
A primary key uniquely identifies each row in a table. It cannot contain NULL values, and its values must be unique.
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
6. What Is a Foreign Key?
A foreign key establishes a relationship with a key in another table, or sometimes in the same table. It helps maintain referential integrity.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
student_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
7. What Are Candidate Keys?
Candidate keys are minimal sets of attributes that can uniquely identify a row. One candidate key is selected as the primary key; other candidate keys may be called alternate keys.
8. What Is a Super Key?
A super key is any set of attributes that uniquely identifies a row. Unlike a candidate key, it may contain extra attributes that are not needed for uniqueness.
9. What Is a Composite Key?
A composite key consists of two or more columns used together to uniquely identify a row.
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
10. What Is Normalization?
Normalization organizes relational tables to reduce unnecessary data duplication and prevent insertion, update, and deletion anomalies.
Common normal forms include 1NF, 2NF, 3NF, and BCNF.
11. Explain First Normal Form (1NF)
A relation is in 1NF when each field contains an atomic value and there are no repeating groups.
For example, instead of storing multiple phone numbers in one field, use a suitable separate structure when the data model requires multiple numbers.
12. Explain Second Normal Form (2NF)
A relation is in 2NF when it is in 1NF and every non-prime attribute is fully dependent on every candidate key, rather than only part of a composite candidate key.
13. Explain Third Normal Form (3NF)
A relation is in 3NF when it is in 2NF and non-key attributes do not have inappropriate transitive dependencies on candidate keys. More formally, for every nontrivial functional dependency X → A, X is a super key or A is a prime attribute.
14. What Is BCNF?
Boyce-Codd Normal Form is a stronger form of 3NF. For every nontrivial functional dependency X → Y, X must be a super key.
15. What Is Denormalization?
Denormalization deliberately introduces redundancy or precomputed data to improve selected read workloads. It can improve performance but adds complexity to updates and consistency.
16. What Is an ER Diagram?
An Entity-Relationship diagram represents entities, their attributes, and relationships.
For a college database:
- Student is an entity.
- Course is an entity.
- Enrollment connects students and courses.
- Student ID and Course ID can identify related records.
17. What Are Database Relationships?
- One-to-one: One record relates to at most one record on the other side.
- One-to-many: One record can relate to many records.
- Many-to-many: Many records on both sides can relate, typically through a junction table.
18. What Is SQL?
SQL stands for Structured Query Language. It is used to define database structures and retrieve, insert, update, and delete data in relational database systems.
19. What Are DDL, DML, DQL, DCL, and TCL?
These are commonly taught SQL command categories:
- DDL: Data Definition Language, such as CREATE, ALTER, and DROP.
- DML: Data Manipulation Language, such as INSERT, UPDATE, and DELETE.
- DQL: Data Query Language, commonly associated with SELECT.
- DCL: Data Control Language, such as GRANT and REVOKE.
- TCL: Transaction Control Language, such as COMMIT and ROLLBACK.
Exact classifications can vary between educational sources and database implementations.
20. What Is the Difference Between DELETE, TRUNCATE, and DROP?
- DELETE removes selected rows, or all rows if no WHERE condition is used.
- TRUNCATE removes all rows from a table using database-specific behavior.
- DROP removes the table object itself.
Transaction, identity-reset, locking, and rollback behavior differ by database system.
21. What Is a JOIN?
A JOIN combines rows from tables based on a matching condition.
Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN.
22. What Is an Index?
An index is a data structure that can help a database find rows more efficiently. Indexes can improve reads but require storage and may increase the cost of INSERT, UPDATE, and DELETE operations.
23. What Is a View?
A view is a named query that can be accessed like a virtual table. Its underlying data generally comes from the referenced tables, although some systems also support materialized views that store results.
24. What Is a Transaction?
A transaction is a logical unit of database work. Depending on the system and operation, it can be committed or rolled back.
25. Explain ACID Properties
- Atomicity: A transaction succeeds as a unit or is rolled back.
- Consistency: Database rules and constraints remain satisfied.
- Isolation: Concurrent transactions are controlled to limit interference.
- Durability: Committed changes persist according to the database's durability guarantees.
26. What Is COMMIT?
COMMIT completes a transaction and makes its changes permanent according to the database system's transaction guarantees.
27. What Is ROLLBACK?
ROLLBACK undoes uncommitted changes within the relevant transaction, subject to the database system's behavior.
28. What Is Concurrency Control?
Concurrency control coordinates simultaneous database operations so they do not cause unacceptable inconsistencies.
Techniques include locks, multiversion concurrency control (MVCC), and isolation-level mechanisms.
29. What Is a Deadlock?
A deadlock occurs when transactions wait on resources held by each other so that none can proceed. Database systems may detect deadlocks and abort a transaction to resolve them.
30. What Are Transaction Isolation Levels?
Common SQL isolation levels are:
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
Their exact behavior varies across database systems. Common phenomena to study include dirty reads, non-repeatable reads, and phantom reads.
31. What Is the Difference Between SQL and NoSQL?
SQL databases commonly use relational tables and structured schemas. NoSQL is a broad category that includes document, key-value, wide-column, and graph databases.
The right choice depends on data relationships, query patterns, consistency needs, scalability, and operational requirements.
32. What Is a Stored Procedure?
A stored procedure is a named routine stored in the database that can execute database operations. Syntax and capabilities vary by system.
33. What Is a Trigger?
A trigger is database logic that executes automatically in response to defined events, such as an INSERT, UPDATE, or DELETE. Triggers should be designed carefully because hidden side effects can complicate debugging.
34. What Is Functional Dependency?
A functional dependency X → Y means that the values of attribute set X determine the values of attribute set Y within a relation.
Functional dependencies are important for understanding candidate keys and normalization.
35. What Is a Schema?
A schema describes database objects and their organization. In systems such as PostgreSQL, a schema is also a namespace that contains objects such as tables, views, and functions.
36. How Do You Find Duplicate Values in SQL?
Example: find email addresses used by more than one row.
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
37. How Do You Find the Second-Highest Salary?
One approach uses a distinct salary subquery:
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
This returns NULL if no second distinct salary exists. If multiple employees have the second-highest salary, this query returns the salary value, not all matching employees.
38. How Do You Find Employees Without a Department?
SELECT e.*
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
This finds employees without a matching department row. If the business rule also considers a NULL department_id unassigned, those employees are included.
39. How Do You Improve a Slow SQL Query?
- Inspect the execution plan.
- Check whether appropriate indexes exist.
- Return only required columns.
- Review joins, filters, and row counts.
- Avoid unnecessary repeated queries.
- Keep statistics current when applicable.
- Test with realistic data and measure the impact.
40. How Should You Prepare for DBMS Interviews?
- Learn database fundamentals and keys.
- Understand normalization and functional dependencies.
- Practice SQL queries and joins.
- Study indexes and execution plans.
- Understand transactions, ACID, and isolation.
- Draw ER diagrams and design sample schemas.
- Solve practical SQL problems.
- Explain your reasoning aloud and discuss trade-offs.
Conclusion
Strong DBMS preparation requires both theory and hands-on SQL practice. Build small databases, write queries, inspect results, and explain why your design works. Focus on understanding concepts rather than memorizing definitions.
More guides
- How to Use Codify: A 7-Day Start Plan — New here? Pick a roadmap, make a study plan, start the DSA sheet, revise with notes, practise for placements and test yourself — one step a day, then a daily routine.
- Infosys Preparation Guide: Online Test, Pseudocode and Interview — Roles (SE, DSE, SP), what each section of the Infosys online test checks, how to crack pseudocode and reasoning, a 4-week plan and the interview.
- Wipro Elite NLTH Preparation: Aptitude, Essay and Coding — The Wipro fresher test round by round — quant, logical and verbal, the written essay, two coding problems — plus a 3-week plan and interview tips.
- Accenture Preparation Guide: Cognitive, Technical, Coding and Communication — Every Accenture round explained — cognitive, MS Office/networking/cloud technical MCQs, pseudocode, coding and the spoken-English test — with a 4-week plan.