DBMS Interview Questions: Part 1

DBMS Interview Questions | Part 1

Introduction

Database Management Systems is the most important subject of Computer Science. In this blog, we are covering the most asked DBMS Interview Questions.

So here, we will be converting the most important DBMS Interview Questions related to this subject so that you can ace your interview after going through this blog.

DBMS Interview Questions

Ques 1. What is DBMS used for?

Ans 1. The database Management system is application software that deals with data. With the help of a database management system, a user can manage and perform operations like updation, deletion, and insertion quite easily.

Ques 2. What does the term Database mean?

Ans 2. Database refers to the storage of data in some specific manner to interact with data quickly.

Ques 3. What are the advantages of having a database management system?

Ans 3. Some significant advantages of database management system include:

Minimizes Redundancy: DBMS helps in removing redundancy through one of its features called Normalization. We’ll discuss more about normalization in the coming questions.

Makes sharing of Data hassle-free: All the users can access that same data, and all the applications can use the same data by just accessing the same database.

Backup and Recovery Facility: The database removes the pain of taking backup of the data repeatedly. It takes the backup of data and recovers all by itself.

Ques 4. What is Normalization?

Ans 4. Normalization is the technique that removes redundancy or duplicates from a database by decomposing the table into smaller tables.

Ques 5. What are the two primary purposes of Normalization?

Ans 5. Two primary purposes are as follows: 

  • To minimize redundancy in data.
  • To minimize updation, insertion, and deletion anomalies.

Ques 6. How many languages are there with the help of those we can interact with the database?

Ans 6. There are three types of languages present to interact with the database:

DDL or Data Definition Language

DCL or Data Control Language

DML or Data Manipulation language

Ques 7. What is the main purpose of DDL in a database?

Ans 7. DDL or Data definition language is used to define the database schema structure by using some set of SQL queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.

Ques 8. What is the main purpose of DCL in a database?

Ans 8. DCL or Data Control Language is used to control the access to the database by using SQL queries like GRANT and REVOKE.

Ques 9. What is the use of DML in a database?

Ans 9. DML or Data Manipulation Language is used to do manipulations on the data like insertion and deletion in the database using SQL queries like SELECT, INSERT, UPDATE and DELETE.

Ques 10. What is the Primary Key in the database?

Ans 10. Primary Key is an attribute or the collection of attributes. The primary key is used to identify a tuple in the table uniquely. Always remember that the primary key can never contain NULL values. Each table can have only one primary key. Suppose you have a Student table, then the student roll no. can be used as a primary key of the table.

Ques 11. What is the use of the DROP command?

Ans 11. DROP is a DDL or Data definition language, and it is used to delete the table from the database. This operation can not be rolled back, so checking twice before executing this command is necessary.

Ques 12. Explain Entity and Entity Set in a database?

Ans 12. Any object that is having an independent existence in this world is called an entity. Like this blog is an entity, your books are an entity.

An entity set is the collection of entities of similar types of entities. As the collection of employees in a firm is an entity set.

Ques 13. What are the different levels of abstraction in a database?

Ans 13. The database includes three levels of data abstraction:-

Physical Level: Physical level is the lowest level of abstraction in a database, and it is used to give you an idea about how data is stored in a database.

Logical Level: Logical level abstraction is essential because it gives the idea about the type of data stored in the table and displays the relationship among the data stored in the table.

View Level: The highest level of abstraction is at a view level, and it only shows the part of the database.

Ques 14. What is the first normal form?

Ans 14. 1NF or first normal form states that the domain of an attribute in a table should only contain atomic values. Let’s understand this with the help of the below example:

Student IdStudent NameCourses
001NinjaOSDBMS
002ShinchanCOAMathematicsPhysics

The above table is not in first normal form because the course attribute is not containing atomic values. This table will be in first normal form only when we put atomic values in the course attribute. Let’s convert the above table to 1NF form

Student IdStudent NameCourses
001NinjaOS
001NinjaDBMS
002ShinchanCOA
002ShinchanMathematics
002ShinchanPhysics

