Top 50 DBMS Interview Questions and Answers

Design a schema for a restaurant app like Zomato.

• Create tables for restaurants, users, reviews, and orders

• Include columns for restaurant details like name, address, cuisine type

• Include columns for user details like name, email, and password

• Link revie...read more

RDBMS is a type of DBMS that stores data in a structured manner using tables and enforces relationships between them.

• DBMS is a software system that manages databases, while RDBMS is a specific type of DBMS.

• RDBMS uses a tabular structure to store data...read more

Different types of joins in SQL include inner join, left join, right join, and full outer join.

• Inner join: Returns rows when there is at least one match in both tables.

• Left join: Returns all rows from the left table and the matched rows from the righ...read more

A transaction is a logical unit of work that consists of multiple database operations that must be executed as a single, indivisible unit.

• A transaction ensures data consistency and integrity.

• It follows the ACID properties: Atomicity, Consistency, Iso...read more

ADBMS stands for Advanced Database Management System which is an extension of DBMS with additional features.

• ADBMS has advanced features like data mining, data warehousing, and online analytical processing.

• ADBMS is used for handling large and complex ...read more

Different types of indexing in DBMS include primary, secondary, clustered, non-clustered, unique, and composite indexing.

• Primary indexing: Index based on the primary key of a table.

• Secondary indexing: Index based on a non-primary key column.

• Clustered...read more

Aggregate functions in DBMS are functions that operate on a set of values and return a single value as output.

• Aggregate functions perform calculations on multiple rows to return a single result.

• Examples include SUM, AVG, COUNT, MIN, and MAX.

• They are ...read more

DBMS stands for Database Management System. SQL is a programming language used to manage and manipulate data in a DBMS.

• DBMS is a software system that allows users to create, manage, and manipulate databases.

• SQL is used to communicate with a DBMS and ...read more

Sharding is a database partitioning technique to improve performance and scalability by distributing data across multiple servers.

• Sharding involves breaking up a database into smaller, more manageable parts called shards.

• Each shard contains a subset ...read more

BCNF stands for Boyce-Codd Normal Form, a higher level of normalization in database management systems.

• BCNF is a stricter form of 3NF (Third Normal Form) and ensures that there are no non-trivial functional dependencies between attributes.

• It helps in...read more

3-Tier Architecture of DBMS includes presentation, application, and data layers.

• Presentation Layer: User interface for interacting with the database (e.g. web browser)

• Application Layer: Business logic and processing of user requests (e.g. server-side...read more

A foreign key is a column or set of columns in a database table that refers to the primary key of another table.

• A foreign key ensures referential integrity between two tables.

• It is used to establish a relationship between two tables.

• It helps in maint...read more

Views in DBMS are virtual tables that display data from one or more tables.

• Views are created using SELECT statements.

• They can be used to simplify complex queries.

• Views can also be used to restrict access to sensitive data.

• They do not store data thems...read more

DBMS is a software system that manages databases, providing features like data storage, retrieval, and manipulation.

• DBMS stands for Database Management System.

• It provides a structured way to store and organize data.

• DBMS allows multiple users to acces...read more

Yes, I can design an ER diagram for an online shopping portal.

• Entities: User, Product, Order, Payment, Cart

• Relationships: User places Order, Order contains Product, Payment for Order, User has Cart

• Attributes: User (id, name, email), Product (id, name...read more

The database design for an ATM system should include tables for users, accounts, transactions, and ATM machines.

• Create a table for users with fields like user_id, name, pin, etc.

• Create a table for accounts with fields like account_id, user_id, balanc...read more

CRUD operations in DBMS refer to Create, Read, Update, and Delete operations for managing data.

• Create: Inserting new data into the database

• Read: Retrieving existing data from the database

• Update: Modifying existing data in the database

• Delete: Removing...read more

To join multiple models in a DBMS, use SQL JOIN statements based on common keys.

• Identify common keys between the models

• Use SQL JOIN statements (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN) to combine the models based on the common keys

• Specify the co...read more

ACID properties in DBMS ensure data integrity and consistency.

• Atomicity: All transactions are either fully completed or fully aborted. For example, transferring money from one account to another should be completed in its entirety.

• Consistency: The da...read more

Concurrency control is a technique used in databases to manage simultaneous access and modification of data by multiple users or processes.

• Concurrency control ensures that transactions are executed in a way that maintains data consistency and integri...read more

A correlated subquery is a subquery that references a column from the outer query, allowing for more complex filtering and data retrieval.

• Correlated subqueries are executed for each row processed by the outer query.

• They can be used to filter results ...read more

Normalization forms in a DBMS help reduce data redundancy and improve data integrity.

• 1NF (First Normal Form) - Each column contains atomic values, and there are no repeating groups.

• 2NF (Second Normal Form) - Meets 1NF and all non-key attributes are f...read more

The three levels of data abstraction in a Database Management System are Physical Level, Logical Level, and View Level.

• Physical Level: Deals with how data is stored on the storage medium. It includes details like data structures, file organization, a...read more

A clustered index is a type of index that sorts and stores the data rows in the table based on their key values.

• Defines the order in which data is physically stored in a table

• Only one clustered index per table

• Helps in improving the performance of que...read more

Integrity rules in a DBMS ensure data accuracy, consistency, and reliability.

• Constraints like primary key, foreign key, unique key enforce data integrity

• Referential integrity ensures relationships between tables are maintained

• Entity integrity ensures...read more

Intension refers to the attributes or properties of a concept, while extension refers to the instances or examples of that concept in a database.

• Intension describes the characteristics or properties of a concept.

• Extension refers to the actual instanc...read more

B+ Trees are advantageous in database management systems due to their ability to efficiently store and retrieve data.

• B+ Trees have a high fanout, allowing for more keys to be stored in each node, reducing the height of the tree and improving search p...read more