Joan Casteel Oracle 11g SQL Chapters 12 Multiple Choice Questions Solution

Joan Casteel Oracle 11g SQL Chapters 12 Multiple Choice Questions Solution

To answer these questions, refer to the tables in the JustLee Books database.
1. Which query identifies customers living in the same state as the customer named Leila Smith?
a. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = ‘SMITH’);
b. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = ‘SMITH’ OR firstname = ‘LEILA’);
c. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = ‘SMITH’ AND firstname = ‘LEILA’ ORDER BY customer);
d. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = ‘SMITH’ AND firstname = ‘LEILA’);

2. Which of the following is a valid SELECT statement?
a. SELECT order# FROM orders WHERE shipdate = SELECT shipdate FROM orders WHERE order# = 1010;
b. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders) AND order# = 1010;
c. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
d. SELECT order# FROM orders HAVING shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);

3. Which of the following operators is considered a single-row operator?
a. IN
b. ALL
c. <>
d. <>ALL

4. Which of the following queries determines which customers have ordered the same books
as customer 1017?
a. SELECT order# FROM orders WHERE customer# = 1017;
b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn = (SELECT isbn FROM orderitems WHERE customer# = 1017);
c. SELECT customer# FROM orders WHERE order# = (SELECT order# FROM orderitems WHERE customer# = 1017);
d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);

5. Which of the following statements is valid?
a. SELECT title FROM books WHERE retail <(SELECT cost FROM books WHERE isbn = ‘9959789321’);
b. SELECT title FROM books WHERE retail = (SELECT cost FROM books WHERE isbn = ‘9959789321’ ORDER BY cost);
c. SELECT title FROM books WHERE category IN (SELECT cost FROM orderitems WHERE isbn = ‘9959789321’);
d. none of the above statements

6. Which of the following statements is correct?
a. 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.
b. 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.
c. 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.
d. none of the above

7. Which of the following queries identifies other customers who were referred to JustLee Books by the same person who referred Jorge Perez?
a. SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = ‘JORGE’ AND lastname = ‘PEREZ’);
b. SELECT referred FROM customers WHERE (customer#, referred) = (SELECT customer# FROM customers WHERE firstname = ‘JORGE’ AND lastname = ‘PEREZ’);
c. SELECT referred FROM customers WHERE (customer#, referred) IN (SELECT customer# FROM customers WHERE firstname = ‘JORGE’ AND lastname = ‘PEREZ’);
d. SELECT customer# FROM customers WHERE customer# = (SELECT customer# FROM customers WHERE firstname = ‘JORGE’ AND lastname = ‘PEREZ’);

8. In which of the following situations is using a subquery suitable?
a. when you need to find all customers living in a particular region of the country
b. when you need to find all publishers who have toll-free telephone numbers
c. when you need to find the titles of all books shipped on the same date as an order placed by a particular customer
d. when you need to find all books published by Publisher 4

9. Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005?
a. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005));
b. 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));
c. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1001 OR 1005));
d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));

10. Which of the following operators is used to find all values greater than the highest value returned by a subquery?
a. >ALL
b. <ALL
c. >ANY
d. <ANY
e. IN

11. Which query determines the customers who have ordered the most books from JustLee Books?
a. 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#;
b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);
c. SELECT customer# FROM orders WHERE MAX(SUM(quantity)) = (SELECT MAX(SUM(quantity) FROM orderitems GROUP BY order#);
d. SELECT customer# FROM orders HAVING quantity = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);

12. Which of the following statements is correct?
a. The IN comparison operator can’t be used with a subquery that returns only one row of results.
b. The equals (=) comparison operator can’t be used with a subquery that returns more than one row of results.
c. In an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed.
d. A subquery can be nested only in the outer query’s SELECT clause.

13. 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%’);
a. It determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same publisher.
b. It lists all publishers and categories containing the value ORACLE.
c. 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.
d. 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.

14. A subquery must be placed in the outer query’s HAVING clause if:
a. The inner query needs to reference the value returned to the outer query.
b. The value returned by the inner query is to be compared to grouped data in the outer query.
c. The subquery returns more than one value to the outer query.
d. None of the above. Subqueries can’t be used in the outer query’s HAVING clause.

15. Which of the following SQL statements lists all books written by the author of The Wok Way to Cook?
a. SELECT title FROM books WHERE isbn IN (SELECT isbn FROM bookauthor HAVING authorid IN ‘THE WOK WAY TO COOK);
b. SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = ‘THE WOK WAY TO COOK’);
c. SELECT title FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = ‘THE WOK WAY TO COOK);
d. SELECT isbn FROM bookauthor HAVING authorid = SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = ‘THE WOK WAY TO COOK’;

16. Which of the following statements is correct?
a. If the subquery returns only a NULL value, the only records returned by an outer query are those containing an equivalent NULL value.
b. A multiple-column subquery can be used only in the outer query’s FROM clause.
c. A subquery can contain only one condition in its WHERE clause.
d. The order of columns listed in the SELECT clause of a multiple-column subquery must be in the same order as the corresponding columns listed in the outer query’s WHERE clause.

17. In a MERGE statement, an INSERT is placed in which conditional clause?
a. USING
b. WHEN MATCHED
c. WHEN NOT MATCHED
d. INSERTs aren’t allowed in a MERGE statement.

18. Given the following query, which statement is correct?
SELECT order# FROM orders
WHERE order# IN (SELECT order# FROM orderitems
WHERE isbn = ‘9959789321’);
a. The statement doesn’t execute because the subquery and outer query don’t reference the same table.
b. The outer query removes duplicates in the subquery’s Order# list.
c. 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.
d. No rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.

19. 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);
a. The SELECT statement fails and returns an Oracle error message.
b. The outer query displays no rows in its results because the subquery passes a NULL value to the outer query.
c. The customer number is displayed for customers whose orders haven’t yet shipped.
d. The customer number of all customers who haven’t placed an order are displayed.

20. Which operator is used to process a correlated subquery?
a. EXISTS
b. IN
c. LINK
d. MERGE