Are you preparing for a DBMS job interview and wondering what questions you might be asked? In this blog we have hand-picked the questions you are likely to be asked in a DBMS job interview and provided to-the-point answers to each one of them so as to help you prepare better for DBMS job interviews.
Always keep in the mind that, only academic knowledge is not enough to crack an interview. Employers expects from the candidate to have practical knowledge and hands-on experience as well. This DBMS Interview Questions designed by industry expert will help you to gain practical knowledge of DBMS.
DBMS Interview Questions and Answers
- What is database or database management systems (DBMS)?
Database provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. Secondly the information has to be persistent, that means even after the application is closed the information should be persisted. Finally it should provide an independent way of accessing data and should not be dependent on the application to access the information.
- What is database?
Answer: A database is a logically coherent collection of data with some inherent meaning, representing some aspect of real world and which is designed, built and populated with data for a specific purpose.
- How many types of database languages are?
Answer: There are four types of database languages:
- Data Definition Language (DDL)g. CREATE, ALTER, DROP etc.
- Data Manipulation Language (DML)g. SELECT, UPDATE, INSERT etc.
- DATA Control Language (DCL)g. GRANT and REVOKE.
- Transaction Control Language (TCL)g. COMMIT and ROLLBACK.
- Can you explain the between clause in DBMS?
Answer: Below SQL selects employees born between '01/01/1995' AND '01/01/1978' as per mysql
SELECT * FROM wbEmployee WHERE DOB BETWEEN '1995-01-01' AND '2011-09-28'
- What is an attribute?
Answer: It is a particular property, which describes the entity.
- What is stored procedure?
Answer: A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.
- What's the difference between file and database? Can files qualify as a database?
Answer: Main difference between a simple file and database that database has independent way (SQL) of accessing information while simple files do not File meets the storing, managing and retrieving part of a database but not the independent way of accessing data. Many experienced programmers think that the main difference is that file can not provide multi-user capabilities which a DBMS provides. But if we look at some old COBOL and C programs where file where the only means of storing data, we can see functionalities like locking, multi-user etc provided very efficiently. So it’s a matter of debate if some interviewers think this as a main difference between files and database accept it… going in to debate is probably loosing a job.
- What is a Database system?
Answer: The database and DBMS software together is called as Database system.
- What is normalization?
Answer: Normalization is a process of analyzing the given relation schemas according to their functional dependencies. It is used to minimize redundancy and also minimize insertion, deletion and update distractions.
- What is the SQL " IN " clause?
Answer: SQL IN operator is used to see if the value exists in a group of values. For instance the below
SQL checks if the Name is either 'David' or 'Craig' SELECT * FROM wbEmployee WHERE name IN ('David','Craig') Also you can specify a not clause with the same. SELECT * FROM wbEmployee WHERE age NOT IN (30,25)
- What is Weak Entity set?
Answer: 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, then it is said to be Weak Entity set.
- What is data independence?
Answer: Data independence specifies that the application is independent of the storage structure and the access strategy of data. It means the ability to modify the schema definition in one level should not affect the schema definition in the next higher level.
There are two types of data independence:
- Physical data independence
- Logical data independence
- What is SQL?
Answer: SQL stands for Structured Query Language.SQL is an ANSI (American National Standards
Institute) standard computer language for accessing and manipulating database systems. SQL
statements are used to retrieve and update data in a database.
- What are the advantages of DBMS?
- Redundancy is controlled.
- Unauthorised access is restricted.
- Providing multiple user interfaces.
- Enforcing integrity constraints.
- Providing backup and recovery.
- What is Denormalization?
Answer: Denormalization is the process of boosting up database performance and adding of redundant data which helps to get rid of complex data.
- What is order by clause in DMBS?
Answer: ORDER BY clause helps to sort the data in either ascending order to descending order.
Ascending order sort query
SELECT name,age FROM pcdsEmployee ORDER BY age ASC
Descending order sort query
SELECT name FROM pcdsEmployee ORDER BY age DESC
- What is an Extension of entity type?
Answer: The collections of entities of a particular entity type are grouped together into an entity set.
- What is entity?
Answer: Entity is a set of attributes in a database.
- What’s difference between DBMS and RDBMS?
Answer: DBMS provides a systematic and organized way of storing, managing and retrieving from
collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity. So in short we can say
RDBMS = DBMS + REFERENTIAL INTEGRITY
These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies claimed there DBMS product was a RDBMS compliant, but according to industry rules and regulations if the DBMS fulfills the twelve CODD rules it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc) fulfills all the twelve CODD rules and are considered as truly RDBMS.
- What are the disadvantage in File Processing System?
- Data redundancy and inconsistency.
- Difficult in accessing data.
- Data isolation.
- Data integrity.
- Concurrent access is not possible.
- Security Problems.
- What is functional Dependency?
Answer: Functional Dependency is the starting point of normalization. It exists when a relation between two attributes allows you to uniquely determine the corresponding attribute's value.
- Can you explain Insert, Update and Delete query in DBMS?
Answer: Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :-
INSERT INTO wbEmployee SET name='maxwell',age='22';
UPDATE wbEmployee SET age='22' where name='maxwell';
DELETE FROM wbEmployee WHERE name = 'david';
- What is an Entity set?
Answer: It is a collection of all entities of particular entity type in the database.
- What is E-R model?
Answer: E-R model is a short name for Entity Relationship model. This model is based on real world. It contains basic objects (known as entities) and relationship among these objects.
- How many types of relationship exist in database designing?
Answer: There are three major relationship models:-
Use Full Link for Training and Certification