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 Abel’s 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 Syntax
A 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.