.png)
With the growing amount of data, it's more crucial than ever to keep it safe and access it when required. Most of the top companies such as Infosys, Amazon TCS, Wipro, Accenture, etc frequently ask questions related to DBMS in their recruitment process.
So, let's begin with the 40 most essential DBMS interview questions and answers.
Mobiprep handbooks are free downloadable placement preparation resources that can help you ace any company placement process. We have curated a list of the 40 most important MCQ questions with detailed explanations to ensure the best placement preparation.
These top Data-Structures Interview questions have been segmented into three sections:
Basic DBMS Interview Questions
Question 1. What is DBMS?
A database management system consists of collection of related data and refers to a set of programs for defining, creation, maintenance and manipulation of a database.
Question 2. What are the disadvantages of file processing system?
Data redundancy and inconsistency.
Difficult in accessing data.
Data isolation.
Data integrity.
Concurrent access is not possible.
Security Problems
Question 3. What are the advantages of DBMS?
Data Independence.
Efficient Data Access.
Data Integrity and security.
Data administration.
Concurrent access and Crash recovery.
Reduced Application Development Time
Question 4. What do you mean by data?
Data is the known facts or figures that have implicit meaning. It can also be defined as it is the representation of facts ,concepts or instruction in a formal manner.
Question 5. What are the levels of data abstraction?
Physical Level: This is a lowest level, which describes how the data is actually stores
Logical Level: This is next higher level that describes what data and what relationships in the database.
Conceptual Level: This is a lowest level, which describes entire database
Question 6. What is Schema?
A schema is a logical data base description and is drawn as a chart of the types of data that are used . It gives the names of the entities and attributes and specify the relationships between them.
Question 7. Define DDL.
DDL is used to define database objects .The conceptual schema is specified by a set of definitions expressed by this language. It also give some details about how to implement this schema in the physical devices used to store the data.
Question 8. Define DML
A DML is a language that enables users to access or manipulate data stored in the database. Data manipulation involves retrieval of data from the database, insertion of new data into the database and deletion of data or modification of existing data
Question 9. Define DCL.
This language enables user to grant authorization and canceling authorization of database objects.
Question 10. Explain physical and logical data independence.
Physical data independence is the ability to modify the physical scheme without making it necessary to rewrite application programs.
Logical data independence is the ability to modify the conceptual scheme without making it necessary to rewrite application programs.
Question 11. What is a data model? Name some.
The data model describes the structure of a database. It is a collection of conceptual tools for describing data, data relationships and consistency constraints and various types of data
model such as-
1. Object based logical model
2. Record based logical model
3. Physical model
Question 12. What is ER-model?
The entity-relationship data model perceives the real world as consisting of basic objects, called entities and relationships among these objects.
Question 13. What is super key?
A super key is a set of one or more attributes that taken collectively, allow us to identify uniquely an entity in the entity set.
Question 14. What is candidate key?
In a relation R, a candidate key for R is a subset of the set of attributes of R, which have the following properties:
Uniqueness: no two distinct tuples in R have the same values for the candidate key
Irreducible: No proper subset of the candidate key has the uniqueness property that is the candidate key
Question 15. Define primary key.
The primary key is the candidate key that is chosen by the database designer as the principal means of identifying entities with in an entity set. The remaining candidate keys if any, are called alternate key.
Intermediate DBMS Interview Questions
Question 16. Define tuple, attribute and domain.
Tuple: Each row in a table represents a record and is called a tuple. A table containing ‘n’ attributes in a record is called is called n-tuple.
Attribute:The name of each column in a table is used to interpret its meaning and is called an attribute.Each table is called a relation.
Domain: A domain is a set of values that can be given to an attributes. So every attribute in a table has a specific domain. Values to these attributes can not be assigned outside their domains.
Question 17. Is it possible for several attributes to have the same domain.
Yes, several attributes to have same domain.attributes:name,address, belong to same domain contains all text string of certain length
Question 18. Define relationship and relationship set.
A relation ship is an association among several entities. For example, a depositor relationship associated a customer with each account. The set of all relationships of the same type are termed a relationship set.
Question 19. What is Weak entity set?
An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity.
Question 20. Explain relational model.
Relational model is a simple model in which database is represented as a collection of “relations” where each relation is represented by two-dimensional table.
-It is column homogeneous. In other words, in any given column of a table, all items are of the same kind.
-All rows of a table are distinct.
-The ordering of rows with in a table is immaterial.
Question 21. Explain domain and key constrants.
DOMAIN CONSTRAINTS: It specifies that each attribute in a relation an atomic value from the corresponding domains. The data types associated with commercial RDBMS domains include:
1. Standard numeric data types for integer
2. Real numbers
3. Characters
4. Fixed length strings and variable length strings.
KEY CONSTRAINT: This constraints states that the key attribute value in each tuple must be unique .i.e, no two tuples contain the same value for the key attribute.(null values can allowed)
Question 22. What is entity integrity constraints?
It states that no primary key value can be null. This is because the primary key is used to identify individual tuple in the relation. So we will not be able to identify the records uniquely containing null values for the primary key attributes. This constraint is specified on one individual relation.
Question 23. Explain referential integrity constraints.
It states that the tuple in one relation that refers to another relation must refer to an existing tuple in that relation. This constraints is specified on two relations . If a column is declared as foreign key that must be primary key of another table
Question 24. Discuss about anomalies in a database.
1. Redundency: Information should be stored only once. Storing information several times leads to the waste of storage space and an increase in total size of the data stored.
2. Update anomalies
3. Insertion anomalies
4. Deletion anomalies
Question 25.Why do we need normalization?
The basic objective of normalization is to reduce redundancy which means that information is to be stored only once. Storing information several times leads to wastage of storage space and increase in the total size of the data stored.
Advanced Data Structures Interview Questions
Question 26. Define 1NF.
A relation scheme is said to be in first normal form(1NF) if the values in the domain of each attribute of the relation are atomic. In other words, only one value is associated with each attribute and the value is not a set of values or a list of values.
Question 27. Define 2NF.
A relation is said to be in 2NF if it is in 1NF and non-key attributes are functionally dependent on the key attribute(s). i.e. there should no partial dependency.
Question 28. Define 3NF.
A relation is said to be in 3NF if it is in 2NF and there should be no transitive dependency.
Question 29. Define BCNF.
A relationalschema R isin Boyce–Codd normal form if and only if for every one of its dependencies X → Y, at least one of the following conditions hold
1. X → Y is a trivial functional dependency.
2. X is a superkey for schema R.
Question 30. What do you mean by transaction?
A transaction is a unit of program execution that accesses and possibly updates various data items.It is initiated by a user program written in a highlevel data-manipulation language or programming language (for example, SQL, COBOL, C, C++, or Java), where it is delimited by statements (or function calls) of the form begin transaction and end transaction.
Question 31. Discuss ACID properties.
Atomicity: Either all operations of the transaction are reflected properly in the database, or none are.
Consistency: Execution of a transaction in isolation (that is, with no other transaction executing concurrently) preserves the consistency of the database.
Isolation: Even though multiple transactions may execute concurrently, the system guarantees that, for every pair of transactions Ti and Tj, it appears to Ti that either Tj finished execution before Ti started, or Tj started execution after Ti finished. Thus, each transaction is unaware of other transactions executing concurrently in the system.
Durability: After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures.
Question 32. What are the constraints in SQL?
UNIQUE - Ensures unique values to be inserted into the field.
INDEX - Indexes a field providing faster retrieval of records.
PRIMARY KEY - Uniquely identifies each record in a table.
FOREIGN KEY - Ensures referential integrity for a record in another table.
NOT NULL - Restricts NULL value from being inserted into a column.
CHECK - Verifies that all values in a field satisfy a condition.
DEFAULT - Automatically assigns a default value if no value has been specified for the field.
Question 33. Write query for creating a table student with name and ID as attributes and ID as a primary key.
CREATE TABLE Students (
ID INT NOT NULL
Name VARCHAR(255)
PRIMARY KEY (ID)
);Question 34. What is the purpose of ROLLBACK?
To undo work done in the current transaction. You can also use this command to manually undo the work done by an in-doubt distributed transaction.
Question 35. List the aggregation functions in SQL.
- COUNT returns the number of rows in a specified column.
- SUM returns the sum of the values in a specified column.
- AVG returns the average of the values in a specified column.
- MIN returns the smallest value in a specified column.
- MAX returns the largest value in a specified column.
Question 36. What is a join in SQL and what are its types?
JOIN clause is used to join two or more tables based on a related column between the two.
There are four types of joins in SQL-
1. Inner join: Retrieves records that have matching values in both tables involved in the join.
2. Left join: Retrieves all the rows from the left and the matched rows from the right table.
3. Right join: Retrieves all the rows from the right and the matched rows from the left table.
4. Full Join: Retrieves all the records where there is a match in either the left or right table.
Question 37. Write a SQL querry to retrieve fname and lname from student table of only students who have even ID.
SELECT fname, lname
FROM students
WHERE MOD(id,2) = 0;Question 38. What are TURNCATE, DELETE and DROP statements?
TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
DELETE statement is used to delete rows from a table.
DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database.
These DBMS interview questions would give you an insight into what kind of questions could be asked.
Free Practice Tests
Most Important Interview Questions
Last Minute Class Notes
This is super helpful!