Oracle 12c: SQL - Chapter 12

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 clause is used when the group results of a subquery need to be restricted, based on some condition?

​HAVING

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

(T/F) A group function cannot be included in the SELECT clause of a single-row subquery.

False

(T/F) A multiple-column subquery cannot be nested in a WHERE clause.

False

(T/F) A subquery can only be nested in the WHERE or HAVING clause of the outer query.

False

(T/F) A subquery is required when the condition for the *inner* query is based upon an unknown.

False

(T/F) A subquery nested in a WHERE clause can only be on the *left* side of the comparison operator.

False

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

False

(T/F) Single-row operators can be used with multiple-row subqueries that return only one column of results.

False

(T/F) The greater than operator, >, is a valid operator for *multiple-row* subqueries.

False

(T/F) Valid *multiple-row* operators include =, >, =, <=.

False

(T/F) A correlated subquery is a subquery that is executed once for each row in the outer query.

True

(T/F) 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

(T/F) A group function can be used in a(n) *inline view*.

True

(T/F) A multiple-row subquery can be nested in a HAVING clause.

True

(T/F) A subquery is required when the condition for the outer query is based upon an unknown.

True

(T/F) A subquery nested in a SELECT clause cannot contain an ORDER BY clause.

True

(T/F) A(n) *correlated* subquery references one or more columns from the outer query.

True

(T/F) A(n) *outer* query is also referred to as a parent query.

True

(T/F) If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator.

True

(T/F) 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

(T/F) Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.

True

(T/F) The *=ANY* operator yields the same results as using the IN multiple-row operator.

True

(T/F) The <ALL operator indicates that for a value to be included in the results, it must be less than the lowest value returned by the subquery.

True

(T/F) The equal sign, =, is a valid single-row operator.

True

(T/F) 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

(T/F) 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

A complete query nested inside another query is called a(n) ____

subquery

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

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

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

Any type of subquery can be used in the ____ clause of a SELECT statement.

​all of the above

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

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

(SELECT category, AVG(retail) cataverage FROM books GROUP BY category);

​multiple-column

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

Based on the contents of the BOOKS table, which line of the following SQL statement contains an error?

​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

The results of a subquery are passed back as input to the ____ query.

​outer

An outer query is also referred to as a(n) ____ query.

​parent query

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