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 .
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.
Simply put, a database is a set of data that has been arranged so that users can easily access, manage and submit it.
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.
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
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.
The abbreviation SQL stands for Structured Query Language, which is used to interact with relational databases by entering, updating, and/or changing data.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Answer − Odd numbers
can be retrieved using the following searchSELECT 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;
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_';
联接是一种 SQL 语句，用于根据共享字段或列组合两个或多个表中的数据或行。
有四种不同类型的SQL连接。
内部联接 - 这种联接用于从两个表共享的表中检索信息。
Left Join − 这只返回连接操作右侧表中的匹配行，返回连接操作左侧表中的所有行。
Right Join - 这仅返回连接左侧表中的匹配行，而不是连接右侧表中的所有行。
完全联接 - 这会从已应用联接条件的每个表中检索所有行，并且不匹配的行具有空值。
答案是触发器是为了响应表或视图中发生的事件而自动运行的关键脚本或程序之一。例如，每当向员工数据库添加新记录时，相关表中都会自动生成数据，例如角色表、部门表和薪酬表。
存储过程是一组SQL语句的集合，组织成一个函数，保存在关系数据库管理系统（RDBMS）中，并在需要时可随时访问。
RDBMS 代表关系数据库管理系统。它是一个数据库管理系统，通过使用不同表中的公共字段来访问数据。
在数据库管理系统中，关系显示了表之间的关联。
各种类型的关系包括
一对一 - 这基本上表示每个表中应该有一条记录，或者表之间存在一对一的关系。例如，已婚夫妇只允许每人有一个配偶。
一对多 - 主键表只有一条记录，根据一对多连接理论，关联表中可能有许多、一个或零条记录。一个母亲可能有很多孩子。
多对多 - 据此，两个表都可以连接到其他几个表。示例：兄弟姐妹可以有很多，而且经常如此。
The above is the detailed content of DBMS interview FAQs. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
