Feb

DBMS Interview Questions
- Aarush Shukla
- 03rd Feb, 2023
- 1076 Followers
DBMS Interview Questions
What is DBMS?
DBMS is an acronym for Database Management System. It is a software system that is used to create, maintain, and manage databases. It is a collection of programs that enables users to create and maintain a database, as well as control access to it. DBMS provides users with the ability to add, modify, and retrieve data from a database, as well as control the security and integrity of the data.
There are several types of DBMS are available in the market, including relational databases, object-oriented databases, hierarchical databases, and NoSQL databases. The most popular and widely used DBMSs are relational databases, such as MySQL, Oracle, and Microsoft SQL Server.
Are you looking to join the industry as a DBMS developer? Have you prepared yourself to face DBMS Interview Questions? If not, then dig into some technical insights for DBMS. Database management has become popular more than ever. A set of logically arranged data for a specific purpose is commonly known as Database. A collection of programs, which can be used to create and maintain a database is known as Database Management System or DBMS. It is basically a systematic way of executing a command with data.
If one is through with the basics, half the battle is won. The more knowledge on the subject, the easier will it be to crack the DBMS Interviews. There is a vast list of questionnaires available on Google to help you gain confidence before facing the interview panel.
Quick Questions About DBMS
DBMS stands for | Database Management System |
DBMS is a | Organized collection of stored data |
DBMS was developed in | 1960s |
DBMS features are | Data Integrity, Data Security, Data Recovery, Data Storage & Scalability |
DBMS applications are | Baking, Railway, Library Management, Education sector, Manufacturing, etc. |
Most Popular DBMS are | MySQL, MariaDB, SQL Server, Oracle, MongoDB, PostgreSQL & Elasticsearch. |
Key Responsibilities of DBMS Developer
As a DBMS developer, some of your key responsibilities may include:
- Designing and implementing databases.
- Writing and maintaining database scripts.
- Ensuring data integrity and security.
- Managing and monitoring database performance.
- Collaborating with other developers and stakeholders.
- Providing technical support and troubleshooting.
- Developing backup and recovery procedures.
- Staying up-to-date with new technologies and trends:
- Data Governance and data quality management.
DBMS Interview Questions for Beginners
1) What is normalization in DBMS?
Normalization is a process in database design that is used to organize data in a way that reduces data redundancy and improves data integrity. The goal of normalization is to separate data into multiple tables with clearly defined relationships between them. This helps to eliminate data inconsistencies and makes it easier to update and query the data. Normalization is typically performed using a series of rules, known as normal forms.
Normalization helps to reduce data duplication and improve data integrity by ensuring that data is stored in a logical and consistent manner.
2) What is DBMS?
DBMS (Database Management System) is a computer software application that allows users and other applications to view, monitor and analyze data. It is an application to handle data on various subjects.
3) Enlist various types of interactions created by DBMS ?
There are various kinds of interactions supported by DBMS like-
- Data definition
- Update
- Retrieval
- Administration
4) What are the features of Database language?
5) What are different relationships existing in database?
- One-to-one– This simple relationship exists among two tables having same rows or columns.
- One-to-many– Two tables connected by a foreign key and a primary key enjoys one-to-many relationship.
- Many-to-many – It is a complex relationship in which many records in one table is connected to many records in another table.
6) State some commands of DDL in DBMS ?
DDL commands are used to modify and edit the structure of a database system. Some of the important commands are-
-
CREATE table command- It defines each column of the table in a unique and distinct manner like name, data type, size, etc.
Syntax: CREATE TABLE [column name] ([column definitions]) [table parameters]
-
ALTER table command- ALTER is used to make changes and edit the existing table of database. Syntax: ALTER objecttype objectname parameters.
-
DROP table command – DROP is used for destroying a table and all the recorded data in it. It destroys an existing database. Syntax: DROP objecttype objectname parameters.
7) What are different cursor types in DBMS ?
A database cursor enables traversal over the records in database. The different cursor types are-
- Dynamic – it is highly subject to changes while scrolling the cursor in database.
- Static – it doesn’t show any changes while scrolling. It also works on recording of snapshot.
- Keyset – it enables the user to modify the data without displaying the new data.
8) Enlist the types of cursor?
- Implicit cursor– Implicit cursors are automatically created whenever an SQL statement is executed and this happens without the user’s notice.
- Explicit cursor– Explicit cursors are created with the awareness of users. It handles the SQP/PL query in one row and exercises more control over the context area.
9) List few restrictions that are imposed while creating views?
- Only current database can have views. No other database is allowed to have views other than the current database.
- You cannot make any changes in the computed values of any view.
- You cannot apply full text index definitions.
- Temporary views cannot be created and temporary tables cannot have views.
- It is not associated with any default definitions.
- There are integrity constants that define the working of commands such as insert and delete.
- Triggers such as INSTEAD of is associated with views.
10) What is join? Mention its types.
Join depicts the relationship between two or more tables. It combines row of different tables and enables you to select data with reference to data of some other table. There are several joins in database like CROSS JOINs, NATURAL JOINs, EQUI-JOIN, etc. Two most important joins are –
- INNER JOIN – this join returns values that have matching record in both the tables. In this, blank rows are combined in middle.
- OUTER JOIN– it returns all the records of left table. It also returns the values that match with the right table. Blank rows are put in specified side by joining tables in other side.
11) How index hunting contributes in improving the query performance?
- In order to coordinate the study of query with workload, query optimizer is used. It also suggests the best use of queries based on the optimizer.
- Performance of query distribution is checked and monitored to know there effects.
- Turning complex database into small chunks of queries is also suggested.
12) Mention some disadvantages of query?
- There are no indexes in query.
- Triggers and procedures are SET NOCOUNT ON.
- Sometimes joins that are complicated and complex are excessively complied.
- Cursors and tables that are temporary depicts a bad presentation.
13) List few ways to code transactions in an efficient manner?
- It doesn’t require input from users during transactions.
- Transactions must not be opened while browsing through data.
- Make use of lower transaction levels.
- While transacting, least information of data must be accessed.
14) Differentiate between Nested loop, Hash loop and Merge Join.
Nested loop- A nested loop is a loop within a loop. It is an inner loop within an outer body that allows fewer entries. Individual entries in nested loop is processed individually in the inner loop. Example-
.Select col.*, col2.* from coll, col2 where coll.coll=col2.col2;
The nested way works in following format-
For i in (select*from coll) loop
For j in (select*from col2 where col2 = i.coll) loop
Results are displayed
End of the loop;
End of the loop;
For nested loop, first outer (driving) table is identified. Then inner tavle is assigned to the outer table and every row in inner column is accessible from every row of outer table. Nested loop is executed from hash join, inner loop and then outer loop.
Hash join- it is divided into-
- Build- this hash table has in-memory which is present on the smaller table.
- Probe – it is the hash value present in each second row element.
- Sort merge element- it joins two independent sources of data.
It is considered a better option as compared with nested loop when data volume is big. The full operation of hash join can be classified as-
Sort join operation-
Get first row R1 from input1 Get first row R2 from input2 Merge join operation- 'while' command is not used at loop's end. If R1 joins with R2 Next row is got R2 from the input2 Return (R1,R2) else if R1 ,style="". Next row is got from R1 from input1 else next row is got from R2 from input 2 end of the loop.
15) what are the importance of partitioning in DBMS ?
Database partitioning is the process of splitting large tables into smaller database entities. Following are the benefits of partitioning-
- When rows are in one partition, query performance in such situations is improved.
- It also helps to access large parts of single partition.
- Data which are rarely used can be stored in slower and cheaper storage medium.
16) Define Atomicity and Aggregation.
Atomicity: In database management, atomicity is a concept that assures the users of the incomplete transactions. It takes care of these transactions and the actions related to incomplete transactions are left undone in DBMS.
Aggregation: It aggregates the collected entities and their relationships. In this, information is gathered and expressed in summary form.
17) Enlist various transaction phases.
18) Mention some advantages of using DBMS.
DBMS is a structure that manages and handles large volumes of data stored in database. it serves as an intermediate between users and the database. Following are few advantages of database management system-
- Data redundancy- this happens when multiple copies of data is stored. With DBMS, data is stored in one structure database and the data is inputted only once.
- No unauthorized access- it discourages unauthorized access and improves data access.
- Supports multiple user interfaces.
- Minimized data inconsistency- In DBMS, data inconsistency is reduced as different versions of same data doesn’t appear in different places.
19) How many types of database language exist?
- DDL– Data Definition Language which includes CREATE, ALTER, DROP.
- DML– Data Manipulation Language which includes SELECT, UPDATE, INSERT, etc.
- DCL– Data Control Language which consists of GRANT and REVOKE.
- TCL– Transaction Control Language such as COMMIT and ROLLBACK.
20) List few disadvantages of File processing system.
21) What is data independence?
22) Define DML and DCL statements?
DML stands for Data Manipulation Language. It is the SQL commands that deal with the manipulation of data present in the database.
For instance, Some DML commands are as follows:
The INSERT command uses insert into keyword which is used to insert data into a table.
The UPDATE command uses an update keyword which is used to update existing data within a table.
DELETE command uses the delete keyword which is used to delete records from a database table.
DCL stands for Data Control Language. It is SQL commands that mainly deal with the rights, permissions, and other controls of the database system.
For instance, Some DCL commands are as follows:
The GRANT command gives the user’s access privileges to the database.
The REVOKE command uses the revoke keyword which is used to withdraw the user’s access privileges given by using the GRANT command.
23) What is checkpoint in DBMS?
In DBMS, the checkpoint is a type of mechanism where all the previous logs are removed from the system and permanently stored in the storage disk. When it reaches the checkpoint, then the transaction will be updated into the database, and to that point, the entire log file will be removed from the file.
24) What is a database index?
Many Information is stored in a database and locating and retrieving the information will be difficult without an index. A database index is, therefore, that which makes searching and retrieving specific data or information from a database easier and faster. A database index can be likened to an old recipe box that has dividers. Through the recipe box, you are directed to various categories such as cereals, legumes, salad, soups, and vegetables. Instead of searching through all the numerous categories, a database index takes you directly to what you are looking for. The science catalog in an online library is another example of a database index.
25) What is atomicity in dbms?
In database practices, atomicity is an ACID (Atomicity, Consistency, Isolation, Durability) transaction features. An atomic transaction is an indivisible and fundamental series of database transactions such that either all occur, or nothing happens and a guarantee of atomicity halts updates to the database occurring only partially, which can provoke greater problems than rejecting the entire series outright. As an outcome, the transaction cannot be recognized to be in progress by another database user.
Leave A Comment :
Valid name is required.
Valid name is required.
Valid email id is required.