CSIT156chap12

multiple-row

A(n) ____ subquery is one that can return several rows of results.

parent query

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

all of the above

Any type of subquery can be used in the ____ clause of a SELECT statement.

line 3

Based on the contents of the BOOKS table, which line in the following SQL statement contains an error?1 SELECT title2 FROM books3 WHERE pubid EXISTS IN4 (SELECT pubid 5 FROM books6 WHERE retail > 41.95);

none of the above

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

none of the above

Based on the contents of the BOOKS table, which of the following SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?

none of the above

Based on the contents of the BOOKS table, which of the following SQL statements will return an error message?

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

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 NVL(referred, 0) =(SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);

Based on the contents of the CUSTOMERS table, which of the following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?

line 4

Based upon the contents of the BOOKS table, which line of the following SQL statement contains an error?1 SELECT title, pubid, cost, retail2 FROM books3 WHERE (pubid, cost)4 (SELECT pubid, cost)5 FROM books6 WHERE pubid = 3);

When searching for all customers who live in the same state as customer# 1007

Based upon the contents of the CUSTOMERS table, which of the following would be the most appropriate use of a 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?

IS NULL

Which comparison operator allows you to search for NULL values in a subquery?

=ANY

...

subquery

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

both a and b

A subquery must include a(n) ____ clause.

ORDER BY

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

inline view

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

NVL

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.

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.

MERGE statement

In Oracle11g, a(n) ____ allows a series of DML actions to occur

HAVING

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

DML

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

single-row

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

single-row

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

less than the lowest

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

single-row

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

single-row

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

more than the highest

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

none of the above

The EXISTS operator must be listed ____.

IN

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

EXISTS

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

single-row

The following SQL statement contains what type of subqueries?SELECT isbn, title FROM books WHERE pubid =(SELECT pubid FROM books WHERE title = 'SHORTEST POEMS')AND retail-cost >(SELECT AVG(retail-cost) FROM books);

inline view

The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;

single-row

The following SQL statement contains which type of subquery? SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;

correlated

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

multiple-row

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

multiple-column

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

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);

single-row

The operators =, <, >, <=, >=, and <> are referred to as ____ operators.

subquery

The outer query receives its input from the ____.

outer

The results of a subquery are passed back as input to the ____ query.

all of the above

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

( )

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

all of the above

Which of the following operators is used with a multiple-row subquery?

multiple-row subquery

Which of the following subqueries returns more than one row of results to the outer query?

correlated subquery

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

<ANY

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

>ALL

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

ALL and ANY

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?