AIT 524 Quiz 10

The operators =, <, >, <=, >=, and <> are referred to as ____ operators.​

​ single-row

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?​

ALL and ANY

A group function cannot be included in the SELECT clause of a single-row subquery.​

False

A multiple-column subquery nested in the SELECT clause of the outer query is known as an inline view.

False

A single-row subquery can return several columns, but only one row, of results to the outer query.

False

A subquery can only be nested in the WHERE or HAVING clause of the outer query.​

False

A(n) uncorrelated subquery is when the outer query is executed first, then the inner query is executed.

False

IN is not a valid operator for a multiple-column subquery.​

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 EXISTED operator is used to determine whether a condition is present in a subquery.

False

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

False

The results of the subquery are passed to the inner query.

False

Valid multiple-row operators include =, >, <, >=, <=.

False

​ The results of the outer query are passed to the inner query.

False

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

A subquery, except one in the FROM clause, cannot contain a(n) ____________________ clause.

ORDER BY

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

A(n) outer query is also referred to as a parent query.

True

A(n) single-row subquery can be nested in a(n) SELECT clause.

True

IN is a valid multiple-row subquery operator.

True

If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison 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

In Oracle12c, subqueries can be nested to a depth of 255 in a WHERE clause.​

True

In Oracle12c, there is no depth limit on the number of subqueries that can be nested in a(n) FROM clause.

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

The EXISTS operator can be used with multiple-row subqueries.​

True

The IN operator can be used with single-row, multiple-row, or multiple-column subqueries.

True

The IN operator is valid for 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, (SELECT AVG(retail) FROM books) FROM books;​

True

Valid single-row operators include =, >, <, >=, <=.​

True

When a multiple-column subquery is included in the outer query's WHERE clause, the column names listed in the WHERE clause must be in the same order as they're listed in the subquery's SELECT clause.​

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 subquero the outer query, the subquery is known as an uncorrelated subquery.​

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

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

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

The >ALL operator indicates that a value must be ____ value returned by the subquery. query?

more than the highest

A(n) ____ subquery is one that can return several rows 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

Which of the following subqueries returns more than one row of results to the outer query?​

multiple-row subquery

A subquery is a(n) ____________________ query — one complete query inside another query

nested

Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title 2 FROM books 3 WHERE pubid = 4 (SELECT pubid 5 FROM books 6 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?

none of the above

The EXISTS operator must be listed ____​

none of the above

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

If a subquery is contained in a WHERE or HAVING clause, the subquery must be on the ____________________ side of the comparison operator.​

right

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

single-row

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

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, (SELECT AVG(retail) FROM books) FROM books;​

single-row

The only type of subquery that can be used in a SELECT clause is a(n) ____________________ subquery.​

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

The outer query receives its input from the ____.​

subquery

​ 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

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

The ____________________ operator indicates that a value must be less than the highest value returned by a subquery.​

<ANY

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 of the following operators is the equivalent of the IN comparison operator?

=ANY

The ____________________ operator indicates that a value must be more than the highest value returned by the subquery.​

>ALL

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

>ALL

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

EXISTS

Which comparison operator allows you to search for NULL values in a subquery?

IS NULL

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

In Oracle12 c, a(n) ____ allows a series of DML actions to occur. ​

MERGE statement

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

True

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

uncorrelated

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

​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

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

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?

IN

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

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

Which of the following operators is used with a multiple-row subquery?​

all of the above

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

With a(n) ____________________ subquery, the subquery is executed once for each row in the outer query.

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