advanced sql Functions in Oracle 10G phần 3

The Analytical Functions in Oracle (Analytical Functions I) A SELECT with Just a FROM Clause SELECT empno, ename, orig_salary FROM employee Gives: EMPNO ENAME ORIG_SALARY ---------- -------------------- ----------- 101 John 35000 102 Stephanie 35000 104 Christina 43000 108 David 37000 111 Katie 45000 106 Chloe 33000 122 Lindsey 40000 A SELECT with Ordering Note that the ordering is applied to the result set after the result is established: SELECT empno, ename, orig_salary FROM employee ORDER BY orig_salary Gives: EMPNO ENAME ORIG_SALARY ---------- -------------------- ----------- 106 Chloe 33000 101 John 35000 102 Stephanie 35000 108 David 37000 122 Lindsey 40000 104 Christina 43000 111 Katie 45000 66 Chapter | 3 A WHERE Clause Is Added to the Statement Notice that the WHERE has excluded rows before the final ordering: SELECT empno, ename, orig_salary FROM employee WHERE orig_salary < 43000 ORDER BY orig_salary Gives: EMPNO ENAME ORIG_SALARY ---------- -------------------- ----------- 106 Chloe 33000 101 John 35000 102 Stephanie 35000 108 David 37000 122 Lindsey 40000 Notice that ORDER BY is applied last — after the SELECT .. FROM .. WHERE. An Analytical Function Is Added to the Statement Note here that the WHERE is applied before the RANK(). SELECT empno, ename, orig_salary, RANK() OVER(ORDER BY orig_salary) rankorder FROM employee WHERE orig_salary < 43000 ORDER BY orig_salary 67 The Analytical Functions in Oracle (Analytical Functions I) Gives: EMPNO ENAME ORIG_SALARY RANKORDER ---------- -------------------- ----------- ---------- 106 Chloe 33000 1 101 John 35000 2 102 Stephanie 35000 2 108 David 37000 4 122 Lindsey 40000 5 A Join Is Added to the Statement What will happen to the order of execution if a join is included in the statement? We will add another table to the statement, then perform a join and see what hap-pens. Suppose we have a table called Job with this description: Name Null? Type ---------------------------------------- -------- ------------ EMPNO JOBTITLE NUMBER(3) VARCHAR2(20) and this data: EMPNO JOBTITLE ---------- --------------------101 Chemist 102 Accountant 102 Mediator 111 Musician 122 Director Personnel 122 Mediator 108 Mediator 106 Computer Programmer 104 Head Mediator 68 Chapter | 3 Now, we’ll perform a join with and without the analyti-cal function. The Join Without the Analytical Function Just adding the join to the query shows that the join is performed with the other WHERE conditions: SELECT e.empno, e.ename, j.jobtitle, e.orig_salary FROM employee e, job j WHERE e.orig_salary < 43000 AND e.empno = j.empno Gives: EMPNO ENAME JOBTITLE ORIG_SALARY ---------- ------------------- -------------------- ----------- 101 John 102 Stephanie 102 Stephanie 106 Chloe 108 David 122 Lindsey 122 Lindsey Chemist 35000 Accountant 35000 Mediator 35000 Computer Programmer 33000 Mediator 37000 Director Personnel 40000 Mediator 40000 Here, the WHERE is used to filter all salaries that are less than 43000 and, because we are using a join (actu-ally an equi-join), the WHERE provides the equality condition for the equi-join. 69 The Analytical Functions in Oracle (Analytical Functions I) Adding Ordering to a Joined Result If an ordering is applied to the statement at this point, it occurs after the WHERE has been executed: SELECT e.empno, e.ename, j.jobtitle, e.orig_salary FROM employee e, job j WHERE e.orig_salary < 43000 AND e.empno = j.empno ORDER BY orig_salary desc Gives: EMPNO ENAME JOBTITLE ORIG_SALARY ---------- ------------------- -------------------- ----------- 122 Lindsey 122 Lindsey 108 David 101 John 102 Stephanie 102 Stephanie 106 Chloe Director Personnel 40000 Mediator 40000 Mediator 37000 Chemist 35000 Accountant 35000 Mediator 35000 Computer Programmer 33000 Note that the same number and content of rows is in the result set, and the ordering was applied after the WHERE clause. 70 ... - tailieumienphi.vn