Chapter 12 - Subqueries and Merge Statements
Why might a MERGE statement be used?
it can compare two data sources or tables and determine which rows need updating and which need inserting.
Which of the following operators is considered a single-row operator? IN ALL <> <>ALL
<>
Which of the following operators is used to find all values greater than the highest value returned by a subquery? >ALL <ALL >ANY <ANY IN
>ALL
In a MERGE statement, an INSERT is placed in which conditional clause? USING WHEN MATCHED WHEN NOT MATCHED INSERTS aren't allowed in a MERGE statement.
WHEN NOT MATCHED
In which of the following situations is using a subquery suitable? when you need to find all customers living in a particular region of the country when you need to find all publishers who have toll-free telephone numbers when you need to find the titles of all books shipped on the same date as an order placed by a particular customer when you need to find all books published by Publisher 4
when you need to find the titles of all books shipped on the same date as an order placed by a particular customer
What's the difference between a single-row subquery and a multiple-row subquery?
A single-row subquery is used when the outer query's results are based on a single, unknown value. Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
What happens if a single-row subquery returns more than one row of results?
An error message is returned
What's the difference between correlated and uncorrelated subqueries?
An uncorrelated subquery is where the subquery is executed, its results are passed to the outer query and the the outer query is executed. 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.
Which operator is used to process a correlated subquery? EXISTS IN LINK MERGE
EXISTS
What comparison operators are required for multiple-row subqueries?
IN, ALL, and ANY
Which of the following statements is correct? If a subquery is used in the outer query's FROM clause, the data in the temporary table can't be referenced by clauses used in the outer query. The temporary table created by a subquery in the outer query's FROM clause must be assigned a table alias, or it can't be joined with another table by using the JOIN keyword. If a temporary table is created through a subquery in the outer query's FROM clause, the data in the temporary table can be referenced by another clause in the outer query. none of the above
If a temporary table is created through a subquery in the outer query's FROM clause, the data in the temporary table can be referenced by another clause in the outer query.
What is the purpose of the following query? SELECT isbn, title FROM books WHERE (pubid, category) IN (SELECT pubid, category FROM books WHERE title LIKE '%ORACLE%'); It determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same publisher. It lists all publishers and categories containing the value ORACLE. It lists the ISBN and title of all books belonging to the same category and having the same publisher as any book with the phrase ORACLE in its title. None of the above. The query contains a multiple-row operator, and because the inner query returns only one value, the SELECT statement will fail and return an error message.
It lists the ISBN and title of all books belonging to the same category and having the same publisher as any book with the phrase ORACLE in its title.
Which SQL clause(s) can't be used in a subquery in the WHERE or HAVING clauses?
ORDER BY
Which of the following queries identifies other customers who were referred to JustLee Books by the same person who referred Jorge Perez? SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ'); SELECT referred FROM customers WHERE (customer#, referred) = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ'); SELECT referred FROM customers WHERE (customer#, referred) IN (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ'); SELECT customer# FROM customers WHERE customer# = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
Which query identifies customers living in the same state as the customer named Leila Smith? SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH'); SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' OR firstname = 'LEILA'); SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA' ORDER BY customer); SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');
SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');
Which query determines the customers who have ordered the most books from JustLee Books? SELECT customer# FROM orders JOIN orderitems USING(order#) HAVING SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orders JOIN orderitems USING(order#) GROUP BY customer#) GROUP BY customer#; SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#); SELECT customer# FROM orders WHERE MAX(SUM(quantity)) = (SELECT MAX(SUM(quantity) FROM orderitems GROUP BY order#); SELECT customer# FROM orders HAVING quantity = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);
SELECT customer# FROM orders JOIN orderitems USING(order#) HAVING SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orders JOIN orderitems USING(order#) GROUP BY customer#) GROUP BY customer#;
Which of the following queries determines which customers have ordered the same books as customer 1017? SELECT order# FROM orders WHERE customer# = 1017; SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn = (SELECT isbn FROM orderitems WHERE customer# = 1017); SELECT customer# FROM orders WHERE order# = (SELECT order# FROM orderitems WHERE customer# = 1017); SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);
SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);
Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005? SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005)); SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn < ANY(SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005)); SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1001 OR 1005)); SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));
SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));
Which of the following SQL statements lists all books written by the author of The Wok Way to Cook? SELECT title FROM books WHERE isbn IN (SELECT isbn FROM bookauthor HAVING authorid IN 'THE WOK WAY TO COOK); SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK'); SELECT title FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK); SELECT isbn FROM bookauthor HAVING authorid = SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK';
SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK');
Which of the following is a valid SELECT statement? SELECT order# FROM orders WHERE shipdate = SELECT shipdate FROM orders WHERE order# = 1010; SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders) AND order# = 1010; SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010); SELECT order# FROM orders HAVING shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
Which of the following statements is valid? SELECT title FROM books WHERE retail < (SELECT cost FROM books WHERE isbn = '9959789321'); SELECT title FROM books WHERE retail = (SELECT cost FROM books WHERE isbn = '9959789321' ORDER BY cost); SELECT title FROM books WHERE category IN (SELECT cost FROM orderitems WHERE isbn = '9959789321'); none of the above statements
SELECT title FROM books WHERE retail < (SELECT cost FROM books WHERE isbn = '9959789321');
Which of the following statements is correct? The IN comparison operator can't be used with a subquery that returns only one row of results. The equals (=) comparison operator can't be used with a subquery that returns more than one row of results. In an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed. A subquery can be nested only in the outer query's SELECT clause.
The equals (=) comparison operator can't be used with a subquery that returns more than one row of results.
Given the following SQL statement, which statement is most accurate? SELECT customer# FROM customers JOIN orders USING(customer#) WHERE shipdate-orderdate IN (SELECT MAX(shipdate-orderdate) FROM orders WHERE shipdate IS NULL); The SELECT statement fails and returns an Oracle error message. The outer query displays no rows in its results because the subquery passes a NULL value to the outer query. The customer number is displayed for customers whose orders haven't yet shipped. The customer numbers of all customers who haven't placed an order are displayed.
The outer query displays no rows in its results because the subquery passes a NULL value to the outer query.
Given the following query, which statement is correct? SELECT order# FROM orders WHERE order# IN (SELECT order# FROM orderitems WHERE isbn = '9959789321'); The statement doesn't execute because the subquery and outer query don't reference the same table. The outer query removes duplicates in the subquery's Order# list. The query fails if only one result is returned to the outer query because the outer query's WHERE clause uses the IN comparison operator. No rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.
The outer query removes duplicates in the subquery's Order# list.
How can Oracle 12c determine whether clauses of a SELECT statement belong to an outer query or a subquery?
This first query is the subquery. The subquery's results are passed as input to the Outer query (also called the parent query). The outer query incorporates this value into its calculations to determine the final output.
If a subquery is used in the FROM clause of a query, how are the subquery's results referenced in other clauses of the query?
This temporary table is more formally called an inline view. The subquery's results are treated like any other table in the FROM clause.
What type of situation requires using a subquery?
When a search is based on an unknown value that can be determined by querying the database.
When should a subquery be nested in a HAVING clause?
When the subquery results are compared with a group function or aggregate data.
A subquery must be placed in the outer query's HAVING clause if: The inner query needs to reference the value returned to the outer query. The value returned by the inner query is to be compared to grouped data in the outer query. The subquery returns more than one value to the outer query. None of the above. Subqueries can't be used in the outer query's HAVING clause.
When the subquery's results are compared to grouped data in the outer query, the subquery must be nested in a HAVING clause in the outer query.
