PRACTICE • LEARN • GROW
Module 9 Final Quiz: Indexing & Query Optimization MCQs
Solve topic-wise placement questions and improve your skills.
Module 9 Final Quiz: Indexing & Query Optimization Progress
0 of 12 completed
0%
Topic Track
You are viewing MCQs in preview mode. Log in to select options, submit answers, and track your progress.
Log In to Answer
1
What is the primary difference between a Clustered Index and a Non-Clustered (Secondary) Index?
A.
Clustered indices are stored in RAM; Non-clustered are stored on disk
B.
A Clustered index physically sorts and stores the actual table rows in search-key order (only 1 per table); a Non-clustered index stores index keys with pointers to data rows (multiple per table)
C.
Non-clustered indices cannot be used with WHERE clauses
D.
A table can have up to 10 clustered indices
Explanation & Key
Correct Answer:
2
Why are B+ Trees universally preferred over binary search trees (BST or AVL) for relational database indexing?
A.
Binary trees cannot store numbers
B.
B+ trees have a huge Fan-Out (each node matches a 4KB-16KB disk page holding hundreds of keys), keeping tree height tiny (2 to 4 levels) and minimizing mechanical disk I/O seeks
C.
B+ trees do not require memory
D.
AVL trees can only store strings
Explanation & Key
Correct Answer:
3
What structural feature of a B+ Tree enables fast range queries (e.g. WHERE age BETWEEN 20 AND 30)?
A.
The root node points to all leaf nodes directly
B.
All leaf nodes are linked horizontally via bidirectional pointers in a contiguous doubly linked list
C.
Internal nodes store all data records
D.
Range queries are converted into full table scans
Explanation & Key
Correct Answer:
4
What is a Covering Index in SQL query optimization?
A.
An index that covers the entire hard drive volume
B.
An index that contains ALL the columns requested by a query (both in SELECT and WHERE), allowing the engine to satisfy the query entirely from the index without accessing the base table
C.
An index that encrypts data columns
D.
An index that automatically fixes syntax errors
Explanation & Key
Correct Answer:
5
What is the difference between a Dense Index and a Sparse Index?
A.
Dense indices are for numbers; Sparse indices are for text
B.
In a Dense index, an index record appears for every single search-key value in the table; in a Sparse index, records appear for only some search-key values (typically one per disk block)
C.
Sparse indices consume more storage than dense indices
D.
Dense indices cannot be searched
Explanation & Key
Correct Answer:
6
What does an SQL query execution plan generated by the EXPLAIN command reveal?
A.
The user's database password hash
B.
The internal physical execution steps chosen by the query optimizer, including whether indices are used (Index Scan) or tables are fully scanned (Table Scan), estimated rows, and join algorithms
C.
The compile errors in the SQL parser
D.
The network ping between client and server
Explanation & Key
Correct Answer:
7
What is the primary heuristic optimization rule applied to relational algebra trees before cost estimation?
A.
Perform Cartesian products first
B.
Push Selections ($sigma$) and Projections ($pi$) down the tree as early as possible to minimize the cardinality of intermediate relations
C.
Evaluate ORDER BY before WHERE
D.
Convert all queries to nested subqueries
Explanation & Key
Correct Answer:
8
What is a Hash Join in relational query engines?
A.
A join that encrypts column values with SHA-256
B.
An algorithm that builds an in-memory hash table on the smaller relation, then streams the larger relation probing the hash table for matches (ideal for large unindexed equi-joins)
C.
A join that requires both tables to be sorted in advance
D.
A join that uses binary trees on disk
Explanation & Key
Correct Answer:
9
What is the disadvantage of having too many indices on an OLTP (Online Transaction Processing) database table?
A.
Queries will fail with syntax errors
B.
Every INSERT, UPDATE, and DELETE statement must synchronously update all existing indices, significantly degrading write performance and consuming disk space
C.
The database cannot support foreign keys
D.
The table cannot be queried concurrently
Explanation & Key
Correct Answer:
10
Why does the query 'SELECT * FROM Users WHERE UPPER(email) = 'TEST@EXAMPLE.COM';' fail to use a standard B-tree index on the email column?
A.
Because email addresses cannot be indexed
B.
Because applying a function (UPPER) to an indexed column prevents the optimizer from performing a direct B-tree range seek (unless an expression/functional index exists)
C.
Because B-trees only store uppercase strings
D.
Because the email string contains '@'
Explanation & Key
Correct Answer:
11
What is a Composite (Multi-Column) Index and the 'Leftmost Prefix' rule?
A.
An index combining two databases; works from right to left
B.
An index created on multiple columns (e.g. INDEX(a, b, c)); it can only be used by queries that filter by the leftmost columns in order (e.g. filtering on `a` or `a, b`, but NOT `b, c` alone)
C.
An index that only stores odd numbered rows
D.
An index that duplicates data across tables
Explanation & Key
Correct Answer:
12
What does the Cost-Based Optimizer (CBO) use to calculate the estimated cost of different execution plans?
A.
The number of lines in the SQL query text
B.
Database catalog statistics including table row counts, page counts, column cardinalities, data histograms, and estimated CPU and disk I/O costs
C.
The current battery percentage of the server
D.
The user's subscription tier
Explanation & Key
Correct Answer:
Question Palette
Answered
Current
Not Attempted
Wrong
