Learn the key concepts and uses of subqueries in Oracle Database 12c SQL for the 1z0-071 certification exam. Discover how subqueries can be utilized in various clauses and understand the behavior of the ANY operator.
Table of Contents
Question
Which three are true about subqueries? (Choose three.)
A. A subquery cannot be used in the select list.
B. < ANY returns true if the argument is less than the highest value returned by the subquery.
C. < ANY returns true if the argument is less than the lowest value returned by the subquery.
D. A subquery can be used in a HAVING clause.
E. A subquery cannot be used in a FROM clause.
F. A subquery can be used in a WHERE clause.
G. = ANY can only evaluate the argument against a subquery if it returns two or more values.
Answer
C. < ANY returns true if the argument is less than the lowest value returned by the subquery.
D. A subquery can be used in a HAVING clause.
F. A subquery can be used in a WHERE clause.
Explanation
Subqueries are powerful tools in SQL that allow you to embed one query within another. They can be used in various clauses of an SQL statement, including the WHERE, HAVING, and FROM clauses.
The < ANY operator compares a value to the results of a subquery and returns true if the value is less than the lowest value returned by the subquery. This is in contrast to the incorrect option B, which states that < ANY returns true if the argument is less than the highest value.
Subqueries can be used in both the WHERE and HAVING clauses to filter results based on conditions that involve the results of the subquery. The WHERE clause filters rows before grouping, while the HAVING clause filters groups after grouping.
Option A is incorrect because subqueries can be used in the select list, although this is less common than using them in other clauses.
Option E is also incorrect, as subqueries can be used in the FROM clause. This is known as an inline view or derived table.
Finally, option G is not accurate. The = ANY operator can evaluate the argument against a subquery even if it returns only one value.
Oracle Database 12c SQL 1z0-071 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Oracle Database 12c SQL 1z0-071 exam and earn Oracle Database 12c SQL 1z0-071 certification.