Sample questions
IndexingDifficulty 1
At the storage level, what is a database index?
- aA compressed, read-only copy of the whole table kept on a separate disk volume
- bAn in-memory cache that stores the result of the most recently executed queries
- cA separate sorted structure that maps column values to the rows that hold them✓
- dA background job that periodically reorganizes the table's own physical layout
Explanation:An index is an auxiliary data structure (usually a B-tree) holding key values in order, each pointing to the row(s) that contain it, so the engine can locate matches without reading the whole table. It is not a full copy of the table (a), and a query-result cache (b) is a different mechanism.
IndexingDifficulty 1
There is no index on products.sku. How does the engine satisfy WHERE sku = 'X-100'?
- aIt performs a full (sequential) scan, reading every row and testing the condition✓
- bIt refuses to run the query until an index is created on the column
- cIt reads only the first matching row and immediately stops, ignoring the rest of the table
- dIt automatically builds a temporary index on
sku and reuses it forever
Explanation:Without a usable index the engine has no shortcut, so it scans the whole table row by row (a sequential/full table scan) and keeps the rows that match. It does not silently create a permanent index (d), and it cannot stop at the first match because other rows could also qualify (c).
IndexingDifficulty 1
Which columns are generally the best candidates for indexing?
- aColumns that are almost never referenced in queries but happen to take up little space
- bColumns that hold large text or binary blobs shown only on detail screens
- cColumns that are updated on nearly every write but are almost never read
- dColumns frequently used in WHERE filters, JOIN conditions, or ORDER BY✓
Explanation:Indexes pay off where the engine repeatedly searches, matches, or sorts by a column — i.e. WHERE, JOIN, and ORDER BY. Indexing rarely-queried columns (a) wastes space and write time, and indexing constantly-updated, rarely-read columns (c) adds maintenance cost with little read benefit.
IndexingDifficulty 1
You declare a PRIMARY KEY on id. What does the database create to support it?
- aNothing extra; the primary key is enforced by fully scanning the table on each insert
- bA unique index on the key column, used automatically to enforce and look it up✓
- cA foreign key pointing the column at an internal system catalog table
- dA trigger that runs before every insert to check for duplicate id values
Explanation:Declaring a primary key automatically creates a backing unique index; the engine uses it both to enforce uniqueness and to locate rows by id quickly. It does not rescan the table per insert (a) — that is exactly what the index avoids — and no user-visible trigger (d) is involved.
IndexingDifficulty 1
Why can a single B-tree index serve both age = 30 and age BETWEEN 20 AND 40?
- aBecause it stores the keys in sorted order, so equality and ranges are both seekable✓
- bBecause it keeps a separate hash bucket for every distinct value in the column
- cBecause it loads the entire indexed column into memory before evaluating the predicate
- dBecause range queries are internally rewritten into a list of equality checks
Explanation:A B-tree keeps keys ordered, so the engine can descend to a starting point and then walk the leaves — that covers exact matches and contiguous ranges alike. A hash structure (b) would support equality but not ranges, which is why range predicates cannot use a hash index.
IndexingDifficulty 1
You add an index that the optimizer decides to use. What can change about the query?
- aThe set of rows returned may shift because the index filters out some matches
- bAggregate results such as SUM and COUNT can come out slightly different
- cThe rows come back in the index's order even without ORDER BY, guaranteed
- dOnly performance changes; the result set stays exactly the same✓
Explanation:An index is a lookup aid, not part of the query's logic, so it can only affect how fast the answer is produced, never which rows or values are returned (a and b). Ordering without an explicit ORDER BY is never guaranteed (c), even if an index happens to return rows in order.