+
Related Topics
Cards In This Set
| Front | Back |
|
What are the 4 rules to keep in mind while working with any type of subquery?
|
1. A subquery must be a complete query in itself (it must have at least a SELECT and FROM clause)
2. a subquery cannot have an ORDER BY clause. If the displayed output needs to be presented in a specific order, an ORDER BY clause should be listed as the last clause of the outer query.
3. A subquery must be enclosed within a set of parentheses to separate it from the outer query.
4. If the subquery is placed in the WHERE or HAVING clause of an outer query, the subquery can be placed only on the right side of the comparison operator.
|
|
When can a single-row subquery be used?
|
When the results of the outer query are based on a single, unknown value.
|
|
What kind of subquery would you use if only one value should be returned from the inner query?
|
Single subquery
|
|
Which query is executed first when using a subquery? The inner or the outer query?
|
The inner query is returned first and then the results are passed to the outer query.
|
|
What are the single row operators?
|
=, , =,
You can also use operators like "IN", but the results must be a single value or you will receive an error.
|
|
When should a subquery be included in a HAVING CLAUSE?
|
When the group results of a query need to be restricted, based on some condition. If the result returned from a subquery must be compared to a group function then the inner query must be nested in the outer query's HAVING clause.
|
|
Why is a subquery seldom nested in the SELECT clause of an outer query?
|
Because when the subquery is listed in the SELECT clause, this means the value returned by the subquery will be displayed for every row of output gerated by the parent query.
|
|
What is a multiple-row subquery?
|
Subqueries that can return more than one row of results to the parent query.
|
|
What is the main rule to remember when using multiple-row subqueries?
|
That you must use multiple-row operators
|
|
What are the multiple-row operators?
|
IN, ALL, ANY
|
|
What does the IN operator indicate?
|
The records processed by the outer query MUST match one of the values returned by the subquer.
|
|
What results will the /
|
Any record with a value greater than the lowest value returned by the subquery.
|
|
What results will the =ANY return?
|
Equal to any value returned by the subquery (same as using IN)
|
|
What is the EXISTS operator used for?
|
To determine whether a condition is present is a subquery. The results are Boolean - it is TRUE if the ocondition exists and FALSE if it does not.
|
|
When must a subquery be nested in a HAVING clause in the parent query?
|
When the results of the subquery are being compared to grouped data in the outer query.
|