Ques 15. What is the Second Normal form?

Ans 15. A table is said to be in second normal form or 2NF only when it follows below two properties:

  • The Table must be in the first normal form or 1NF.
  • The Table should not have a partial dependency.

Ques 16. What is the third normal form or 3NF?

Ans 16. A table is said to be in third normal form only when it satisfies below conditions:

  • The Table must be in Second Normal form or 2NF.
  • The Table should not contain transitive dependency.

Ques 17. What is Boyce Codd Normal Form?

Ans 17. A table will be said to be in BCNF only when it’ll be following below two conditions:

  • The table must be in the Third normal form 3NF.
  • For every functional dependency, A->B A must be a super key.

Ques 18. How is the pattern matching done in SQL?

Ans 18. Pattern matching is done in SQL using the LIKE operator.’%’ operator is used when we want to match 0 or more characters, and ‘_’ is used when you want to match only one particular character. For example: 

SELECT * from Student  WHERE name like ‘b%’;

Ques 19. What is a Join in SQL?

Ans 19. Join is a statement in SQL which is used to join data or rows from more than one table on the basis of common attributes among them.

Ques 20. What are the different types of relationships in the database?

Ans 20. The Database contains majorly three types of relationships:

  • One -to-One
  • One-to-Many
  • Many-to-Many

Ques 21. What is the TRUNCATE command in a database?

Ans 21. TRUNCATE command is used to remove complete data from a table in a database. It can be rolled back in some databases depending upon the version of the database.

Ques 22. What is a candidate key in a database?

Ans 22. A minimal or efficient super key is known as a candidate key in the database. A candidate key is used for identifying a tuple uniquely, and a table can have more than one candidate key.

Ques 23. State integrity rules in the database.

Ans 23. There are mainly two integrity rules in the database:

  • Entity Integrity: It states that a primary key of a table can not contain NULLValues.
  • Referential Integrity: It states that a foreign key can either contain a NULL value or the primary key value of another table.

Ques 24. What is a 2-tier architecture? 

Ans 24. The client-server model is known as 2-tier architecture. In the 2-tier architecture, a client can directly access a database at the server-side.

Ques 25. What is a Foreign Key ?

 Ans 25 A Foreign key is the primary key of one table that has a relationship with another table. It is used as a reference between tables.

Ques 26. Is the NULL Value the same as a zero or blank space?

Ans 26. No NULL value is quite different from zero or blank space.NULL means that the value is unavailable but the zero is a number and blank space signifies a character.

Ques 27. What do you understand by the term atomicity? 

Ans 27 Atomicity dictates that whenever you are doing an operation on data in a database, then this modification either must follow all the rules or it should not follow any of the rules. For example If one part of a transaction fails, then the entire transaction must fail during a transaction.

Ques 28. What are indices in the database? 

Ans 28. Indexes are the data structures that are used for quick access of data from the database. Indexing is of two types: 

Clustered Index: It sorts the row with the help of a column that is set for clustered indexing. A table can contain only one clustered index.

Non-Clustered Index: It does not alter the way the rows are stored in a table.A table can have more than one non-clustered index.

Ques 29. State the difference between UNION and UNION ALL.

Ans 29. The differences are as follows: 

UNIONUNION ALL
It combines the result of those two or more SELECT statements which are containing distinct values.It combines the result of those two or more SELECT statements which are containing duplicate values.
Its syntax is UNIONIts syntax is UNION ALL
Duplicate rows need to be removed here so it has low performance than UNION ALLAs duplicate rows need not have to be removed here so it has better performance than UNION.

Ques 30. Explain the differences between HAVING and WHERE clauses? 

Ans 30. The differences are depicted in the table below: 

WHEREHAVING
It can be applied to each row before they are a part of the GROUP BY function in a query.It is used with the GROUP BY function in a query.
Used in a GROUP BY clauseUsed only with SELECT statement

Key Takeaways

This article discussed the most asked DBMS Interview questions for freshers and experienced developers.
The article covered the most heated topics in DBMS.

By: Deeksha Sharma

Exit mobile version