Indexes Quiz - Languages SQL

Free practice quiz: Indexes (Languages SQL). 54 multiple-choice questions with answers and explanations. Play online, no sign-up needed.

Sample questions

1. Which of the following is the primary purpose of a database index?

   1. To enforce data integrity constraints
   2. To improve the speed of data retrieval operations
   3. To reduce the storage space required for the database
   4. To automatically back up the database
   Show answer

   Answer: B. To improve the speed of data retrieval operations

   Database indexes are data structures that improve the speed of data retrieval operations on a database table. They work by providing a quick lookup mechanism, similar to an index in a book.

2. Which index type is most suitable for columns with a high degree of selectivity and frequently used in range-based queries (e.g., `>` , `<` , `BETWEEN`)?

   1. Hash Index
   2. B-tree Index
   3. Bitmap Index
   4. Full-text Index
   Show answer

   Answer: B. B-tree Index

   B-tree indexes are optimized for both equality and range searches, making them ideal for columns frequently used in conditions involving comparisons like greater than, less than, or between.

3. Consider a query: `SELECT * FROM products WHERE price BETWEEN 10 AND 50 ORDER BY product_name;`. Which type of index would be most beneficial if `price` has high cardinality?

   1. Hash Index
   2. Full-text Index
   3. B-tree Index
   4. Bitmap Index
   Show answer

   Answer: C. B-tree Index

   B-tree indexes are well-suited for range queries (like BETWEEN) and sorting. Since 'price' is likely to have high cardinality, a B-tree index will be efficient for locating the rows within the specified price range and potentially for ordering.

4. Consider the query: `SELECT COUNT(*) FROM orders WHERE order_date >= '2023-01-01';`. If `order_date` is indexed with a B-tree, what is the likely execution plan?

   1. Full table scan
   2. Index seek followed by a table scan for remaining rows
   3. Index scan of the entire index
   4. Index seek to the first matching row, then index scan for subsequent rows
   Show answer

   Answer: D. Index seek to the first matching row, then index scan for subsequent rows

   A B-tree index on `order_date` allows the database to seek directly to the first entry matching `order_date >= '2023-01-01'`. It can then efficiently scan the index sequentially from that point onwards to find all subsequent entries, which is usually faster than scanning the entire table.

5. What is the potential issue with creating an index on a column with extremely high cardinality, such as a unique identifier for every row?

   1. The index will be too small to be effective.
   2. The index might not provide significant benefits over a table scan if the index is not covering and requires bookmark lookups for most queries.
   3. The database will automatically disallow such an index.
   4. It will always lead to slower query performance.
   Show answer

   Answer: B. The index might not provide significant benefits over a table scan if the index is not covering and requires bookmark lookups for most queries.

   While indexing a high-cardinality column is often beneficial, if the index is not covering, and the query needs more columns than are in the index, the database must perform an additional step to fetch the remaining data from the table (a 'bookmark lookup' or 'key lookup'). For very wide tables or queries selecting many columns, this can negate some of the index's benefits compared to a full table scan if the table is small enough or the index is sufficiently wide.

More quizzes

• Joins
• Stored Procedures
• DDL
• DML

Browse all SQL quizzes