dbms-interview-questions

Commonly asked DBMS Interview Questions | 2022

I am a backend developer by profession and recently I got a chance to be interviewed by a few companies. These were the most commonly asked DBMS interview questions and I would like to share them with all.

Q. What is the difference between DBMS and RDBMS?

DBMS RDBMS
The file is used for storing data. Tables are used for storing data.
In DBMS, the data is stored in the form of hierarchical arrangement. Rows and columns are used to store the data where data-name goes into columns and data values go in rows.
It does not follow ACID properties. It follows ACID properties.
The program of DMBS cannot be normalized. The program of RDBMS supports normalization.
There is no support for distributed databases in DBMS. RDBMS allows for distributed databases.
Handles a small amount of data. Handles a very high amount of data.
Accessing data in DBMS is expensive. Accessing data is easy in RDBMS.
Users cannot create a relationship between data. Users can create a relationship between data.
Data in DBMS is not secure. Data is secure in RDBMS due to logs. 

 

Q. What is the difference between SQL and  NoSQL Databases?

SQL NOSQL
Mysql is a relational database NoSQL is a non-relational database
It stores data in the form of rows and columns which provides it rigidity. NoSQL saves data in the form of a document and key-value pair.
SQL should be used when there is clarity in designing the structure of the database NoSQL should be used when the schema is not very clear.

 

Q. What is indexing and how does it work internally?
Indexing is the most common way of optimizing a database performance with the help of a data structure.
The index is performed on a column. When we index a column, the column is sorted and stored in a B-Tree due to which the time complexity of accessing, inserting, and deleting data reduces from O(n) to O[logn) which results in improved performance. The only negative side of this approach is that it consumes extra memory due to B-Tree.

 

Q. What are the properties on which our database works?

There are 4 fundamental properties on which our database works and they are together called ACID properties.

  1. A: Atomicity – All modifications done in the database are either completely successful or complete failure.
  2. C: Consistency – Before and after any transaction, the state of the database must be consistent.
  3. I: Isolation – In the case of a transaction, no other process should be able to interact with the transaction.
  4. D: Durability – The data should persist before and after a transaction. 

Q. Explain the difference between the DELETE and TRUNCATE command in a DBMS.

DELETE

TRUNCATE

Delete command is used to delete the rows of a table. Rows can be pointed using a where clause. The truncate command deletes all the rows of the table.
It can be rolled back thus, preventing data loss. It cannot always be rolled back which might lead to data loss.
It maintains a log due to which it is relatively slower than truncate. It does not maintain a log due to which it is relatively faster than delete.

So, these were the DBMS interview questions that were commonly asked to me in my recent interviews. I hope this helps you to crack database-related interviews. All the best.

Also read:-

Interview experience at Sigtuple – Backend role

 

Leave a Comment