Most common DBMS Interview questions you should know about

<a href=”https://www.freepik.com/vectors/technology">Technology vector created by macrovector — www.freepik.com</a>

What is Data Abstraction in DBMS? What are the three levels of abstraction in the DBMS?

Data abstraction is the approach used by developers to hide irrelevant details related to data in database so that the user interaction with the database is more efficient and the user gets the necessary information without getting involved in complex details.

Explain ACID properties.

ACID properties are essential for maintaining the integrity of data. These properties are used as parameters to assess data transactions in a database. These properties are defined as the following:

  • Durability: By durability, we mean that once a transaction is completed whatever changes, updates which are done to the database are stored in a disk. So that whenever there is hardware or software failure, the updates remain stored and aren’t lost.

What is normalization in DBMS?

Normalization is the process of analysing a database to avoid data redundancy across tables. In normalization, we break down a big table into smaller tables and then create links among those small tables to analyse better. Thus, normalization is helping minimize insertion, update and detect anomalies in a database.

  • Boyce & Codd Normal Form, BCNF

What is the 3-tier architecture in DBMS?

In 3-tier architecture, DBMS is divided into 3 levels:

  • External Level: This level is used to cater to the needs of different users who are accessing the database. For different types of users, data is presented in a user-friendly manner and in such a way, that they can interpret it conveniently.

What are the differences between DROP, DELETE and TRUNCATE?

What is the RDBMS?

RDBMS is a software which works upon the relational database. In a relational database, data is stored in form of tables which consists of rows and columns. The benefit of the relational database is that the data across different tables are related to each other so queries can be efficiently run over multiple tables in one go.

What is functional dependency?

For example, a store manager maintains a database of customers with attributes customer_id, customer_name, and customer_adress. Now here if we know the customer_id we also get to know customer_name. So here attribute customer_id uniquely determines the attribute customer_name and we say that customer_name is functionally dependent on customer_id. That is

What is Deadlock?

Ans: Deadlock is a situation where two or more transaction logs are indefinitely waiting for one another to give up on locks.

How can Deadlock be prevented?

Deadlock can be prevented by avoiding entry into any of the 4 Coffman conditions:

What is meant by query optimization?

Query optimization is an evaluation technique to find out the most efficient query for a task. The concept of query optimization is used when there are various algorithms and methods present for the same task and we want to obtain the one with the least cost.

What are different types of keys in DBMS?

Different types of keys in DBMS are:

What is the difference between WHERE and HAVING clause?

• HAVING clause is used to choose records from groups or aggregated row which satisfy given conditions while WHERE clause is used to choose records from the table which satisfies certain conditions.

  • HAVING clause always require GROUP BY clause and is used after it but WHERE clause can still function without GROUP BY clause and when used with GROUP BY, is used before it.

What are different types of Join in SQL?

There are 4 types of join:

  • Full join: It is a combination of both left and right join where all the matched rows of two tables are returned first followed by non-matching rows of two or more tables.

What is a Trigger?

Triggers are SQL statements that are automatically executed whenever there is modification or update in a database.

What are the differences between network and hierarchical data models?

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store