-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
Clauses Group By clause: Group by clause is used to divide the rows in table to different groups. Ex1: SQL> select deptno, sum(sal) As the standard emp tables has 3 types of deptno ( 10, 20, 30), 14 rows of the table are divided 3 groups. SUM( ) function is applied on each group. Ex2: SQL> select job, avg(sal) Ex3: SQL> select deptno, SUM(sal), MAX(sal), MIN(sal), AVG(sal), COUNT(*) Ex4: SQL> select deptno, job, SUM(sal) In the above query, first grouping is done based on deptno and sub grouping is done based on job. SQL> select deptno, SUM(sal), ename The above query will result in error. The rule of GROUP BY clause: All the columns in the select list should use GROUP functions or should be included in the GROUP BY clause. In the above query, ename is not satisfying the rule, we get the error. HAVING Clause: HAVING clause is used to filter the results of GROUP BY clause. Ex: SQL> Select deptno, SUM(sal) Query execution process: Existance of GROUP BY clause does not gaurantee the existance of HAVING clause, But Few more examples: SQL> Select deptno, AVG(sal), SUM(sal) SQL> Select deptno, MIN(sal), MAX(sal)
ORDER BY clause: ORDER BY clause is used to arrange the rows in ascending or in the descending order. Ex: SQL> select * from emp SQL> Select ename , job, deptno, hiredate SQL> Select * from emp SQL> Select ename , job, sal SQL> Select empno, ename, sal, sal*12 annual_sal |