Database Management System or DBMS in the short form is system software that used for creating, modifying, and improving the databases. A Database may be defined as the collection of several records. A Database Management System makes it possible for the users and the developers to create, update, read, and delete the data that exists in a database.

Essentially, or most significantly, the Database Management System acts as an interface between the databases and the front end application programs. It also ensures that the data is well organized and is user-friendly, which in other words means that the data and information residing on the database are easily accessible.

Interview Questions on Database Management System:-

1. What are the main advantages of the Database Management System over traditional file-based systems?

•  Database Management Systems were mainly developed to handle and ease the following difficulties that were posed by the typical File Processing Systems and which were not at all user-friendly with the operating systems that were available on the market.

Some of the problems that were faced by the traditional file-based systems are as follows-

1. Data inconsistency.
2. Difficulty in accessing the data from the file.
3. Integrity problems.
4. Atomicity problems.
5. Data Isolation.
6. Concurrent access from multiple users.
7. Security problems posed on the information as anyone can access it.

2. What are super, primary, and candidate keys in a database management system?

A super key can be defined as a set of attributes of a relation scheme that are mutually and functionally dependent. No two rows in a table can have the same value as super key attributes.

A Candidate key is a minimal superkey that is it not a proper subset of a candidate key can be a superkey.

A primary key is also one of the candidate keys.

The Candidate key, which can serve as the most important key, is identified as the Primary key. Most importantly, there cannot be more than one primary key in a table. 

Additionally, a foreign key may be defined as a field that uniquely identifies two tables, having one attribute in common.

3. What is the difference between Primary key and Unique constraints?

• It should be noted that a primary key cannot have or must not contain a NULL value, whereas the unique constraints can have NULL values. Also, there can be only one key in the table which can serve as the Primary key, but on the other hand, there can be multiple unique constraints residing on the same table.

4. What is Database Normalization?

• Database Normalization is a process which analyses the given relation schemas based on their primary keys and functional dependencies to achieve some of the desirable properties which are mentioned as follows:-

1. Minimizing redundancy.
2. Minimizing the insertion, deletion, and update operations.

5. What do you mean by SQL or Structured Query Language?

• Structure Query Language short for SQL is basically designed and manipulated for the process of inserting and modifying data and information in a relational database management system.

6. What are the differences between DDL, DML, and DCL in Structured Query Language?

• DDL stands for Data Definition Language. Structured Query Language queries like CREATE, ALTER, DROP, and RENAME come under this DDL.

DML stands for Data Manipulation Language. Structured Query Language queries like SELECT, INSERT, and UPDATE come under this DML.

DCL stands for Data Control Language. SQL Queries like GRANT and REVOKE come under this DCL.

7. What is the difference between having and where clause?

• Having clause is used to specify the condition for a group or an aggregate function which is used in the select statement. The where clause selects the data before grouping. The having clause, on the other hand, selects the rows after grouping. Unlike the having clause, the where clause is not able to contain the aggregate functions. 

8. What is Join?

• In Structured Query Language, a join is used to combine data from two or more tables based on the common field or attribute which exists between them.

9. What is Identity?

• Identity, also is known as the auto number, is generally a column that is used to automatically generate numeric values. A start and an increment value can be set for the same. Additionally, the identity columns do not need to have an index position.

10. What is a view in Structured Query Language, and what are its uses?

• A view is a virtual table in Structured Query Language based on the result set of an SQL statement. Its uses are mentioned in the following points below:-

1. Views are used to represent a subset of data that are combined in a table; consequently, a view can also limit the degree of exposure that an underlying table poses to the outer world. A given user may be permitted to view the structure of the table.
2. Views can be used to join and simplify different tables into one single virtual table.
3. Views can also be used to act as aggregate tables where the database is used to represent the calculated results as per the data, and information is concerned.
4. Views can also be used to hide the complexity of the data from the user.
5. Views as compared to the other queries in the Structured Query Language take very less amount of space to store the data and information concerned in the table. 
6. Additionally, depending upon the Structured Query Language Engine used, Views can be used to provide extra security to the data and information of the tables concerned.

11. What is a Trigger?

• A Trigger is a code that is associated with the Structured Query Language with insert, update or deletes operations. The code is usually executed automatically without the prior knowledge of the user and is executed on the table itself. Triggers mainly come into effect to maintain the integrity of the table and the integrity of the database as a whole.

12. What is a stored procedure?

• A stored procedure is synonymous with a function that comes with a set of operations and which are executed together or simultaneously. It generally contains the set of operations that are used in the applications to do some common database tasks based on the data and information on the table.

