Clause in SQL

Clause is nothing but statement which is used to add to SQL query for providing some facilities are “filtering rows, sorting values, grouping data, finding sub total and grand total ” based on columns automatically.

Oracle supports the following clauses are:

                        i) WHERE

                        ii) ORDER BY

                        iii) GROUP BY

                        iv) HAVING

                        v) ROLLUP

                        vi) CUBE

syntax: <SQL QUERY> + <clause statement>;

i) WHERE :

The WHERE clause in SQLwhere like is used to filter records that meet specified criteria. It is typically used in SELECT, UPDATE, and DELETE statements to specify conditions that must be met for the operation to affect rows in the database .

– filtering rows before grouping data in a table.

– it can be used under “SELECT,UPDATE,DELETE” statements.

syntax: Where <filtering conditional  value>;

ex:
sql> select * from emp where empno=7788;
sql> update emp set sal=34000 where ename=’smith’;
sql> delete from emp where deptno=10;

ii) ORDER BY :

The Order by SQL clause is used to sort the result-set in ascending or descending order based on one or more columns.

-to arrange a specific column values either in ascending (or) descending order.

by default order by clause in sql will arrange the values in ascending order if we want to arrange the values order by clause in sql in descending then use “DESC” keyword.

it can implement with “SELECT” command.

syntax: SELECT * FROM <TN> ORDER BY <column name1> <asc/desc>,<column name2> <asc/desc>,…..;

EX: SQL> SELECT * FROM EMP ORDER BY ENAME;

SQL> SELECT * FROM EMP ORDER BY ENAME DESC;

SQL> SELECT * FROM EMP ORDER BY HIREDATE;

SQL> SELECT * FROM EMP ORDER BY HIREDATE DESC;

SQL> SELECT * FROM EMP ORDER BY SAL;

SQL> SELECT * FROM EMP ORDER BY SAL DESC;

EX: Want to display employees who are working under deptno is 20 and arrange those

employees salaries in descending order?

SQL> SELECT * FROM EMP WHERE DEPTNO=20 ORDER BY SAL DESC;

EX: Want to arrange employees deptno’s in ascending order and their salaries in descending order from each deptno wise?

SQL> SELECT * FROM EMP ORDER BY DEPTNO,SAL DESC;

NOTE: Order by clause can apply on not only column names even though we can apply on the position of column in SELECT query.

EX: SQL> SELECT * FROM EMP ORDER BY SAL DESC;

                        (OR)

SQL> SELECT * FROM EMP ORDER BY 6 DESC;

EX: SQL> SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY 3 DESC;

SQL> SELECT ENAME,SAL FROM EMP ORDER BY 2 DESC;

SQL> SELECT SAL FROM EMP ORDER BY 1 DESC;

ORDER BY clause with NULLS:

i) NULLS FIRST: By default order by clause on NULLS in ascending order:

                                    First : values

                                    Later : nulls

                        Ex: SQL> SELECT * FROM EMP ORDER BY COMM;

To overcome the above problem then we use “NULL FIRST” clause in SELECT query like below.

Ex: SQL> SELECT * FROM EMP ORDER BY COMM NULLS FIRST;

ii) NULLS LAST: By default sql order by null last clause on NULLS in descending order:

                                    First : nulls

                                    Later : values

Ex: SQL> SELECT * FROM EMP ORDER BY COMM DESC;

To overcome the above problem then we use “NULLS LAST”

clause in SELECT query like below,

Ex: SQL> SELECT * FROM EMP ORDER BY COMM DESC NULLS LAST;

iii) GROUP BY:

Dividing groups based on a specific column / columns wise.                     

When we use group by clause then we must use “grouping / aggregative” functions are sum(),coun(),min(),max() and avg().

It can be implemented in “SELECT” command only.

syntax: SELECT <column name1>,<column name2>,……..,<grouping function name1>,…..FROM <TABLE NAME> GROUP BY <column name1>,<column name2>,…..;

Ex: Want to find out no.of employees working in each job wise from emp table?

SQL> SELECT JOB,COUNT(*) AS NO_OF_EMPLOYEES FROM EMP GROUP BY JOB;

Ex:

Want to find out no.of employees working in each job along with deptno wise from emp table?

