A database is a “structured set of data held in a computer, especially one that is accessible in various ways.”
Databases have evolved over the years and database management systems are now widely used by organizations as it increases organizational accessibility to data and helps them to share the data quickly and effectively. It is an important activity for any company that wants to secure its data or process it to get valuable information out of it.
There are a lot of opportunities in various industries for database professionals who are willing to learn new things and are capable of analyzing, managing, and securing data.
As per the U.S. Bureau of Labor Statistics, the “employment of database administrators is projected to grow 10 percent from 2019 to 2029.”
If you are planning to prepare for your upcoming Database interview, then this article will boost your interview preparation by providing the top 35 Database interview questions which an interviewer could ask you during your next interview.
Top Database Interview Questions & Answers
Following are some of the commonly-asked Database interview questions to help you crack your next Database job interview:
Q1. What is DBMS?
Ans. Database Management System (DBMS) is a collection of programs that enables users to store, retrieve, update, and delete information from a database.
Q2. What is RDBMS?
Ans. Relational Database Management system (RDBMS) is a type of DBMS that is based on the relational model. One can access or reassemble the data from the relational databases in many different ways without having to reorganize the database tables.
Q3. What is a ‘record’ in a database?
Ans. A ‘record’ is the collection of values/fields of a specific entity.
Q4. What is a ‘field’ in a database?
Ans. A ‘field’ is an area within a record reserved for a specific piece of data.
Q5. What are database languages? What are the types?
Ans. Database languages are used to write or create a database management system. There are three types: data definition language, data manipulation language, and query language.
Q6. Name the various relationships of the database. Describe briefly.
Ans. The various relationships of the database are:
- One-to-one: Single table having drawn a relationship with another table having similar kinds of columns.
- One-to-many: Two tables having primary and foreign key relations.
- Many-to-many: Junction table having many tables related to many tables.
Q7. What is ‘normalization’?
Ans. Organized data void of inconsistent dependency and redundancy within a database is called ‘normalization.
Q8. What are the different types of normalization?
Ans. The different types of normalization are – First Formal Form (1NF), Second Normal Form (2NF), and Third Normal Form (2NF).
Q9. What is the ‘primary key’?
Ans. A ‘primary key’ is a column whose values uniquely identify every row in a table. Primary key values can never be reused.
Q10. What is ‘denormalization’?
Ans. Boosting up database performance, adding redundant data which in turn helps rid of complex data is called ‘denormalization’.
Q11. What are the conditions to be met for a field to be defined as a primary key?
Ans. The conditions are:
- No two rows can have the same primary key value.
- Every row must have a primary key value.
- The primary key field cannot be null.
- Value in a primary key column can never be modified or updated if any foreign key refers to that primary key.
Q12. What is a ‘composite key’?
Ans. A ‘composite key’ is a combination of two or more columns in a table that can be used to uniquely identify each row in the table.
Also Read>> Top Online Courses for IT Professionals
Q13. What is a ‘foreign key’?
Ans. A ‘foreign key’ is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.
Q14. What is a ‘unique key’?
Ans. The unique key is the same as primary with the difference being the existence of null. The unique key field allows one value as a NULL value.
Q15. What is a ‘cursor’?
Ans. A database object which helps in manipulating data row by row representing a result set is called a cursor.
Q16. What are the different types of cursors? Define
Ans. The different types of cursors are:
- Dynamic: it reflects changes while scrolling.
- Static: doesn’t reflect changes while scrolling and works on the recording of a snapshot.
- Keyset: data modification without reflection of new data is seen.
Q17. What is ‘sub-query’?
Ans. A query contained by a query is called sub-query.
Q18. What is a ‘view’?
Ans. The views are virtual tables. Unlike Tables that contain data, views simply contain queries that dynamically retrieve data when used.
Q19. What is a materialized view?
Ans. Materialized views are also a view but are disk-based. Materialized views get updates on specific duration, based upon the interval specified in the query definition. It can be indexed.
Q20. Define ‘join’.
Ans. Joins help in explaining the relation between different tables.
These are some of the popular questions asked in a Database interview. Always be prepared to answer all types of questions — technical skills, interpersonal, leadership, or methodology. If you are someone who has recently started your career in database management, you can enroll in a database certification course to get the techniques and skills required to be an expert in the field.
Q21. What are the advantages of DBMS?
Ans. The advantages of DBMS are:
- Restriction on unauthorized access
- Redundancy control as data is stored in a structured way
- Multiple user interfaces
- Backup and recovery for data
- Data consistency
- Easy accessibility
- Easy data extraction and data processing
Q22. Explain checkpoint?
Ans. A Checkpoint is a mechanism in which all the previous logs are eliminated from the system and permanently stored in the storage disk. It declares a point before which the DBMS was in a consistent state.
On reaching the checkpoint, the log file is destroyed and its update is saved in the database. A new log will then be created with the upcoming execution operations of the transaction. It keeps updating until the next checkpoint. Such checkpoints are traced during transaction execution and transaction log files are created after execution.
Q23. What are the disadvantages of file processing systems?
Ans. The disadvantages of file processing systems are:
- Data redundancy and inconsistency
- Data accessing is not easy – different programs are needed to access data differently
- Concurrent access anomalies
- Data could be stored in different formats, so it could be challenging to share
- Issues with concurrent access
- Use of many copies of the same data
- Data isolation
- Atomicity problems
Q24. Explain DBMS data abstraction. What are its different levels?
Ans. Data abstraction is the process of hiding irrelevant information/details from users to ease user interaction with the database. The three levels of Data abstraction are:
- Physical Level: It is the lowest level of abstraction. It specifies how data is stored in memory. Data can be accessed through sequential or random access.
- Conceptual/Logical Level: This is the second-highest level of abstraction. It stores information in the form of tables. It specifies what data is stored and what is the relationship among those data.
- View Level: It is the highest level of data abstraction. In this level, users can only view a part of the actual database, in the form of rows and columns. It hides the details about storage and implementation from the users. You can also have multiple views of the same database.
Q25. What is Functional Dependency in DBMS?
Ans. Functional dependency refers to the relationship between two attributes, usually the non-key attributes and the primary key of a table. There are two types of functional dependency:
- Trivial – A → B has trivial functional dependency if B is a subset of A.
- Non-trivial – A → B has a non-trivial functional dependency if B is not a subset of A.
Q26. What is the difference between extension and intension?
Ans. The intention of a database is the constant value. It is the description of the database or the schema, which will not change frequently and is specified during the database design phase. On the other hand, the extension or database snapshot of the database is the set of database values that populate the data structures. It refers to the data at a particular time. It may change frequently.
Q27. What is a Data Model?
Ans. Data models specify how the logical structure of a database is modeled. They offer a collection of conceptual tools for describing data, data relationships, data semantics, and constraints. Data models describe the relationship between the entities and their attributes. Some of the data models are:
- Hierarchical Data Model
- Network Model
- Entity-Relationship Model
- Relational Model
- Semi-Structured Data Model
- Object-Relational Data Model
- Object-Oriented Data Model
Q28. Explain correlated subquery.
Ans. Also known as a synchronized subquery, a correlated subquery is a query nested inside another query that relies on the outer query to return a value. Since it is correlated with a column of the outer query, it must be re-executed for each row of the result. Thus, during execution, the subquery is executed first and then the correlated query.
Also Read>> 7 Trending Tech Skills to Master in 2020
Q29. Explain the Database transaction.
Ans. A database transaction (DB transaction) is a unit of work performed within a database management system – either completed as a unit or undone as a unit. Once the transaction is complete, either the successful completion will reflect in the system or the transaction fails and no change will be reflected. Database transaction processing helps in maintaining the integrity of the database.
Q30. What is query optimization? What are its benefits?
Ans. Query optimization refers to the execution plan for evaluating a query efficiently and at the least estimated cost. Since a single query can be executed through different methods and algorithms, query optimization helps by specifying the most efficient way to execute a given query by considering all the possible query plans.
The benefits of query optimization are:
- Reduces the system resources
- Reduces the time and space complexity
- Provide the correct output faster
Q31. Explain Relational Algebra.
Ans. Relational Algebra is a procedural query language. It has a set of operations for the relational data model. Using these operations, you can specify basic retrieval requests or queries. Relational Algebra takes instances of one or two relations as input and provides a new relation as output. It uses operators to perform queries and is carried out recursively on the relation. The intermediate results are also considered relations. This feature makes the algebra ‘closed’. Some of the fundamental operations of relational algebra are:
- set difference
Q32. Explain Relational Calculus?
Ans. Relational Calculus is a non-procedural query language. Instead of Algebra, it uses mathematical predicate calculus. It is not the same as differential and integral calculus in mathematics. It is rather based on a branch of mathematical logic known as ‘predicate calculus’.
The two types of relational calculus are:
- Tuple relational calculus
- Domain relational calculus
Q33. Explain Database Index and Index hunting.
Ans. A database index is a data structure that improves the speed of data retrieval operations on a database.
The procedure of boosting the collection of indexes is known as Index hunting. It improves the speed and query performance of the database. It can be done by various methods, such as query optimization and query distribution.
Q34. What is the difference between a DELETE command and TRUNCATE command?
Ans. The differences between DELETE and TRUNCATE command are:
|DELETE Command||TRUNCATE Command|
|Data Manipulation Language (DML) type.||Data Definition Language (DDL) type.|
|It deletes only those rows which have the WHERE clause.||It removes all the rows from the table.|
|This can be rolled back.||This cannot be rolled back.|
|The speed of execution is slow as it maintains a log.||It is faster as it does not maintain a log.|
|Delete uses a row lock to perform the function.||Truncate uses a table lock to perform the function.|
Q35. What are the ACID properties?
Ans. ACID stands for Atomicity Consistency Isolation Durability. ACID properties are the rules that need to be fulfilled by every transaction to maintain integrity. The ACID properties are:
- Atomicity: It is an indivisible series of database operations that implies that either all transactions take place and run to completion in one go or no execution occurs at all.
- Consistency: This property specifies the uniformity of data. It means that the database must be consistent before and after the transaction.
- Isolation: It means that multiple transactions can be executed simultaneously without interfering with each other.
- Durability: It means that a successful transaction will be stored in the non-volatile memory and will not be affected by system failure.
In case you have recently completed a professional course/certification, then