Using  WHERE , GROUP BY , HAVING AND ORDER BY clauses together.

General form of  the SELECT statement is

         SELECT <column1, column2, . . .>
         FROM   <table1, table2, . . .>
         WHERE  <where clause>       
         GROUP BY <column1, column2, . . .>
         HAVING   <having clause>
         ORDER BY <column1, column2, . . .>

when all the above said clauses are used in a select query they need to appear in the order specified above, violation of which will lead to errors.

Q.  A query to display departments paid 500 or more and have 2 or less employess can be displayed as shown below

SQL >SELECT DEPTNO ,COUNT(*) , SUM(SAL) FROM EMP
  2      WHERE JOB = 'CLERK'
  3       GROUP BY DEPTNO
  4       HAVING SUM(SAL) >=500 AND COUNT(*) < 2
  5       ORDER BY SUM(SAL) ASC;

    DEPTNO   COUNT(*)   SUM(SAL)
---------- ---------- ----------
        30          1        950
        10          1       1300

First Page: Tutorial 4: SQL functions