Commonly asked DBMS Interview Questions


To familiarize you with the kind of questions that may be asked during a job interview pertaining to the Database Management System, we will explore the most crucial DBMS Interview Questions in this post (DBMS).

Q1)What are some uses for DBMS?

The acronym DBMS, or database management system, stands for an application system whose primary function is around data. This system enables the user to design, save, retrieve, and update data as well as information about the data as it is stored in the database.

Q2)What does the term "database" refer to?

Simply said, a database is a collection of data that has been arranged so that users can access, manage, and submit the data with ease.

Q3) Why is the usage of DBMS advised? List some of its main advantages to further explain.

The following are some of the main benefits of DBMS

Controlled Redundancy − DBMS enables a way to prevent duplicate data from being saved since all data is kept in a single database, which eliminates redundancy within the database.

Data sharing − Since the same database will be shared by all of the users and by various application programs, data sharing between several users at once is also possible with DBMS.

Backup and Recovery Facility − By offering a function known as "backup and recovery," which automatically generates the data backup and restores the data as needed, DBMS lessens the agony of producing the backup of the data repeatedly.

Application of Integrity Constraints − Integrity Constraints must be applied to the data in order for the refined data to be saved in the database and to be processed by DBMS.

Independence of Data − Data independence basically implies that you may modify the data's structure without altering the design of any underlying application applications.

Q4) Why is normalization used in DBMSs?

The analysis of relational schemas that are based on their unique functional dependencies and primary keys in order to satisfy specific criteria is known as normalization.

The attributes consist of:

to reduce the data's redundancy.

in order to reduce Insert, Delete, and Update Anomalies

Q5)What are the different categories of languages that the DBMS supports?

In the DBMS, there are basically three different kinds of languages, as follows

DDL − Data Definition Language, or DDL, is a collection of SQL queries, such as CREATE, ALTER, TRUNCATE, DROP, and RENAME, that are used to describe the database and schema structure.

DCL − Data Control Language (DCL): DCL is a series of SQL queries, such as GRANT and REVOKE, that are used to manage user access to databases.

DML − Data Manipulation Language, or DML, is used to do database manipulations including inserting, deleting, and updating data using a series of SQL queries such as select, insert, delete, and update.

Q 6) Why is SQL used?

The acronym SQL stands for Structured Query Language, and it is used to interact with relational databases by entering, updating, and/or changing data.

Q 7) Explain the concepts of a Primary key and a Foreign Key.

In a database table, a primary key is used to uniquely identify each record, whereas a foreign key—a specific field or set of fields in one table that serves as the primary key for another table—is primarily used to connect two or more tables together.

Q8) What are the primary distinctions between the Unique Key and the Primary Key?

A few variations are shown below:

The primary distinction between a primary key and a unique key is that a primary key can never contain a null value, but a unique key can.

There can be more than one unique key in a table, but there can only be one main key per table.

Q9)What does the phrase "sub-query" mean in relation to SQL?

Answer − A sub-query is essentially a query that is contained within another query; it is also referred to as an inner query because it is contained within the outer query.

Q 10) What is the use of the DROP command and what are the differences between DROP, TRUNCATE and DELETE commands?

A table, database, index, or view can be dropped or deleted from the database using the DDL command "DROP."

There are three main distinctions between the DROP, TRUNCATE, and DELETE commands:

Tables may be deleted from the database using the DDL commands DROP and TRUNCATE, and once a table is destroyed, all associated rights and indexes are likewise erased. These 2 procedures cannot be undone, thus they should only be utilized when absolutely required.

On the other hand, the DELETE command is a DML Command that may also be used to delete rows from a table.

It is advised to use the "WHERE" clause in conjunction with the DELETE command to prevent the whole table from being destroyed from the database.

Q 11) What is the main difference between UNION and UNION ALL?

When joining data from two or more tables, UNION and UNION ALL are used; UNION eliminates duplicate rows and selects the distinct rows after merging the data from the tables; UNION ALL does not do this; it just selects all the data from the tables.

Q12)Explain the idea of ACID characteristics in DBMS?

The combination of Atomicity, Consistency, Isolation, and Durability traits is known as ACID attributes. These characteristics make it possible for several people to share data in a safe and secure manner.

Atomicity − This is based on the idea of "either all or nothing," which essentially implies that if a database change occurs, it should either be accessible to everyone besides the user and application program or it shouldn't be accessible to anybody besides them at all.

Consistency − This guarantees that the database's consistency is preserved both during and following any internal transactions.

Isolation − As the name implies, this feature specifies that each transaction that takes place is isolated from others. For example, a transaction that has started but has not yet finished should be isolated from others so that other transactions are not influenced by it.

Durability − This feature specifies that the data should always be in a durable state, meaning that any committed data should be accessible in the same state even if the system has a failure or restarts.

Q 13: What Does a DBMS Correlated Subquery Do?

A subquery is sometimes referred to as a nested query or a query that is written inside another query. A subquery is referred to as correlating when it is done for each row of the outer query.

An illustration of a non-related subquery is −

SELECT * from EMP WHERE 'AJITESH' IN (SELECT Name from DEPT WHERE EMP.EMPID=DEPT.EMPID);

In this case, the inner query is not run for every row of the outer query.

Q 14) Explain Entity, Entity Type, and Entity Set in DBMS.

