CSIT156chap12
multiple-row
A(n) ____ subquery is one that can return several rows of results.
parent query
An outer query is also referred to as a(n) ____ query.
all of the above
Any type of subquery can be used in the ____ clause of a SELECT statement.
line 3
Based on the contents of the BOOKS table, which line in the following SQL statement contains an error?1 SELECT title2 FROM books3 WHERE pubid EXISTS IN4 (SELECT pubid 5 FROM books6 WHERE retail > 41.95);
none of the above
Based on the contents of the BOOKS table, which line of the following SQL statement contains an error?1 SELECT isbn, title2 FROM books3 WHERE pubid =4 (SELECT pubid5 FROM books6 WHERE title = 'SHORTEST POEMS')7 AND retail-cost >8 (SELECT AVG(retail-cost)9 FROM books);
none of the above
Based on the contents of the BOOKS table, which of the following SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?
none of the above
Based on the contents of the BOOKS table, which of the following SQL statements will return an error message?
SELECT customer# FROM customers WHERE state =(SELECT state FROM customers WHERE customer#=1013);
Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?
SELECT customer# FROM customers WHERE NVL(referred, 0) =(SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);
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?
line 4
Based upon the contents of the BOOKS table, which line of the following SQL statement contains an error?1 SELECT title, pubid, cost, retail2 FROM books3 WHERE (pubid, cost)4 (SELECT pubid, cost)5 FROM books6 WHERE pubid = 3);
When searching for all customers who live in the same state as customer# 1007
Based upon the contents of the CUSTOMERS table, which of the following would be the most appropriate use of a subquery?
IN
When a multiple-column subquery is included in the WHERE or HAVING clause of the outer query, which operator is used by the outer query to evaluate the results of the subquery?
IS NULL
Which comparison operator allows you to search for NULL values in a subquery?
=ANY
...
subquery
A complete query nested inside another query is called a(n) ____.
both a and b
A subquery must include a(n) ____ clause.
ORDER BY
A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.
inline view
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) ____.
NVL
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.
HAVING
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.
MERGE statement
In Oracle11g, a(n) ____ allows a series of DML actions to occur
HAVING
Which clause is used when the group results of a subquery need to be restricted, based on some condition?
DML
With a MERGE statement, a series of ____ actions can occur with a single SQL statement.
single-row
The <= operator is referred to as a(n) ____ operator
single-row
The <> operator is referred to as a(n) ____ operator.
less than the lowest
The <ALL operator indicates that a value must be ____ value returned by the subquery
single-row
The = operator is referred to as a(n) ____ operator
single-row
The > operator is referred to as a(n) ____ operator.
more than the highest
The >ALL operator indicates that a value must be ____ value returned by the subquery.
none of the above
The EXISTS operator must be listed ____.
IN
The ____ operator indicates that the records processed by the outer query must match one of the values returned by the subquery
EXISTS
The ____ operator is used to determine whether a condition is present in a subquery
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);
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;
single-row
The following SQL statement contains which type of subquery? SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;
correlated
The following SQL statement contains which type of subquery?SELECT title FROM books WHERE EXISTS(SELECT isbn FROM orderitems WHERE books.isbn = orderitems.isbn);
multiple-row
The following SQL statement contains which type of subquery?SELECT title, retail, category FROM booksWHERE retail IN (SELECT MAX(retail) FROM books GROUP BY category);
multiple-column
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 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);
single-row
The operators =, <, >, <=, >=, and <> are referred to as ____ operators.
subquery
The outer query receives its input from the ____.
outer
The results of a subquery are passed back as input to the ____ query.
all of the above
Which of the following can be used in a WHERE clause?
( )
Which of the following must be used to separate a subquery from the outer query?
all of the above
Which of the following operators is used with a multiple-row subquery?
multiple-row subquery
Which of the following subqueries returns more than one row of results to the outer query?
correlated subquery
Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?
<ANY
Which operator will instruct Oracle10g to list all records with a value that is less than the highest value returned by the subquery?
>ALL
Which operator will instruct Oracle11g to list all records with a value that is more than the highest value returned by the subquery?
ALL and ANY
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?