SQL Query Optimization and Execution Worksheet

A banking analytics system receives SQL queries joining three tables: Accounts, Transactions, and Customers. The optimizer rewrites the query tree before execution. Which transformation occurs during this stage?

• Logical optimization through heuristic rules

• Query parsing and translation into relational algebra

• Execution plan generation

• Cost estimation using statistics

A data warehouse query SELECT * FROM Sales WHERE amount>1000 AND region='APAC' passes through several stages. Which step determines which indexes or scans are used?

• Parsing

• Evaluation/Execution planning

• Syntax checking

• Logical transformation

A government tax portal executes complex joins. What metric does the planner use to estimate cost?

• Number of queries

• CPU usage only

• Number of disk I/Os and tuple comparisons

• Cache miss rate

In an OLTP system, optimizer chooses between join types. Orders fits in memory but Products does not. Which plan minimizes I/O?

• Merge join

• Block nested loop join

• Cartesian join

• Index join

Optimizer converts a subquery in WHERE clause into a join. What is this process?

• Physical optimization

• Query rewriting (logical optimization)

• Parsing

• Execution

An ecommerce dashboard runs SELECT COUNT(*) FROM Orders WHERE status='Shipped' using index on status. What happens during execution?

• Index ignored

• Index scan counts tuples without fetching full rows

• Full table scan

• Cached result

Temporary relations are created during query execution. Which operator generates the largest temporary relation?

• Projection

• Join

• Selection

• Aggregation

Financial database: Transactions (100 GB), Accounts (1 GB). Which heuristic is applied for join?

• Join larger to smaller

• Outer = smaller table

• Sort both tables

• Always hash join

SELECT * FROM Employees WHERE salary>70000 ORDER BY dept_id. Which step is postponed?

• Selection

• Sorting

• Projection

Distributed query combining fragments. Which component coordinates?

• Parser

• Catalog Manager

• Optimizer

• Executor