1 / 55

Chapter 12 Subqueries and Merge Statements (up to p.451)

Chapter 12 Subqueries and Merge Statements (up to p.451). Jason C. H. Chen , Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu. FOCUS – most of them have been studied. Three topics (Learn up to p. 451): Single-Row subquery

kenaz
Download Presentation

Chapter 12 Subqueries and Merge Statements (up to p.451)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 12Subqueries and Merge Statements(up to p.451) Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

  2. FOCUS – most of them have been studied • Three topics (Learn up to p. 451): • Single-Row subquery • Multiple-Row subquery • Multiple-Column subquery • We will focus on the following: • Single-Row Subquery in a HAVING Clause (Query#4) • Multiple-Row Subquery in “IN” (Query#7) • Multiple-Column Subquery in a FROMClause (Query#11 – revisit Query#7)

  3. Objectives • Determine when using a subquery is appropriate • Identify which clauses can contain subqueries • Distinguish between an outer query and a subquery • Use a single-row subquery in a WHERE clause • Use a single-row subquery in a HAVING clause • Use a single-row subquery in a SELECT clause • Distinguish between single-row and multiple-row comparison operators • Use a multiple-row subquery in a WHERE clause

  4. Objectives (continued) • Use a multiple-row subquery in a HAVING clause • Use a multiple-column subquery in a WHERE clause • Create an inline view using a multiple-column subquery in a FROM clause • Compensate for NULL values in subqueries • Distinguish between correlated and uncorrelated subqueries • Nest a subquery inside another subquery • Use a subquery in a DML action • Process multiple DML actions with a MERGE statement

  5. Refresh the Database • 1. Run the following script file • Start c:\oradata\chapter12\JLDB_Build_12.sql • 2. We will focus on the following: • Single-Row Subquery in a HAVING Clause (Query#4) • Multiple-Column Subquery in a FROM Clause

  6. Creating Nested Queries • Used to select results based on the result of a query • Consists of a main query and one or more subqueries. • Main/outer query: first query that appears in the SELECT command • Subqueryretrieves values that the main query’s search condition must match that return one value that return one value

  7. Subqueries and Their Rules/Uses Subquery – a query nested inside another query and used when a query is based on an unknown value. • A subquery must be complete query in itself – it requires SELECT and FROM clauses • A subquery, except one in the FROM clause, can’t have an ORDER BY clause (on the outer query’s last clause). • Must be enclosed in parentheses to separate it from the outer/main query • Place on right side of comparison operator

  8. Types of Subqueries Table 12-1 Topics Covered in This Chapter

  9. I. Single-Row Subqueries • Can only return one result to the outer query • Operators include =, >, <, >=, <=, < >

  10. Query: List all computer books with a retail price higher than the book “Database Implementation” First, find out the “cost” of book “Database Implementation” Next, plug in the “found cost” into the second query -- chapter 12, Figure 12-2; p.430 SELECT category, title, cost FROM books WHERE cost > 31.4 AND category = 'COMPUTER'; Figure 12-3 A single-row subquery

  11. Single-Row Subquery in a WHERE Clause Query1: List all computer books with a retail price higher than the book “Database Implementation” Subquery – a query nested inside another query and used when a query is based on an unknown value. -- chapter 12, Figure 12-3; p.431 SELECT category, title, cost FROM books WHERE cost > AND category = 'COMPUTER' Only one value should be returned from the inner query (SELECT cost FROM books WHERE title = 'DATABASE IMPLEMENTATION’)

  12. Query2: List title of the most expensive book sold by JustLee Books (incorrect example) Figure 12-4 Flawed query: attempt to determine the book with the highest retail value

  13. Query2: List title of the most expensive book sold by JustLee Books (a correct example) SELECT title, retail FROM books WHERE retail = (SELECT MAX(retail) FROM books); Figure 12-5 Query to determine the title of the most expensive book

  14. Query3: List title of all books published by publisher of ‘Big Bear and Little Dove’ that generate more than the average profit Tasks: two unknown values: 1) the pubid of ‘Big Bear and Little Dove’, 2) the average profit of the all books. SELECT isbn, title FROM books WHERE pubid = AND retail-cost > (SELECT pubid FROM books WHERE title = 'Big Bear and Little Dove') (SELECT AVG(retail-cost) FROM books); Figure 12-6 SELECT statement with two single-row subqueries

  15. Query4: List all book categories returning a higher average profit than the ‘Literature’ category. Three steps are needed for this task: • 1. calculate the average profit for all ‘Literature’ books. • 2. calculate the average profit for each category. • 3. compare the average profit for each category with the average profit for the ‘Literature’ category.

  16. Single-Row Subquery in a HAVING Clause Query4: List all book categories returning a higher average profit than the ‘Literature’ category. • Required when returned value is compared to grouped data #2 #1 #3 Figure 12-7 Single-row subquery nested in a HAVING clause

  17. Single-Row Subquery in a SELECT Clause Query5: Compare the price of each book in inventory against average price of all books in inventory. • Replicates subquery value for each row displayed Figure 12-8 Single-row subquery in a SELECT clause

  18. Single-Row Subquery in a SELECT Clause (cont.) Query6: List the difference between each book price and the average. • Replicates subquery value for each row displayed Figure 12-9 Use a subquery in a calculation in the SELECT clause

  19. II. Multiple-Row Subqueries • Return more than one row of results • Require use of IN, ANY, ALL, or EXISTS operators

  20. ANY and ALL Operators • Combine with arithmetic operators Table 12-2 ALL and ANY Operator Combinations

  21. Multiple-Row Subquery in a WHERE Clause Query7: List book titles, retail value and category that match the highest retail value for any book category • Determine the price of the most expensive book in each category • The maximum retail price in each category is to the WHERE clause of the outer query (more than one) • The outer query compares each book’s price to the prices from #2 • If a book’s rail price matches one of the prices returned, the book’s title, retail price, and category are displayed in the output # this part will be executed first -- chapter 12, Figure 12-10; p.438 SELECT title, retail, category FROM books WHERE retail ORDER BY category; IN (SELECT MAX(retail) FROM books GROUP BY category) Note: Could use IN operator or =ANY

  22. Multiple-Row Subquery in a WHERE Clause SQL> SELECT MAX(retail) 2 FROM books 3 GROUP BY category; MAX(RETAIL) ----------- 75.95 28.75 59.95 39.95 31.95 30.95 89.95 29.95 8 rows selected. Figure 12-10 Multiple-row subquery with the IN operator

  23. Multiple-Row Subquery in a WHERE Clause Q: what might be a problem on this query? A: retail might match “MAX” from a different category. Q: how to solve the problem? A: Use “Multiple-column” subquery (see next session). Figure 12-10 Multiple-row subquery with the IN operator

  24. Multiple-Row Subquery in a WHERE Clause (cont.) Query8: List all books with a retail price less than the most expensive book in the Cooking category. Practice other examples with >ALL, <ALL and >ANY Figure 12-14 Using <ANY operator

  25. Query9: Check whether any customer’s recently placed order has a total amount due greater than the total amount due for every order placed recently by customers in Florida. Multiple-Row Subquery in a HAVING Clause Q: try to use “ANY” and see the result. Highest ---SUBQUERY SELECT SUM( quantity paideach) FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) WHERE state = 'FL' GROUP BY order#; SUM(QUANTITY*PAIDEACH) ---------------------- 106.85 85.45 54.5 17.9 75.9 Figure 12-17 Multiple-row subquery in a HAVING clause

  26. ---SUBQUERY SELECT SUM( quantity paideach) FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) WHERE state = 'FL' GROUP BY order#; SUM(QUANTITY*PAIDEACH) ---------------------- 106.85 85.45 54.5 17.9 75.9 SQL> SELECT order#, SUM( quantity * paideach) 2 FROM orderitems 3 HAVING SUM( quantity * paideach) > 4 ANY (SELECT SUM( quantity * paideach) 5 FROM customers JOIN orders USING (customer#) 6 JOIN orderitems USING (order#) 7 WHERE state = 'FL' 8 GROUP BY order#) 9 GROUP BY order#; ORDER# SUM(QUANTITY*PAIDEACH) ---------- ---------------------- 1000 19.95 1001 117.4 1002 111.9 1003 106.85 1004 170.9 1005 39.95 1006 54.5 1007 335.85 1008 39.9 1009 41.95 1010 55.95 Lowest ORDER# SUM(QUANTITY*PAIDEACH) ---------- ---------------------- 1011 85.45 1012 166.4 1013 55.95 1014 44 1015 19.95 1016 85.45 1018 75.9 1019 22 1020 19.95

  27. III. Multiple-Column Subqueries • Return more than one column in results • Creates a temporary table (or inline view) • Can return more than one column to the outer query (more than one column from the subquery) • Column list on the left side of operator must be in parentheses • Use the IN operator for WHERE and HAVING clauses

  28. Multiple-Column Subquery in a FROM Clause Query10: List all books that have a higher-than-average selling price compared with other books in the same category. SELECT category, AVG(retail) cataverage FROM books GROUP BY category; CATEGORY CATAVERAGE ------------ ---------- COMPUTER 52.85 COOKING 24.35 CHILDREN 34.45 LITERATURE 39.95 BUSINESS 31.95 FITNESS 30.95 FAMILY LIFE 55.975 SELF HELP 29.95 it is considered a new table of “a” Figure 12-19 Multiple-column subquery in a FROM clause

  29. Multiple-Column Subquery in a FROM Clause (2nd solution) Figure 12-20 Using a Join with a multiple-column subquery in the FROM clause

  30. Multiple-Column Subquery in a WHERE Clause Query11(#7): List book titles, retail value and category that match the highest retail value for any book category - revisit (the right solution) Retail might match “MAX” from a different category. Hoewver, this example returns the same answer as the right one. Returns multiple columns for evaluation Figure 12-10: Multiple-row subquery with the IN operator Figure 12-21 Multiple-column subquery in a WHERE clause

  31. NVL Function (also see p. 359) • The NVL function is to address problems caused when performing arithmetic operations with fields that might contain NULL values. • NULL value is the absence of data, not a blank space or a zero. • NVL(x,y) where y represents the value to substitute if x is NULL.

  32. NULL Values Query11: List all customers (customer#) who referred customer 1005 has referred any other customers to JustLee Books. Q: what causes the problem? -- chapter 12, Figure 12-22; p.449 SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE customer# = 1005); SELECT referred FROM customers WHERE customer# = 1005; REFERRED ---------- Figure 12-22 Flawed query: NULL results from a subquery

  33. SQL> select customer#, referred from customers; CUSTOMER# REFERRED ---------- ---------- 1001 1002 1003 1004 1005 1006 1007 1003 1008 1009 1003 1010 1011 1012 1013 1006 1014 1015 1016 1010 1017 1018 1019 1003 1020 20 rows selected. When a subquery might return NULL values, use NVL function Figure 12-23 Using the NVL function to handle NULL values

  34. IV. Uncorrelated Subqueries • Processing sequence • Inner query is executed first • Result is passed to outer query • Outer query is executed • Most of subqueries we studied are uncorrelated subqueries.

  35. Nested Subqueries • Maximum of 255 subqueries if nested in the WHERE clause • No limit if nested in the FROM clause • Innermost subquery is resolved first, then the next level, etc.

  36. Nested Subqueries (continued) Query12: List the name of the customer who has ordered the most books on one order (not including multiple quantities of the same book). • Innermost is resolved first (A), then the second level (B), then the outer query (C) -- chapter 12, Figure 12-27; p.454 SELECT customer#, lastname, firstname FROM customers JOIN orders USING (customer#) WHERE order# IN (SELECT order# FROM orderitems GROUP BY order# HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM orderitems GROUP BY order#)); (A): returns 4 (B): returns 1007 1012 Figure 12-27 Nested subqueries

  37. Subquery in a DML action Figure 12-28 An UPDATE statement using a subquery

  38. Exercises • Practice all the examples in the text. • A Script file is available on the Bb (file name: ch12Queries.sql) • After completing all examples, do the HW. 3. SELECT order# FROM orders WHERE shipstate = (SELECT shipstate FROM orders WHERE order# = 1014); • In-class Exercise • #3 (p.468)

  39. Homework - Hands-On Assignments Email me with one attachment (Oracle_ch12_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch12 (or Bmis441-02_Oracle_ch12) Read and Practice all examples on Chapters 12 • 1. Run the script files (in the folder \oradata\chapter12\): JLDB_Build_12.sql • 2. Read Oracle assignment and create a script file Oracle_ch12_Lname_Fname.sql for questions (#1,2, 5, 10; p. 468) on “Hands-on Assignments”. Use appropriate COLUMN statements to produce readable outputs • 3. Execute and test one problem at a time and make sure they are all running successfully. • 4. When you done, spool the script files (see next slide for spooling instructions) and email the file (Oracle_ch12_Spool_Lname_Fname.txt) to me by the midnight before the next class.

  40. How to Spool your Script and Output Files After you tested the script file of Oracle_ch12_Lname_Fname.sql successfully, follow the instructions below to spool both script and output files: Step 0. Run the following script file from SQL*Plus (since you have created JLDB tables) • Start c:\oradata\chapter12\JLDB_Build_12.sql • 1. type the following on SQL> • Spool c:\oradata\Oracle_ch12_Spool_Lname_Fname.txt (make sure your name is entered) • 2. open Oracle_ch12_Lname_Fname.sql that you already tested • 3. copy and paste all the SQL commands (including all comments) to the SQL*PLUS • 4. type Spool Off on the SQL> The output should contain your personal information, all SQL commands and their solution on the .txt file and saved in C: drive (oradata\ folder) Email me with the spooled file (.txt) with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch12 (or Bmis441-02_Oracle_ch12)

  41. Summary • A subquery is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query • The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum • Subqueries are completed first; the result of the subquery is used as input for the outer query • A single-row subquery can return a maximum of one value • Single-row operators include =, >, <, >=, <=, and <> • Multiple-row subqueries return more than one row of results

  42. Summary (continued) • Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS • Multiple-column subqueries return more than one column to the outer query • NULL values returned by a multiple-row or multiple-column subquery will not present a problem if the IN or =ANY operator is used • Correlated subqueries reference a column contained in the outer query • Subqueries can be nested to a maximum depth of 255 subqueries in the WHERE clause of the parent query

  43. Summary (continued) • With nested subqueries, the innermost subquery is executed first, then the next highest level subquery is executed, and so on, until the outermost query is reached • A MERGE statement allows multiple DML actions to be conditionally performed while comparing data of two tables

  44. V. Correlated Subqueries (SKIP) • Inner query is executed once for each row processed by the outer query • Inner query references the row contained in the outer query

  45. Correlated Subqueries (continued) Figure 12-25 Correlated subquery

  46. Creating Nested Queries • Used to select results based on the result of a query • Consists of a main query and one or more subqueries. • Main query: first query that appears in the SELECT command • Subqueryretrieves values that the main query’s search condition must match that return one value

  47. ( ) Subquery Returns Single Value Query: List all students who have the same S_CLASS value as student Jorge Perez. SELECT s_last, s_first FROM student WHERE s_class = SELECT s_class FROM student WHERE s_last = 'Perez' AND s_first = 'Jorge';

  48. Nested Query WhereSubquery Returns Multiple Values • Syntax: SELECT column1, column2, … FROM table1, table2, … WHERE join conditions AND search_column1 IN (SELECT column1 FROM table1, table2, … WHERE search and join conditions) Subquery that returns multiple values

  49. Nested Query WhereSubquery Returns Multiple Values • Display the names of al students wwho hae enrolled in the same course sections as Jorge Perez. SELECT DISTINCT s_last, s_first FROM student, enrollment WHERE student.s_id = enrollment.s_id AND c_sec_id IN (SELECT c_sec_id FROM student, enrollment WHERE student.s_id = enrollment.s_id AND s_last = 'Perez' AND s_first = 'Jorge');

More Related