What is a database index?
A A duplicate table copy always
B A transaction rollback log
C A foreign key constraint
D Structure that speeds data lookup
Answer: D. Structure that speeds data lookup. An index provides an access path to find rows more efficiently.
What structure commonly supports ordered range lookup?
A Hash bucket
B B tree
C Stack
D Bloom filter only
Answer: B. B tree. B trees keep keys ordered and support range searches.
What is a common B plus tree property?
A All values stored at root
B No internal index keys
C Leaves have no ordering
D Records referenced at linked leaves
Answer: D. Records referenced at linked leaves. B plus trees keep data entries at linked leaves, supporting ordered traversal.
What does a clustered index influence?
A Physical row organization
B SQL language syntax
C Transaction isolation level
D User permissions
Answer: A. Physical row organization. A clustered index determines or closely matches the physical order of table rows.
What does a nonclustered index store?
A Search keys with row references
B Only complete database backups
C Query text and results
D Transaction locks only
Answer: A. Search keys with row references. A nonclustered index stores key values and references to their rows.
What can a composite index accelerate?
A Any query on any column
B Only table creation
C Queries on indexed column sequence
D Every full table scan
Answer: C. Queries on indexed column sequence. A composite index is most useful for predicates aligned with its leading columns.
Why does composite index column order matter?
A Column names must be unique
B Leading columns guide lookup
C The index stores no values
D Rows cannot contain nulls
Answer: B. Leading columns guide lookup. An index on A then B may not efficiently support a predicate on B alone.
What is a hash index best suited for?
A Ordered range scans
B Equality lookup
C Sorting entire tables
D Prefix matching on all keys
Answer: B. Equality lookup. Hash indexes are designed for exact key lookup rather than ordered ranges.
What does index selectivity describe?
A Index file name
B Number of table columns
C Query output order
D Distinctness of indexed values
Answer: D. Distinctness of indexed values. Highly selective values narrow a search to relatively few matching rows.
What is a query execution plan?
A Database user list
B Table definition only
C Chosen operations to run query
D A backup schedule
Answer: C. Chosen operations to run query. An execution plan shows operators and access methods used for a query.
What does a query optimizer choose?
A Efficient execution plan
B Database password
C Table schema name
D Transaction commit value
Answer: A. Efficient execution plan. The optimizer compares plan alternatives using available statistics and cost estimates.
What does EXPLAIN commonly show?
A Query plan information
B All database passwords
C Only table contents
D Committed transaction log
Answer: A. Query plan information. EXPLAIN displays how the database expects to execute a query.
What is a covering index?
A Index stores no key
B Index covers every table
C Index contains query's needed data
D Index prevents all table updates
Answer: C. Index contains query's needed data. A covering index can answer a query without fetching additional table columns.
What is a tradeoff of adding indexes?
A Slower every read
B More storage and write work
C No additional storage
D Automatic schema normalization
Answer: B. More storage and write work. Indexes consume space and must be maintained when rows change.
What is a materialized view?
A Virtual query with no storage
B Stored result of a query
C An ordinary index only
D A table with no columns
Answer: B. Stored result of a query. A materialized view stores query results and may need refreshing.
What is a database trigger?
A A query execution plan
B Code activated by database event
C An index key
D A primary key alias
Answer: B. Code activated by database event. A trigger runs automatically in response to events such as row changes.
What is a stored procedure?
A A physical table copy
B A query result cache only
C Saved executable database routine
D A foreign key constraint
Answer: C. Saved executable database routine. A stored procedure packages SQL logic in the database for later invocation.
What is a transaction log used for?
A Record changes for recovery
B Store only user passwords
C Sort query outputs
D Define a database schema
Answer: A. Record changes for recovery. Logs record changes that help restore database consistency after failures.
What does write ahead logging require?
A Write pages before logging
B Delete logs after every query
C Keep no recovery information
D Log changes before data pages
Answer: D. Log changes before data pages. Write ahead logging ensures relevant log records reach storage before modified data pages.
What does a checkpoint help recovery do?
A Remove every index
B Prevent transactions
C Limit log replay work
D Reorder all tables
Answer: C. Limit log replay work. A checkpoint records a recovery position so fewer log records must be processed.
What do undo and redo support?
A Transaction recovery
B Query parsing
C Index selection
D Schema normalization
Answer: A. Transaction recovery. Recovery may undo incomplete work and redo committed changes.
What is database replication?
A Partition by row range
B Convert SQL to files
C Maintain copies across nodes
D Sort all records
Answer: C. Maintain copies across nodes. Replication copies data to multiple database nodes for availability or performance.
What is sharding?
A Create multiple indexes only
B Full copy on every node
C Merge tables into a view
D Distribute data across partitions
Answer: D. Distribute data across partitions. Sharding distributes subsets of data across servers.
What is a key value database?
A Store key associated with value
B Store only graph edges
C Store tables with fixed joins
D Store documents as XML only
Answer: A. Store key associated with value. Key value stores retrieve values by a unique key.
What is a document database?
A Store only fixed width rows
B Store only graph paths
C Store processor instructions
D Store semi structured documents
Answer: D. Store semi structured documents. Document databases organize data as flexible documents such as JSON-like records.