Indexes Quiz - Languages SQL

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

This Languages SQL practice quiz has 54 multiple-choice questions on Indexes, and 54 of them come with an explanation. Try the 5 sample questions below (tap an option to check yourself), then play the full quiz 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 questions in this quiz

Answers and explanations are in the full quiz.

1. What type of data structure is commonly used to implement B-tree indexes in SQL databases?
2. A composite index is an index on how many columns?
3. Which SQL clause is typically used to create an index on a table?
4. What is the primary drawback of having too many indexes on a table?
5. What is a 'covering index'?
6. Which of the following is NOT a common type of SQL index?
7. What is the potential impact of indexing a column with very low cardinality (few distinct values)?
8. When should a UNIQUE index be used?
9. What is the main advantage of a Hash Index over a B-tree Index for equality lookups?
10. What does the `EXPLAIN` (or `EXPLAIN PLAN`) statement in SQL typically show?
11. Consider a query: `SELECT name FROM users WHERE city = 'New York' ORDER BY registration_date;`. If we create a composite index on `(city, registration_date)`, which part of the query will this index primarily optimize?
12. What is the term for an index that is automatically created by the database system for primary key constraints?
13. When is a full-text index typically used?
14. What is a potential downside of creating an index on a column that is frequently updated?
15. Which of the following query patterns benefits most from a composite index where the columns are ordered `(A, B)`?
16. What is a 'non-clustered index'?
17. A BITMAP index is typically most efficient for columns with:
18. Which of the following is a valid reason to create a PRIMARY KEY constraint on a column?
19. What is the primary goal of database indexing?
20. What is the significance of the 'leading column' in a composite index?
21. Which of the following conditions CANNOT be efficiently handled by a standard B-tree index?
22. What is an 'index seek' in the context of query execution plans?
23. When creating a composite index on `(col1, col2, col3)`, in which order would the database query optimizer typically prefer to use it?
24. What is the purpose of the `INCLUDE` clause in some SQL `CREATE INDEX` statements?
25. Which type of index is generally NOT suitable for columns that are frequently updated?
26. What is the main purpose of a `REVERSE` index (if supported by the database)?
27. When would you consider creating an index on a `JOIN` column between two large tables?
28. What is an 'index scan' operation?
29. Which index type is typically best suited for a column that has many distinct values (high cardinality) and is often used in equality comparisons?
30. What does 'index selectivity' refer to?
31. Which SQL statement is used to remove an index from a table?
32. What is the main advantage of a Clustered Index compared to a Non-Clustered Index?
33. If a query filters on a column that is NOT indexed, what is the most likely consequence?
34. Which of the following scenarios is LEAST likely to benefit from an index?
35. What is a 'covering index' primarily designed to achieve?
36. Which of the following is a characteristic of a B+ Tree index?
37. When using a composite index on `(col1, col2)`, which query condition is LEAST likely to utilize the index effectively?
38. What is the main purpose of a FULLTEXT index?
39. Which statement about index maintenance is generally TRUE?
40. When considering a composite index on `(col1, col2, col3)`, which query is LEAST likely to benefit from the index?
41. What is the primary function of a 'clustered index'?
42. Which SQL statement best describes the creation of an index named 'idx_lastname' on the 'lastname' column of a table named 'employees'?
43. What is the primary advantage of a Hash Index for equality-based lookups?
44. Consider a query: `SELECT email FROM users WHERE registration_date < '2024-01-01' AND country = 'USA'`. If a composite index exists on `(country, registration_date)`, which part of the query will it optimize most effectively?
45. What is the main purpose of a 'bookmark lookup' or 'key lookup' in query execution?
46. Which of the following statements about index fragmentation is generally TRUE?
47. What is the primary benefit of creating a UNIQUE index on a column?
48. Which type of index is often implemented as a tree structure and is highly efficient for both equality and range searches?
49. In the context of SQL indexes, what does 'covering index' mean?

More quizzes

• Joins▶ Play
• Stored Procedures▶ Play
• DDL▶ Play
• DML▶ Play

Browse all SQL quizzes

More to play