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.
- A: Atomicity – All modifications done in the database are either completely successful or complete failure.
- C: Consistency – Before and after any transaction, the state of the database must be consistent.
- I: Isolation – In the case of a transaction, no other process should be able to interact with the transaction.
- 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
