DBMS Interview Questions: In this article, we’ll learn about the DBMS interview questions and how to answer these questions.
Also Check Out OOPs Interview Questions 2024 (Updated)
DBMS Interview Questions
1) What are the advantage of DBMS over traditional file-Based system ?
The DBMS were designed to handle the following difficulties :
I) Data redundancy
II) Inconsistency
III) Difficulty in accessing data
IV) Security problem
V) Concurrent Access
2) What are the different type of key ?
I) Super key : Super key is the key which can be uniquely identify any row of the database.
II) Candidate key : Candidate key is minimal super key i.e no proper subset of candidates key attributes is super key.
III) Primary key : Primary key is one of the candidate key. There can’t be more than one primary key in a table. It is used to uniquely identify the data in database table.
IV) Foreign key : Foreign key is a field in one table which uniquely identify a row of another table. It is useful in linking two tables together.
3) What are difference between primary key unique constraints ?
Primary key can’t have NULL values but the unique constraints can have NULL value. In a table there is only one primary key but multiple unique constraints.
4) What is database normalization ?
It is a process of analyzing the given relation based on their functional dependency and primary key to achieve following property :
I) Minimizing redundancy
II) Minimize insertion, deletion and updation anomalies.
III) It is helpful to divide the large database table into smaller table and link them using relationship.
5) What are the different types languages in SQL ? Explain.
I) DDL : DDL stands for Data definition language. SQL queries like CREATE, ALTER, DROP, TRUNCATE and RENAME comes under DDL. It all applied on table not on data.
II) DML : DML stands for Data manipulation language. SQL queries like SELECT, INSERT, DELETE and UPDATE comes under DML. It all applied on data not on table.
III) DCL : DCL stands for Data control Language. SQL query like GRANTS, REVOKES comes under this.
6) What are the difference between having and where clause in SQL ?
Where clause is used to filter the record from table but having clause is used to filter the record from groups based on the given condition.
7) How to print duplicate rows in table ?
Select NAME from PERSON
Group by NAME
Having count(NAME) > 1;
8) What is Join ?
An SQL join is used to combine data from two or more table, based ona common field between them.
9) What is Identity ?
It is also called as Auto Number. It is a column that automatically generates numeric value. A start and increment value can be set. Most DBA leaves these at 1.
10) what is view ? How to create a view ?
A view is an virtual table based on the result set of an SQL statement.
CREATE VIEW view_name AS
SELECT column_names
FROM table_name
WHERE conditions;
11) What is database schema ?
The overall design of the database is called the database schema.
12) What is purpose of SQL ?
SQL stands for structured query language whose main purpose is to interact with relational database in the form of inserting, deleting, and updating the data in the database.
13) What is the concept of sub-query in terms of SQL ?
Sub-query is basically the query that is included inside some other query and can also be called as inner query which is found inside the outer query. It is used in DML.
14) What are the difference between DROP, TRUNCATE and DELETE command ?
Drop is comes under DDL. It is used to drop/delete the table or view from the database.
Drop command is used to delete the table and all its privileges permanently. This operation can’t be rolled back so we should be used when it necessary.
Truncate is comes under DDL. Truncate command can only delete data stored in a table and the structure of table is preserved. It can be rolled back until the commit has been made.
Delete command on the hand is DML command it is used to delete the rows from the table and it can be rolled back. It is slower than truncate.
15) What is the difference between union and union all ?
Union and Union All both are used to join the data from two or more table but union removes duplicate rows and picks the rows which have distinct after joining the data but Union All doesn’t remove duplicate rows.
16) What is correlated subquery in DBMS ?
The subquery which is executed for each of the rows of the outer query then it is termed as correlated subquery.
17) What is entity, entity type and entity set in DBMS ?
Entity is an object, place or things which has its independent existence in the real world.
Entity type is a collection of entities that have the same attributes like Student table where each row contain same types of attributes like name, age, roll, email, etc.
Entity set is a collection of entities of the same type like a collection of employees in a firm.
18) What are the different level of abstraction in the DBMS ?
There are three 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 database.
Logical level: This is the next level of data abstraction which states the types of data and relationship among the data that is stored in the database.
View Level: This is the highest level of data abstraction show/states only a part of the database.
19) What integrity rule exist in DBMS ?
There are mainly two integrity rule exist in DBMS:
I) Entity integrity : This states a very important rule that the value of primary key can never be NULL value.
II) Referential Integrity : This rule is related to foreign key which states that either the value of foreign key is a NULL value or it should be the primary key of any other relation.
20) What is E-R model in the DBMS ?
E-R model is known as entity relationship model int DBMS which is based on the concept of Entities and the relationship that exist among these entities.
21) What is functional dependency in the DBMS?
This is basically a constrains that is useful in describing the relationship among the different attributes in a relation. Like x–>y here we can say that y is functionally dependent on x.
22) What is 1NF in the DBMS ?
1NF stands for first normal form. This is easiest form of the normalization process which states that the domain of an attributes should have only atomic values. The objective of this is to remove the duplicate columns that are present in the table.
23) What is 2NF in the DBMS ?
It stands for second normal form. Any table is said to be in 2NF if it is 1NF and no non-prime attributes of a table is partially dependent on the primary key.
24) What is 3NF in the DBMS ?
It stands for third normal form. Any table is said to be in 3NF if it is 2NF and there is no any transitive dependency present.
25) What is BCNF in the DBMS ?
BCNF stands for Boyce Codd Normal from which is stricter than 3NF. Any table is said to bebin BCNF if it is in 3Nf and each of the functional dependencies X–>Y that exists, X is the super key of a table.
26) How can you get the alternate records from the table in the SQL ?
SELECT emp_id FROM (SELECT rowno,emp_id FROM empl)
WHERE mod(rowno,2) = 1;
27) How is Pattern matching done in the SQL ?
With the help of LIKE operator.
28) What are the different types of joins in SQL?
I) Inner join : This type of join is used to fetch the data among the tables which are common in both tables.
II) 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 tables which is on the right side of the join.
III) 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 tables which is on the left side of the join.
IV) Full join : This returns all the row from all the tables on which the join condition has been put and the rows which do not match hold NULL values.
29) What is RDBMS ?
RDBMS stands for relational database management system which contains data in the from of the tables and the data is accessed on the basis of the common fields among the tables.
30) What are the different types of relationships in the DBMS ?
A relationship in DBMS depicts an association between the tables. Different types of relationships are :
I) One-to-One : This basically states that there should be a one-to-one relationship between the tables i.e there should be one record in both the tables.
II) One-to-Many : This states that there can be many relationships for one i.e primary table hold only one record which can have many, one or none record in the related table.
III) Many-to-Many : This states that when one or more items in one table can have a relationship to one or more items in another table.
31) What is a transaction ? What are ACID properties ?
A database transaction is a set of database operations that must be treated as a whole, which means either all operation that must be treated as a whole, which means either all operations are executed or none of them. An example can be a bank transaction from one account to another account. Either both debit and credit operation must be executed or none of them. ACID stands for Atomicity, Consistency, Isolation, Durability is a set of properties that guarantee that database transactions are processed reliably.
32) What is Denormalization ?
Denormalization is a database optimization technique in which we add redundant data to one or more table.
