1 / 50

Database Development

Database Development. ORACLE (SQL Components). DDL. JL_D.B. DML. DCL. (Retrieve Data and Produce Information from Multiple Tables ). Chapter 9 Joining Data from Multiple Tables (p.284-296; p.312-330). Jason C. H. Chen , Ph.D. Professor of MIS School of Business Gonzaga University

cree
Download Presentation

Database Development

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. Database Development ORACLE (SQL Components) DDL JL_D.B. DML DCL (Retrieve Data and Produce Information from Multiple Tables)

  2. Chapter 9Joining Data from Multiple Tables(p.284-296; p.312-330) Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

  3. Objectives • Identify a Cartesian join • Create an equality join using the WHERE clause • Create an equality join using the JOIN keyword • Create a non-equality join using the WHERE clause • Create a non-equality join using the JOIN…ON approach

  4. Objectives (continued) • Create a self-join using the WHERE clause • Create a self-join using the JOIN keyword • Distinguish an inner join from an outer join • Create an outer join using the WHERE clause • Create an outer join using the OUTER keyword • Use set operators to combine the results of multiple queries

  5. Creating additional Tables for chapter 9 • Run the following command for creating additional tables: SQL>start c:\oradata\chapter9\JLDB_Build_9.sql • The following new four tables will be added to your database (see next slide for details): • Warehouses, • Publisher2, Publisher3 and • Employees

  6. Additional Database for chapter 9 pk warehouses warehouses pk Publishe2 pk Publishe3 Do we really need to create two additional Publisher tables (i.e., Publisher2 and Publisher3) ? Publishe2 Publishe3 pk Employees

  7. Purpose of Joins • Joins are used to link tables and reconstruct data in a relational database • Joins can be created through: • Conditions in a WHERE clause • Use of JOIN keywords in FROM clause

  8. How many records will be in the Cartesian Join? Figure 9-1 Results of a Cartesian Product

  9. Cartesian Joins • Created by omitting joining condition in the WHERE clause or through CROSS JOIN keywords in the FROM clause • Results in every possible row combination (m * n) • They are useful when • performing certain statistical procedures for data analysis

  10. Cartesian Join Example:Omitted Condition Q: Why unintentional Cartesian join is produced? A: Because Oracle didn’t know what data the two tables had in common. Q: How to solve the problem? A: Use of Equality Joins (or inner/simple/natural). (70 rows produced) -- chapter 9, Figure 9-3; p.288 SELECT title, name FROM books, publisher; Figure 9-3 Producing an unintentional Cartesian join

  11. Cartesian Join Example:CROSS (Cartesian) JOIN Keywords -- chapter 9, Figure 9-2; p.287 SELECT isbn, title, location, ' ' Count FROM books, warehouses ORDER BY location, title; -- chapter 9, Figure 9-2b; p.287 SELECT count (location) FROM books, warehouses ORDER BY location, title; (42 rows produced) -- chapter 9, Figure 9-4; p.289 SELECT isbn, title, location, ' ' Count FROM books CROSS JOIN warehouses ORDER BY location, title; Figure 9-4 Using the CROSS JOIN keywords

  12. Equality Joins • Link rows through equivalent data that exists in both tables • Created by: • Creating equivalency condition in the WHERE clause • Using NATURAL JOIN, JOIN…USING, or JOIN…ON keywords in the FROM clause

  13. Joining Multiple Tables • Join: combine data from multiple database tables using foreign keyreferences • SELECT field1, field2, ... FROM table1, table2 WHERE table1.joinfield = table2.joinfield ANDsearch_condition(s); • If tables share field names, must prefix field in select with table name (table1.field1, table2.field1) • Join condition: part of where clause indicating how tables are related (table1.foreign_key = table2.primary key) • Search conditions can be added to join condition using AND operator

  14. Inner Join (cont.) Q: why “Brown” is not on the result? Q: How many rows will be produced if “C-Join” is used? S_ID S_LAST S_FIRST F_ID F_LAST ------ -------- -------- ---- -------- 1 Jones Tammy 1 Cox 2 Perez Jorge 1 Cox 3 Marsh John 1 Cox 4 Smith Mike 2 Blanchard 5 Johnson Lisa 4 Sheng 6 Nguyen Ni 3 Williams 6 rows selected. -- Exatra example SELECT s_id, s_last, s_first, student.f_id, f_last FROM student, faculty WHERE student.f_id = faculty.f_id;

  15. Natural Join – Inner Join • It can be used when the tables have a singlecommonly named and defined column. S_ID S_LAST S_FIRST F_ID F_LAST ------ -------- -------- ---- -------- 1 Jones Tammy 1 Cox 2 Perez Jorge 1 Cox 3 Marsh John 1 Cox 4 Smith Mike 2 Blanchard 5 Johnson Lisa 4 Sheng 6 Nguyen Ni 3 Williams 6 rows selected. -- Extra Example SELECT s_id, s_last, s_first, student.f_id, f_last FROM student, faculty WHERE student.f_id = faculty.f_id; -- Use NATURAL JOIN SELECT s_id, s_last, s_first, f_id, f_last FROM student NATURAL JOIN faculty;

  16. Equality Joins (Traditional Method): WHERE Clause Example Figure 9-6 An equality join

  17. Qualifying Column Names • Columns in both tables must be qualified • Which table is “pubid” from? • Is it from publisher or books? -- chapter 9, Figure 9-7(b); p.292 SELECT title, books.pubid, name FROM books, publisher WHERE books.pubid = publisher.pubid; Figure 9-7 A “column ambiguously defined” error

  18. WHERE Clause Supports Join and Other Conditions Figure 9-8 Including search and join conditions in a WHERE clause -- Use aliases -- chapter 9, Figure 9-9; p.294 SELECT b.title, b.pubid, p.name FROM books b, publisher p WHERE b.pubid = p.pubid AND (b.cost < 15 OR p.pubid = 1) ORDER BY title;

  19. Using a Query Design Diagram • Helpful for creating complicated queries • Can use a formula to derive actual query from diagram Query: Display customers’ lastname, firstname and books’ title they purchased • Customers • customer# (j) • lastname (d) • firstname (d) • Orders • order# (j) • customer# (j) • Orderitems • order# (j) • isbn (j) • Books • isbn (j) • title (d) j: join s: search d: display

  20. Query: Display customers’ lastname, firstname and books’ title they purchased • Customers • customer# (j) • lastname (d) • firstname (d) • Orders • order# (j) • customer# (j) • Orderitems • order# (j) • isbn (j) • Books • isbn (j) • title (d) Figure: Join query design diagram j: join s: search d: display You can derive your query from the diagram by following these steps: 1. Place the display fields in the SELECT clause 2. List all of the tables in the FROM clause 3. Include the links in join conditions in the WHERE clause 4. Include all of the search fields in the WHERE clause (if needed)

  21. Query: Display customers’ lastname, firstname and books’ title they purchased • Customers • customer# (j) • lastname (d) • firstname (d) • Orders • order# (j) • customer# (j) • Orderitems • order# (j) • isbn (j) • Books • isbn (j) • title (d) Figure: Join query design diagram j: join s: search d: display -- chapter 9, Figure 9-10; p.295 SELECTc.lastname, c.firstname, b.title FROM customers c, orders o, orderitemsoi, books b WHEREc.customer# = o.customer# ANDo.order# = oi.order# ANDoi.isbn = b.isbn ORDER BY lastname, firstname;

  22. Joining More Than Two Tables • Joining four tables requires three join conditions • Joining N tables requires ___ join conditions Figure 9-10 Joining four tables

  23. Query: Display customers’ lastname, firstname and books’ title and only in ‘COMPUTER’ category • Customers • customer# (j) • lastname (d) • firstname (d) • Orders • order# (j) • customer# (j) • Orderitems • order# (j) • isbn (j) • Books • isbn (j) • title (d) • _______ category (s) Figure: Join query design diagram j: join s: search d: display -- chapter 9, Figure 9-10; p.295 SELECTc.lastname, c.firstname, b.title FROM customers c, orders o, orderitemsoi, books b WHEREc.customer# = o.customer# ANDo.order# = oi.order# ANDoi.isbn = b.isbn ORDER BY lastname, firstname; AND category = ‘COMPUTER’

  24. Equality Joins: NATURAL JOIN Figure 9-12 Using the NATURAL JOIN keywords

  25. No Qualifiers with a NATURAL JOIN Figure 9-13 Column qualifier error with a NATURAL JOIN Natural Join keyword (e.g, pubid), we are not required to be specified when the two tables have it in common. Therefore most developers avoid using a NATURAL JOIN because it can cause unexpected results.

  26. Equality Joins: JOIN…USING Figure 9-14 Performing a join with the JOIN … USING keywords

  27. Break • Assignments (#2 & #5 on p. 329; see at the end of the slides) • Figure out how to produce the output with ‘$’ displayed (below is a sample output for #5, p.329) – hint: • a) column … • b) TO_CHAR (see p.365-367 and ‘learning to learn’) TITLE PROFIT ------------------------------ -------- PAINLESS CHILD-REARING $37.45 HOW TO MANAGE THE MANAGER $16.55 PAINLESS CHILD-REARING $37.45 TO_CHAR( ) PROFIT, _______

  28. Query: list all author IDs with books in the ‘Family Life’ category Query: list all author IDs with books in the ‘Children’ category SELECT ba.authorid FROM books b JOIN bookauthor ba USING (isbn) WHERE category = 'FAMILY LIFE‘; SELECT ba.authorid FROM books b JOIN bookauthor ba USING (isbn) WHERE category = 'CHILDREN';

  29. Query: list all author IDs with books in the ‘Family Life’ or ‘Children’ category -- chapter 9, Figure 9-28; p. 313(version 1) SELECT ba.authorid FROM books b JOIN bookauthorba USING (isbn) WHERE category = ‘FAMILY LIFE’ OR category = ‘CHILDREN’; Anything not appropriate in the output? How to take care this type of problem in an easy way? -- chapter 9, Figure 9-28; p. 313(version 2) SELECT _______ (ba.authorid) FROM books b JOIN bookauthorba USING (isbn) WHERE category = 'FAMILY LIFE' OR category = 'CHILDREN'; What else we can achieve the same goal (and other more complicated situation) ?

  30. Using Set Operators to Combine Query Results • Performs set operations on outputs of two unrelated queries • They all require that both queries • have the same number of display fields in the SELECT statement, and that • each field in the first query has the same data type as the corresponding column in the second query. • <query 1> <SET Operator> <query 2>

  31. SET THEORY

  32. SET THEORY A C A-B B-A B =A-B =A INTERSECTB= C =A UNION B (with C counted just once) =B-A All Regions

  33. SET THEORY A C D E B A MINUS B=D A INTERSECT B= C B MINUS A=E D+C+E A UNION B = C A UNION ALL B = D+C+E+__

  34. Oracle 11g/SQL SetOperator (Table 9-2) • Use to select data from multiple tables not connected with foreign key relationships • Used to combine the results of two or more SELECT statements • UNION (see Figure 9-28) • returns all rows from both queries, but ONLY displays duplicate rows once • UNION ALL (see Figure 9-30) • returns all (duplicate)rows from both queries, and displays ALL duplicate rows • INTERSECT (see Figure 9-34) • returns all matching rows that are returned by both queries • MINUS (see Figure 9-35) • returns all rows returned by the first query minus the matching rows returned by the second query

  35. Query: list all author IDs with books in the ‘Family Life’ or ‘Children’ category SELECT ba.authorid FROM books b JOIN bookauthor ba USING (isbn) WHERE category = 'FAMILY LIFE‘ UNION SELECT ba.authorid FROM books b JOIN bookauthor ba USING (isbn) WHERE category = 'CHILDREN'; Figure 9-28 Producing an unduplicated combined list with the UNION set operator

  36. Set Operators: UNION and UNION ALL Examples What is the “difference” on the outputs?

  37. Query: list all customer numbers and those customers who have placed an order recently SELECT customer# FROM customers INTERSECT SELECT customer# FROM orders; Figure 9-34 Identifying overlapping values with the INTERSECT set operator

  38. Query: list all customer numbers but haven’t placed an order recently SELECT customer# FROM customers MINUS SELECT customer# FROM orders; Figure 9-35 Subtract result sets with the MINUS set operator

  39. MORE EXAMPLES on SET OPERATORS You need to run the following command to make the example work: @ c:\oradata\NW_CW\northwoods.sql Query:A list of faculty members whose offices are in the BUS building. (Extra example) • FACULTY • f_first (d) • f_last (d) • loc_id (j) • LOCATION • loc_id (j) • bldg_code (s) SELECT f_first, f_last FROM faculty, location WHERE faculty.loc_id = location.loc_id AND bldg_code = 'BUS';

  40. Query:A list of faculty members who have taught a course in the BUS building. • FACULTY • f_first (d) • f_last (d) • f_id (j) • COURSE_ • SECTION • f_id (j) • loc_id (j) • LOCATION • loc_id (j) • bldg_code (s) SELECT DISTINCT f_first, f_last FROM faculty, location, course_section WHERE faculty.f_id = course_section.f_id AND location.loc_id = course_section.loc_id AND bldg_code = 'BUS';

  41. Query:A list of faculty members whose offices are in the BUS building or who have taught a course in the BUS building. (extra example) SELECT f_first, f_last FROM faculty, location WHERE faculty.loc_id = location.loc_id AND bldg_code = 'BUS' Office in ‘BUS’ UNION SELECT f_first, f_last FROM faculty, location, course_section WHERE faculty.f_id = course_section.f_id AND location.loc_id = course_section.loc_id AND bldg_code = 'BUS'; Taught courses in ‘BUS’

  42. Query:A list of faculty members whose offices are in the BUS building and who have taught a course in the BUS building. (extra example) SELECT f_first, f_last FROM faculty, location WHERE faculty.loc_id = location.loc_id AND bldg_code = 'BUS' Office in ‘BUS’ INTERSECT SELECT f_first, f_last FROM faculty, location, course_section WHERE faculty.f_id = course_section.f_id AND location.loc_id = course_section.loc_id AND bldg_code = 'BUS'; Taught courses in ‘BUS’

  43. And NEXT ... Query:A list of faculty members who have taught a course in the BUS building, but whose office are NOT located in the BUS SELECT f_first, f_last FROM faculty, location, course_section WHERE faculty.f_id = course_section.f_id AND location.loc_id = course_section.loc_id AND bldg_code = 'BUS'; Taught courses in ‘BUS’ MINUS SELECT f_first, f_last FROM faculty, location WHERE faculty.loc_id = location.loc_id AND bldg_code = 'BUS' Office in ‘BUS’

  44. Practice all the examples in the text. • A Script file is available on the Bb (file name: ch9Queries.sql) • After completing all examples, do the HW.

  45. Homework - Hands-On Assignments Email me with one attachment (Oracle_ch9_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch9 (or Bmis441-02_Oracle_ch9) Read and Practice all examples on Chapters 9 • 1. Run the script files (in the folder \oradata\chapter9\): JLDB_Build_9.sql • 2a. Read Oracle assignment and create a script file Oracle_ch9_Lname_Fname.sql for questions (#2 & #5 ; p.329) on “Hands-on Assignments” (use TWO SQL queries, traditional one and with “JOIN” read instructions carefully) • 2b. Be sure to use i) traditional method, ii) JOIN keyword, iii) draw Query Design Diagrams for each problem, and iv) use COLUMN statement to produce readable outputs – see next slide for details • 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 • 5. Include Query Design Diagrams

  46. Bonus Assignment --**************************************************************** -- Marketing department is analyzing books that don't sell. -- A list of ISBNs and Book Titles for all books is needed. -- Use a set operation (based on JL_Database to complete this task. --**************************************************************** -- a sample solution ISBN TITLE ---------- ------------------------------ 0132149871 HOW TO GET FASTER PIZZA 0299282519 THE WOK WAY TO COOK 4981341710 BUILDING A CAR WITH TOOTHPICKS

  47. How to Spool your Script and Output Files and add Query Design Diagrams After you tested the script file of Oracle_ch9_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\chapter9\JLDB_Build_9.sql • 1. type the following on SQL> • Spool c:\oradata\Oracle_ch9_Spool_Lname_Fname.txt (make sure your name is entered) • 2. open Oracle_ch9_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). Be sure that COLUMNcommands might be needed on the script file to align the output.

  48. How to Spool your Script and Output Files and add Query Design Diagrams (continued) • 5. Next, you should use MS/Word to open the *.txt file and include/DRAW “Query Design Diagram” figures for queries with multiple tables. You may use font of “Courier new” to align the output. • 6. Save it as *.docx file. Email me with the *.doc (or *.docx) file that includes all required documents with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch9 (or Bmis441-02_Oracle_ch9)

  49. Summary • Data stored in multiple tables regarding a single entity can be linked together through the use of joins • A Cartesian join between two tables returns every possible combination of rows from the tables; the resulting number of rows is always m * n • An equality join is created when the data joining the records from two different tables are an exact match • A non-equality join establishes a relationship based upon anything other than an equal condition • Self-joins are used when a table must be joined to itself to retrieve needed data

  50. Summary (continued) • Inner joins are categorized as being equality, non-equality, or self-joins • An outer join is created when records need to be included in the results without having corresponding records in the join tables • The record is matched with a NULL record so it will be included in the output • Set operators such as UNION, UNION ALL, INTERSECT, and MINUS can be used to combine the results of multiple queries

More Related