Tutorial 4: SQL functions

Article Index
Tutorial 4: SQL functions
Page 2
Page 3
Page 4
Page 5
Page 6
Page 7

Page 7 of 7

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

Tags