SubQueries and MERGE Statements
Contents of the CUSTOMERS table 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. >
Which of the following must be used to separate a subquery from the outer query?
()
In Oracle12c, subqueries nested in a WHERE clause can be nested to a depth of ____________________ subqueries.
255
Which of the following operators is the equivalent of the IN comparison operator?
=ANY
Any type of subquery can be used in the ____ clause of a SELECT statement.
ALL OF THE ABOVE
Which of the following operators is used with a multiple-row subquery?
ALL OF THE ABOVE
With a MERGE statement, a series of ____ actions can occur with a single SQL statement.
DML
The ____________________ operator is used to determine whether a condition is present in a subquery.
EXISTS
The ____ operator is used to determine whether a condition is present in a subquery.
EXISTS
A multiple-column subquery cannot be nested in a WHERE clause.
FALSE
A multiple-column subquery nested in the SELECT clause of the outer query is known as an inline view. _________________________
FALSE
A subquery is required when the condition for the inner query is based upon an unknown. _________________________
FALSE
A subquery nested in a WHERE clause can only be on the left side of the comparison operator. _________________________
FALSE
A(n) uncorrelated subquery is when the outer query is executed first, then the inner query is executed. _________________________
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 double-quotation marks.
FALSE
If a subquery's result must be compared with a group function, you must nest the inner query in the ____________________ clause of the outer query.
HAVING
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
Valid multiple-row operators include ANY, ALL, and ____________________.
IN
Which comparison operator allows you to search for NULL values in a subquery?
IS NULL
Contents of the BOOKS table Based upon the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT title, pubid, cost, retail 2 FROM books 3 WHERE (pubid, cost) 4 (SELECT pubid, cost) 5 FROM books 6 WHERE pubid = 3);
LINE 4
With a(n) ____________________ statement, a series of DML actions can occur with a single SQL statement.
MERGE
In Oracle12 c, a(n) ____ allows a series of DML actions to occur.
MERGE statement
A subquery is a(n) ____________________ query — one complete query inside another query
NESTED
Contents of the BOOKS table 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
A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.
ORDER BY
Contents of the CUSTOMERS table 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 state = (SELECT state FROM customers WHERE customer#=1013);
The = operator is referred to as a(n) ____ operator.
SINGLE-ROW
A complete query nested inside another query is called a(n) ____
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 group function can be used in a(n) inline view. _________________________
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 subquery nested in a SELECT clause cannot contain an ORDER BY clause.
TRUE
A(n) GROUP BY clause can be used in a subquery.
TRUE
A(n) correlated subquery is when the outer query is executed first, and then the inner query is executed. _________________________
TRUE
A(n) correlated subquery references one or more columns from the outer query. _________________________
TRUE
IN is a valid multiple-row subquery operator. _________________________
TRUE
If the value of the EXISTS operator is ____________________, then the rows meeting the condition are displayed.
TRUE
In Oracle12c, subqueries can be nested to a depth of 255 in a WHERE clause.
TRUE
The EXISTS operator can be used with multiple-row subqueries.
TRUE
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.
TRUE
You can include multiple subqueries in a SELECT statement. _________________________
TRUE
If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator.
TRUE
With a(n) ____________________ subquery, the inner query is executed first and the results are passed back to the outer query.
UNCORRELATED
The following SQL statement contains which type of subquery? SELECT title FROM books WHERE EXISTS (SELECT isbn FROM orderitems WHERE books.isbn = orderitems.isbn);
correlated
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 correlated subquery is one in which the inner query is executed first, and then the outer query is executed.
false
A multiple-column subquery cannot be nested in a WHERE clause.
false
A subquery can only be nested in the WHERE or HAVING clause of the outer query.
false
A subquery is required when the condition for the inner query is based upon an unknown. _________________________
false
A subquery must be enclosed in a set of quotation marks. _________________________
false
A subquery nested in a WHERE clause can only be on the left side of the comparison operator. _________________________
false
A(n) uncorrelated subquery is when the outer query is executed first, then the inner query is executed. _________________________
false
The EXISTED operator is used to determine whether a condition is present in a subquery. _________________________
false
Valid multiple-row operators include =, >, <, >=, <=. _________________________
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
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?
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
A(n) ____________________ subquery returns more than one column to the outer query.
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
multiple-column
A subquery is a(n) ____________________ query — one complete query inside another query
nested
A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.
order by
A subquery, except one in the FROM clause, cannot contain a(n) ____________________ clause.
order by
The = operator is referred to as a(n) ____ operator.
single-row
The operators =, <, >, <=, >=, and <> are referred to as ____ operators.
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
A(n) ____________________ subquery can return only one row of results to the outer query.
single-row or single row
A complete query nested inside another query is called a(n) ____
subquery
A(n) ____________________ is a nested query - one complete query inside another query.
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 single-row subquery can be nested in the SELECT clause of the outer function.
true
A subquery is required when the condition for the outer query is based upon an unknown.
true
A subquery, except one in the FROM clause, can't have an ORDER BY clause. _________________________
true
A(n) correlated subquery is when the outer query is executed first, and then the inner query is executed. _________________________
true
IN is a valid multiple-row subquery operator. _________________________
true
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.
true
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
true
The EXISTS operator can be used with multiple-row subqueries.
true
The equal sign, =, is a valid single-row operator.
true
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
