DBA QUIZ 14
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 operators =, <, >, <=, >=, and <> are referred to as ____ operators.
single-row
Which of the following must be used to separate a subquery from the outer query?
()
Which operator will instruct Oracle12c to list all records with a value that is less than the highest value returned by the subquery?
<ANY
Which operator will instruct Oracle12c to list all records with a value that is more than the highest value returned by the subquery?
>ALL
Which of the following can be used in a WHERE clause?
ALL OF THE ABOVE
Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?
Correlated Subquery
A multiple-column subquery nested in the SELECT clause of the outer query is known as an inline view. _________________________
FALSE
A subquery must be enclosed in a set of quotation marks. _________________________
FALSE
In Oracle12c, subqueries in a WHERE clause can be nested to a depth of 2. _________________________
FALSE
Single-row operators can be used with multiple-row subqueries that return only one column of results.
FALSE
The <ANY operator indicates that a value must be less than the lowest value returned by the subquery to be included in the results. _________________________
FALSE
The results of the subquery are passed to the inner query. _________________________
FALSE
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. _________________________
FALSE
The results of the outer query are passed to the inner query.
FALSE
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.
HAVING
Which clause is used when the group results of a subquery need to be restricted, based on some condition?
HAVING
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
The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;
INLINE VIEW
In Oracle12 c, a(n) ____ allows a series of DML actions to occur.
MERGE STATEMENT
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
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
Multiple-Column
Based on the contents of the BOOKS table, which of the following SQL statements will return an error message?
NONE OF THE ABOVE
If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.
NVL
The results of a subquery are passed back as input to the ____ query.
OUTER
The <> operator is referred to as a(n) ____ operator.
SINGLE-ROW
The outer query receives its input from the ____.
SUBQUERY
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.
TRUE
A multiple-row subquery can be nested in a HAVING clause.
TRUE
A subquery is required when the condition for the outer query is based upon an unknown.
TRUE
A(n) correlated subquery is when the outer query is executed first, and then the inner query is executed. _________________________
TRUE
A(n) single-row subquery can be nested in a(n) SELECT clause. _________________________
TRUE
IN is a valid multiple-row subquery operator. _________________________
TRUE
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
TRUE
The =ANY operator yields the same results as using the IN multiple-row operator. _________________________
TRUE
Valid single-row operators include =, >, <, >=, <=.
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
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);
uncorrelated
The <ALL operator indicates that a value must be ____ value returned by the subquery.
less than the lowest
