- Technical Interview Questions
- HTML Interview Questions
- CSS Interview Questions
- JavaScript Interview Questions
- React JS Interview Questions
- Node.js interview questions
- MERN Stack Interview Questions
- Data Analytics Interview Questions
- C++ Technical Interview Questions
- Python Interview Questions
- Java Interview Question
- Most Asked Coding Question
- DSA interview questions
- Computer Network Interview Question
- Cloud Computing Interview Question
- Other Related Links
Apply For Jobs
Get Hiring UpdatesDBMS Interview Questions: The Complete 2025 Study Guide
Top 50 Database Interview Questions and Answers
Are you gearing up for a DBMS interview? PrepInsta provides a wealth of commonly asked DBMS interview questions and answers. Whether you’re a newcomer to the field or an experienced candidate,
You can access the most up-to-date insights into DBMS topics. Enhance your interview readiness for DBMS with the invaluable resources available at PrepInsta and approach your interview with confidence.
Introduction to DBMS
Answer:-
A Database Management System (DBMS) is a software application or system that enables users to efficiently store, organize, retrieve, and manipulate data. It serves as an intermediary between the user and the underlying database, providing a structured and controlled environment for managing data.
USES OF Database
- Business: Manages customer data, products, finances, and employees.
- E-commerce: Handles product catalogs, inventory, orders, and customer data.
- Banking: Manages accounts, transactions, loans, and risk analysis.
- Education: Organizes student records, schedules, and administrative data.
- Government: Handles citizen data, tax collection, and infrastructure.
- Transportation: Manages schedules, reservations, and logistics.
- Manufacturing: Controls inventory, production, and supply chains.
- Telecom: Stores call records, customer data, and network info.
- Social Media: Manages user content, profiles, and interactions.
- Scientific Research: Stores and analyzes research data.
DBMS Interview Questions and Answers for Freshers
What is DBMS?
Ans.
DBMS is a collection of programs that provides users to create and maintain a database. It manages incoming data, Organizes to provide a better and easy way to users. Or in a Simpler way, DBMS provides us an interface or tool for performing different operations such as the creation of a database, inserting data into it, deleting data from it, updating the data, etc. DBMS is a software in which data is stored in a more secure way as compared to the file-based system. Using DBMS, we can overcome many problems such as data redundancy, data inconsistency, easy access, more organized and understandable, and so on.
Types of DBMS.
Ans.
Types of DBMS:
- RDBMS: Organizes data in tables using SQL. Examples: Oracle, MySQL, SQL Server.
- NoSQL: Handles unstructured data, scales horizontally. Types: Document, Key-Value, Column-Family, Graph. Examples: MongoDB, Cassandra.
- OODBMS: Stores data in object-oriented models. Examples: ObjectDB, db4o.
- In-Memory DBMS: Stores data in main memory for speed. Examples: Redis, SAP HANA.
- Distributed DBMS: Spreads data across servers for scalability. Examples: Google Bigtable, Hadoop.
Benefits of Database.
Ans.
- Data Centralization: All data is stored in one place, making it easier to manage and maintain.
- Data Consistency: DBMS enforces data integrity rules, ensuring that data remains accurate and consistent.
- Data Security: Access control mechanisms protect sensitive information from unauthorized users.
- Data Scalability: Many DBMS systems can scale to handle increasing data volumes and user loads.
- Data Recovery: Built-in backup and recovery mechanisms safeguard against data loss.
- Data Querying: SQL support allows users to retrieve and analyze data efficiently.
Key functions of DBMS.
Ans.
- Data Storage: Organizes data in structured formats like tables.
- Data Retrieval: Allows efficient data access through queries.
- Data Manipulation: Supports adding, updating, and deleting data.
- Data Security: Controls data access for authorized users.
- Data Integrity: Enforces data accuracy and reliability.
- Concurrency Control: Manages simultaneous data access.
- Backup and Recovery: Safeguards data and offers recovery tools.
- Query Optimization: Enhances query performance.
What is database partitioning?
Ans.
It (Database partitioning) is a process where a logical database is divided into different independent parts. The database objects like tables, indexes are subdivided and managed, and accessed at the granular level.
Database Normalization Interview questions
Define Normalization?
Ans. Organizing data into a relatable Table is known as Normalization.
Enlist the advantages of a normalizing database?
Advantages of the normalizing database are:
- No duplicate entries
- Saves storage space
- Boasts the query performances.
Why do you need to use functional dependencies while normalizing databases?
Ans.
Functional dependencies are essential in the process of normalizing databases because they help ensure that a database design adheres to certain rules and best practices for data organization. Normalization is the process of structuring a database schema to eliminate redundancy and improve data integrity.
What are the different steps involved in normalizing a database?
Ans.
Steps in Normalizing a Database:
- Identify entities and attributes.
- Define primary keys.
- Apply 1NF (atomic values, no repeating groups).
- Apply 2NF (full key dependency).
- Apply 3NF (remove transitive dependencies).
- Apply BCNF (no partial/overlapping dependencies).
- Apply 4NF (handle multi-valued dependencies).
- Apply 5NF (address join dependencies).
- Evaluate and optimize for performance.
- Document schema, implement tables, test, and maintain.
Is there any difference between “normalization” and “database normalization”? If yes, then what’s the difference?
Ans.
“Normalization” is a general concept of organizing data, while “database normalization” specifically refers to structuring data in relational databases to reduce redundancy and enhance integrity.
Database Questions Asked in Interview
What are the different levels of abstraction in the DBMS?
Ans.
There are 3 levels of data abstraction in the DBMS.
They include:
Physical Level: This is the lowest level of the data abstraction which states how the data is stored in the database.
Logical Level: This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.
View Level: This is the highest level in the data abstraction which shows/states only a part of the database.
What is a checkpoint in DBMS and when does it occur?
Ans.
A checkpoint is a mechanism where all the previous logs are removed from the system and are permanently stored on the storage disk. So, basically, checkpoints are those points from where the transaction log record can be used to recover all the committed data up to the point of crash.
What is the Relationship?
Ans.
The Relationship is defined as an association among two or more entities. There are three types of relationships in DBMS-
- One-To-One: Here one record of any object can be related to one record of another object.
- One-To-Many (many-to-one): Here one record of any object can be related to many records of other objects and vice versa.
- Many-to-many: Here more than one record of an object can be related to n number of records of another object.
What is RDBMS?
Ans.
RDBMS is the Relational Database Management System which contains data in the form of the tables and data is accessed on the basis of the common fields among the tables.
Enlist the disadvantages of the query?
Ans.
The disadvantages of the query are:
- No indexes
- Stored procedures are excessively compiled.
- Triggers and procedures are without SET NOCOUNT ON.
- Complicated joins making up inadequately written queries.
- Cursors and temporary tables showcase a bad presentation.
Database Questions in Interview
What is the purpose of normalization in DBMS?
Ans.
Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys to fulfilling certain properties.
The properties include:
- To minimize the redundancy of the data.
- To minimize the Insert, Delete, and Update Anomalies.
What is an ER diagram in DBMS?
Ans.
An entity-relationship model or an entity-relationship diagram is a visual representation of data that is represented as entities, attributes, and relationships are set between entities.
What are the integrity rules in DBMS?
Ans.
Data integrity is one significant aspect while maintaining the database. So, data integrity is enforced in the database system by imposing a series of rules. Those set of integrity is known as the integrity rules.
There are two integrity rules in DBMS:
Entity Integrity: It specifies that the “Primary key cannot have a NULL value.”
Referential Integrity: It specifies that the “Foreign Key can be either a NULL value or should be the Primary Key value of other relation
Define Atomicity and Aggregation?
Ans.
- Atomicity: It’s an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.
- Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.
What is an entity-relationship model?
Ans.
It is a diagrammatic approach to database design, where you represent real-world objects as entities and mention relationships between them. This approach helps the team of DBA’s to understand the schema easily.
Understanding SQL's Role in Interviews
SQL is a core skill expected in many job roles, especially where data handling and backend logic are involved. Interviewers use SQL to assess logical thinking, database familiarity, and real world problem solving.
- Used across software development, analytics, and data engineering roles.
- Essential for interacting with relational databases.
- Often included in screening rounds and technical interviews.
SQL Interview Questions And Answers
What is the difference between `DELETE`, `TRUNCATE`, and `DROP`?
Ans.
DELETE removes rows from a table based on a condition and can be rolled
back.
TRUNCATE removes all rows from a table without logging individual row
deletions; it cannot be rolled back.
DROP removes the entire table or database structure permanently
Explain the concept of normalization and denormalization.
Ans.
Normalization is the process of organizing data to reduce redundancy and
improve data integrity. It involves dividing large tables into smaller
ones and defining relationships between them.
Denormalization is the process of combining tables to reduce the
complexity of queries, often at the expense of redundancy
What are window functions in SQL? Provide an example
Ans.
Window functions perform calculations across a set of table rows related
to the current row. For example, ROW_NUMBER() assigns a unique number to
each row within a partition of a result set.
Example:
SELECT name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees
What is a correlated subquery?
Ans.
A correlated subquery is a subquery that references columns from the outer
query. It is evaluated once for each row processed by the outer query.
Example:
SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department)
How would you find the second highest salary from a table?
Ans.
You can use a subquery to find the second highest salary:
SELECT MAX(salary) AS SecondHighestSalary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)
What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?
Ans.
INNER JOIN returns rows when there is a match in both tables.
LEFT JOIN returns all rows from the left table, and matched rows from the
right table; NULLs if no match.
RIGHT JOIN returns all rows from the right table, and matched rows from
the left table; NULLs if no match
Explain the use of `GROUP BY` and `HAVING` clauses.
Ans.
`GROUP BY` groups rows that have the same values into summary rows.
`HAVING` filters records after the `GROUP BY` operation.
Example:
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000
What are indexes, and why are they used?
Ans.
Indexes are database objects that improve the speed of data retrieval
operations on a table. They are used to quickly locate data without having
to search every row.
What is a stored procedure?
Ans.
A stored procedure is a set of SQL statements that can be stored and
executed on the database server. It allows for modular programming and can
improve performance by reducing network traffic.
Explain the concept of ACID properties.
Ans.
ACID stands for:-
Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted.
Consistency: Ensures that the database moves from one valid state to another.
Isolation: Ensures that operations of one transaction are isolated from
others.
Durability: Ensures that once a transaction is committed, it will remain
so, even in the event of a system failure
What is a composite key?
Ans.
A composite key is a primary key composed of two or more columns in a table. It is used when a single column is not sufficient to uniquely identify a record.
How would you handle NULL values in SQL?
Ans.
You can handle NULL values using:-
`IS NULL` or `IS NOT NULL` to check for NULL values.
`COALESCE()` to replace NULL with a specified value.
`IFNULL()` or `NVL()` to return a specified value if the expression is
NULL
What is a trigger in SQL?
Ans.
A trigger is a set of SQL statements that automatically execute or fire
when certain events occur, such as `INSERT`, `UPDATE`, or `DELETE`
operations on a table
Explain the difference between `CHAR` and `VARCHAR` data types.
Ans.
`CHAR` is a fixed-length data type; it pads the data with spaces if the value is shorter than the defined length. `VARCHAR` is a variable-length data type; it only uses as much space as needed for the data.
What is a foreign key?
Ans.
A foreign key is a column or a set of columns in one table that uniquely identifies a row of another table. It is used to establish and enforce a link between the data in the two tables
What is a view in SQL?
Ans.
A view is a virtual table based on the result set of an SQL query. It contains rows and columns, just like a real table, but it does not store data itself
What is the purpose of the `DISTINCT` keyword?
Ans.
The `DISTINCT` keyword is used to return only distinct (different) values in the result set, eliminating duplicate records.
Explain the difference between `TRUNCATE` and `DELETE`.
Ans.
TRUNCATE is a DDL command that removes all rows from a table without
logging individual row deletions; it cannot be rolled back.
DELETE is a DML command that removes rows based on a condition and can be
rolled back
What is a subquery?
Ans.
A subquery is a query nested within another query. It is used to perform operations that require multiple steps, such as filtering records based on the result of another query
How would you calculate the running total in SQL?
Ans.
You can calculate the running total using a window function:
SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_id) AS running_total FROM orders;
DBMS Interview Questions and Answers For Experienced.
Popular DBMS Software?
Ans.
Here, is the list of some popular DBMS systems:
- MySQL
- Microsoft Access
- Oracle
- PostgreSQL
- dBASE
- FoxPro
- SQLite
- IBM DB2
- LibreOffice Base
- MariaDB
- Microsoft SQL Server etc.
What is concurrency control?
Ans.
This is a process managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control:
- Optimistic approach – Involves versioning
- Pessimistic approach – Involves locking
What is the purpose of SQL?
Ans.
SQL stands for Structured Query Language whose main purpose is to interact with the relational databases in the form of inserting and updating/modifying the data in the database.
Disadvantage of DBMS?
Ans.
DBMS may offer plenty of advantages but, it has certain flaws-
- Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization.
- Most database management systems are often complex systems, so training for users to use the DBMS is required.
- In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media
- Use of the same program at a time by many users sometimes leads to the loss of some data.
- DBMS can’t perform sophisticated calculations
What are the unary operations in Relational Algebra?
Ans.
Unary operations are operations that use single operands. Unary operations in relational algebra are PROJECTION and SELECTION.
-=,>=,<=,+= are the relational operators used in SELECTION.
Database Management Interview Questions and Answers
How many types of database languages are?
Ans.
There are four types of database languages:
- Data Definition Language (DDL): These commands are used for updating the data. CREATE, ALTER, DROP, TRUNCATE, RENAME are some examples of DDL commands.
- Data Manipulation Language (DML): These commands are used for the manipulation of already updated data. SELECT, UPDATE, INSERT, DELETE are some examples of DML commands.
- Data Control Language (DCL) : These commands are used for giving and removing user access to the database. GRANT and REVOKE are the examples of DCL commands.
- Transaction Control Language (TCL) : These are the commands used for managing transactions in the database. TCL is used for managing the changes made by DML. COMMIT, ROLLBACK, and SAVEPOINT are the examples of TCL commands.
Define a Relation Schema?
Ans.
A relation schema is known as the blueprint with the help of which we can explain how the data is organized into tables.
What is a degree of Relation?
Ans.
A degree of relation is also known as Cardinality it is defined as the number of occurrence of one entity which is connected to the number of occurrence of other entity.
What are the disadvantages of file processing systems?
Ans.
There are some disadvantages of file processing system :
- Inconsistent
- Not secure
- Data redundancy
- Difficult in accessing data
- Data isolation
- Data integrity
- Concurrent access is not possible
- Limited data sharing
- Atomicity problem
What is data abstraction in DBMS?
Ans.
Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interaction with the database.
DBMS Interview Questions Intermediate Level
What is Relational Algebra?
Ans.
Relational Algebra is a Procedural Query Language that contains a set of operations that take one or two relations as input and produce a new relationship. Relational algebra is the basic set of operations for the relational model. The decisive point of relational algebra is that it is similar to the algebra which operates on the number.
There are a few fundamental operations of relational algebra:
- select
- project
- set difference
- union
- rename, etc.
What is Relational Calculus?
Ans.
Relational Calculus is a Non-procedural Query Language that uses mathematical predicate calculus instead of algebra. Relational calculus doesn’t work on mathematics fundamentals such as algebra, differential, integration, etc. That’s why it is also known as predicate calculus.
There is two types of relational calculus:
- Tuple relational calculus
- Domain relational calculus
Explain ACID properties?
Ans.
ACID properties are some basic rules, which have to be satisfied by every transaction to preserve the integrity. These properties and rules are:
ATOMICITY: Atomicity is more generally known as ? all or nothing rule.’ This implies all are considered as one unit, and they either run to completion or are not executed at all.
CONSISTENCY: This property refers to the uniformity of the data. Consistency implies that the database is consistent before and after the transaction.
ISOLATION: This property states that the number of transactions can be executed concurrently without leading to the inconsistency of the database state.
DURABILITY: This property ensures that once the transaction is committed it will be stored in the non-volatile memory and a system crash can also not affect it anymore.
What do you understand by Data Model?
Ans.
A data model is a conceptual framework that defines how data is organized, structured, and related within a database or information system. It includes entities (objects), attributes (properties), relationships (connections), and constraints (rules). Data models are essential for designing and managing data effectively in software and database development. accessible. In this architecture, the application on the client-end interacts with an application on the server which further communicates with the database system.
What is Data Warehousing?
Ans.
Data warehousing is the process of collecting, storing, and managing data from various sources in a central repository. It’s used to support business intelligence and analytics by providing a unified, historical, and structured view of an organization’s data for better decision-making.
Explain the difference between intension and extension in a database.
Ans.
- Intension: The conceptual design of a database, specifying its structure, rules, and relationships. Static and defines how data should be organized. Example: Defining tables like “Authors” with attributes.
- Extension: Actual data instances in the database at a given time. Dynamic and changes as data is added, modified, or deleted. Example: Specific books and authors currently in the library database.
Explain the difference between the DELETE and TRUNCATE command in a DBMS.
Ans.
- DELETE Command:
- Granularity: Row-level deletion based on conditions.
- Logging: Generates individual log entries for each deleted row.
- Performance: Slower, especially for many rows.
- Usage: Selective row removal.
- Example:
DELETE FROM employees WHERE salary < 50000;
- TRUNCATE Command:
- Granularity: Table-level, removes all rows.
- Logging: Minimally logged or non-logged, faster.
- Performance: Faster, especially for large tables.
- Usage: Quick removal of all data in a table.
- Example:
TRUNCATE TABLE employees;
What is meant by normalization and denormalization?
Ans.
- Normalization: Organizes data to eliminate redundancy and maintain integrity by dividing it into related tables.
- Denormalization: Introduces redundancy to enhance query performance and simplify joins by combining tables or duplicating data.
- Benefits: Normalization reduces data duplication and minimizes anomalies, while denormalization improves query speed and simplifies queries.
- Example: Normalization separates customer and order data, while denormalization may duplicate customer info in the order table for faster queries.
Explain different types of Normalization forms in a DBMS.
Ans.
Normalization forms in database design:
- 1NF: Ensure columns have atomic values, unique names, and order independence.
- 2NF: Eliminate partial dependencies, ensuring non-key attributes depend on the entire primary key.
- 3NF: Remove transitive dependencies, so non-key attributes rely solely on the primary key.
- BCNF: Eliminate all non-trivial functional dependencies, ensuring tables are free from redundancy.
- 4NF: Address multi-valued dependencies to prevent anomalies with multiple attribute values.
- 5NF: Handle join dependencies, enabling further table decomposition without data loss.
Each form enhances data integrity and reduces redundancy based on specific rules and objectives. Choice depends on database requirements and trade-offs.
What is the 3-Tier architecture?
Ans.
The 3-Tier architecture splits an application into three parts: user interface (presentation), logic (application), and data storage (database). It enhances scalability, maintainability, and security in software design.
The 3-Tier architecture divides an application into three layers:
- Presentation Tier (UI).
- Application Tier (Logic).
- Data Tier (Database).
It improves scalability, maintainability, security, and flexibility in software development.
What is 2-Tier architecture?
Ans.
2-Tier architecture divides an app into a client-side (UI) and server-side (logic and data) component. It’s simple but lacks scalability and flexibility seen in newer architectures.
2-Tier architecture divides an app into:
Client Tier: Handles UI and user interaction.
Server Tier: Manages logic and data processing.
It’s simple but less scalable and flexible compared to newer architectures like 3-Tier.
What is the difference between a shared lock and exclusive lock?
Ans.
Shared Lock vs. Exclusive Lock:
Shared Lock: For reading, allows multiple transactions to read concurrently, compatible with other shared locks, doesn’t block them.
Exclusive Lock: For writing, ensures only one transaction can write, blocks all other locks (shared and exclusive) until released.
How do you communicate with an RDBMS?
Ans.
To communicate with an RDBMS:
- Use SQL queries for data operations.
- Utilize programming language APIs (e.g., JDBC, psycopg2).
- Employ ORM frameworks for object-oriented interaction.
- Access via command-line interfaces, GUIs, or web-based interfaces.
- Middleware and REST APIs enable web services.
- Stored procedures and functions for predefined operations.
What is Weak Entity set?
Ans.
A Weak Entity Set is an entity in a database that lacks a unique identifier on its own and depends on another related entity (the owning entity) for uniqueness. It often has an existence dependency on the owning entity and uses a combination of attributes and the owning entity’s identifier to uniquely identify its instances.
What is an attribute?
Ans.
In a DBMS, an attribute is a characteristic or property that describes a specific aspect of an entity or object within a database. It corresponds to a column in a table and has a defined data type.
What is 1NF in the DBMS?
Ans.
1NF (First Normal Form) in DBMS:
- Ensures each attribute contains atomic (indivisible) values.
- Eliminates repeating groups by separating them into separate tables.
- Creates separate tables for many-to-many relationships using junction tables.
What is 2NF in the DBMS?
Ans.
2NF (Second Normal Form) in DBMS:
- Builds upon 1NF.
- Ensures that non-key attributes are fully functionally dependent on the entire primary key.
- Eliminates partial dependencies by separating them into related tables.
- Suitable for more complex databases with composite primary keys.
What is 3NF in the DBMS?
Ans.
3NF (Third Normal Form) in DBMS:
- Builds upon 2NF.
- Ensures that non-key attributes are not transitively dependent on the primary key.
- Eliminates transitive dependencies by separating them into related tables.
- Aims to achieve better data integrity and reduce data redundancy.
What is BCNF in the DBMS?
Ans.
BCNF (Boyce-Codd Normal Form) in DBMS:
- A stricter form of normalization.
- Ensures that for every non-trivial functional dependency X -> Y, X is a superkey.
- Eliminates partial and transitive dependencies.
- Reduces data redundancy and enhances data integrity.
- Suitable for complex databases with more stringent constraints.
What is the main goal of RAID technology?
Ans.
The main goal of RAID (Redundant Array of Independent Disks) technology is to improve data storage reliability, availability, and performance by combining multiple physical hard drives into a single logical unit. RAID achieves this primarily through techniques like data striping, mirroring, and redundancy. The specific goals of RAID technology include:
Also Check
Join Our Interview Course Now to Get Yourself Prepared -
Join Our Interview Course Now to Get Yourself Prepared
Prepare for the interview process in both Service and Product Based companies along with Group Discussion, Puzzles, Resume Building, HR, MR and Technical Interviews.
Login/Signup to comment