Last Updated on October 3, 2022 by Ria Pathak
DBMS stands for Database management System. DBMS is a software which is used to manage databases. It is used to insert, delete, update and retrieve data from the database by running some queries. It acts as an interface between the user and the database. Examples: MySQL, Oracle, SQL server, PostgreSQL and Microsoft Access.
DBMS is one of the must have skills for software engineers as proper analysis of data and generating insightful business insights using DBMS can help in growth of the business. So, a lot of companies ask questions related to DBMS in SDE interviews. Studying frequently asked DBMS interview questions before your interview can increase your chances of clearing the interview.
DBMS Interview Questions
1. What is a Database?
Answer: A database is an organized and systematic collection of data, which is stored electronically on a computer. The database helps in managing large amounts of data efficiently. A software called DBMS can be used to extract data from the database by running some queries. A database consists of sets of tables or objects which contain records and fields. A row in the database represents a single entry and columns define the attributes which define a particular aspect or property of the table.
2. Explain different languages present in DBMS?
Answer: Different languages present in DBMS are:
-
DDL(Data Definition Language): DDL commands operate on the structure of the database and not on the data. This commands are required to define the database.I
- CREATE: This command is used to create a new database, tables, procedure, etc.
- ALTER: This command is used to alter the structure of the database.
- DROP: This command is used to delete records or tables from the database
- TRUNCATE: This command is used to delete all the records from a table.
- RENAME: This command is used to change the name of the database.
-
DML(Data Manipulation Language): DML commands are used to manipulate the data in the database.
- SELECT: This command is used to retrieve data from the database.
- UPDATE: This command is used to update existing data in the database.
- INSERT: This command is used to insert new data in the database.
- DELETE: This command is used to delete data from the database.
-
DCL(Data Control Language): DCL commands are used to control permissions for the database system access.
- GRANT: This command is used to provide access to the database to an user.
- REVOKE: This command is used to revoke access to the database from an user.
-
TCL(Transaction Control Language): TCL commands are used to handle transactions in the database.
- COMMIT: This command is used to commit a transaction.
- ROLLBACK: This command is used to rollback a transaction in case of a failure.
- SAVEPOINT: This command is used to set a savepoint within a transaction.
3. What are the different levels of data abstraction in the database?
Answer: Data abstraction is the process of hiding complex and unwanted information from the user and showing only the essential information. Data abstraction makes the system simple and more secure.
Levels of data abstraction are:
-
Physical or Internal Level
It is the lowest level of abstraction for DBMS . It defines the structure of the database schema. It defines how the data is actually stored, it defines data-structures to store data and access methods used by the database. It is decided by developers or database application programmers how to store the data in the database. -
Logical or Conceptual Level
Logical level is the intermediate level or next higher level. It describes what data is stored in the database and also defines the relationship among the data. It tries to define entire data in the database. It also describes whether new tables need to be created and if new tables are created what be the links among those tables that are created. -
View or External Level
It is the highest level. It simplifies the view of the database by providing multiple views of the database. It also increases interaction between the user and the database. In view level, there are different levels of views and every view only defines a part of the entire data. View level can be used by all users (all levels’ users).
4. What is a join?
Answer: Join is the process of joining two or more tables, based on some common field between them.
Example:
Table: Student
| Roll_no | Student_name | Address |
|---|---|---|
| 1001 | John | Norway |
| 1002 | Sam | San Jose |
| 1003 | Marco | California |
| 1004 | Riza | New York |
Table: Course
| Course_ID | Roll_no |
|---|---|
| 1 | 1001 |
| 2 | 1002 |
| 4 | 1004 |
| 3 | 1003 |
Query:
SELECT Course.Course_ID, Student.Student_name
FROM Course
INNER JOIN Student
ON Course.Roll_no = Student.Roll_no
ORDER BY tCourse.Course_ID;
The above query will produce the following table as the result:
| Course_ID | Student_name |
|---|---|
| 1 | John |
| 2 | Sam |
| 3 | Riza |
| 4 | Marco |
5. What is the difference between left outer join and right outer join?
Answer:
Left outer join: If we perform left outer join or simply left join, on two tables then the resulting table will contain the overlapping data of both the tables and the non-overlapping data of the FIRST table.
Right outer join: If we perform right outer join or simply right join, on two tables then the resulting table will contain the overlapping data of both the tables and the non-overlapping data of the SECOND table.
6. What is the difference between DELETE and TRUNCATE command?
Answer:
DELETE command: DELETE command is a data manipulation command which is used to delete the rows from the table based on the condition passed in the WHERE clause. This command maintains a log for each row it deletes and uses row lock while performing the DELETE function. The DELETE command can be rolled back.
TRUNCATE command: TRUNCATE command is a data definition command which is used to delete all rows from a table by deallocating the data pages used to store the table data. It is faster than the DELETE command because it records only the page deallocations in the transaction log. Unlike delete command TRUNCATE command cannot be rolled back.
7. What are relationships and mention different types of relationships in the DBMS
Answer: Relationship in DBMS refers to the scenario when two entities are related to each other. Types of relationships in DBMS are:
-
One-to-one relationship: When one row in database A is related to only one row in database B and vice versa.
-
Many-to-one relationship: When many rows in database A are related to only one row in database B.
-
Many-to-Many relationship: When multiple rows in database A are related to multiple rows in database B.
8. What do you mean by entity in DBMS?
Answer: An entity can be any real world object or thing that has an independent existence and can be uniquely identified.
Entity can be of two types:
-
Tangible entity: An entity which has a physical existence is called a tangible entity. Ex: Laptop, bike, car, person, etc.
-
Intangible entity: An entity which does not have a physical existence and only exists logically is called an intangible entity. Example: Prepbytes courses, bank account, etc.
9. How do you communicate with an RDBMS?
You have to use Structured Query Language (SQL) to communicate with the RDBMS. Using queries of SQL, we can give the input to the database and then after processing of the queries database will provide us the required output.
10. What is BCNF?
BCMF stands for Boyce-Codd Normal Form. It is an advanced version of 3NF, so it is also referred to as 3.5NF. BCNF is stricter than 3NF.
A table complies with BCNF if it satisfies the following conditions:
- It is in 3NF.
- For every functional dependency X->Y, X should be the super key of the table. It merely means that X cannot be a non-prime attribute if Y is a prime attribute.
Also you can prefer this DBMS Interview Questions article for better understanding, go and check it out!
We tried to discuss DBMS Interview Questions in this article. We hope this article gives you a better understanding of the type of DBMS Interview Questions.
Prepbytes also provides a good collection of Foundation Courses that can help you enhance your coding skills. Want to make sure you ace the interview in one go? Join our Placement Program that will help you get prepared and land your dream job at MNCs. Mentors of Prepbytes are highly experienced and can provide you with basic, in-depth subject knowledge for better understanding.