QUIZ: Sub-queries and MERGE Statement

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

<ANY

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

=ANY

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

EXISTS

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

False

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?

SELECT customer# FROM customers WHERE NVL(referred, 0) = (SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);

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

True

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

True

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

True

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

True

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

True

The NVL function can be used in a subquery.

True

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.

True

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.

True

A MERGE statement containing an UPDATE and an INSERT clause is also called a(n) ____________________ statement.

UPSERT

Based upon the contents of the CUSTOMERS table, which of the following would be the most appropriate use of a subquery?

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

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

multiple-column

A(n) ____________________ subquery is a nested query that returns more than one row of results.

multiple-row

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

multiple-row

Based on the contents of the BOOKS table, which line of the following SQL statement contains an error?

none of the above

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

outer

The results of a subquery are passed back to the ____________________ query.

outer

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

parent query

A subquery must be enclosed in a set of ____________________.

parentheses

A(n) ____________________ subquery can return only one row of results to the outer query.

single-row

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

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

single-row

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

With a(n) ____________________ subquery, the inner query is executed first and the results are passed back to the outer query.

uncorrelated

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

HAVING

If a multiple-column subquery is contained in the WHERE or HAVING clause of the outer query, the ____________________ operator must be used.

IN

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

IN

Valid multiple-row operators include ANY, ALL, and ____________________.

IN

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

inline view

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

all of the above

A subquery must include a(n) ____ clause.

both a and b