TRB PG Assistant - Query Processing and Optimization - algorithms for query processing, query optimization Online Test (MCQ Quiz)

1. What is the primary goal of query processing?

A) To minimize disk I/O operations B) To minimize CPU usage C) To retrieve data as quickly as possible D) To reduce network traffic

2. Which of the following is NOT a phase of query processing?

A) Parsing and Translation B) Optimization C) Execution D) Data Entry

3. What is the purpose of the parsing phase in query processing?

A) To generate an execution plan B) To check for syntax errors and translate SQL into a relational algebra expression C) To estimate the cost of different execution plans D) To perform the actual data retrieval

4. Which component is responsible for transforming a high-level query language statement into an internal representation?

A) Query Optimizer B) Query Executor C) Parser D) Relational Algebra Translator

5. What is the output of the query parsing and translation phase?

A) The final query result B) An optimized execution plan C) An intermediate representation like a relational algebra tree or query graph D) Raw data from the tables

6. What is the primary function of the query optimization phase?

A) To execute the query and retrieve data B) To check the query syntax C) To find the most efficient execution plan for a given query D) To store the query results

7. Which of the following is a key factor considered during query optimization?

A) The number of users accessing the database B) The size of the database and available indexes C) The operating system version D) The programming language used to write the query

8. What is a 'heuristic' in the context of query optimization?

A) An exact calculation of the best plan B) A rule of thumb or approximation to find a good plan quickly C) A definitive proof of query correctness D) A method to verify data integrity

9. Which type of query optimization aims to find the absolute best execution plan, even if it takes a long time?

A) Heuristic Optimization B) System R Style Optimization C) Exhaustive Optimization D) Dynamic Programming Optimization

10. What is a 'cost model' used for in query optimization?

A) To determine the user's access privileges B) To estimate the resource consumption (e.g., I/O, CPU) of different query plans C) To measure the network latency D) To evaluate the accuracy of the query results

11. Which of the following is a common algorithm used in query optimization?

A) Dijkstra's Algorithm B) Dynamic Programming C) Quick Sort D) Binary Search

12. What is the 'System R' style of query optimization?

A) A top-down approach using dynamic programming B) A bottom-up approach that builds plans from sub-plans C) A greedy approach that makes locally optimal choices D) A brute-force approach checking all possible plans

13. In dynamic programming for query optimization, what is the principle of optimality?

A) An optimal plan for a query is composed of optimal sub-plans B) The fastest plan is always the cheapest C) All plans must be evaluated exhaustively D) The query must be simple to be optimal

14. Which of the following is a common operation in relational algebra used in query processing?

A) Insert B) Update C) Join D) Delete

15. What does a 'join' operation typically do?

A) Selects rows based on a condition B) Combines rows from two or more tables based on a related column C) Removes duplicate rows D) Projects columns from a table

16. Which join algorithm is generally efficient when both relations are sorted on the join attribute?

A) Nested Loop Join B) Block Nested Loop Join C) Sort-Merge Join D) Hash Join

17. What is the basic idea behind the Nested Loop Join algorithm?

A) Build a hash table on one relation and probe with the other B) Sort both relations and then merge them C) For each row in the outer relation, scan the entire inner relation D) Process blocks of tuples instead of single tuples

18. When is Nested Loop Join most efficient?

A) When both relations are very large B) When one relation is small and the other is indexed on the join attribute C) When both relations are sorted on the join attribute D) When memory is abundant

19. Which join algorithm is typically efficient when one relation is significantly smaller than the other?

A) Sort-Merge Join B) Hash Join C) Nested Loop Join D) Index Nested Loop Join

20. What is the main advantage of Hash Join?

A) It requires the relations to be sorted B) It can be very efficient for large relations, especially with sufficient memory C) It is simple to implement D) It guarantees an optimal plan

21. In Hash Join, what is the 'build' phase?

A) Probing the hash table with tuples from the second relation B) Scanning the second relation C) Creating a hash table on the smaller relation D) Merging the results

22. What is the 'probe' phase in Hash Join?

A) Building the hash table on the first relation B) Using the hash table built on one relation to find matches in the other relation C) Sorting the relations D) Performing a full table scan

23. Which operation is used to reduce the number of columns in a query result?

A) Selection B) Projection C) Join D) Union

24. What is the purpose of the 'selection' operation in relational algebra?

A) To combine rows from two tables B) To filter rows based on a condition C) To remove duplicate rows D) To select specific columns

25. Consider a query 'SELECT * FROM Students WHERE GPA > 3.5;'. Which operation does this primarily represent?

A) Join B) Projection C) Selection D) Aggregation

26. Consider a query 'SELECT Name, Age FROM Students;'. Which operation does this primarily represent?

A) Selection B) Projection C) Join D) Cartesian Product

27. What is a 'Cartesian Product' (or Cross Join)?

