IT314 CH12 Sub- Queries and MERGE Statement

Which of the following must be used to separate a subquery from the outer query?

( )

In Oracle11g, subqueries nested in a WHERE clause can be nested to a depth of ____________________ subqueries

255

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

=ANY

With a MERGE statement, a series of ____ actions can occur with a single SQL statement.

DML

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

EXISTS

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

EXISTS

A correlated subquery is one in which the inner query is executed first, and then the outer query is executed.

False

A group function cannot be included in the SELECT clause of a single-row subquery.

False

A subquery nested in a WHERE clause can only be on the left side of the comparison operator.

False

Single-row operators can be used with multiple-row subqueries that return only one column of results.

False

The <ANY operator indicates that a value must be less than the lowest value returned by the subquery to be included in the results.

False

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

False

The results of the outer query are passed to the inner query.

False

If a subquery's result must be compared with a group function, you must nest the inner query in the ____________________ clause of the outer query.

HAVING

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 ____ clause.

HAVING

The ____ operator indicates that the records processed by the outer query must match one of the values returned by the subquery.

IN

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

Based on the contents of the BOOKS table, which line in the following SQL statement contains an error? 1 SELECT title 2 FROM books 3 WHERE pubid EXISTS IN 4 (SELECT pubid 5 FROM books 6 WHERE retail > 41.95);

Line 3

With a(n) ____________________ statement, a series of DML actions can occur with a single SQL statement.

MERGE

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

Merge statement

If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.

NVL

A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.

ORDER BY

Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?

SELECT customer# FROM customers WHERE state =(SELECT state FROM customers WHERE customer#=1013);

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

Single Row

A multiple-row subquery can be nested in a HAVING clause.

True

A single-row subquery can be nested in the SELECT clause of the outer function.

True

A subquery nested in a SELECT clause cannot contain an ORDER BY clause.

True

A subquery, except one in the FROM clause, can't have an ORDER BY clause.

True

A(n) GROUP BY clause can be used in a subquery.

True

A(n) outer query is also referred to as a parent query.

True

If the value of the EXISTS operator is ____________________, then the rows meeting the condition are displayed.

True

In Oracle11 g, there is no depth limit on the number of subqueries that can be nested in a(n) FROM clause.

True

The EXISTS operator can be used with multiple-row subqueries.

True

The IN operator can be used with single-row, multiple-row, or multiple-column subqueries.

True

The equal sign, =, is a valid single-row operator.

True

When the subquery is executed first and the value is passed back as input to the outer query, the subquery is known as an uncorrelated subquery.

True

You can include multiple subqueries in a SELECT statement.

True

A MERGE statement containing an UPDATE and an INSERT clause is also called a(n) ____________________ statement.

UPSERT

Which of the following can be used in a WHERE clause?

all of the above

If a subquery references a column from the outer query, then it is a(n) ____________________ subquery.

correlated

Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?

correlated subquery

Single-row operators can be used with multiple-row subqueries that return only one column of results

false

A temporary table that is created when a multiple-column subquery is used in the FROM clause of an outer query is called a(n) ____.

inline view

The <ALL operator indicates that a value must be ____ value returned by the subquery.

less than the lowest

The >ALL operator indicates that a value must be ____ value returned by the subquery.

more than the highest

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

multiple column

A(n) ____________________ subquery is a nested query that returns more than one row of results.

multiple row

Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title 2 FROM books 3 WHERE pubid = 4 (SELECT pubid 5 FROM books 6 WHERE title = 'SHORTEST POEMS') 7 AND retail-cost > 8 (SELECT AVG(retail-cost) 9 FROM books);

none of the above

The EXISTS operator must be listed ____.

none of the above

The results of a subquery are passed back to the ____________________ query.

outer/parent

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

single row

The only type of subquery that can be used in a SELECT clause is a(n) ____________________ subquery.

single row

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

single-row

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

single-row

A complete query nested inside another query is called a(n) ____.

subquery

A group function can be used in a(n) inline view.

true

The NVL function can be used in a subquery.

true

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