1 / 19

Agenda for 02/16/2006

Agenda for 02/16/2006. Answer any questions about SQL project. Do you want to see any of the answers for the queries? Discuss additional formatting options available via SQLPlus. Learn how to use more than one table in a query. Discuss how DBMS processes multiple tables.

maximos-zale
Download Presentation

Agenda for 02/16/2006

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. Agenda for 02/16/2006 • Answer any questions about SQL project. • Do you want to see any of the answers for the queries? • Discuss additional formatting options available via SQLPlus. • Learn how to use more than one table in a query. • Discuss how DBMS processes multiple tables. • Explain the different types of joins. • Describe join conditions.

  2. Sample Database ERD Assume referential integrity is NOT maintained in this database

  3. Accessing data from multiple tables • Why do you want to access data from multiple tables in a single query? • To provide more complete information in a result table. • To support decision making. • What happens when multiple tables are accessed in a single query?

  4. Multiple Table Access SELECT * FROM emp, time; Vocabulary Words Select List Result Table Join Cartesian Product/Cross Join Join Condition

  5. Eliminate columns to enhance readability SELECT name, contractid, datetime, amount/60 FROM emp, time; Let’s eliminate some columns from the select list to make a more readable result table. Does this SQL code produce a reasonable response?

  6. Combining Tables Based on a Shared Column Option 1 (older syntax - using WHERE clause) SELECT name, contractid, datetime, amount/60 FROM time, emp WHERE time.empid = emp.empid; The shared column is usually the foreign key that sustains the relationship between the tables on the ERD.

  7. Combining Tables Based on a Shared Column Option 2 (newer syntax – using join condition) SELECT name, contractid, datetime, amount/60 FROM time INNER JOIN emp ON time.empid = emp.empid

  8. Combining tables on a shared column that has the Same name: Natural Join SELECT name, contractid, datetime, amount/60 FROM time NATURAL JOIN emp

  9. Viewing all rows from one of the tables in a join Option 1 (older syntax using WHERE clause) Syntax for a left outer-join in Oracle: SELECT name, time.empid contractid, datetime, amount/60 FROM time, emp WHERE time.empid = emp.empid (+) All data in the table on the left side of the condition will be displayed because the plus sign (+) is on the right side of the condition.

  10. Viewing all rows from one of the tables in a join Option 2 (newer syntax using join condition) SELECT name, time.empid contractid, datetime, amount/60 FROM time LEFT OUTER JOIN emp ON emp.empid = time.empid Imagine that the time table is “left” (because it is declared first) and the emp table is “right” (because it is declared second)

  11. Viewing all rows from the other table in a join Option 1 (older syntax using WHERE clause) Syntax for a right outer-join in Oracle: SELECT name, time.empid contractid, datetime, amount/60 FROM time, emp WHERE time.empid (+) = emp.empid

  12. Right outer join – Viewing all rows in other table Option 2 (newer syntax using join condition) SELECT name, time.empid, contractid, datetime, amount/60 FROM time RIGHT OUTER JOIN emp ON time.empid = emp.empid; Remember that the time table is “left” (because it is declared first) and the emp table is “right” (because it is declared second)

  13. Full outer join – Viewing all rows in both tables Option 2 (newer syntax using join condition – option 1 is not available) SELECT name, time.empid, contractid, datetime, amount/60 FROM time FULL OUTER JOIN emp ON time.empid = emp.empid;

  14. Combining more than two tables into a single result table SELECT emp.name, time.empid, time.worktypeid, work.description, datetime, amount/60 FROM time INNER JOIN emp ON time.empid = emp.empid INNER JOIN work ON time.worktypeid = work.worktypeid;

  15. Displaying all data in TIME table COLUMN emp_name heading “Employee Name” COLUMN descr heading “Type of Work” SELECT NVL(emp.name,'NOT IN EMPLOYEE TABLE') emp_name, time.empid, time.worktypeid, NVL(work.description,'NOT IN WORK TABLE’) descr, datetime, amount/60 FROM time LEFT OUTER JOIN emp ON time.empid = emp.empid LEFT OUTER JOIN work ON time.worktypeid = work.worktypeid ORDER BY emp.name;

  16. Summarizing Data COLUMN descr heading “Type of Work” SELECT NVL(work.description, ‘No Description’) descr, round(sum(amount/60),2) FROM time LEFT OUTER JOIN work ON work.worktypeid = time.worktypeid GROUP BY work.description; The GROUP BY statement is frequently used with a result table created by multiple underlying tables

  17. Recursive Relationship with Employee to Assign Manager

  18. Self-join also called a recursive join Option 1 – (older syntax using WHERE clause) SELECT worker.empid "worker#", worker.name "worker name", manager.empid "manager#", manager.name "manager name" FROM emp worker, emp manager WHERE worker.mgrid = manager.empid; What change is necessary to make all employees appear whether there is a manager?

  19. Self-join also called a recursive join Option 2 – (newer syntax using join condition) SELECT worker.empid "worker#", worker.name "worker name", manager.empid "manager#", manager.name "manager name" FROM emp worker INNER JOIN emp manager ON worker.mgrid = manager.empid; What change is necessary to make all employees appear whether there is a manager?

More Related