Term
| What are the 4 rules to keep in mind while working with any type of subquery? |
|
Definition
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. |
|
|
Term
| When can a single-row subquery be used? |
|
Definition
| When the results of the outer query are based on a single, unknown value. |
|
|
Term
| What kind of subquery would you use if only one value should be returned from the inner query? |
|
Definition
|
|
Term
| Which query is executed first when using a subquery? The inner or the outer query? |
|
Definition
| The inner query is returned first and then the results are passed to the outer query. |
|
|
Term
| What are the single row operators? |
|
Definition
=, <, >, <=, >=, <>
You can also use operators like "IN", but the results must be a single value or you will receive an error. |
|
|
Term
| When should a subquery be included in a HAVING CLAUSE? |
|
Definition
| 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. |
|
|
Term
| Why is a subquery seldom nested in the SELECT clause of an outer query? |
|
Definition
| 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. |
|
|
Term
| What is a multiple-row subquery? |
|
Definition
| subqueries that can return more than one row of results to the parent query. |
|
|
Term
| What is the main rule to remember when using multiple-row subqueries? |
|
Definition
| That you must use multiple-row operators |
|
|
Term
| What are the multiple-row operators? |
|
Definition
|
|
Term
| What does the IN operator indicate? |
|
Definition
| the records processed by the outer query MUST match one of the values returned by the subquer. |
|
|
Term
|
Definition
| Any record with a value greater than the lowest value returned by the subquery. |
|
|
Term
| What results will the =ANY return? |
|
Definition
| Equal to any value returned by the subquery (same as using IN) |
|
|
Term
| What is the EXISTS operator used for? |
|
Definition
| 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. |
|
|
Term
| When must a subquery be nested in a HAVING clause in the parent query? |
|
Definition
| When the results of the subquery are being compared to grouped data in the outer query. |
|
|
Term
| What is the difference between a single-row subquery and multiple_row subquery? |
|
Definition
| A single-row subquery can only return one value, a multiple-row subquery can return several values. |
|
|
Term
| What does a multiple-column subquery return? |
|
Definition
| It returns more than one column to the outer query. |
|
|
Term
| Which clauses can a mulitple-column subquery be used? |
|
Definition
|
|
Term
| What is the result when a multiple-column subquery is included in the FROM clause of the outer query? |
|
Definition
| The subquery actually generates a temporary table that can be referenced by other clauses of the outer query. |
|
|
Term
|
Definition
| A temporary table that can be referenced by other clauses of the outer query. |
|
|
Term
| What does a mulitple-comumn subquery return? |
|
Definition
| returns more than one column to the outer query. |
|
|
Term
| Are column qualifiers (alias) allowed when using a NATURAL JOIN? |
|
Definition
|
|
Term
| When a mulitple-column subquery is included in the WHERE or HAVING clause of the outer query, what operator is used by the outer query to evaluate the rsults of the subquery? |
|
Definition
|
|
Term
| What is the format for the outer WHERE clase (using multiple columns)? |
|
Definition
WHERE (columnname,columnname,..)
IN (subquery) |
|
|
Term
| What are the two rules to remember when creating a mulitple column query in the WHERE clause? |
|
Definition
1. Since the WHERE clause contains more than one column name, the column list must be enclosed within parentheses.
2. Column names listed in the WHERE clause must be in the same oreder as they are listed in the SELECT clause of the subquery. |
|
|
Term
| When should you use a mulitple-column subquery in the HAVING cluase of an outside query? |
|
Definition
| It is generally only used when analyzing extremely large sets of numeric data that have been grouped and is generally presented in more advanced courses focusing upon quantitative methods. |
|
|
Term
| What is the result if a NULL value is passed from a subquery? |
|
Definition
| The results of the outer query will be "no rows selected." |
|
|
Term
| What function should be used to substitute an actual value for the NULL? |
|
Definition
|
|
Term
| What two things need to be kept in mind when using the NVL function. |
|
Definition
1. The substitution of the NULL value must occure for the NULL value both in the subquery and in the outer query.
2. The value substituted for the NULL value must be one that could not possibly exist anywhere else in the column. |
|
|
Term
| How can you search for NULL values in a subquery? |
|
Definition
| Use the IS NULL comparison operator |
|
|
Term
| What is an uncorrelated subquery? |
|
Definition
| The subquery is executed first, the results of the subquery are passed to the outer query, and then the outer query is executed. |
|
|
Term
| What is a correlated subquery? |
|
Definition
| a subquery that is processed, or executed once for each row in the outer query. |
|
|
Term
| How does Oracle9i distinguish between an uncorrelated and a correlated subquery. |
|
Definition
| If a subquery references a column from the outer query, then it is a correlated subquery. |
|
|
Term
| Where can subqueries be nested in other subqueries? |
|
Definition
| In the FROM, WHERE, or HAVING clauses. |
|
|
