Essential Concepts in Database Indexing Quiz

Test your knowledge of database indexing basics with this quiz covering composite keys, covering indexes, and using EXPLAIN for query diagnosis. Ideal for beginners seeking to understand key indexing strategies and performance analysis in databases.

1. Identifying Composite Keys

   Which statement best describes a composite key in a database table?

   1. A set of two or more columns used together as a unique identifier
   2. A field used only for sorting rows
   3. A single column that uniquely identifies each row
   4. A key that automatically increases in value

2. Understanding Covering Indexes

   What makes an index a covering index for a specific query?

   1. It includes all columns referenced by the query
   2. It covers all the tables in the database
   3. It has the largest number of indexed columns
   4. It only covers the primary key column

3. Composite Index Use Case

   If a table has a composite index on (last_name, first_name), which query will most efficiently use this index?

   1. SELECT * FROM employees WHERE age u003E 30
   2. SELECT * FROM employees
   3. SELECT * FROM employees WHERE first_name = 'Anna'
   4. SELECT * FROM employees WHERE last_name = 'Lee' AND first_name = 'Anna'

4. Diagnosing Queries with EXPLAIN

   What is the primary purpose of using the EXPLAIN command in database queries?

   1. To find syntax errors in the query
   2. To display the database schema
   3. To display how the database will execute the query
   4. To show the actual query results

5. Index Benefits

   How do indexes most commonly improve query performance in databases?

   1. By storing entire tables in memory
   2. By enforcing numeric data types
   3. By encrypting rows automatically
   4. By reducing the amount of data scanned for query matches

6. Partial Index Condition

   Suppose a composite index is created on (city, postal_code); which query will not fully benefit from this index?

   1. SELECT * FROM addresses WHERE postal_code = '75001'
   2. SELECT * FROM addresses WHERE city LIKE 'Pa%'
   3. SELECT * FROM addresses WHERE city = 'Paris'
   4. SELECT * FROM addresses WHERE city = 'Paris' AND postal_code = '75001'

7. EXPLAIN Output Understanding

   If the EXPLAIN output for a query shows 'Using index' in the Extra column, what does it indicate?

   1. The index is about to be created
   2. The result is retrieved using only the index, without accessing table rows
   3. The query plan is unknown
   4. The query uses no index and does a full table scan

8. Index Choice for Range Queries

   Which index would be most suitable for the query: SELECT * FROM sales WHERE date u003E= '2023-01-01' AND date u003C= '2023-06-30'?

   1. A composite index on (product_id, amount)
   2. No index at all
   3. A composite index on (date, customer_id)
   4. A single-column index on amount

9. Order of Columns in Composite Indexes

   Why does the order of columns in a composite index affect query performance?

   1. Because the indexing engine searches leftmost columns first
   2. Because queries must filter on all columns at once
   3. It has no impact at all
   4. Because columns are always searched in reverse order

10. Avoiding Duplicate Indexes

    Which of the following is a reason to avoid creating duplicate indexes on the same columns?

    1. It is required for all composite keys
    2. It speeds up query execution further
    3. It improves insert performance
    4. It increases storage requirements unnecessarily