Sub-queries and MERGE statements

Which of the following operators is the equivalent of the IN comparison operator?

=ANY

Which operator will instruct Oracle11 g to list all records with a value that is more than the highest value returned by the subquery?

>ALL

Which operators can be combined with other comparison operators to treat the results of a subquery as a set of values, rather than as individual values?

ALL and ANY

A single-row subquery can return several columns, but only one row, of results to the outer query.

False

A subquery is required when the condition for the inner query is based upon an unknown.

False

The greater than operator, >, is a valid operator for multiple-row subqueries.

False

When a multiple-column subquery is used in the WHERE clause of the outer query, the column names listed on the left side of the comparison operator must be enclosed in double-quotation marks.

False

Which clause is used when the group results of a subquery need to be restricted, based on some condition?

HAVING

When a multiple-column subquery is included in the WHERE or HAVING clause of the outer query, which operator is used by the outer query to evaluate the results of the subquery?

IN

In Oracle11 g, a(n) ____ allows a series of DML actions to occur.

MERGE Statement

The following SQL statement contains which type of subquery? SELECT title, retail, category FROM books WHERE retail IN (SELECT MAX(retail) FROM books GROUP BY category);

Multiple-row

A subquery is required when the condition for the outer query is based upon an unknown.

True

In Oracle11 g, subqueries can be nested to a depth of 255 in a WHERE clause.

True

The NVL function can be used in a subquery.

True

When a multiple-column subquery is used in the outer query's FROM clause, it creates a temporary table, called an inline view, that can be referenced by other clauses of the outer query.

True

The following SQL statement contains which type of subquery? SELECT title FROM books WHERE EXISTS (SELECT isbn FROM orderitems WHERE books.isbn = orderitems.isbn);

correlated

With a(n) ____________________ subquery, the subquery is executed once for each row in the outer query.

correlated

The ____ operator is used to determine whether a condition is present in a subquery.

exists

A(n) ____________________ subquery returns more than one column to the outer query.

multiple-column

A(n) ____________________ query can return several rows of results, but only one column.

multiple-row

An outer query is also referred to as a(n) ____ query.

parent query

The > operator is referred to as a(n) ____ operator.

single row

A(n) ____________________ subquery can return only one row of results to the outer query.

single-row

The <= operator is referred to as a(n) ____ operator.

single-row

The <> operator is referred to as a(n) ____ operator.

single-row

The following SQL statement contains which type of subquery? SELECT title, retail, category, cataverage FROM books NATURAL JOIN (SELECT category, AVG(retail) cataverage FROM books GROUP BY category);

uncorrelated

With a(n) ____________________ subquery, the inner query is executed first and the results are passed back to the outer query.

uncorrelated