Top DBMS Interview Questions For Freshers
Here are the most commonly asked interview questions and answers on DBMS in any technical interview round.
Short answers to know the concept and remind it.
1. What is DBMS ?
This is a system that allows its user to store the data, define it, retrieve it and update the data inside the database.
2. What is Database ?
Database is a collection of data in some organized way by which users can easily access, manage and upload the data.
3. What is the normalization in DBMS ?
It is the process of analyzing the relational schemas which are based on their respective functional dependencies and the primary keys in order to fulfill following properties.
- To minimize the redundancy of the data.
- To minimize the Insert, Delete and Update Anomalies.
4. What is use of Database/What are advantages of DBMS ?
Controlled Redundancy: Control the redundancy of data inside the database by combining all the data into a single database and data is stored at only one place so duplicity of data does not occur.
Data Sharing: Sharing of data among multiple users simultaneously by different application programs.
Backup and Recovery: Automatically creates the data backup and restores the data whenever required.
Enforcement of Integrity Constraints: Refined data after putting some constraints are stored in the database.
Independence of data: We can change the structure of the data without changing the structure of any other application programs.
4. What are different languages in DBMS ?
1. DDL: Data Definition Language is used to define the database and schema using SQL Queries like CREATE, ALTER, TRUNCATE, DROP and RENAME.
2. DCL: Data Control Language is used to control the access of the users inside the database by using SQL Queries like GRANT and REVOKE.
3. DML: Data Manipulation Language is used to do some manipulations in the database like SELECT, INSERT, DELETE and UPDATE.
4. TCL: Transaction Control Language is used for managing transactions in the database like COMMIT, ROLLBACK and SAVEPOINT.
5. What is SQL ?
Structured Query Language which is interact with the relational databases in the form of inserting and updating the data in the database.
6. What is Primary Key ?
It is used to uniquely identifies the records in the database table.
7. What is Foreign Key ?
Foreign key is the primary key of other table and used to join two or more tables together.
Ex: Teacher and Subject Both tables have one common column as ‘ID’ where ID is the primary key of the Teacher table while this is the foreign key for the Subject table.
8. What is main difference between Primary Key and Unique Key ?
1. Primary key can never have a null value while the Unique key may consist of null value.
2. In every table, there can be only one primary key while there can be more than one unique key in a table.
9. What is Nested query in DBMS ?
The query which is written inside some other query.
10. What are the different levels of abstraction in the DBMS ?
Physical Level: This is the lowest level which states how the data is stored in the database.
Logical Level: This is the next level 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 which shows only a part of the database.
11. What are the Integrity Rules in the DBMS ?
1. Entity Integrity: This states that value of a Primary key can never have a NULL value.
2. Referential Integrity: This states that either the value of a Foreign key is a NULL value or it should be the primary key of any other relation.
12. What is E-R model in the DBMS ?
E-R model is an Entity-Relationship model which is based on the Entities and the relationship among these entities.
13. What is functional dependency in the DBMS ?
This is useful in describing the relationship among the different attributes in a relation.
Example: If there is relation ‘R1’ which has 2 attributes as X and Y then the functional dependency among these 2 attributes can be shown as X->Y which states that Y is functionally dependent on X.
14. Explain acid properties in DBMS ? (IMP)
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” 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: 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: 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.
15. What is the main difference between UNION and UNION ALL ?
Both are used to join the data from 2 or more tables but UNION removes duplicate rows and picks the rows which are distinct whereas UNION ALL does not remove the duplicate rows, it just picks all the data from the tables.
16. What is the main difference between DROP, TRUNCATE and DELETE ?
DROP and TRUNCATE — DDL commands which are used to delete tables from the database and once the table gets deleted, it cannot be rolled back.
DELETE — DML Command which is used to delete rows from the table and this can be rolled back.
17. What is 1NF in the DBMS?
First Normal Form.
This is the easiest form of the normalization which states that the domain of an attribute should have only atomic values. It is to remove the duplicate columns in the table.
18. What is 2NF in the DBMS?
Second Normal Form.
Any table is said to be in 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 on the primary key.
19. What is 3NF in the DBMS?
Third Normal Form.
Any table is said to be in 3NF if it satisfies the following 2 conditions:
- A table is in the 2NF.
- Each non-prime attribute of a table is said to be non-transitively dependent on every key of the table.
20. What is BCNF in the DBMS?
Boyce Codd Normal Form
Any table is said to be in the BCNF if it satisfies the following 2 conditions:
- A table is in the 3NF.
- For each of the functional dependency X->Y that exists, X is the super key of a table.
21. How pattern matching done in the SQL ?
With the help of the LIKE operator, pattern matching is possible in the SQL.’%’ is used with the LIKE operator when it matches with the 0 or more characters and ‘_’ is used to match the one particular character.
Example:
SELECT * from Stud WHERE name like ‘r%’;
22. How you can get alternate records in the table ?
If you want odd numbers then :
SELECT StudId from (SELECT rowno,StudId from Stud) WHERE mod(rowno,2)=1;
If you want even numbers then :
SELECT StudId from (SELECT rowno,StudId from Stud) WHERE mod(rowno,2)=0;
23. What is join and what are different types of joins ?
A Join is used to join the data or the rows from 2 or more tables on the basis of a common field/column among them.
There are 4 types of SQL Joins:
- Inner Join: 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.
24. What is trigger ?
Which get executed automatically in response to the events that occur in a table or a view. For Example, If a new record is inserted in an student database then the data gets created automatically in the related tables like roll no, department and subject tables.
