Database Interview Questions and Answers
Question - 91 : - Compare Non-clustered and clustered index
Answer - 91 : -
Both having B-tree structure, non-clustered index has data pointers enabling one table many non-clustered indexes while clustered index is distinct for every table.
Question - 92 : - Define Aggregate functions.
Answer - 92 : -
Functions which operate against a collection of values and returning single value is called aggregate functions
Question - 93 : - Define Data Warehousing.
Answer - 93 : -
Storage and access of data from the central location in order to take some strategic decision is called Data Warehousing. Enterprise management is used for managing the information whose framework is known as Data Warehousing.
Question - 94 : - What do you mean by Index hunting?
Answer - 94 : -
Indexes help in improving the speed as well as the query performance of database. The procedure of boosting the collection of indexes is named as Index hunting.
Question - 95 : - How does Index hunting help in improving query performance?
Answer - 95 : -
Index hunting helps in improving the speed as well as the query performance of database. The followed measures are achieved to do that:
- The query optimizer is used to coordinate the study of queries with the workload and the best use of queries suggested based on this.
- Index, query distribution along with their performance is observed to check the effect.
- Tuning databases to a small collection of problem queries is also recommended.
Question - 96 : - Define B-trees
Answer - 96 : -
A data structure in the form of tree which stores sorted data and searches, insertions, sequential access and deletions are allowed in logarithmic time.
Question - 97 : - Differentiate Table Scan from Index Scan.
Answer - 97 : -
Iterating over all the table rows is called Table Scan while iterating over all the index items is defined as Index Scan.
Question - 98 : - Explain the importance of partitioning.
Answer - 98 : -
Splitting of one table which is large into smaller database entities logically is called database partitioning. Its benefits are:
- To improve query performance in situations dramatically when mostly rows which are heavily accessed are in one partition.
- Accessing large parts of a single partition
- Slower and cheaper storage media can be used for data which is seldom used.
Question - 99 : - Define Database system.
Answer - 99 : -
DBMS along with database is called Database system.
Question - 100 : - Define Atomicity and Aggregation.
Answer - 100 : -
Atomicity: It’s an all or none concept which enables the user to be assured of incomplete transactions to be taken care of. The actions involving incomplete transactions are left undone in DBMS.
Aggregation: The collected entities and their relationship are aggregated in this model. It is mainly used in expressing relationships within relationships.