Practice
1. You have a large table frequently queried by range conditions on a date column and occasionally updated. Which indexing strategy is most appropriate to optimize query performance?
easy
Solution
Step 1: Understand query pattern for range scans on date
Range queries on the date column benefit from data physically sorted by date, enabling efficient range scans.Step 2: Consider clustered index effect on physical ordering
A clustered index stores rows physically ordered by the indexed column, making range queries faster.Step 3: Evaluate update impact tradeoff
Although updates cause some overhead due to physical reordering, occasional updates are an acceptable tradeoff for query speed.Step 4: Analyze other options
Non-clustered index avoids physical reordering but range queries require extra lookups; no index leads to slow scans; clustered on unique ID doesn't optimize date range queries.Final Answer:
Option A -> Option AQuick Check:
Range queries -> clustered index on date column [OK]
Hint: Range queries -> clustered index on that column [OK]
Common Mistakes:
- Assuming non-clustered index always better for updates
- Ignoring physical data order benefits for range queries
- Believing no index is better if updates exist
2. When executing a range query on a B+ tree index, what sequence of steps occurs internally to retrieve all matching records?
easy
Solution
Step 1: Locate start key
B+ trees use a balanced tree traversal to find the leaf node containing the first key in the range.Step 2: Sequential leaf traversal
Leaf nodes are linked, allowing sequential scanning of keys in order until the range end is reached.Step 3: Evaluate distractors
Hash the range boundaries to find corresponding buckets, then scan all buckets between them. incorrectly applies hashing to range queries. Traverse the tree to the root, then scan all leaf nodes in order regardless of the range. suggests scanning all leaves, which is inefficient. Perform a full table scan because B+ trees do not support range queries efficiently. falsely claims B+ trees do not support range queries.Final Answer:
Option D -> Option DQuick Check:
Range query -> locate start + sequential leaf scan [OK]
Hint: B+ tree range query = find start + linked leaf scan
Common Mistakes:
- Thinking hash indexes can handle range queries by scanning buckets
- Assuming full leaf scan is needed for range queries
- Believing B+ trees do not support range queries
3. Consider the following Python code simulating SQL query execution order using linked nodes. What is the exact output when executing the query plan starting from the
FROM clause node?
class QueryPlanNode:
def __init__(self, name):
self.name = name
self.next = None
def set_next(self, node):
self.next = node
def execute(self):
print(f"Executing {self.name} clause")
if self.next:
self.next.execute()
from_clause = QueryPlanNode('FROM')
where_clause = QueryPlanNode('WHERE')
group_by_clause = QueryPlanNode('GROUP BY')
having_clause = QueryPlanNode('HAVING')
select_clause = QueryPlanNode('SELECT')
order_by_clause = QueryPlanNode('ORDER BY')
limit_clause = QueryPlanNode('LIMIT')
from_clause.set_next(where_clause)
where_clause.set_next(group_by_clause)
group_by_clause.set_next(having_clause)
having_clause.set_next(select_clause)
select_clause.set_next(order_by_clause)
order_by_clause.set_next(limit_clause)
from_clause.execute()easy
Solution
Step 1: Trace execution starting from FROM node
Theexecute()method prints the current node's clause name, then recursively callsexecute()on the next node if it exists. The chain is FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT.Step 2: Confirm output order matches linked nodes
Each clause prints in the linked order, so output lines are exactly the clauses in the chain order.Final Answer:
Option A -> Option AQuick Check:
Output matches linked list traversal order [OK]
Hint: Linked nodes print clauses in linked order [OK]
Common Mistakes:
- Confusing printed order with written SQL order
4. Which of the following statements about dependency preservation in decomposition is INCORRECT?
medium
Solution
Step 1: Define dependency preservation
It means all FDs can be enforced on decomposed relations without joins.Step 2: Understand relation to lossless join
Dependency preservation does not guarantee lossless join; they are independent properties.Step 3: Evaluate options
If a decomposition is dependency preserving, it must also be lossless join. incorrectly claims dependency preservation implies lossless join.Final Answer:
Option D -> Option DQuick Check:
Options A, B, and D are correct statements about dependency preservation.
Hint: Dependency preservation ≠ lossless join; they are separate properties.
Common Mistakes:
- Assuming dependency preservation guarantees lossless join
- Confusing enforcement of dependencies with data loss
- Ignoring performance implications of missing dependency preservation
5. Why might using stored procedures for all business logic in a database be a poor architectural choice?
medium
Solution
Step 1: Analyze architectural implications
Embedding all business logic in stored procedures couples application logic tightly to the database schema and vendor, reducing portability and flexibility.Step 2: Evaluate other options
Stored procedures can be version controlled and support transactions; performance gains depend on use case.Final Answer:
Option B -> Option BQuick Check:
Recognize trade-offs between maintainability and performance.
Hint: Stored procedures can hurt flexibility by mixing logic with data storage.
Common Mistakes:
- Assuming stored procedures always improve performance
- Believing stored procedures cannot be version controlled
- Thinking stored procedures lack transaction support