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
