Deck 12: Subqueries and Merge Statements

Question 1

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

Answer 1

True

Question 2

Valid single-row operators include =, >, <, >=, <=.

Answer 2

True

Question 3

A correlated subquery references one or more columns in the outer query, and the EXISTS operator is used to test whether the relationship or link is present.

Answer 3

True

Question 4

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

Question 5

Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.

Question 6

A subquery is a complete query nested inside another query.

Question 7

When used with a multiple-row subquery, the IN operator indicates that the records processed by the outer query must match one of the values returned by the subquery.

Question 8

A subquery can only be nested in the WHERE or HAVING clause of the outer query.

Question 9

If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator.

Question 10

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

Question 11

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

Question 12

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

Question 13

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

Question 14

A multiple-column subquery cannot be nested in a WHERE clause.

Question 15

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

Question 16

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

Question 17

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.

Question 18

The

Question 19

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

Question 20

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.

Question 21

In Oracle11g, a MERGE statement compares data between two tables and can perform a series of DML actions to assist in synchronizing the data of the two tables.

Question 22

A(n) single-row subquery can be nested in a(n) $\underline {\text { SELECT } }$ clause. _________________________

Question 23

A subquery is required when the condition for the $\underline {\text { inner } }$ query is based upon an unknown. _________________________

Question 24

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

Question 25

IN is a valid $\underline { \text { multiple-row } }$ subquery operator. _________________________

Question 26

The greater than operator, >, is a valid operator for $\underline { \text { multiple-row } }$ subqueries. _________________________

Question 27

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.

Question 28

A subquery nested in a WHERE clause can only be on the $\underline {\text { left }}$ side of the comparison operator. _________________________

Question 29

A subquery must be enclosed in a set of $\underline {\text { quotation marks. } }$ _________________________

Question 30

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

Question 31

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

Question 32

Valid $\underline { \text { multiple-row } }$ operators include =, >, <, >=, <=. _________________________

Question 33

The NVL function can be used in a subquery.

Question 34

A correlated subquery is a subquery that is executed once for each row in the outer query.

Question 35

The results of the subquery are passed to the $\underline {\text { inner } }$ query. _________________________

Question 36

When a multiple-column subquery is included in the outer query's WHERE clause, the column names listed in the WHERE clause must be in the same order as they're listed in the subquery's SELECT clause.

Question 37

A(n) $\underline {\text { outer } }$ query is also referred to as a parent query. _________________________

Question 38

IN is not a valid operator for a multiple-column subquery.

Question 39

The $\underline {\mathrm{< ANY} }$ operator indicates that a value must be less than the lowest value returned by the subquery to be included in the results. _________________________

Question 40

A subquery, except one in the FROM clause, can't have an $\underline { \text { ORDER BY } }$ clause. _________________________

Question 41

A multiple-column subquery nested in the $\underline {\text { SELECT } }$ clause of the outer query is known as an inline view. _________________________

Question 42

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

A) WHERE
B) HAVING
C) FROM
D) all of the above

Question 43

A(n) $\underline {\text { GROUP BY }}$ clause can be used in a subquery. _________________________

Question 44

The outer query receives its input from the ____.

A) inner view
B) outer view
C) nested function
D) subquery

Question 45

The $\underline {\text { IN } }$ operator can be used with single-row, multiple-row, or multiple-column subqueries. _________________________

Question 46

In Oracle11g, there is no depth limit on the number of subqueries that can be nested in a(n) $\underline { \text { FROM } }$ clause. _________________________

Question 47

In Oracle11g, subqueries in a WHERE clause can be nested to a depth of $\underline { 2 }$ . _________________________

Question 48

The $\underline {\text { IN } }$ operator is valid for multiple-row subqueries. _________________________

Question 49

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 $\underline {\text { single-quotation marks }}$ . _________________________

Question 50

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

A) multiple-column subquery
B) single-row subquery
C) multiple-row subquery
D) correlated subquery

Question 51

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

A) parent query
B) outer view
C) outline view
D) all of the above

Question 52

A(n) correlated subquery references one or more columns $\underline { \text { FROM } }$ the outer query. _________________________

Question 53

A group function can be used in a(n) $\underline {\text { inline view }}$ . _________________________

Question 54

The $\underline{=\mathrm{ANY}}$ operator yields the same results as using the IN multiple-row operator. _________________________

Question 55

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

A) inner view
B) subquery
C) child view
D) all of the above

Question 56

A(n) $\underline {\text { correlated }}$ subquery is when the outer query is executed first, and then the inner query is executed. _________________________

Question 57

You $\underline {\text { can } }$ include multiple subqueries in a SELECT statement. _________________________

Question 58

A(n) $\underline { \text { uncorrelated } }$ subquery is when the outer query is executed first, then the inner query is executed. _________________________

Question 59

The $\underline { \text { EXISTED } }$ operator is used to determine whether a condition is present in a subquery. _________________________

Question 60

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

A) single-row subquery
B) multiple-column subquery
C) multiple-row subquery
D) all of the above

Question 61

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

A) / /
B) " "
C) | |
D) ( )

Question 62

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

A) GROUP BY
B) HAVING
C) WHERE
D) ORDER BY

Question 63

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

A) multiple-row
B) multiple-column
C) single-row
D) none of the above

Question 64

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

A) IN
B) >ANY
C) D) >ALL

Question 65

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

A) single-row
B) multiple-row
C) multiple-column
D) inline view

Question 66

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

A) IN
B) ANY
C) ALL
D) all of the above

Question 67

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

A) multiple-row
B) correlated
C) uncorrelated
D) single-row

Question 68

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

A) single-row
B) multiple-row
C) multiple-column
D) inline view

Question 69

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

A) multiple-row
B) single-row
C) correlated
D) multiple column

Question 70

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

A) correlated
B) single-row
C) multiple-row
D) uncorrelated

Question 71

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

A) inner
B) outer
C) correlated
D) uncorrelated

Question 72

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?

A) IN and ANY
B) IN and ALL
C) ALL and ANY
D) EXISTS and IN

Question 73

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.

A) GROUP BY
B) WHERE
C) HAVING
D) FROM

Question 74

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

A) more than the highest
B) less than the highest
C) more than the lowest
D) less than the lowest

Question 75

A subquery must include a(n) ____ clause.

A) SELECT
B) FROM
C) WHERE
D) both a and b

Question 76

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

A) single-row
B) multiple row
C) multiple-column
D) correlated

Question 77

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

A) GROUP BY
B) HAVING
C) ORDER BY
D) WHERE

Question 78

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

A) single-row
B) multiple-row
C) multiple-column
D) correlated

Question 79

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

A) single-row
B) multiple-row
C) correlated
D) uncorrelated

Question 80

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

A) B) C) >ALL
D) >ANY