Software Specification KXA233 Lecture 4A Oracle Working With Multiple Table

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