Before diving into the DBMS Interview questions, let’s understand the importance of data. Data is one of the most valuable resources for any organization, it helps in gaining valuable insights, storing records, and performing various data-dependent tasks.
The requirement of collecting, evaluating, and managing the data is at an all-time high and hence the need for skilled people in Database Management Systems (DBMS).
So the interview is near, don’t worry, in this article, we will discuss the Top 30 most frequently asked DBMS interview questions and answers for freshers to professionals.
Let’s begin!
DBMS Interview Questions For Freshers
Question 1. What is a Database?
Answer – Databases are systems that store, modify and access collections of information stored electronically in a computer system.
Question 2. What is DBMS?
Answer – A DBMS (Data Base Management System) is software that manages a database. DBMS allows developers to establish communication with the database with the help of query languages or graphical user interface (GUI) and it facilitates database operations and database administrative actions.
Question 3. How is RDBMS different from DBMS?
Answer – RDBMS i.e. Relational Database Management System is just like DBMS but it maintains the relational integrity of the Database.
RDBMS stores data in the form of collections of tables and relations can be defined between common fields of these tables.
Examples: MySQL, PostgreSQL, SQL Server, Oracle, etc.
Question 4. Discuss the advantages of DBMS over traditional file-based systems.
Answer – The DBMS advantages over File system are as follows:
- Data redundancy and inconsistency – The file system can not control the repetition of data, and since there is a repetition of data/ files when one user makes changes to a certain file, the changes will not get updated in other instances of the same file and this may lead to inconsistency in data.
- Data Sharing – In the file system, it is difficult to share the data with multiple users, on the other hand, DBMS allows more than one user to access data from the same database.
- Data Integrity – In the process of adding data to the database, we put some checks or user-defined constraints for the data. On the other hand, the file system does not provide any procedure to check these constraints.
- Data Security – DBMS is architectured to ensure data security, it ships with features like Backup and Recovery to restore data when required from automatic backup.
Question 5. What are the different types of DBMS?
Answer – The different types of DBMS are as follows:
- Relational DBMS (RDBMS): This type of DBMS, uses a structure that allows the users to access data in relation to another piece of data in a database. In RDBMS data is stored in the table format e.g. MySQL.
- Hierarchical DBMS: In this, the DBMS has a structure similar to that of a tree, wherein the nodes represent records and the branches of the tree represent fields e.g. RDM Mobile.
- Network DBMS: Network DBMS supports many-to-many relations i.e. multiple member records can be linked e.g. Raima Database Manager.
- Object-oriented DBMS: Uses small individual software called objects to store pieces of data and the instructions for the actions to be done with the data e.g. Objectivity/DB.
Question 6. What are the different types of languages present in DBMS?
Answer – The different languages present in DBMS with some of their commands are as follows:
- DDL(Data Definition Language) –DDL commands are used to define the database.
- DML(Data Manipulation Language) – DML commands are used to manipulate the data present in the database.
- DCL(Data Control Language) – DCL commands deal with the user permissions and controls of the database system.
- TCL(Transaction Control Language) – TCL commands are used to deal with the transaction of the database.
Question 7. What is SQL?
Answer – SQL (Structured Query Language) is the global standard language for RDBMS. SQL facilitates access and manipulation of the database.
Question 8. How is SQL different from MySQL?
Answer – SQL is a standard language for accessing and manipulating databases.
MySQL is an RDBMS and SQL is a query language used to access and manipulate databases.
Question 9. What are Tables and Fields?
Answer – A table is a collection of data in the form of rows and columns. Columns are called fields and rows in a table are called records. Explore how to create a SQL table and you will get to know the different commands, and concepts used in tables. This is also a very important concept that will help you in answering DBMS interview questions based on tables and fields.
Question 10. What are the Constraints in SQL?
Answer – SQL constraints are used to specify rules for data in a table.
Constraints are specified when the table is created with the CREATE TABLE statement. We can also add constraints with ALTER TABLE statements.
SYNTAX
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint
);Some of the common constraints used in SQL are:
- NOT NULL – column cannot have a NULL value
- UNIQUE – values in a column are different
- CHECK – values in a column satisfy a specific condition/ rule
- DEFAULT – the default value for a column if no value is associated
- PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
- FOREIGN KEY – Prevents actions that would destroy links between tables
Question 11. Is the NULL value the same as the blank space in SQL?
Answer – A Null Value represents a value that is not available (missing values), unknown, assigned, or not applicable values. Null Values are not equal to zero. They are not equal to space too.
Question 12. What is a Query?
Answer – Query in DBMS corresponds to user commands which are used to interact with the database. The query can request data from multiple tables in a database. The query can be of two types, Select queries, and Action queries.
Query language like SQL is used to perform these actions.
Question 13. What is the SELECT statement?
Answer – The SELECT statement returns a result of records, from one or multiple tables.
SYNTAX
SELECT * FROM demoDB.students;Question 14. What are some of the clauses used in the SELECT statement?
Answer – The SELECT statement has many optional clauses:
- SELECT clause returns a result-set of data from specified columns in the query.
- AS renames the column or table with an alias name mentioned in the SELECT clause. It only renames the result set and not in the database.
- FROM specifies the target table from which data will be retrieved.
- WHERE specifies which rows to retrieve based on some conditions.
- GROUP BY groups rows according to their property or values in the result set, it is used with aggregate functions like COUNT, AVG, and more.
- HAVING is used with aggregate functions in addition to the Where clause as Where doesn’t support aggregate functions.
- ORDER BY is used to specify the sorting order in the result set.
Question 15. What is a Primary Key?
Answer – The primary key is a column or set of attributes that uniquely identifies each row in a table. The primary key defined from multiple columns is known as the composite primary key.
- A primary key cannot have a null value.
- Only one primary key per table is allowed.
- Each and every value in the primary key column is unique.
Question 16. What is a Foreign Key?
Answer – A foreign key column is used to establish a link between two tables. Foreign key constraint ensures referential integrity in the relation between two tables.
The table with the foreign key is called the child table, and the table containing the candidate key is called the parent table.
Question 17. What is a UNIQUE constraint?
Answer – To ensure the uniqueness of all the values in the column we use UNIQUE constraints. Both UNIQUE and PRIMARY KEY constraints ensure the uniqueness of values but we can have multiple UNIQUE constraints but only one PRIMARY KEY constraint.
Question 18. Differentiate between UNIQUE and PRIMARY KEY?
| UNIQUE KEY | PRIMARY KEY |
| Unique key can have a NULL value | The primary key does not have a NULL value |
| There can be multiple columns with unique as a constraint. | Only one primary key is allowed per table. |
Question 19. What are the ACID properties in DBMS?
Answer – ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties are used to ensure the transactions of data are processed accurately in a DBMS.
- Atomicity – Atomicity means that the entire transaction should be done at once or no transactions at all. Partial transactions are not allowed.
Ever heard ‘All or nothing!’? Now that’s atomicity.
- Consistency – Consistency means that the entire data should remain consistent and its integrity constraints should be maintained before and after a transaction.
- Isolation – Isolation refers to transactions being executed in serialized order so that no two transactions happen together.
- Durability – Durability means any changes made to a database via transactions that have been committed should be saved on disk even in case of system failures and hence saving the database from crashes, service outages, etc.
Question 20. What are the different types of relationships in SQL?
Answer – The Different types of relationships in SQL are:
- One-to-One – It is said to be a one-to-one relationship between two tables where each record in the first table is associated with only one record in the second table.
- One-to-Many & Many-to-One – One-to-Many is the most used relationship where a record in a table is linked with multiple records in the other table.
- Many-to-Many – This is used in cases when multiple records on both tables are required to define a relationship.
- Self-Referencing – A self-Referencing Relationship is used when a table needs to define a relationship with itself.
DBMS Interview Questions For Experienced
Question 21. What is a View?
Answer – In SQL, a View is a virtual table, a result set returned by a SQL query. A view has a table-like representation i.e. it is composed of rows and columns. Views are not stored-set in the database. Views are temporary; therefore, they don’t require extra space and are not stored physically on disk.
Question 22. What is Data Integrity?
Answer – Data integrity is the assurance of data accuracy and consistency over its life cycle. It is critical to the implementation, and usage of any system with operations such as storing, processing, or retrieval of data. It defines integrity constraints to enforce rules on the data when it is entered into an application or a database.
Question 23. What are the different levels of abstraction in the DBMS?
Answer – DBMS has 3 levels of abstraction:
- Physical Level: Physical level states how data is being stored in the database. It is the lowest level of data abstraction.
- Logical Level: Logical level states the relationship between data and their types in a database. It is the middle level of data abstraction.
- View Level: View level shows/ states only a part of the database. It is the highest level of data abstraction.
Question 24. What integrity rules exist in the DBMS?
Answer – Integrity rules that exist in the DBMS are:
- Entity Integrity: This rule states a very important rule i.e. Primary key can never have a NULL value.
- Referential Integrity: This rule states that either the value of a Foreign key is a NULL value or it should be the primary key of any other relation.
Question 25. What are an Entity and Relationship?
Answer – Entity: An entity is an object in the real world that is unique and can be distinguished from others. For example, in a Hospital database, doctors, nurses, active patients, departments, and beds can be referred to as entities. Each entity has some associated properties that provide it with an identity.
Relationships: Relations between entities that have some link with each other. For example – The Hospital database, bed number, room type, and the doctor can be associated with the patient.
Question 26. What is the ER model?
Answer – ER model stands for Entity-Relationship model. It is based on the concept of entities and the relationship between those entities.
Entities are tables that hold specific information and relationships are associations defined between those entities.
Question 27. What is Normalization?
Answer – Normalization is a database design process that reduces data redundancy and avoids anomalies and ensures that data get distributed properly among the tables. In normalization, we divide the database into multiple tables and create relationships between them and remove the duplicate data in the process.
Question 28. What is Denormalization?
Answer – Denormalization is the inverse of normalization, it converts the normalized schema to schema with redundant data. The denormalized structure has improved performance. We perform denormalization when e overheads are produced in the query processor by an over-normalized structure.
Question 29. What are the various forms of Normalization?
Answer – Normalization works through a series of stages called Normal forms. The normal forms apply to individual relations. The relation is said to be in particular normal form if it satisfies constraints.
| Normal Form | Description |
| 1NF | A relation is in 1NF if it contains an atomic value. |
| 2NF | A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional and are dependent on the primary key. |
| 3NF | A relation will be in 3NF if it is in 2NF and no transition dependency exists. |
| BCNF | A stronger definition of 3NF is known as Boyce Codd’s normal form. |
| 4NF | A relation will be in 4NF if it is in Boyce Codd’s normal form and has no multivalued dependency. |
| 5NF | A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining should be lossless. |
Question 30. What is a checkpoint in DBMS and when does it occur?
Answer – In the checkpoint mechanism, we can remove all log files from the system and store them as secondary storage disks permanently. We can declare a checkpoint before which the DBMS is in an inconsistent state and all transactions are committed.
The checkpoint is like a bookmark. The log files will be created as the transaction is being carried out, such checkpoints being marked, and carried out using the transaction’s steps.
In this article, we quickly revised the Top 30 frequently asked DBMS interview questions and answers. Now all the best. May the force be with you 🔥
If you are preparing for tech interviews and have doubts in your mind, then join Geekster’s full stack web development course to learn programming and prepare for tech interviews from scratch. Get complete placement support with mock interview sessions.
Read Other Related Articles
- Top 9 Interview Questions and Answers for Freshers Success
- How To Introduce Yourself In Interview?: A Winning Guide To First Impressions
- How To Answer The ‘Tell Me About Yourself’
- How To Craft A Winning Data Analyst Resume As A Fresher?
- Top 25 OOPs Interview Questions and Answers (2023)
