DBMS interview FAQs

To familiarize you with the types of database management system-related questions you may be asked in job interviews, we will explore the most critical DBMS interview questions (DBMS) in this article .

Q1) What are the uses of DBMS?

The acronym DBMS (Database Management System) represents an application system whose primary function revolves around data. The system enables users to design, save, retrieve and update data, as well as information about the data stored in the database.

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

Simply put, a database is a set of data that has been arranged so that users can easily access, manage and submit it.

Q3) Why is it recommended to use a database management system (DBMS)? List some of the main advantages to explain further.

Here are some of the main advantages of DBMS

Controlled Redundancy - DBMS provides a way to prevent duplicate data from being saved because all data is saved in a single database, thus eliminating redundancy within the database.

Data Sharing - Since the same database will be shared by all users and various applications, data can also be shared among multiple users at the same time using a DBMS.

Backup and Recovery Tools - DBMS takes the pain out of generating data backups by providing a feature called "Backup and Recovery" (automatically generating backups of data and restoring data on demand). Duplicate data.

Application of Integrity Constraints− Integrity constraints must be applied to the data so that the refined data is saved in the database and processed by the DBMS.

Data Independence - Data independence basically means that you can modify the structure of your data without changing the design of any underlying application.

Q4) Why is normalization used in database management systems?

The process of analyzing the schema of a relationship to meet specific criteria based on its unique functional dependencies and primary keys is called normalization.

Attributes include:

Reduce data redundancy.

In order to reduce insertion, deletion and update exceptions

Q5) What are the different categories of languages ​​supported by DBMS?

In DBMS, there are basically three different types of languages ​​as follows

DDL - Data Definition Language (DDL) is a collection of SQL queries, such as CREATE, ALTER, TRUNCATE, DROP, and RENAME, used to describe database and schema structures.

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

DML − Data Manipulation Language (Data Manipulation Language), or DML, is used to perform database operations, including inserting and deleting using a series of SQL query statements (such as select, insert, delete, and update) and update data.

Question 6) Why use SQL?

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

Question 7) Explain the concepts of primary key and foreign key.

In a database table, the primary key is used to uniquely identify each record, while the foreign key (a specific field or a group of fields in one table, used as the primary key of another table) is mainly used to connect two tables or multiple tables. The table is together.

Q8) What is the main difference between unique key and primary key?

Several variations are shown below:

The main difference between primary key and unique key is that primary key can never contain null values, while unique key can.

There can be multiple unique keys in a table, but each table can only have one primary key.

Q9) What does the term "subquery" mean in SQL?

Answer − A subquery is essentially a query contained within another query; it is also called an inner query because it is contained within an outer query.

Q 10) What is the purpose of the DROP command? What is the difference between DROP, TRUNCATE and DELETE commands?

You can use the DDL command "DROP" to delete a table, database, index, or view from the database.

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

You can use the DDL commands DROP and TRUNCATE to delete a table from the database. Once the table is destroyed, all related permissions and indexes will also be deleted. Both procedures cannot be undone, so they should only be used if absolutely necessary.

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

It is recommended to use the DELETE command in conjunction with the "WHERE" clause to prevent the entire table from being deleted from the database.

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

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

Q12) Explain the concept of ACID properties in DBMS?

The combination of Atomicity, Consistency, Isolation and Durability properties is called an ACID property. These features allow multiple people to share data in a safe and secure manner.

Atomicity - This is based on the idea of ​​"all or nothing", which essentially means that if a database change occurs, everyone except the user and application should be able to access it, or it cannot be accessed by anyone but them.

Consistency − This guarantees that the database remains consistent during and after any internal transactions.

Isolation - As the name suggests, this feature specifies that every transaction that occurs is isolated from other transactions. For example, a transaction that has been started but not yet completed should be isolated from other transactions so that other transactions are not affected by it.

Persistence − This feature specifies that the data should always be in a persistent state, i.e. any committed data should be accessed in the same state upon a system failure or restart.

Q 13: What is the role of DBMS correlated subquery?

Subqueries are sometimes called nested queries or queries written within another query. When a subquery is executed on each row of the outer query, the subquery is called a correlation.

An example of a non-correlated subquery is -

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

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

Q 14) Explain the entities (Entity), entity types (Entity Type) and entity sets (Entity Set) in DBMS.

An entity is any thing, place or object that exists independently in reality and whose details can be saved in a database. For example, any person, book, etc.

An entity type is a combination of entities with similar properties. Taking the student table as an example, each row is an entity that stores the student's name, age, and student ID. Therefore, Student is an entity type with the same properties.

