Intermediate Database Indexing and Query Optimization
Assess your understanding of database indexing and query optimization techniques.
Questions in this quiz
12 questions on Intermediate Database Indexing and Query Optimization. Work through them here, then start the quiz to answer them and see your score.
1.What is the primary purpose of creating an index on a database column?
- ATo increase storage space
- BTo improve query performance
- CTo facilitate data encryption
- DTo ensure data integrity
2.Which type of index is best suited for a column with a high number of unique values?
- AHash index
- BB-tree index
- CBitmap index
- DFull-text index
3.What is a covering index?
- AAn index that is automatically created by the database
- BAn index that includes all columns of a table
- CAn index that includes the primary key column
- DAn index that contains all the columns needed to satisfy a query
4.How does a clustered index differ from a non-clustered index?
- AA non-clustered index sorts the data rows, while a clustered index does not
- BClustered indexes are faster to create than non-clustered indexes
- CA clustered index sorts the data rows, while a non-clustered index does not
- DNon-clustered indexes can only be created on primary keys
5.Which SQL clause can benefit most from indexing?
- AORDER BY
- BWHERE
- CGROUP BY
- DSELECT
6.What is the effect of adding too many indexes to a table?
- ADecreases read performance
- BIncreases storage requirements
- CReduces data redundancy
- DImproves write performance
7.What is an execution plan in the context of query optimization?
- AA detailed breakdown of how a query will be executed
- BA list of all indexes in a database
- CA plan to backup the database
- DA summary of all queries executed in a database
8.Which of the following is a common method to optimize query performance?
- AIncreasing the size of the database
- BUsing SELECT * in queries
- CAvoiding the use of indexes
- DDenormalizing data when appropriate
9.What is the impact of using a composite index?
- AIt reduces the number of indexes needed
- BIt can optimize queries that filter on multiple columns
- CIt is only useful for primary keys
- DIt increases the query execution time
10.Which of the following scenarios would benefit most from a full-text index?
- AFiltering rows based on boolean values
- BSearching for exact matches in a numeric column
- CSearching large text fields for keywords
- DPerforming range queries on date fields
11.What does the term 'index selectivity' refer to?
- AThe size of the index
- BThe number of indexes on a table
- CThe uniqueness of the indexed values
- DThe speed of index creation
12.In query optimization, what is a 'cost-based optimizer'?
- AAn optimizer that minimizes the cost of database maintenance
- BAn optimizer that evaluates the cost of different query execution plans
- CAn optimizer that uses the least expensive hardware
- DAn optimizer that reduces the cost of data storage