An entity is anything, place, or object that exists independently in reality and whose details may be saved in a database. For instance, any individual, book, etc.

An entity type is a group of entities with similar properties. As an illustration, the STUDENT table comprises rows, each of which is an entity storing the name, age, and student ID of the students. As a result, STUDENT is an entity type that has entities with the same properties.

A grouping of entities that have the same type is an entity set. An example would be a group of a company's employees.

Q 15) What are the different levels of abstraction in the DBMS?

In the DBMS, there are three layers of data abstraction.

They consist of

Physical Level − The physical level describes how the data is kept in the database and is the lowest level of data abstraction.

Logical Level − The following level of data abstraction, known as the logical level, describes the kind of data and the connections between the data that are kept in the database.

View Level − The highest level of data abstraction, known as the view level, only displays or states a portion of the database.

Q16)What integrity guidelines are there in the DBMS?

The DBMS has two main integrity rules, to be precise.

As follows

Entity Integrity: Declares a crucial principle that a primary key's value can never be NULL

Referential Integrity: According to this rule, a foreign key's value must either be NULL or it must serve as the primary key for every other relation.

Q 17) What is the E-R model in the DBMS?

In the DBMS, the E-R model is referred to as an Entity-Relationship model since it is built on the idea of entities and the relationships that exist between them.

Q18) What does a DBMS functional dependence mean?

The relationship between the various qualities of a relation may be described by this constraint, in essence.

For instance, if a relation called "R1" includes the characteristics "Y" and "Z," then the functional dependence between these two attributes may be represented as "Y->Z," indicating that Z is dependent on Y in order to operate.

Q19)What is 1NF in the DBMS stand for?

The First Normal Form, or 1NF, is the correct response.

The domain of an attribute should only have atomic values in this kind of normalization, which is the simplest. The purpose of this is to eliminate any duplicate columns from the table.

Q20)What does the DBMS's 2NF stand for?

The Second Normal Form, or 2NF.

Any table that meets the following two requirements is considered to have in the 2NF:

A table is in the 1NF.

A table's non-prime attributes are considered to be completely functionally dependent on its main key.

Q21)What is 3NF in the DBMS stand for?

The Third Normal Form, or 3NF.

Any table that meets the following two requirements is said to have in the 3NF:

A table is in the 2NF.

It is argued that every non-prime attribute in a table is non-transitively reliant on every table key.

Q22)What is BCNF in the DBMS, question #22?

The Boyce Codd Normal Form, which is tighter than the 3NF, is known as BCNF.

Any table that meets the following two requirements is said to have in the BCNF:

A table is in the 3NF.

X is a table's super key for any functional dependence X->Y that exists.

Q23)What does a CLAUSE mean in relation to SQL?

This is used in conjunction with SQL queries to get specified data based on user needs and SQL-defined constraints. This is particularly useful for selecting certain records from the entire set of records.

As an illustration, there are queries with the WHERE condition and those with the HAVING clause.

Q24)How can the alternative records from the table in SQL be retrieved?

Answer − The following search may be used to retrieve odd numbers

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(rowno,2)=1;

The following query may be used to retrieve the even numbers −

SELECT EmpId from (SELECT rowno,EmpId from Emp) WHERE mod(rowno,2)=0;

Q 25) How does SQL handle pattern matching?

The LIKE operator in SQL makes it feasible to match patterns.

When the LIKE operator matches 0 or more characters, it uses the character "%," and when it matches only one, it uses the character " ."

Example

SELECT * from Emp WHERE name like 'b%';
SELECT * from Emp WHERE name like 'hans_';

Q26)What does a join in SQL mean?

A join is a type of SQL statement that is used to combine data or rows from two or more tables based on a shared field or column.

Q27)What various kinds of SQL joins are there?

There are four different kinds of SQL joins.

Inner Join − This kind of join is used to retrieve information from tables that are shared by both tables.

Left Join − This only returns the matching rows from the table on the right side of the join, returning all the rows from the table on the left of the join.

Right Join − This just returns matching rows from the table on the left of the join, not all the rows from the table on the right of the join.

Full Join − This retrieves all the rows from every table that the join condition has been applied to, and the rows that do not match have null values.

Q28) What does the term "trigger" mean?

The answer is that a trigger is one of the crucial scripts or programs that are automatically run in response to events occurring in a table or view. As an illustration, whenever a new record is added to an employee database, the data is automatically produced in the relevant tables, such as the roles, departments, and compensation tables.

Q29) What is the Stored Procedure? (Question 29)

A stored procedure is a collection of SQL statements organized into a function that is saved in relational database management systems (RDBMS) and accessible whenever necessary.

Q30)What is RDBMS, question 30?

RDBMS stands for Relational Database Management System. It is a database management system that accesses data by using common fields found in different tables.

Q31)What various kinds of associations does the DBMS support?

In DBMS, relationships show how the tables are related to one another.

Various kinds of relationships include

One-to-One − This essentially indicates that there should be one record in each table, or a one-to-one relationship, between the tables. For instance, a married couple is only permitted to have one spouse each.

One-to-Many − A primary key table has just one record, although there may be many, one, or no records in the linked table according to the one-to-many connection theory. A mother could have a lot of kids.

Many-to-Many − According to this, both tables may be connected to several other tables. Example: Siblings can be many and often are.

Updated on: 06-Apr-2023

867 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements