- What is DBMS and what is it used for?
- What are the advantages of using DBMS?
- Name different languages available in DBMS.
- What are the differences between DROP, TURNCATE and DELETE commands
- State the difference between UNION and UNION ALL.
- What are the integrity rules that exist in the DBMS?
- What is the concept of ACID properties in DBMS?
- Mention the different levels of abstraction in the DBMS.
- What is the E-R model in DBMS?
- Explain the concept of sub-query in terms of SQL.
- What is a Trigger?
- What are the different keys? Explain each of them.
- Explain database normalization.
- State the differences between HAVING and WHERE clause.
- What is identity?
- What are indexes?
- What is a checkpoint in DBMS?
- What is a Relation and Relation Schema?
- What is Relation Algebra?
- What is query optimization?
- Explain functional dependency.
- What is an Entity?
DBMS is one of the popular databases and is frequently asked about in interviews related to the IT field.
We’ve compiled some of the most popular DBMS interview questions and answers. Prepare for your next interview with these top DBMS interview questions to ace any interview.
Let’s get started!
1. What is DBMS and what is it used for?
DBMS or Database Management System as the name suggests is a database application system which allows users to store, retrieve, edit and manage data stored on this database.
2. What are the advantages of using DBMS?
There are several advantages of using DBMS and some of them are:
- Independent of data: With a DBMS, you can change the structure of data without affecting any of the application programs.
- Redundancy control: DBMS integrates all the data into a single database, which prohibits data duplicity.
- Recovery and backup: DBMS offers a feature of backup and recovery, which automatically creates a backup of data. Data can be restored whenever required.
- Seamless sharing: With DBMS it’s very easy to share data to multiple users simultaneously.
3. Name different languages available in DBMS.
DBMS offers 3 different types of languages:
- DML: Data Manipulation Language is used to make changes in the database such as deletion, insertion and more with the help of SQL queries like DELETE, INSERT, UPDATE and SELECT.
- DDL: Data Definition Language as the name suggests defines the database and schema structure with the help of SQL queries like DROP, RENAME, TRUNCATE, ALTER & CREATE.
- DCL: Data Control Language is used to control access of users inside a database with the help of SQL queries like REVOKE and GRANT.
4. What are the differences between DROP, TRUNCATE and DELETE commands?
This a very typical question which is asked as a DBMS interview question. The main differences between the three are mentioned below.
Using DROP or TRUNCATE commands deletes tables from the database along with the privileges and indexes related to the tables.
Also, there is no way to roll back after executing these commands.
DELETE command however can be reversed.
5. State the difference between UNION and UNION ALL.
Both UNION and UNION ALL are used to join data from more than 2 tables.
The difference is that UNION removes any duplicate rows in the tables while UNION ALL does not remove any duplicate data.
6. What are the integrity rules that exist in the DBMS?
The 2 integrity rules that exist in DBMS:
- Referential Integrity: This rule is associated with Foreign Key which states if the key has a NULL value or whether it should be a primary key of some other relation.
- Entity Integrity: This rule suggests that the value of a Primary Key can never be NULL.
7. What is the concept of ACID properties in DBMS?
ACID properties help in sharing data in a secured and safe way to multiple users. ACID is just an abbreviation of 4 properties which are:
Atomicity
Consistency
Isolation
Durability
- Atomicity: It is simply based on the condition of either all or nothing. So, if there’s an update in a database, it would be either available with all the users or none of them.
- Consistency: This property ensures consistency in the database whenever a transaction is made.
- Isolation: It ensures that an ongoing transaction remains in isolation until it’s completed. This way it doesn’t affect other transactions.
- Durability: This ensures that data stays in a durable state. This way any data in the committed state would be available in the same state even if there are failures or a restart occurs.
8. Mention the different levels of abstraction in the DBMS.
The 3 levels of abstraction are:
- Physical Level: This is the first level of abstraction which states how data is stored in a database.
- Logical Level: This is the second level of abstraction which states the type and relationship among the data which is stored in the database.
- View Level: The third level of abstraction which shows only a fragment of the database.
9. What is the E-R model in DBMS?
Entity-Relationship, as the name suggests, is the concept which showcases the relationship between different entities that exist.
10. Explain the concept of sub-query in terms of SQL.
A very simple question but it’s commonly asked as DBMS interview questions.
A sub-query is a query inside a bigger query. It’s also called an inner query at times.
11. What is a Trigger?
A trigger is one of the most significant codes which is deployed automatically in response to events occurring in tables or views.
12. What are the different keys? Explain each of them.
The main keys are as follows:
- Foreign Key: It’s a field or collection of fields which identifies a row of another table.
- Super Key: It’s a set of attributes of a relation schema. All attributes of the schema are functionally dependent.
- Primary Key: It’s one of the many candidate keys. The candidate key which is selected as the most important one eventually becomes the primary key.
- Candidate Key: It’s a column or set of columns which can identify any record in the database without referring to any data.
13. Explain database normalization.
It’s a process based on functional dependencies and primary key which analyze the given relation schemas to achieve the following:
- Minimized redundancy
- Minimized update anomalies, insertion and deletion
14. State the differences between HAVING and WHERE clause.
HAVING is used in select statement for an aggerated function or to specify the condition of a group.
On the other hand, the WHERE clause is used before grouping. The WHERE clause doesn’t contain aggerated functions.
15. What is identity?
Identity also is known as Auto Number is a column which generates numeric values automatically.
Increment value of it can be set and also the start value. But most DBA leave it at 1.
16. What are indexes?
A database index improves the speed of data retrieval at the cost of additional writes and more storage space so that it can maintain a copy of the data.
It’s a data structure which allows faster searching by creating on the tables.
17. What is a checkpoint in DBMS?
A checkpoint in DBMS is a point at which all the previous logs are deleted from the system and stored on the storage disk.
These stored logs can be used in log recovery to recover the database engine.
18. What is a Relation and a Relation Schema?
Also known as the Table Schema, a Relation Schema is specified as a set of attributes which defines the name of a table.
While a Relation is a set of connected attributes with similar key attributes.
19. What is Relational Algebra?
It’s a procedural query language which consists of sets of operations that produce new relationships with one or two relations as inputs.
Some fundamental operations of Relation Algebra are:
- Set difference
- Select
- Union
- Rename
- Project
20. What is query optimization?
In simple terms, query optimization means curating a plan to execute the queries most efficiently.
Due to multiple methods and algorithms, a need to find the most efficient way to execute queries was essential, which is known as query optimization.
The key benefits of query optimization are:
- Reduction of time and space
- Optimized queries take less time to execute
- User satisfaction due to fast-paced output
21. Explain functional dependency.
Functional Dependency is the first step of normalization. Functional Dependency exists when a relation between two different attributes can help you find the corresponding attribute’s value uniquely.
It is represented mathematically as:
A->B
Where B is functionally dependent on A
22. What is an Entity?
It’s a set of attributes in any database. It’s mostly real-world or physical objects which are represented in a database.
For example: In an employee database, the name of the organization, department etc. can be considered as entities.
Tips to ace DBMS interview
Here are some tips to ace your DBMS interview.
Know about the company
Before you interview at any company, make sure you’ve researched enough about them.
This includes when it was founded, their industry, their products and so on.
Ask questions
People who don’t question interviewers think that’s what an interviewer expects, which is completely wrong.
If you have questions about your role, the organization or the work you’ll be doing, this gives out an impression that you’re interested and proactive.
So, if you have questions for them, fire it away.
Use your experience to showcase examples
Whenever answering questions related to DBMS or, any technology, use your experience to answer questions.
Answer the interviewer how a particular question is reminiscent of what you did back in one of your organization.
This will not only help you answer a question but also showcase your experience.
Make a good first impression
You must make a really good impression on the interviewers.
You can do this by doing simple things like being polite, greeting, asking permission to sit, stand up to greet interviewers when they walk in the room etc.
