Ch. 12 Subqueries

Question 1

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

• correlated
• single-row
• multiple-row
• uncorrelated

Answer 1

-multiple-row

Question 2

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

• parent query
• outer view
• outline view
• all of the above

Answer 2

-parent query

Question 3

The outer query receives its input from the ____.

• inner view
• outer view
• nested function
• subquery

Answer 3

-subquery

Question 4

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

• single-row
• multiple-row
• multiple-column
• inline view

Answer 4

-single-row

Question 5

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?

SELECT title FROM books WHERE pubid >ANY
(SELECT pubid FROM books WHERE title = ‘SHORTEST POEMS’);

SELECT title FROM books WHERE pubid =ANY
SELECT pubid FROM books WHERE title = ‘SHORTEST POEMS’;

SELECT title FROM books WHERE pubid IN
(SELECT pubid, title FROM books WHERE title = ‘SHORTEST POEMS’);

none of the above

Answer 5

none of the above

Question 6

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.

• GROUP BY
• WHERE
• HAVING
• FROM

Answer 6

-HAVING

Question 7

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

> ANY
ALL

Question 8

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

ANY
=ANY
=ALL

Answer 8

=ANY

Question 9

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

• inner view
• outer view
• inline view
• natural view

Answer 9

-inline view

Question 10

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

• IN
• ANY
• ALL
• all of the above

Answer 10

-all of the above

Question 11

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

• single-row
• multiple-row
• correlated
• uncorrelated

Answer 11

-single-row

Question 12

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

• Line 3
• Line 5
• Line 7
• none of the above

Answer 12

-none of the above

Question 13

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

• multiple-row
• multiple-column
• single-row
• none of the above

Answer 13

-single-row

Question 14

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

ALL
>ANY

Answer 14

> ALL

Question 15

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

/ /
“ “
| |
( )

Answer 15

( )

Question 16

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

• single-row
• multiple row
• multiple-column
• correlated

Answer 16

-single-row

Question 17

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?

• IN and ANY
• IN and ALL
• ALL and ANY
• EXISTS and IN

Answer 17

-ALL and ANY

Question 18

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

• WHERE
• HAVING
• FROM
• all of the above

Answer 18

-all of the above

Question 19

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

IN
>ANY
ALL

Answer 19

IN

Question 20

A subquery must include a(n) ____ clause.

• SELECT
• FROM
• WHERE
• both a and b

Answer 20

-both a and b