Is640 Chapter 12
Multiple choice 1 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');
1. D d. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');
Review 3 What happens if a single-row subquery returns more than one row of results?
An error message is returned.
Review question 6 Why might a MERGE statement be used?
Answers will vary. Example: Replicate data for reporting purposes to separate transaction and reporting processing to maintain efficiency of transactional processing.
Syntax 6 =ANY
Equal to any value returned by the subquery (same as IN)
Review question 2 What comparison operators are required for multiple-row subqueries?
IN, ALL, or ANY
Syntax 7 [NOT] EXISTS
Row must match a value in the subquery
Hands on assignment 1 1. List the book title and retail price for all books with a retail price lower than the average retail price of all books sold by JustLee Books.
1. SELECT title, retail FROM books WHERE retail < (SELECT AVG(retail) FROM books);
Hands on assignment 10 10. Determine which books were published by the publisher of The Wok Way to Cook.
10. SELECT title FROM books WHERE pubid = (SELECT pubid FROM books WHERE title = 'THE WOK WAY TO COOK');
Multiple choice 16 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.
16. d. 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.
Multiple choice 17 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.
17. c. c. WHEN NOT MATCHED
Multiple choice 18 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.
18. b b. The outer query removes duplicates in the subquery's Order# list.
Multiple choice 2 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);
2 c. c. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
Hands on assignment 2 2. Determine which books cost less than the average cost of other books in the same category.
2. SELECT a.title, b.category, a.cost FROM books a, (SELECT category, AVG(cost) averagecost FROM books GROUP BY category) b WHERE a.category = b.category AND a.cost < b.averagecost;
Hands on assignment 3 3. Determine which orders were shipped to the same state as order 1014.
3. SELECT order# FROM orders WHERE shipstate = (SELECT shipstate FROM orders WHERE order# = 1014);
Hands on assignment 4 4. Determine which orders had a higher total amount due than order 1008.
4. SELECT oi.order#, SUM(oi.quantity*oi.paideach) FROM orderitems oi, books b WHERE oi.isbn = b.isbn GROUP BY oi.order# HAVING SUM(oi.quantity*oi.paideach) > (SELECT SUM(oi.quantity*oi.paideach) FROM orderitems oi, books b WHERE oi.isbn = b.isbn AND oi.order# = 1008); Note: Could have more than one book matching the highest sales
Hands on assignment 5 5. Determine which author or authors wrote the books most frequently purchased by customers of JustLee Books.
5. SELECT lname, fname FROM bookauthor JOIN author USING (authorid) WHERE isbn IN (SELECT isbn FROM orderitems GROUP BY isbn HAVING SUM(quantity) = (SELECT MAX(COUNT(*)) FROM orderitems GROUP BY isbn) ); Note: Could have more than one book matching the highest sales
Hands on assignment 6 6. List the title of all books in the same category as books previously purchased by customer 1007. Don't include books this customer has already purchased.
6. SELECT title FROM books WHERE category IN (SELECT DISTINCT category FROM books JOIN orderitems USING (isbn) JOIN orders USING(order#) WHERE customer# = 1007) AND isbn NOT IN (SELECT isbn FROM orders JOIN orderitems USING (order#) WHERE customer# = 1007);
Hands on assignment 7 7. List the shipping city and state for the order that had the longest shipping delay.
7. SELECT shipcity, shipstate FROM orders WHERE shipdate-orderdate = (SELECT MAX(shipdate-orderdate) FROM orders);
Hands on assignment 8 8. Determine which customers placed orders for the least expensive book (in terms of regular retail price) carried by JustLee Books.
8. SELECT customer# FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) JOIN books USING (isbn) WHERE retail = (SELECT MIN(retail) FROM books);
Hands on assignment 9 9. Determine the number of different customers who have placed an order for books written or cowritten by James Austin.
9. SELECT COUNT(DISTINCT customer#) FROM orders JOIN orderitems USING (order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN bookauthor USING (isbn) JOIN author USING (authorid) WHERE lname = 'AUSTIN' AND fname = 'JAMES');
Review question 1 What's the difference between a single-row subquery and a multiple-row subquery?
A single-row subquery can return only one row of results; a multiple-row subquery can return several rows of results.
Review question 5 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?
By using a table alias assigned to the subquery's results
Syntax 4 <ALL
Less than the lowest value returned by the subquery
Multiple choice 13 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.
MC 13. c. 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
Multiple choice 14 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.
MC 14. b. b. The value returned by the inner query is to be compared to grouped data in the outer query.
Multiple choice 15 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';
MC 15. b. SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK');
Multiple choice 19 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 numbers of all customers who haven't placed an order are displayed.
MC 19. b. b. The outer query displays no rows in its results because the subquery passes a NULL value to the outer query.
Multiple choice 20 20. Which operator is used to process a correlated subquery? a. EXISTS b. IN c. LINK d. MERGE
MC 20 a. EXISTS
Multiple choice 3 3. Which of the following operators is considered a single-row operator? a. IN b. ALL c. <> d. <>ALL
MC 3. c. c. <>
Multiple choice 4 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);
MC 4. d. d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);
Multiple choice 5 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
MC 5. a. a. SELECT title FROM books WHERE retail<(SELECT cost FROM books WHERE isbn = '9959789321');
Multiple choice 6 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
MC 6. c. 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.
Multiple choice 8 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
MC 8. c. c. when you need to find the titles of all books shipped on the same date as an order placed by a particular customer
Multiple choice 10 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
MC. 10. a a. >ALL
Multiple choice 11 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#);
MC. 11. a. 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#;
Multiple choice 12 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.
MC. 12 b. b. The equals (=) comparison operator can't be used with a subquery that returns more than one row of results.
Multiple 7 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');
MC. 7. a. a. SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
Syntax 8 DML Action with a merge statement Conditionally performs a series of DML actions
MERGE INTO books_l a USING books_2 b ON (a. isbn = b.isbn) WHEN MATCHED THEN UPDATE SET a. retail = b. retail, a.category = b.category WHEN NOT MATCHED THEN INSERT (isbn, title, pubdate, retail, category) VALUES (b.isbn, b.title, b.pubdate, b.retail, b.category);
Syntax 3 >ALL
More than the highest value returned by the subquery
Syntax 5 >ANY
More than the lowest value returned by the subquery
Review Question 4 Which SQL clause(s) can't be used in a subquery in the WHERE or HAVING clauses?
ORDER BY clause
Syntax 1 Correlated subquery: References a column in the outer query. Executes the subquery once for every row in the outer query.
SELECT title FROM books b WHERE b.isbn IN (SELECT isbn FROM orderitems o WHERE b.isbn = o.isbn);
Syntax 2 Uncorrelated subquery: Executes the subquery first and passes the value to the outer query.
SELECT title FROM books b, orderitems o WHERE books isbn IN (SELECT isbn FROM orderitems) AND b.isbn = o.isbn;
Review question 7 How can Oracle 12c determine whether clauses of a SELECT statement belong to an outer query or a subquery?
The subquery is enclosed in parentheses
Review question 10 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
Review question 9 What's the difference between correlated and uncorrelated subqueries?
When an uncorrelated subquery is processed, the inner query is executed once, and then the outer query is processed, using the inner query's results. A correlated subquery references a column from the outer query, so the subquery is executed for each row in the outer query.
Review 8 When should a subquery be nested in a HAVING clause?
When the subquery results are compared with a group function or aggregate data
Multiple choice 9 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));
mc 9 D. 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));