Deck 12: Subqueries and Merge Statements

Question 1

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.​

Answer 1

True

Question 2

A subquery is a complete query nested inside another query.

Answer 2

True

Question 3

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

Answer 3

False

Question 4

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

Question 5

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

Question 6

The

Question 7

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

Question 8

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 9

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

Question 10

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 11

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

Question 12

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.​

Question 13

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

Question 14

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

Question 15

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

Question 16

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

Question 17

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

Question 18

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

Question 19

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

Question 20

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

Question 21

A(n)single-row subquery can be nested in a(n)SELECT clause._________________________

Question 22

The

Question 23

The greater than operator,>,is a valid operator for multiple-row subqueries._________________________​

Question 24

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

Question 25

A subquery must be enclosed in a set of quotation marks._________________________​

Question 26

A subquery is required when the condition for the inner query is based upon an unknown._________________________​

Question 27

In Oracle12c,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 28

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

Question 29

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 30

IN is a valid multiple-row subquery operator._________________________​

Question 31

The results of the subquery are passed to the inner query._________________________

Question 32

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

Question 33

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

Question 34

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

Question 35

Valid multiple-row operators include =,>,<,>=,<=._________________________​

Question 36

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

Question 37

The NVL function can be used in a subquery.​

Question 38

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 39

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

Question 40

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

Question 41

A(n)correlated subquery is when the outer query is executed first,and then the inner query is executed._________________________​

Question 42

The outer query receives its input from the ____.​

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

Question 43

A(n)uncorrelated subquery is when the outer query is executed first,then the inner query is executed._________________________​

Question 44

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 45

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

Question 46

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

Question 47

A(n)correlated subquery references one or more columns from the outer query._________________________​

Question 48

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 single-quotation marks._________________________​

Question 49

The =ANY operator yields the same results as using the IN multiple-row operator._________________________​

Question 50

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

Question 51

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 52

A multiple-column subquery nested in the SELECT clause of the outer query is known as an inline view._________________________​

Question 53

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

Question 54

You can include multiple subqueries in a SELECT statement._________________________​

Question 55

The IN operator is valid for multiple-row subqueries._________________________​

Question 56

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

Question 57

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 58

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 59

In Oracle12c,subqueries in a WHERE clause can be nested to a depth of 2._________________________​

Question 60

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 61

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

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

Question 62

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

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

Question 63

​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 64

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

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

Question 65

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

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

Question 66

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

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

Question 67

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

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

Question 68

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

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

Question 69

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 70

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

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

Question 71

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 72

A subquery must include a(n)____ clause.​

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

Question 73

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

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

Question 74

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 75

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

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

Question 76

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 77

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

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

Question 78

​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 79

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 80

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

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