Home

Learning Hub

Articles

Mostly Asked DBMS Interview Questions (With Answers)

Database Management Systems (DBMS) have become one of the fastest-growing fields, assisting cloud technology. Today, data has become a significantly crucial asset for all, and without DBMS, cloud computing cannot be implemented as it provides all the methods and techniques to manage huge amounts of data. Therefore understanding and mastering DBMS techniques is highly essential for anyone preparing for a related role in any organisation.

In this post, we will be looking for mostly asked DBMS interview questions (with answers), which are categorised on the basis of difficulty level. Followed by data security questions and real-life DBMS implementation interview questions.

DBMS Interview Questions for Beginners

1. What is DBMS, and what are its advantages?

Database Management System (DBMS) is used to manage, store, manipulate, retrieve and organise all the data present in any database. DBMS works as an intermediate between the users and the data hosted on any cloud platform or even offline databases. There are lots of advantages to using DBMS such as data sharing, data backup and recovery, controlled redundancy, independence and accessibility of data anywhere in the world, and data security, etc.

2. What is a JOIN? Explain its types.

A JOIN keyword is often used to combine two or more database tables in order to find a relationship between them or to form a combined database. In SQL, there are four types of joins.

• LEFT JOIN: This keyword is used for the left join table, i.e., it returns all the rows from the table on the left side of JOIN but takes only the matching rows from the right side of JOIN.
• RIGHT JOIN: Implementing this will return all the rows from the table which is on the right, but only those matching values from the left side table of the join.
• INNER JOIN: To fetch only the common elements from both the database tables, an inner join is used.
• FULL JOIN: Returns all the rows from all tables used, and where there is not any matching value, it will hold null values.

3. What is the difference between DBMS and RDBMS?

4. Define a relation schema.

Relation schema is a concept to find relationships between entities in the database with the help of ER diagrams, that can visually show us the true relation. This helps in quickly understanding the database and we can define the database structure more effectively and accurately.

5. What are the advantages of using an object-oriented database management system?

In Object-Oriented Database Management System (OODBMS), the data is stored in objects which can represent the real-world entities more effectively. Hence, it becomes easy and precise to find the relationship between two datasets present as two objects. It has a great advantage rather than using traditional RDBMS.

6. What is concurrency control in DBMS?

Concurrency ensures the simultaneous execution of transactions in a multi-user database system. It does not conflict with each other and maintains data consistency and security through low transaction interaction. Overall, it helps to prevent data loss, loss of updates and packages, and errors in the database.

7. What is a deadlock in DBMS?

Deadlock refers to a condition where two or more transactions cannot proceed further because each one is waiting for a new resource, but it is locked by the other. Deadlock results in a non-proceeding cycle which lacks dependencies and prevents any running transactions from completion.

8. What is conceptual design in DBMS?

This is part of creating a high-level model of the database, which helps to form a structured approach towards data insertion and its organisation. It is independent of how the data is physically stored in the database, and using entities, attributes, relationships, and constraints results in the development of perfect logical and physical database schemas.

9. What is a cursor in SQL?

To manipulate, navigate and return any value from the database, in a specific manner or say row-by-row, a cursor object is used. Cursors are generally stored in triggers, scripts and procedures. It mainly helps in row-by-row handling of data, like complex calculations and conditional logic.

10. Differentiate between logical database design and physical database design.

11. Explain the different types of DBMS.

Based on the data models and how data is stored and organised, we can categorise different DBMS such as:

a. Hierarchical DBMS:

• Structure: Organises data in a tree-like manner so that it can have parent-child relationships, forming overall hierarchical data.
• Usage: It is generally used to run applications like organisational charts, work management or hierarchical file systems to handle the company’s data.
• Example: IBM’s Information Management System (IMS)

b. Relational DBMS (RDBMS):

• Structure: Here the data is stored in tables or relations with rows and columns, managed with the help of foreign keys.
• Usage: This is mostly used DBMS all over the world, as it supports a broad range of applications for all levels.
• Examples: MySQL, Oracle, SQL Server, PostgreSQL.

c. Network DBMS:

• Structure: In network DBMS, data is represented with the help of a graph which contains many-to-many relationships.
• Usage: Used mainly in telecommunications and transport network databases to manage complex relationships/
• Example: IDS (Integrated Data Store)

d. Object-Oriented DBMS (OODBMS):

• Structure: It supports object-oriented concepts like classes, inheritance, polymorphism, etc., inside database management to ensure laminar relationships.
• Usage: Multimedia, gaming database, videos and graphics selection database.
• Examples: db4o, ObjectDB

e. OBject-relational DBMS (ORDBMS):

• Structure: This is a combination of OODBMS and RDBMS to handle more complex data and tables.
• Usage: It helps in running complex database relations along with relational database benefits.
• Examples: PostgreSQL, Oracle

f. NoSQL DBMS:

• Structure: This can store all the databases, whether they are unstructured, semi-structured, or large-scale, by using graphs, documents, or key-value relationships.
• Usage: Used for big data and highly scalable real-time web applications.
• Examples: MongoDB, Redis, Cassandra, Neo4j