SQL> SELECT JOB,DEPTNO,COUNT(*) AS  NO_OF_EMPLOYEES 2  FROM EMP GROUP BY JOB,DEPTNO;

Ex:Want to display sum of salaries of each deptno wise from emp table?

SQL> SELECT DEPTNO,SUM(SAL) AS SUM_OF_SALARY   2  FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

Using all aggregative function along with GROUP BY clause:

Ex:

SQL> SELECT DEPTNO,COUNT(*) AS NO_OF_EMPLOYEES,

  2  SUM(SAL) AS SUM_OF_SALARY,

  3  AVG(SAL) AS AVG_SALARY,

  4  MIN(SAL) AS MIN_SALARY,

  5  MAX(SAL) AS MAX_SALARY

  6  FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

Having clause in SQL with group by:

You may not want all the summary rows returned by a Group BY query. You know that you can use WHERE to eliminate details rows returned by a regular query. with summary queries, you can use the Having SQL clause to eliminate summary rows.

• It used generally with GROUP BY clause the clause having SQL is useful for specifying a condition for the group.
• The clause is used to filter data that is associated with group function.
• Filtering rows after grouping data in a table.
• It will use after group by clause only.

syntax: SELECT <column name1>,<column name2>,……..,<grouping function name1>,…..

FROM <TABLE NAME> GROUP BY <column name1>,<column name2>,…..HAVING <filtering condition>;

Ex: Want to display sum of salary of deptno from emp table if sum of salary of deptno is

less than 10000?

SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)<10000;

Ex: Want to display no.of employees working in a job from emp table in which job the no.of

employees are more than 3?

SQL> SELECT JOB,COUNT(*) FROM EMP   2  GROUP BY JOB HAVING COUNT(*)>3;

Using all clauses in a single SELECT statement:

We can use group by having clause in sql in below in a proper way.

syntax: SELECT <column name1>,<column name2>,……..,<grouping function name1>,…..

FROM <TABLE NAME>

[ WHERE <filtering condition>

     GROUP BY <column name1>,<column name2>,…..

         HAVING <filtering condition>

                   ORDER BY <column name1> <asc/desc>,…… ] ;

EX: SQL> SELECT DEPTNO,COUNT(*) FROM EMP

WHERE SAL>1000

       GROUP BY DEPTNO

              HAVING COUNT(*)>3

                     ORDER BY DEPTNO;

OUTPUT:

    DEPTNO   COUNT(*)

        20          4

        30          5

ROLLUP & CUBE :

ROLLUP :

• It is used with group by clause to display the summarized data.
• rollup GROUPING produces a results set containing the regular group rows and the subtotal values.
• ROLLUP grouping produces subtotal and Grant total.
• The totaling is based on a one dimensional data hierarchy of grouped information.

To find out sub total and grand total based on column / columns.

   Rollup : to find sub & grand total based on a single column.

   Cube : to find sub & grand total based on multiple columns.

   These two clauses are implementing along with “group by” clause.

syntax for Rollup:

SELECT <column name1>,<column name2>,……..,<grouping function name1>,………….

FROM <TABLE NAME> GROUP BY ROLLUP(<column name1>,<column name2>,…..);

Rollup with a single column:

SQL> SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY ROLLUP(DEPTNO);

Rollup with multiple columns:

SQL> SELECT DEPTNO,JOB,COUNT(*) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB);

SQL> SELECT JOB,DEPTNO,COUNT(*) FROM EMP GROUP BY ROLLUP(JOB,DEPTNO);

Syntax for CUBE:

SELECT <column name1>,<column name2>,……..,<grouping function name1>,…..

FROM <TABLE NAME> GROUP BY CUBE(<column name1>,<column name2>,…..);

Cube with a single column:

SQL> SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY CUBE(DEPTNO) ORDER BY DEPTNO;

Cube with multiple columns:

SQL> SELECT DEPTNO,JOB,COUNT(*) FROM EMP GROUP BY CUBE(DEPTNO,JOB) ORDER BY DEPTNO,JOB;

To learn about Primary Key and Foreign Key then please visit

To learn about Joins in SQL with practical then please visit

To learn about Joins in SQL Functions then please visit

To practice SQL online visit