1 / 21

Working with Multiple Tables in Oracle: Table Joins and Aggregations

Learn how to perform table joins, avoid Cartesian products, use aliases, and aggregate data using group functions in Oracle SQL.

nelson
Download Presentation

Working with Multiple Tables in Oracle: Table Joins and Aggregations

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. Software SpecificationKXA233Lecture 4AOracleWorking With Multiple Table Paul Crowther School of Computing University of Tasmania

  2. Today... • Table joins • Aggregating data using group functions

  3. EMP DEPT DEPTNO DNAME LOC --------------------------------------------- 10 SALES NEW YORK 30 I.T. HOBART 50 RESEARCH BURNIE EMPNO ENAME DEPTNO --------------------------------------- 7839 KING …. 10 7698 BLAKE …. 30 EMPNO DEPTNO LOC ----------------------------------------------- 7839 10 NEW YORK 7698 30 HOBART Obtaining Data From Multiple Tables

  4. What is a Join? Use a join to query data from more than one table SELECT table1. column, table2.columnFROM table1, table2WHERE table1.coumn1 = table2.column2; • Write the join condition in the WHERE clause • Prefix the column name with the table name when the same column name appears in more than one table

  5. Cartesian Product • A Cartesian product is formed when: • A join condition is omitted • A join condition is invalid • All rows in the first table are joined to all rows in the second table • To avoid a Cartesian product, always include a valid join condition in a WHERE clause

  6. Cartesian Product • You know you have one when you have two tables - sayemp (of 14 records) and dept (of 4 records) • WHEN56 rows of all combinations generated: • SQL> SELECT ename, dname 2 FROM emp, dept; • WHERE clause missing!!!

  7. Types of Joins • Equijoin • 1:1 relationship between a foreign and primary key • Non-equijoin • foreign key falls into a range between primary keys • Outer join • where no equivalent records in the joined table • Self join • joins table to itself

  8. Equijoin Retrieval SQL> SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno = dept.deptno; EMPNO ENAME DEPTNO DEPTNO LOC-----------------------------------------------------------------------7839 KING 10 10 HOBART7698 BLAKE 30 30 BURNIE7782 CLARK 10 10 HOBART…14 rows selected

  9. Qualifying Ambiguous Column Names • Use table prefixes to qualify column names that are in multiple tables. • Improve performance by using table prefixes. • Distinguish columns that have identical names but reside in different tables by using column aliases.

  10. Another example... SQL> SELECT empno, ename, emp.deptno, loc 2 FROM emp, dept 3 WHERE emp.deptno = dept.deptno 4 AND INITCAP(ename) = ‘King’; EMPNO ENAME DEPTNO LOC---------------------------------------------------------- 7839 KING 10 HOBART

  11. Aliases SQL> SELECT emp.empno, emp.ename, 2 emp.deptno, dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno = dept.deptno; SQL> SELECT e.empno, e.ename, 2 e.deptno, d.deptno, d.loc 3 FROM emp e, dept d 4 WHERE e.deptno = d.deptno

  12. 3 Tables... SQL> SELECT c.name, o.ordid, i.itemid, 2 i.itemtot, o.total 3 FROM customer c, ord o, item I 4 WHERE c.custid = o.custid 5 AND o.ordid = i.ordid 6 AND c.name = ‘TKB SPORTS’; NAME ORDID ITEMID ITEMTOT TOTAL-------------------------------------------------------------------------------TKB SPORTS 610 3 58 101.4TKB SPORTS 610 1 35 101.4TKB SPORTS 610 2 8.4 101.4

  13. salgrade emp GRADE LOSAL HISAL -------------------------------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 EMPNO ENAME SAL --------------------------------- 7867 KING 5000 4356 BLAKE 2850 4433 CLARK 2450 6678 JAMES 950 … 14 rows selected Non-Equijoins Foreign Key

  14. Non - Equijoins SQL> SELECT e.name, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal 4 BETWEEN s.losal AND s.hisal; ENAME SAL GRADE-----------------------------------------------------JAMES 950 1SMITH 800 1ADAMS 1100 1…14 rows selected

  15. Outer Joins SQL> SELECT e.name, d.deptno, d.dname 2 FROM emp e, dept d 3 WHERE e.deptno(+) = d.deptno 4 ORDER BY e.deptno; • Returns records with no direct match ENAME DEPTNO DNAME---------------------------------------------------KING 10 ACCOUNTINGCLARK 10 ACCOUNTING… 40 OPERATIONS15 rows selected

  16. EMP (MANAGER) EMP (WORKER) EMPNO ENAME MGR ------------------------------------ 7839 KING 7698 BLAKE 7839 7782 CLARK 7839 7566 JONES 7839 7654 MARTIN 7698 7499 ALLEN 7698 EMPNO ENAME ---------------------------- 7839 KING 7839 KING 7839 KING 7698 BLAKE 7698 BLAKE Self Joins MGR in the WORKER table is equal to EMPNO in the MANAGER table

  17. Self Joins SQL> SELECT worker.ename | | ‘ works for ‘ | | manager.ename 2 FROM emp worker, emp manager 3 WHERE worker.mgr = manager.empno; WORKER.ENAME | | ‘WORKSFOR’ | | MANAGER.ENAME------------------------------------------------------------------------BLAKE works for KINGCLARK works for KINGJONES works for KINGMARTIN works for BLAKE…13 rows selected

  18. Aggregating Data Using Group Functions • Group functions operate on sets of rows to give one result per group • AVG • COUNT • MAX • MIN • SUM • VARIANCE

  19. Group Functions SELECT [column,] group_function (column)FROM table[WHERE condition] [GROUP BY column] [ORDER BY column];

  20. Examples... SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE ‘SALES%’; AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)------------------------------------------------------------------ 1400 1600 1250 5600 • Group functions ignore NULL values in the column

  21. Next Week... • More group functions using the GROUP BY clause • Subqueries

More Related