A grouping of entities of the same type is an entity set. An example is a group of employees of a company.

Q 15) What are the different levels of abstraction in a database management system?

In DBMS, there are three levels of data abstraction.

They are provided by

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

Logical Level - As the next level of data abstraction, the logical level describes the data types retained in the database and the connections between data.

View Level - The highest level of data abstraction, called the view level, displays or illustrates only a portion of the database.

Q16) What are the integrity criteria in DBMS?

There are two main integrity rules for DBMS, to be precise.

as follows

Entity integrity: declares a key principle, that is, the value of the primary key can never be NULL

Referential Integrity: According to this rule, the value of a foreign key must be NULL or must serve as the primary key for all other relationships.

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

In DBMS, the E-R model is called the entity-relationship model because it is built on the concept of entities and the relationships that exist between them.

Q18) What does DBMS functional dependency mean?

Essentially, the relationship between various properties of the relationship can be described by this constraint.

For example, if a relationship named "R1" contains features "Y" and "Z", then the functional dependency relationship between these two attributes can be expressed as "Y->Z", indicating that Z depends on Only Y can operate.

Q19) What does 1NF stand for in DBMS?

First normal form, or 1NF, is the correct answer.

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

Q20) What does 2NF of DBMS stand for?

Second Normal Form, or 2NF.

Any table that meets the following two requirements is considered to be in second normal form (2NF):

The table is in 1NF.

Non-primary attributes of a table are considered to be fully functionally dependent on its primary key.

Q21) What does 3NF stand for in DBMS?

Third Normal Form, or 3NF.

Any table that satisfies the following two requirements is said to be in third normal form (3NF):

The table is in 2NF.

Some people think that every non-prime attribute in the table depends non-transitively on every table key.

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

Boyce Codd normalized normal form, which is more strict than the third normal form, is called BCNF.

Any table that meets the following two requirements is considered to be in BCNF:

A table is in third normal form.

X is the superkey of any existing table that has a functional dependency on X->Y.

Q23) In SQL, what does CLAUSE mean?

It is used in conjunction with SQL queries to obtain specified data based on user requirements and SQL-defined constraints. This is particularly useful for selecting certain records from an entire record set.

As an example, there are queries with WHERE conditions and queries with HAVING clauses.

Q24) How to retrieve alternative records in a table from SQL?

Answer − Odd numbers

can be retrieved using the following search

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

The following query can be used to retrieve 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 possible to match patterns.

The LIKE operator uses the character "%" when matching 0 or more characters, and the character "." when matching only 1 character

示例

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

SELECT * from Emp WHERE name like 'hans_';

Q26）SQL 中的连接是什么意思？

联接是一种 SQL 语句，用于根据共享字段或列组合两个或多个表中的数据或行。

Q27）SQL 连接有哪几种类型？

有四种不同类型的SQL连接。

内部联接 - 这种联接用于从两个表共享的表中检索信息。

Left Join − 这只返回连接操作右侧表中的匹配行，返回连接操作左侧表中的所有行。

Right Join - 这仅返回连接左侧表中的匹配行，而不是连接右侧表中的所有行。

完全联接 - 这会从已应用联接条件的每个表中检索所有行，并且不匹配的行具有空值。

Q28）“触发”一词是什么意思？

答案是触发器是为了响应表或视图中发生的事件而自动运行的关键脚本或程序之一。例如，每当向员工数据库添加新记录时，相关表中都会自动生成数据，例如角色表、部门表和薪酬表。

Q29）什么是存储过程？（问题29）

存储过程是一组SQL语句的集合，组织成一个函数，保存在关系数据库管理系统（RDBMS）中，并在需要时可随时访问。

Q30)什么是关系数据库管理系统（RDBMS），第30个问题？

RDBMS 代表关系数据库管理系统。它是一个数据库管理系统，通过使用不同表中的公共字段来访问数据。

Q31）DBMS 支持哪些类型的关联？

在数据库管理系统中，关系显示了表之间的关联。

各种类型的关系包括

一对一 - 这基本上表示每个表中应该有一条记录，或者表之间存在一对一的关系。例如，已婚夫妇只允许每人有一个配偶。

一对多 - 主键表只有一条记录，根据一对多连接理论，关联表中可能有许多、一个或零条记录。一个母亲可能有很多孩子。

多对多 - 据此，两个表都可以连接到其他几个表。示例：兄弟姐妹可以有很多，而且经常如此。

The above is the detailed content of DBMS interview FAQs. For more information, please follow other related articles on the PHP Chinese website!