g. Distributed DBMS:

• Structure: It supports homogeneous (one DBMS software) or heterogeneous (multiple DBMS software running all nodes) both remotely.
• Usage: Used in organisations which have geographically diverse branches.
• Example: Google Spanner, Apache Cassandra

12. What is the purpose of the GRANT and REVOKE statements in DBMS?

GRANT statement provides some specific permissions to the users or any different person who has some role or access to database objects. It helps in assigning permissions such as SELECT, INSERT, DELETE, EXECUTE and UPDATE.

The REVOKE statement, on the other hand, removes the previously granted privileges from those users. This ensures that the user does not perform any specific operation on revoked objects.

Also read- SQL Interview Questions

13. What is the purpose of the COMMIT and ROLLBACK statements in DBMS?

14. What is the purpose of a data dictionary in a DBMS?

A data dictionary acts as a centralised repository of metadata, which can have information about the database’s structure, constraints and relationships. It helps in maintaining metadata storage, data integrity, enhanced collaboration, query optimization and better decision-making.

15. What are savepoints in a transaction?

Savepoints allow us to mark a point during a transaction so that we can roll back later on without performing the entire transaction from the start.

Intermediate DBMS Interview Questions

16. Explain the concept of data mining.

The process of discovering different patterns or trends from large datasets with the help of various methods and techniques like statistics, machine learning and database system decision-making is called data mining.

17. What is the E-R model in the DBMS?

The Entity-Relationship (E-R model) describes the relationship between various entities. Hence, this graphical representation helps any database engineer to explain the structure in depth, representing complex relations through charts and making the visualisation easy to understand. It represents entities inside rectangles, attributes as ovals, and diamonds to show concurrent relationships.

18. Describe the difference between the HAVING and WHERE clause.

19. What do you mean by Entity type extension?

This refers to adding new attributes or any specific property to an existing entity type. Hence, it can modify or expand any entity’s structure without changing its entire core definition.

20. What is a Live Lock?

Live Lock happens in concurrent systems when there are two or more processes that change their states continuously to respond to each other’s actions. This hinders the process and stalls in an effectively stuck position.

21. What is a data warehouse, and how does it differ from a database?

22. What are temporary tables? When are they useful?

As the name suggests, those tables are temporarily stored in a database session to store immediate results, queries and any temporary data. They are particularly used to break down complex scenarios into simpler manageable parts.

23. What is the main goal of RAID technology?

Redundant Array of Independent Disks (RAID) technology is used to improve storage performance, accessibility, reliability and capacity with the help of multiple physical disk drivers combined into a single logical unit.

24. Give the difference between Union and Union All.

25. What integrity rules exist in the DBMS?

Integrity rules include entity integrity, i.e. each row in a table must have a unique primary key, referential integrity to check foreign key values must match primary key values in another table, user-defined integrity to apply custom rules specified by the user and domain integrity to ensure data values must be within a specified range.

Advanced DBMS Interview Questions

26. What is a Correlated Subquery in DBMS?

A correlated subquery depends on the outer query for its values is executed once for each row and is processed by the outer query. Hence it makes it closely related to the outer query.

27. What is the difference between a database schema and a database state?

28. What are the different levels of abstraction in the DBMS?

• Physical level: This describes how the data is stored and the details like file structure, organisation and access methods.
• Logical Level: This abstraction level specifies the database schema, including tables, rows, columns and relationships independent of how data is stored.
• View Level: A subset of the database represented by a specific user application for a customised view of the database, hiding many unimportant details.

29. What is QBE?

QBE or Query By Example method ensures the representation of querying databases and their methods based on a simple graphical interface. Rather than using SQL statements, QBE can be filed with a grid or form with example data to specify the conditions.

30. What is a data warehouse, and how does it differ from a database?

31. What are stored procedures, and what are their advantages?

These are pre-compiled SQL statements that already contain the basic structure of specific conditions and require only the fine-tuning of the statement. There are many advantages of stored procedures like:

• Enhanced Security
• Improved Performance
• Centralised Logic Unit
• Transaction Control
• Reduced Network Traffic
• Version Control
• Code Reusability

32. Describe the difference between optimistic and pessimistic locking.

33. What is a two-phase commit?

A two-phase commit (2PC) works as a distributed algorithm to assist a transaction process via all participating databases to either commit or rollback. It ensures that all databases commit the transaction or none will commit, based on any unanimous agreement, ensuring data consistency across all distributed databases,

34. Vertical Scaling vs. Horizontal Scaling

35. What is a surrogate key? When and why is it used in a DBMS?

To serve as a primary key in a table, instead of using natural keys, we can use surrogate keys, which can act as a unique identifier. They are basically either auto-incremented integers or globally unique identifiers, used when natural keys are not suitable or too complex.

36. What is the difference between horizontal partitioning and slashing in a database?

37. How is the pattern matching done in the SQL?

Pattern matching is done using the LIKE operator in SQL in addition to some wildcard characters. For example, ‘%’ represents zero or more characters, and ‘_’ represents a single character.

• To match any element starting with a, the following query is used:

WHERE column_name LIKE ‘a%

• Now, to match any element with its second character as ‘r’ then, its query is:

WHERE column_name LIKE ‘_r%

38. How do you implement pagination in SQL queries?

Pagination is performed to break down a large set of data into smaller, manageable parts called pages. It is used in web development and database queries to improve the performance and usability of applications and reduce the time to load database elements on screen. Pagination can be implemented using LIMIT and OFFSET clauses.

Example:

SELECT * FROM table_name

ORDER BY column_name

LIMIT page_size OFFSET offset_value;

39. Describe the role of an ORM (Object-Relational Mapping) tool.

ORM tool works as a bridge between OOP and Relational databases, allowing them to work together abstracting away the complexities of database interactions and also implementing object-oriented concepts.

40. Describe the difference between Normalisation and Denormalization

Data Security Questions in DBMS Interview

41. Why are backup and recovery tools crucial for DBMS?

Backup and recovery tools are highly crucial in protecting any database against database security issues, SQL injections or any other data threat. To minimise data loss and fast hardware failure recovery, backups are very essential. Without it, it is impossible to restore data to a previous state, and it becomes too difficult to handle or work with the corrupt data.

42. What are the common security threats to a database?

There are many threats to any database system either present on a cloud network or hardware data. Some of the threats are:

• SQL Injection
• Unauthorised Access
• Data Breaches
• Lack of Encryption
• Insecure Configurations
• Malware Attacks
• Trojan Attacks
• Insider Threats
• Denial of Service (DoS) or Distributed Denial of Service (DDoS) attacks

43. Explain the role of caching in database systems.

Caching improves the performance and computation power of any database model by storing frequently accessed data in memory, and the workflow becomes faster. Hence, it reduces the time to reload all the basic things by not accessing the main disk for repeated tasks while completing a query.

44. How can database performance be monitored and improved?

Here, you can mention the following tasks to improve database performance.

• Monitoring queries (identifying slow queries)
• Query optimization
• Indexing
• Database tuning
• Hardware upgrades
• Caching
• Regular maintenance
• Load balancing
• Using different monitoring tools

45. What are the best practices for database disaster recovery planning?

1. Some of the methods you can follow for disaster recovery planning.
2. Maintaining regular backups
3. Offsite storage for backups and its prevention
4. Backup testing to restore successfully when needed.
5. Monitoring access permissions
6. Documentation of database
7. Keeping logs and history records
8. Reviews and feedbacks
9. Considering and Comparing Data Recovery Plans

46. Describe the purpose of database audits.

These are used to monitor database activities to make sure of whether all the tasks are performed under regulations or not by the privileged users. Hence it reduces the amount of threat or human error to a great extent. Audits include certain regulations, internal policies, best practices for security and backup plans.

Case Study and Practical Situation-Based DBMS Questions

47. How can you optimise a slow-running SQL query?

Any slow-running SQL query can be enhanced by initially indexing out the rows on which operation is required. Next, the limit and result set can be used to limit out-of-domain answers. Avoid using the SELECT keyword and select only important columns.  Use INNER JOIN instead of OUTER JOIN where possible to avoid joining large tables and also avoid functions inside the WHERE clause. Temporary tables also help in reducing complexity. Other than this, you can use EXISTS or IN instead of DISTINCT clause.

48. Suppose you come up with a deadlock situation. How do you handle it without losing any data?

You must identify the deadlock first, then follow procedures to kill a process hindering the whole sequence. After that you must try to perform the transaction again, followed by a review and more optimised queries. Deadlock detection and prevention mechanism tools can also help in identifying and preventing deadlock situations.

49. Describe a data warehousing project you worked on

Here, you can describe any project with data warehousing concepts, like a retail company, to analyse sales data. Defining schemas you created for storing consumer details, admin details and other user details. You can mention the usage of ETL (extraction, transform, load) processes to populate the data warehouse from various sources and optimization of inventory management.

50. If you were given the task of database maintenance, what first steps would you perform?

For this, you must look for the existing database backup, review it and test the backup. If there are no backups, then the first task will be creating a regular backup schema. After that check the database health and all the privileges to monitor activity. Updating statistics and reviewing security are also important for routine maintenance. Logs are very crucial in errors and backup recovery.

51. Based on a set of requirements, which DBMS tool would you recommend and why?

This generally depends on how big the database is and what type of data is going to be stored inside it. There are some commonly used databases which have easy-to-adopt interfaces, and all the general-purpose techniques. Some of the databases are Oracle, Microsoft SQL Server, MySQL, PostgreSQL, etc.

Conclusion

In conclusion, database management systems and their best practices will help you handle complex queries and scenarios. Successfully implementing DBMS techniques and looking for regular updates and new inventions also assist you in keeping up with the tools that you are working on. Hope these DBMS interview questions will give you an idea about what types of questions are asked in interviews.