Query Processing & Optimization

Q1. Query processing involves:

1. A.Only creating tables without any query functionality
2. B.Only writing queries without any execution or output
3. C.Only storing data without any retrieval functionality
4. D.Parsing, optimizing, and executing database queries✓ Correct

Report an error in this question

Q2. A query execution plan describes:

1. A.The user who wrote the query and their access privilege details
2. B.The table creation date and the schema modification history
3. C.The sequence of operations the DBMS will perform to execute a query✓ Correct
4. D.The query syntax and grammatical structure in the SQL language

Report an error in this question

Q3. Query optimization aims to:

1. A.Find the most efficient way to execute a query✓ Correct
2. B.Make queries longer and more complex to write
3. C.Increase the number of disk accesses required
4. D.Slow down query processing for all operations

Report an error in this question

Q4. The EXPLAIN command in SQL shows:

1. A.The database schema layout
2. B.The query execution plan✓ Correct
3. C.User access permissions
4. D.The table data content

Report an error in this question

Q5. A full table scan reads:

1. A.Only indexed rows stored
2. B.Only the last row stored
3. C.Every row in the table✓ Correct
4. D.Only the first row found

Report an error in this question

Q6. Using an index for a query is generally:

1. A.Always slower than performing a full table scan
2. B.Faster than a full table scan for selective queries✓ Correct
3. C.The same speed as a full table scan on all data
4. D.Not recommended for any database query at all

Report an error in this question

Q7. Which join algorithm compares every tuple in one relation with every tuple in another?

1. A.Nested Loop Join✓ Correct
2. B.Sort-Merge Join
3. C.Index Nested Join
4. D.Hash Join method

Report an error in this question

Q8. The cost of a query is typically measured in:

1. A.Number of SQL statements used
2. B.Lines of code in the procedure
3. C.Number of disk I/O operations✓ Correct
4. D.Number of tables in the schema

Report an error in this question

Q9. Selectivity of a condition refers to:

1. A.The size of the database measured in total bytes
2. B.The number of columns selected in the projection
3. C.The fraction of tuples that satisfy the condition✓ Correct
4. D.The number of tables referenced in the SQL query

Report an error in this question

Q10. A query optimizer can be:

1. A.Only cost-based approach
2. B.Rule-based or cost-based✓ Correct
3. C.Neither rule nor cost based
4. D.Only rule-based approach

Report an error in this question

Q11. Sort-Merge Join works by:

1. A.Using a full table scan on both relations without any index use
2. B.Sorting both relations on the join attribute and then merging them✓ Correct
3. C.Creating a hash table on one relation and probing with the other
4. D.Using nested loops to compare every tuple in both the relations

Report an error in this question

Q12. Hash Join works by:

1. A.Nested iteration comparing every pair of tuples from relations
2. B.Sorting both relations on the join attribute and merging results
3. C.Using indexes on both tables to find matching join attributes
4. D.Building a hash table on one relation and probing with the other✓ Correct

Report an error in this question

Q13. An index nested loop join uses:

1. A.No index at all on either of the two relations being joined here
2. B.An index on the inner relation to find matching tuples efficiently✓ Correct
3. C.An index on the outer relation only for scanning its tuple values
4. D.A full scan of both relations without using any index structures

Report an error in this question

Q14. Pushing selections down in a query tree:

1. A.Increases the size of intermediate results
2. B.Removes selections entirely from the plan
3. C.Has no effect on the intermediate results
4. D.Reduces the size of intermediate results✓ Correct

Report an error in this question

Q15. Materialization in query processing means:

1. A.Not storing any intermediate results and processing everything in one single pass
2. B.Using only indexes to process the query without accessing the base table data
3. C.Computing and storing the result of each operation before passing it to the next✓ Correct
4. D.Only computing the final result without evaluating any intermediate operations

Report an error in this question

Q16. Pipelining in query processing means:

1. A.Processing operations one at a time in a strict sequential fashion
2. B.Storing all intermediate results on disk before the next operation
3. C.Passing tuples from one operation to the next as they are produced✓ Correct
4. D.Using only a single CPU core for all database query computations

Report an error in this question

Q17. The catalog statistics used by a cost-based optimizer include:

