Which two statements are true regarding subqueries? (Choose two.)
A.
A subquery can retrieve zero or more rows.
B.
Only two subqueries can be placed at one level.
C.
A subquery can be used only in SQL query statements.
D.
A subquery can appear on either side of a comparison operator.
E.
There is no limit on the number of subquery levels in the WHERE clause of a SELECT
statement.
Explanation:
Using a Subquery to Solve a Problem
Suppose you want to write a query to find out who earns a salary greater than Abels salary.
To solve this problem, you need two queries: one to find how much Abel earns, and a second
query to find who earns more than that amount.
You can solve this problem by combining the two queries, placing one query inside the other
query. The inner query (or subquery) returns a value that is used by the outer query (or main
query).
Using a subquery is equivalent to performing two sequential queries and using the result of the
first query as the search value in the second query.
Subquery SyntaxA subquery is a SELECT statement that is embedded in the clause of another SELECT statement.
You can build powerful statements out of simple ones by using subqueries. They can be very
useful when you need to select rows from a table with a condition that depends on the data in the
table itself.
You can place the subquery in a number of SQL clauses, including the following:
WHERE clause
HAVING clause
FROM clause
In the syntax:
operator includes a comparison condition such as >, =, or IN
Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and
multiple-row operators (IN, ANY, ALL, EXISTS).
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement.
The subquery generally executes first, and its output is used to complete the query condition for
the main (or outer) query.
Guidelines for Using Subqueries
Enclose subqueries in parentheses. Place subqueries on the right side of the comparison
condition for readability. (However, the subquery can appear on either side of the comparison
operator.) Use single-row operators with single-row subqueries and multiple-row operators with
multiple-row subqueries.
Subqueries can be nested to an unlimited depth in a FROM clause but to only 255 levels in a
WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.