To crack the Interviews for various Computer and IT Sector jobs in TCS, Freshers as well as experience technicals must go through the top DBMS Interview Questions given in the following section of this page. You can also download free DBMS Interview Questions and Answers PDFs through the links given below.
DBMS Interview Questions PDF
|DBMS Short Question And Answers PDF||Download|
|DBMS Questions And Answers PDF||Download|
DBMS Interview Questions
Here we have provided some commonly asked DBMS interview questions along with their specific answers. Concerned aspirants seeking jobs in IT and Computer Sector must check these DBMS Viva Questions and Answers in order to prepare for the interviews effectively.
DBMS Interview Questions with Answers:
1) What is DBMS used for?
Answer: DBMS, commonly known as Database Management System, is an application system whose main purpose revolves around the data. This is a system that allows its user to store the data, define it, retrieve it and update the information about the data inside the database.
2) What is meant by a Database?
Answer: In simple terms, Database is a collection of data in some organized way to facilitate its users to easily access, manage and upload the data.
3) Why is the use of DBMS recommended? Explain by listing some of its major advantages.
Answer: Some of the major advantages of DBMS are as follows:
Controlled Redundancy: DBMS supports a mechanism to control the redundancy of data inside the database by integrating all the data into a single database and as data is stored at only one place, the duplicity of data does not happen.
Data Sharing: Sharing of data among multiple users simultaneously can also be done in DBMS as the same database will be shared among all the users and by different application programs.
Backup and Recovery Facility: DBMS minimizes the pain of creating the backup of data again and again by providing a feature of ‘backup and recovery’ which automatically creates the data backup and restores the data whenever required.
Enforcement of Integrity Constraints: Integrity Constraints are very important to be enforced on the data so that the refined data after putting some constraints are stored in the database and this is followed by DBMS.
Independence of data: It simply means that you can change the structure of the data without affecting the structure of any of the application programs.
4) What is the purpose of normalization in DBMS?
Answer: Normalization is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys in order to fulfil certain properties.
The properties include:
- To minimize the redundancy of the data.
- To minimize the Insert, Delete and Update Anomalies.
5) What are the different types of languages that are available in the DBMS?
Answer: Basically, there are 3 types of languages in the DBMS as mentioned below:
DDL: DDL is Data Definition Language which is used to define the database and schema structure by using some set of SQL Queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.
DCL: DCL is Data Control Language which is used to control the access of the users inside the database by using some set of SQL Queries like GRANT and REVOKE.
DML: DML is Data Manipulation Language which is used to do some manipulations in the database like Insertion, Deletion, etc. by using some set of SQL Queries like SELECT, INSERT, DELETE and UPDATE.
6) What is the purpose of SQL?
Answer: SQL stands for Structured Query Language whose main purpose is to interact with the relational databases in the form of inserting and updating/modifying the data in the database.
7) Explain the concepts of a Primary key and Foreign Key.
Answer: Primary Key is used to uniquely identify the records in a database table while Foreign Key is mainly used to link two or more tables together, as this is a particular field(s) in one of the database tables which are the primary key of some other table.
Example: There are 2 tables – Employee and Department. Both have one common field/column as ‘ID’ where ID is the primary key of the Employee table while this is the foreign key for the Department table.
Know Here: How to Prepare For an Interview
8) What are the main differences between Primary key and Unique Key?
Answer: Given below are few differences:
The main difference between the Primary key and Unique key is that the Primary key can never have a null value while the unique key may consist of null value.
In each table, there can be only one primary key while there can be more than one unique key in a table.
9) What is the concept of sub-query in terms of SQL?
Answer: Sub-query is basically the query which is included inside some other query and can also be called as an inner query which is found inside the outer query.
10) What is the use of DROP command and what are the differences between DROP, TRUNCATE and DELETE commands?
Answer: DROP command is a DDL command which is used to drop/delete the existing table, database, index or view from the database.
The major difference between DROP, TRUNCATE and DELETE commands are:
DROP and TRUNCATE commands are the DDL commands which are used to delete tables from the database and once the table gets deleted, all the privileges and indexes that are related to the table also get deleted. These 2 operations cannot be rolled back and so should be used only when necessary.
DELETE command, on the other hand, is a DML Command which is also used to delete rows from the table and this can be rolled back.
Note: It is recommended to use the ‘WHERE’ clause along with the DELETE command else the complete table will get deleted from the database.
11) What is the main difference between UNION and UNION ALL?
Answer: UNION and UNION ALL are used to join the data from 2 or more tables but UNION removes duplicate rows and picks the rows which are distinct after combining the data from the tables whereas UNION ALL does not remove the duplicate rows, it just picks all the data from the tables.
12) Explain the concept of ACID properties in DBMS?
Answer: ACID properties is the combination of Atomicity, Consistency, Isolation, and Durability properties. These properties are very helpful in allowing a safe and secure way of sharing the data among multiple users.
Atomicity: This is based on the concept of “either all or nothing” which basically means that if any update occurs inside the database then that update should either be available to all the others beyond user and application program or it should not be available to anyone beyond the user and application program.
Consistency: This ensures that the consistency is maintained in the database before or after any transaction that takes place inside the database.
Isolation: As the name itself suggests, this property states that each transaction that occurs is in isolation with others i.e. a transaction which has started but not yet completed should be in isolation with others so that the other transaction does not get impacted with this transaction.
Durability: This property states that the data should always be in a durable state i.e. any data which is in the committed state should be available in the same state even if any failure or restart occurs in the system.
13) What is Correlated Subquery in DBMS?
Answer: A Subquery is also known as a nested query i.e. a query written inside some query. When a Subquery is executed for each of the rows of the outer query then it is termed as a Correlated Subquery.
Example of Non-Correlated Subquery is:
|SELECT * from EMP WHERE ‘RIYA’ IN (SELECT Name from DEPT WHERE EMP.EMPID=DEPT.EMPID);|
Here, the inner query is not executed for each of the rows of the outer query.
14) Explain Entity, Entity Type, and Entity Set in DBMS?
Entity is an object, place or thing which has its independent existence in the real world and about which data can be stored in a database. For Example, any person, book, etc.
Entity Type is a collection of entities that have the same attributes. For Example, the STUDENT table contains rows in which each row is an entity holding the attributes like name, age, and id of the students; hence STUDENT is an Entity Type which holds the entities having the same attributes.
Entity Set is a collection of entities of the same type. For Example, A collection of the employees of a firm.
15) What are the different levels of abstraction in the DBMS?
Answer: There are 3 levels of data abstraction in the DBMS.
Physical Level: This is the lowest level of the data abstraction which states how the data is stored in the database.
Conceptual or Logical Level: This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.
View Level: This is the highest level in the data abstraction which shows/states only a part of the database.
16) What integrity rules exist in the DBMS?
Answer: There are 2 major integrity rules that exist in the DBMS.
Entity Integrity: This states a very important rule that value of a Primary key can never have a NULL value.
Referential Integrity: This rule is related to the Foreign key which states that either the value of a Foreign key is a NULL value or it should be the primary key of any other relation.
17) What is E-R model in the DBMS?
Answer: E-R model is known as an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.
18) What is a functional dependency in the DBMS?
Answer: This is basically a constraint which is useful in describing the relationship among the different attributes in a relation.
Example: If there is some relation ‘R1’ which has 2 attributes as Y and Z then the functional dependency among these 2 attributes can be shown as Y->Z which states that Z is functionally dependent on Y.
19) What is 1NF in the DBMS?
Answer: 1NF is known as the First Normal Form.
This is the easiest form of the normalization process which states that the domain of an attribute should have only atomic values. The objective of this is to remove the duplicate columns that are present in the table.
20) What is 2NF in the DBMS?
Answer: 2NF is the Second Normal Form.
Any table is said to have in the 2NF if it satisfies the following 2 conditions:
A table is in the 1NF.
Each non-prime attribute of a table is said to be functionally dependent in totality on the primary key.
DBMS Interview Theory Questions
Q1. What are super, primary, candidate and foreign keys?
Ans: A super key is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes.
A Candidate key is minimal superkey, i.e., no proper subset of Candidate key attributes can be a superkey.
A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more that one primary keys in a table.
Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.
Must Read Latest: Interview Questions and Answers
Q2. What are the differences between DDL, DML and DCL in SQL?
Ans: Following are some details of three.
DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME come under this.
DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and UPDATE come under this.
DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.
Q3. What is a Trigger?
Ans: A Trigger is a code that associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.
Q4. What is a stored procedure?
Ans: A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.
Q5. Explain the concept of ACID properties in DBMS?
- Atomicity: This is based on the concept of “either all or nothing” which basically means that if any update occurs inside the database then that update should either be available to all the others beyond user and application program or it should not be available to anyone beyond the user and application program.
- Consistency: This ensures that the consistency is maintained in the databasebefore or after any transaction that takes place inside the database.
- Isolation: As the name itself suggests, this property states that each transaction that is occurring is in isolation with otherse. a transaction which has started but not yet completed should be in isolation with others so that the other transaction does not get impacted with this transaction.
- Durability: This property states that the data should always be in a durable statee. any data which is in the committed state should be available in the same state even if any failure or restart occurs in the system.
Also Read: Interview Preparation Tips
Q6. What is Correlated Subquery in DBMS?
Ans: A Subquery is also known as a nested query i.e. a query written inside some query. When a Subquery is executed for each of the rows of the outer query then it is termed as a Correlated Subquery.
Example of Non-Correlated Subquery is:
Select * from EMP where ‘RIYA’ IN (Select Name from DEPT where EMP.EMPID=DEPT.EMPID)
Here, inner query is not executed for each of the rows of the outer query.
Q7. Explain Entity, Entity Type, and Entity Set in DBMS?
Ans: Entity is an object, place or thing which has its independent existence in the real world and about which data can be stored in a database. Eg: any person, book, etc.
Entity Type is a collection of the entities which have the same attributes. Eg: STUDENT table contains rows in which each row is an entity holding attributes like name, age, and id of the students; hence STUDENT is an Entity Type which holds the entities having same attributes.
Entity Set is a collection of the entities of the same type. Eg: A collection of the employees of a firm.
Q8. What are the different levels of abstraction in the DBMS?
Ans: There are 3 levels of data abstraction in the DBMS. They include:
- Physical Level:This is the lowest level of the data abstraction which states how the data is stored in the database.
- Logical Level:This is the next level of the data abstraction which states the type of the data and the relationship among the data that is stored in the database.
- View Level:This is the highest level in the data abstraction which shows/states only a part of the database.
Q9. What integrity rules exist in the DBMS?
Ans: There are 2 major integrity rules that exist in the DBMS. They are:
- Entity Integrity:This states a very important rule that value of a Primary key can never have a NULL value.
- Referential Integrity: This rule is related to the Foreign Key which states that either the value of a Foreign Key is a NULL value or it should be the primary key of any other relation.
Q10. What is E-R model in the DBMS?
Ans: E-R model is known as an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.
Q11. What are different types of joins in the SQL?
Ans: There are 4 types of SQL Joins:
- Inner Join:This type of join is used to fetch the data among the tables which are common in both the tables.
- Left Join:This returns all the rows from the table which is on the left side of the join but only the matching rows from the table which is on the right side of the join.
- Right Join:This returns all the rows from the table which is on the right side of the join but only the matching rows from the table which is on the left side of the join.
- Full Join:This returns the rows from all the tables on which the join condition has put and the rows which do not match hold null values.
Q12. What is VDL (View Definition Language)?
Ans: It specifies user views and their mappings to the conceptual schema.
Q13. What is SDL (Storage Definition Language)?
Ans: This language is to specify the internal schema. This language may specify the mapping between two schemas.
Q14. What is SQL Deadlock?
Ans: Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource.
Q15. What is difference between DELETE & TRUNCATE commands?
Ans: Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
Q16. What is Hashing technique?
Ans: This is a primary file organization technique that provides very fast access to records on certain search conditions.
Q17. What is Enterprise Resource Planning (ERP), and what kind of a database is used in an ERP application?
Ans: Enterprise Resource Planning (ERP) is an information system used in manufacturing companies and includes sales, inventory, production planning, purchasing and other business functions. An ERP system typically uses a multiuser database.
Q18. What is Database?
Ans: A database is a logically coherent collection of data and gives a systematic and organized way of storing, managing and retrieving from collection of logically related information and can be used for representing some aspect of real world.
Get Here: Interview Tips For Freshers
Q19. What is DBMS?
Ans: The acronym of DBMS is Database Management System and it is a collection of programs that enables user to create and maintain a database. It gives the facility of independent way of accessing data and should not be dependent on the application to access the information.
Q20. What is a Database system?
Ans: The database and DBMS software together is called as Database system and major difference between a file and database is that database has independent way of retrieving information while simple files do not.
Q21. What are the advantages of DBMS?
Ans: There are the various advantages of using the database by organization, some of its uses are as shown below:
- Unauthorized right of entry is constrained.
- Redundancy or data duplicacy is controlled.
- Enforcing integrity constraints.
- Providing backup and recovery facilities.
- Providing multiple user interfaces to accomplish the task.
Q22. What’s difference between DBMS and RDBMS?
Ans: Database Management Systems provides a systematic and organized way of storing, managing and retrieving from collection of logically related information and RDBMS also provides but above that it provides relationship integrity as shown below:
RDBMS (Relational Database Management Systems): DBMS + Referential Integrity
Q23. What are the disadvantages in File Processing System?
Ans: There are some disadvantages of using the file processing system in database as furnished below:
- Data redundancy and discrepancy occurred.
- Difficult to accessing data information.
- Difficult to handle the data isolation.
- Data integrity in the files.
- Simultaneous access is not possible.
- Security (Protection) Problems.
Q24. What are E-R diagrams?
Ans: E-R diagram also known as Entity-Relationship diagram that shows the relationship between various tables in the database.
Q25. What are CODD rules?
Ans: There are 12 rules which a DBMS should adhere in order to get the logo of a true RDBMS which is created by the Dr. E. F. Codd In 1969.
- Information Rule
- Guaranteed access Rule.
- Systematic treatment of null values
- Dynamic on-line catalog based on the relational model
- Comprehensive data sub-language Rule
- View updating Rule
- High-level insert, update and delete
- Physical data independence.
- Logical data independence
- Integrity independence
- Distribution independence
- Non-subversion Rule
Q26. How many types of relationship exist in database designing?
Ans: There are three major types of the relationship models:
Check Out: HR Interview Questions and Answers
Q27. What is normalization?
Ans: In database, there is set of rules that have been recognized to help in the design of tables that are meant to be linked through relationships. This set of rules is known as Normalization. There are some benefits of Normalizing your database include as shown below:
- Helps in avoiding repetitive entries
- To reduce the required storage space
- Use to preventing the need to restructure existing tables to accommodate new data.
- Help to Increased speed and flexibility of queries, sorts, and summaries.
Q28. What is denormalization?
Ans: Denormalization is the procedure of setting one fact in various places (its vice-versa of Normalization). Only one valid reason exists for denormalizing a relational design – to improve performance. The sacrifice to presentation is that you boost redundancy in database.
Q29. What is extension and intension?
Ans: Extension: It is the number of tuples available in a table at any instance. This is time dependent.
Intension: It is a constant value that gives the name, structure of table and the constraints settled on it.
Q30. What is a view?
Ans: A view may be idea of as a virtual table, a table that does not really exist in its own right or database but as an alternative is derived from one or more underlying base table. We can say that, there is no stored file in database that direct represents the view in its place a definition of view is stored in data dictionary.
Q31. What is System R?
Ans: The System R was designed and developed at IBM San Jose Research Center over a period of 1974-79. It is a first of its kind and its function was to show that it is possible to build a Relational System that can be used in a real life environment to resolve real life problems, with performance at least comparable to that of existing system.
It has two subsystems are as indicated below:
- Research Storage
- System Relational Data System
Q32. How is the data structure of System R different from the relational structure?
Ans: There are some major differences between Relational systems in System R as provided below:
- The Domains are not supported by the user.
- The Enforcement of candidate key uniqueness is not obligatory.
- Enforcement of entity integrity is non-compulsory
- Referential integrity is not imposed.
Q33. What is transparent DBMS?
Ans: It is one, which keeps its physical structure hidden from user.
Q34. Explain the difference between attributes and identifiers.
Ans: Entities have attributes. Attributes are properties that describe the entity’s characteristics. Entity instances have identifiers. Identifiers are attributes that name, or identify, entity instances.
Q35. What is schema?
Ans: The description of a data base is called the database schema, which is specified during database design and is not expected to change frequently. A displayed schema is called schema diagram. We call each object in the schema as schema construct.
Q36. When does checkpoint occur in DBMS?
Ans: checkpoint is like a snapshot of the DBMS state. Using checkpoints, the DBMS can reduce the amount of work to be done during a restart in the event of subsequent crashes. Checkpoints are used for the recovery of the database after the system crash.
Q37. What do you mean by transparent DBMS?
Ans: The transparent DBMS is a type of DBMS which keeps its physical structure hidden from users. Physical structure or physical storage structure implies to the memory manager of the DBMS, and it describes how the data stored on disk.
Q38. What are the unary operations in Relational Algebra?
Ans: PROJECTION and SELECTION are the unary operations in relational algebra. Unary operations are those operations which use single operands. Unary operations are SELECTION, PROJECTION, and RENAME.
As in SELECTION relational operators are used for example – =,<=,>=, etc.
Must Read: Java Programming Interview Questions
Q39. What is RDBMS?
Ans: RDBMS stands for Relational Database Management Systems. It is used to maintain the data records and indices in tables. RDBMS is the form of DBMS which uses the structure to identify and access data concerning the other piece of data in the database. RDBMS is the system that enables you to perform different operations such as- update, insert, delete, manipulate and administer a relational database with minimal difficulties.
Q40. What is a degree of Relation?
Ans: The degree of relation is a number of attribute of its relation schema. A degree of relation is also known as Cardinality it is defined as the number of occurrence of one entity which is connected to the number of occurrence of other entity. There are three degree of relation they are one-to-one(1:1), one-to-many(1:M), many-to-one(M:M).
DBMS Technical Interview Questions:
Q1. Differentiate Nested Loop, Hash Join and Merge Join.
Nested loop (loop over loop)
An outer loop within an inner loop is formed consisting of fewer entries and then for individual entry, inner loop is individually processed.
- Select col1.*, col2.* from coll, col2 where coll.col1=col2.col2;
It’s processing takes place in this way:
For i in (select * from col1) loop
For j in (select * from col2 where col2=i.col1) loop
Results are displayed;
End of the loop;
End of the loop;
The Steps of nested loop are:
- Identify outer (driving) table
- Assign inner (driven) table to outer table.
- For every row of outer table, access the rows of inner table.
Nested Loops is executed from the inner to the outer as:
- Hash join
While joining large tables, the use of Hash Join is preferred.
Algorithm of Hash Join is divided into:
- Build: It is a hash table having in-memory which is present on the smaller table.
- Probe: this hash value of the hash table is applicable for each second row element.
- Sort merge join
Two independent sources of data are joined in sort merge join. They performance is better as compared to nested loop when the data volume is big enough but it is not good as hash joins generally.The full operation can be divided into parts of two:
Sort join operation:
Get first row R1 from input1
Get first row R2 from input2.
Merge join operation:
‘while’ is not present at either loop’s end.
if R1 joins with R2
next row is got R2 from the input 2
return (R1, R2)
else if R1 < style=””> next row is got from R1 from input 1
next row is got from R2 from input 2
end of the loop
Q2. How to create Table?
Ans: For creating a Table in database, we have to use ‘CREATE TABLE’ Statement.
CREATE TABLE “SomeName”
Q3. What is a Join?
Ans: An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables.
Student Course Table
Following is join query that shows names of students enrolled in different courseIDs.
SELECT StudentCourse.CourseID, Student.StudentName
INNER JOIN Customers
ON StudentCourse.EnrollNo = Student.EnrollNo
ORDER BY StudentCourse.CourseID;
The above query would produce following result.
Q4. What is a view in SQL? How to create one
Ans: A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.
CREATE VIEW view_name AS
Q5. There is a table where only one row is fully repeated. Write a Query to find the repeated row
In the above table, we can find duplicate row using below query.
SELECT name, section FROM tbl
GROUP BY name, section
HAVING COUNT(*) > 1
Check Here: SQL Server Interview Questions
DBMS Interview Questions for Freshers
Database Management System is one of the essentials part of IT Section. Knowledge of DBMS Basics is essential to perform well in the interviews of any IT sector firm including TCS. But answering the DBMS Interview Questions is not an easy task.
There are various vacancies related to DBMS like the SQL Analyst, Database Administrator etc. Some of the top DBMS Interview Questions in TCS provided here will help you in preparing for these interviews in an effective manner.
There are various types of questions that may be asked in the interview like the DBMS SQL Questions or DBMS Technical Questions, RDBMS Interview questions etc.
Find Here: Latest Jobs Openings For Freshers
Along with these DBMS Viva Questions, you can also download Database Interview Questions and Answers PDF by pressing the direct links facilitated at the top of this page. It is advised to learn more and more DBMS questions and answers in order to develop your knowledge over this section.
We hope that these DBMS Interview Questions and Answers will help you to great extent in the preparation for related jobs. We will keep on updating these DBMS Interview Questions for Freshers time to time. Therefore, it is advised to bookmark this page by pressing CTRL +D and keep visiting regularly to check further updates of DBMS Interview Questions.