A) Combines rows from two tables based on a join condition B) Combines every row from the first table with every row from the second table C) Selects rows based on a filter D) Projects specific columns

28. Which of the following is a common data structure used to speed up selections and joins?

A) Linked List B) Hash Table C) B-Tree D) Stack

29. What is an 'index' in a database context?

A) A table containing only primary keys B) A data structure that improves the speed of data retrieval operations on a database table C) A backup copy of the database D) A log of all database transactions

30. Which type of index is commonly used in relational databases for efficient range queries and ordered retrieval?

A) Hash Index B) B+ Tree Index C) Bitmap Index D) Full-Text Index

31. What is the primary advantage of using a B+ Tree index for joins?

A) It allows for very fast random access only B) It supports efficient ordered traversal, which can be used for sort-merge joins C) It is only suitable for equality comparisons D) It requires all data to be in memory

32. When would a Hash Index be more beneficial than a B+ Tree Index?

A) For range queries and ordered retrieval B) For queries involving equality comparisons on the indexed attribute C) When the data is frequently updated D) When disk I/O needs to be minimized for sequential scans

33. What is 'query decomposition' in query optimization?

A) Breaking down a complex query into simpler sub-queries or operations B) Executing a query in parallel C) Combining results from multiple tables D) Estimating the cost of a query

34. What is 'data localization' in the context of distributed query processing?

A) Ensuring all data resides on a single server B) Minimizing data transfer between sites by processing data close to where it is stored C) Encrypting data for security D) Compressing data before transmission

35. Which of the following is a common challenge in distributed query optimization?

A) Lack of indexes B) High network latency and bandwidth limitations C) Single point of failure D) Limited CPU power

36. What is a 'semi-join' operation used for in distributed query processing?

A) To combine all rows from two tables B) To reduce the size of one relation by sending only the necessary rows to another site for join processing C) To perform a full table scan D) To select specific columns

37. Which optimization technique involves executing parts of a query in parallel across multiple processors?

A) Sequential Execution B) Query Pipelining C) Parallel Query Execution D) Batch Processing

38. What is 'query pipelining'?

A) Executing operations one after another sequentially B) Allowing the output of one operation to be consumed by the next operation without waiting for the first to complete entirely C) Running multiple queries simultaneously D) Storing query results for future use

39. Consider the relational algebra expression (R JOIN S) WHERE condition. Which is generally a better optimization strategy?

A) Perform the JOIN first, then the WHERE B) Perform the WHERE first, then the JOIN C) Perform the JOIN and WHERE simultaneously D) There is no significant difference

40. Why is it generally better to apply selection operations as early as possible in a query plan?

A) To increase the number of rows processed B) To reduce the number of rows that need to be processed by subsequent operations C) To guarantee a unique result set D) To speed up data insertion

41. What is a 'query block' or 'subquery' in the context of optimization?

A) A single SQL statement B) A part of a larger query that can be optimized independently C) The final result set D) A temporary table

42. Which of the following is an example of a 'physical' optimization choice?

A) Choosing between different join algorithms (e.g., Hash Join vs. Sort-Merge Join) B) Rewriting a query using different relational algebra operators C) Decomposing a complex query into simpler ones D) Eliminating redundant subqueries

43. What is 'view materialization' in query optimization?

A) Creating a virtual table definition B) Storing the results of a view query to avoid recomputing it C) Deleting a view from the database D) Translating a view into SQL

44. What is the role of statistics in query optimization?

A) To store user login information B) To provide information about the data distribution and size of tables and indexes, used for cost estimation C) To record query execution times D) To manage database schema definitions

45. Which of these statistics are crucial for estimating the cost of a selection operation?

A) Number of distinct values in the column B) Number of tuples in the table C) Selectivity of the condition D) All of the above

46. What is 'predicate pushdown'?

A) Pushing a selection predicate down to an earlier stage in the query plan, ideally to the data source B) Pushing a join predicate up to the top of the query plan C) Pushing projection operations down D) Pushing aggregation operations down

47. What is the goal of 'rule-based optimization'?

A) To find the absolutely cheapest plan using a cost model B) To apply a set of predefined transformation rules to rewrite the query into an equivalent but more efficient form C) To execute the query using the most complex algorithm D) To ignore all available indexes

48. What is 'cost-based optimization'?

A) To apply a fixed set of transformation rules B) To estimate the cost of various query plans and select the one with the lowest estimated cost C) To minimize the number of lines of code in the query D) To prioritize user experience over performance

49. Which of the following is a common type of join optimization?

A) Early selection B) Predicate pushdown C) Choosing the best join algorithm D) View materialization

50. What is the main drawback of exhaustive query optimization?

A) It may not find the optimal plan B) It can be computationally very expensive and time-consuming for complex queries C) It requires a simple query structure D) It cannot handle joins effectively

Query Processing and Optimization - algorithms for query processing, query optimization - Online Test

Test Results