1. A.Only the table name without any other statistical information
2. B.Only the primary key definition of each table in the catalog
3. C.Number of tuples, number of distinct values, number of blocks✓ Correct
4. D.Only column names without cardinality or distribution details

Report an error in this question

Q18. Heuristic optimization rules include:

1. A.Perform joins before selections and projections always
2. B.Always use Cartesian products first before other joins
3. C.Perform selections and projections as early as possible✓ Correct
4. D.Avoid using indexes for any data retrieval operations

Report an error in this question

Q19. The join ordering problem in query optimization refers to:

1. A.Ordering rows within a single table by a specific column
2. B.Ordering columns in a table based on their data type size
3. C.Determining the most efficient order to join multiple tables✓ Correct
4. D.Sorting query results in ascending or descending sequence

Report an error in this question

Q20. Dynamic programming is used in query optimization for:

1. A.Creating tables and defining column data types
2. B.Parsing SQL queries into abstract syntax trees
3. C.Managing locks in the concurrency control layer
4. D.Finding the optimal join order for multiple tables✓ Correct

Report an error in this question

Q21. The Selinger optimizer (System R) uses:

1. A.Only heuristic rules without any cost estimation being performed
2. B.Random plan selection from all possible execution plan choices
3. C.Dynamic programming with interesting orders to find optimal plans✓ Correct
4. D.No optimization at all with queries running as they are written

Report an error in this question

Q22. An interesting sort order in query optimization is:

1. A.Any random sort order applied to tuples without considering the query operations
2. B.A sort order used only for displaying the final result to the end user query
3. C.A sort order that increases cost by requiring additional sorting in later steps
4. D.A sort order produced by one operation that is useful for a subsequent operation✓ Correct

Report an error in this question

Q23. The number of possible join orders for n relations is:

1. A.Grows exponentially (Catalan number)✓ Correct
2. B.Always n squared for n relations
3. C.Always n for n given relations total
4. D.Exactly n! for n relations total

Report an error in this question

Q24. In cost-based optimization, the estimated cost of a nested loop join R ⋈ S is approximately:

1. A.Always 1 block access regardless of the sizes of relations R and S used
2. B.b_R plus b_S (sum of blocks from both R and S relations) in simple analysis
3. C.n_R plus n_S (sum of tuples from both R and S relations) in simple analysis
4. D.n_R times b_S plus b_R (b is blocks, n_R is tuples in R) in simple analysis✓ Correct

Report an error in this question

Q25. Histogram statistics improve query optimization by:

1. A.Increasing query complexity by adding more steps to the optimization process
2. B.Providing more accurate selectivity estimates for non-uniform data distributions✓ Correct
3. C.Removing the need for indexes by providing direct access to all stored records
4. D.Making all estimates uniform regardless of the actual data value distribution

Report an error in this question

Q26. A left-deep join tree is preferred by many optimizers because:

1. A.It is the only valid tree shape for all joins
2. B.It avoids all disk I/O during the join process
3. C.It minimizes the number of tables in joins
4. D.It allows pipelining of intermediate results✓ Correct

Report an error in this question

Q27. Adaptive query processing differs from traditional optimization by:

1. A.Only optimizing before execution begins and then running without changes
2. B.Adjusting the execution plan during query execution based on runtime statistics✓ Correct
3. C.Never changing the plan once it has been selected by the query optimizer
4. D.Ignoring all statistics and using a fixed plan for every query submitted

Report an error in this question

Q28. The concept of equivalent query expressions means:

1. A.Identical query text that is written exactly the same way in SQL
2. B.Different relational algebra expressions that produce the same result✓ Correct
3. C.Queries that produce different results from the same input relations
4. D.Queries executed on different databases with different table schemas

Report an error in this question

Q29. Predicate pushdown in distributed queries:

1. A.Ignores remote data sources completely and only queries local tables
2. B.Sends all data to a central location first before any filtering occurs
3. C.Sends filter conditions to remote data sources to reduce data transfer✓ Correct
4. D.Removes all predicates from the query and returns all unfiltered data

Report an error in this question

Q30. Columnar storage benefits analytical queries because:

1. A.No indexes are needed for any query on columnar data store
2. B.Row storage is used instead of column-oriented data format
3. C.All columns are always read regardless of the query needs
4. D.Only the needed columns are read from disk, reducing I/O✓ Correct

Report an error in this question