Ch. 12 Subqueries Flashcards Preview

Oracle 11g SQL > Ch. 12 Subqueries > Flashcards

Flashcards in Ch. 12 Subqueries Deck (20):
1


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

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

-multiple-row

2


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

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

-parent query

3


The outer query receives its input from the ____.

-inner view
-outer view
-nested function
-subquery

-subquery

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

-single-row

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

none of the above

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

-HAVING

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

8


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

ANY
=ANY
=ALL

=ANY

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

-inline view

10


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

-IN
-ANY
-ALL
-all of the above

-all of the above

11


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

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

-single-row

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

-none of the above

13


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

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

-single-row

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

>ALL

15


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

/ /
" "
| |
( )

( )

16


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

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

-single-row

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

-ALL and ANY

18


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

-WHERE
-HAVING
-FROM
-all of the above

-all of the above

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

IN

20


A subquery must include a(n) ____ clause.

-SELECT
-FROM
-WHERE
-both a and b

-both a and b