13. What is the basic difference between Trigger and stored procedure in a Structured Query Language?

• Unlike the stored procedures, Triggers cannot be used or called directly. They can only be used or associated with the queries concerned.

14. What is a Transaction? What are the ACID properties? 

• A Database Transaction is a set of database operations which in all situations must be treated as a whole, which means that either all of its operations must be executed or none of the operations should be executed in all time. ACID properties mainly refer to the Atomicity, Consistency, Isolation, and Durability. These are mainly the set of properties that guarantee that the database transactions that are being done on the system by the user are processed reliably without any error or discrepancy.

15. What are indexes?

• A database index in Structured Query Language is a data structure that is used to improve the speed and reliability of data retrieval operations that take place on the database table on the cost of additional lines of queries and the use of more storage space to maintain the extra copy of data for future reference if needed. Data can be stored only in one order on the disk. To facilitate faster searching of files, algorithms like binary search are used for the different values of the data concerned so that the data can be found in minimal time. Mainly due to this purpose, these indexes are created on tables. These indexes occupy extra space on the disk of the system, but they allow the user to enjoy faster searches according to the frequency of the searched values.

16. What has Clustered indexes and non-Clustered indexes?

• Clustered Indexes are the indexes according to which the data is being physically stored in the disk. So, it can be seen that only one clustered index can be created on a given database table.

Non-Clustered indexes do not define the physical ordering of the data but help in determining the logical ordering of the data.

17. There is a table which contains two columns, Student and Marks, and you need to find all the students whose marks are greater than average marks that is a list of all average students?

• SELECT STUDENT, MARKS FROM TABLE WHERE MARKS> SELECT(avg MARKS) FROM TABLE;

18. Name the student who has secured the third highest marks using subqueries?

• SELECT NAME FROM TABLE WHERE 2= (SELECT COUNT(DISTINCT(NAME) FROM TABLE);

19. What is the difference between a materialized and dynamic view?

• Materialized views:-

1. Disk space is updated periodically based on the query that is given as input by the user.
2. A materialized table that is being created must be synchronized at all times with its associated base tables.

• Dynamic views:-

1. Dynamic views are virtual, and the queries define how much time they will be accessed based upon the query definition.
2. A dynamic view can be created whenever a specific view is requested by the user.

20. What is Embedded Structured Query Language and Dynamic Structured Query Language?

• Static or Embedded Structured Query Language is the statements that have no effect or are invariant during runtime and are therefore hardcoded into the application of the system.

Dynamic Structured Query Language is generally a programming technique that enables the user to build Structured Query Language statements dynamically at runtime of the queries concerned.

21. What is the difference between char and varchar?

• Char and varchar both differ in storage as well as retrieval is concerned.

Char field length is fixed while the field length of the varchar field is variable and can be changed according to the requirements of the user. The maximum number of characters that char data type can hold is 255 characters, while varchar datatype can hold up to 4000 characters at a time. On the other hand, char is about 50% faster datatype than varchar datatype. This is mainly because char datatype uses static memory allocation while varchar datatype uses dynamic memory allocation.

22. What is the purpose of normalization in DBMS?

• Normalization mainly refers to the process of analyzing the relational schemas that are generally based on their functional dependencies and the primary keys that are concerned with the table to fulfill the requirements of the user concerned.

23. What are the different types of languages that are available in the Database Management System?

• There are three types of languages that are available in the Database Management System. They are as follows:-

1. Data Definition Langauge.
2. Data Control Language.
3. Data Manipulation Language.

24. What is the main purpose of Structured Query Language?

• The main purpose of the Structured Query Language is to interact with the relational databases that are concurrently running in the system in the form of updating/ modifying data and information in the database.

25. What is the concept of a subquery in Structured Query Language? 

• A Subquery is generally a query which is included inside some other queries which are again inside some other queries.

26. What is the use of the DROP command?

• DOP command is a DDL command that is used to delete the existing table, database from the system as per the requirement of the user.

27. What is the main difference between UNION and UNION ALL?

• UNION and UNION ALL are used to join tables from a given database, but UNION removes duplicate rows and only shows the distinct rows that are present in the tables. On the other hand, UNION ALL does not remove the duplicate rows and picks up all the data that are available in both the tables.

28. What is a correlated subquery in DBMS?

• When a subquery is executed for each of the rows of the outer query, then it is known as a Correlated Subquery.

29. What is an Entity?

• An Entity is an object that poses an independent existence in the real world and about which the data can be stored in the database as it is required by the user.

30. Name the different levels of abstraction in DBMS?

• The different levels of abstraction are as follows:-

1. Physical level.
2. Logical level.
3. View level.