CLAUSES in Oracle with Example

1)WHERE:-

Use:-

            Oracle provides the option of using a WHERE CLAUSE in an SQL query to apply a filter on the row retrieved.

            Syntax:-

                        Select *from <tablename> where <condition>;

            Example:-

                        Select *from emp2 where eno=2;

2)ORDER BY:-

Use:-

            Oracle allows data from a table to be viewed in a sorted order. The rows retrieved from the table will be sorted in either ascending or descending order.

Syntax:-

Select *from <tablename> order by <columnname> <sortorder>;

Example:-

Select *from emp order by name desc;

3)GROUP BY WITH HAVING:-

The GROUP BY clause is used to produce summary information for a subset of the table. The GROUP BY clause will produce a single row for all the rows that meet a particular condition.

Syntax:-

Select <columnname1>,<columnname2>, AGGREGET FUNCTION (<expression>) from <tablename> where <condition> GROUP BY<column1>;

Examples:-

Consider the table, SALARY:

EMP_NO	BASIC	COMM	DEDUCT	SALARY_DATE
1001	3000	200	250	30-JUN-96
1002	2500	120	200	30-JUN-96
1003	3000	500	290	30-JUN-96
1004	2500	200	300	30-JUN-96
1005	2800	100	250	30-JUN-96
1001	3000	200	250	31-JUL-96
1002	2500	120	200	31-JUL-96
1003	3000	500	290	31-JUL-96
1004	2500	200	300	31-JUL-96
1005	2800	100	150	31-JUL-96

To SUM the Salary of Each Employee:

SQL> SELECT EMP_NO,SUM(BASIC) FROM SALARY GROUP BY EMP_NO;

EMP_NO	SUM(BASIC)
1001	6000
1002	5000
1003	6000
1004	5000
1005	5600

To SUM the Salary of Each Employee and Sort it on the Sum of Basic:

SQL> SELECT EMP_NO, SUM (BASIC) FROM SALARY GROUP BY EMP_NO ORDER BY SUM (BASIC);

EMP_NO	SUM(BASIC)
1002	5000
1004	5000
1005	5600
1001	6000
1003	6000

To SUM the Salary of Each Employee and Sort it in the Descending Order of the Sum of Basic:

SQL> SELECT EMP_NO, SUM(BASIC) FROM SALARY GROUP BY EMP_NO ORDER BY SUM(BASIC) DESC;

4)HAVING:-

HAVING Clause:

The HAVING clause can also be used with a join query that includes a GROUP BY clause. The HAVING is useful for specifying a condition for the group. The WHERE clause is used to specify conditions to retrieve Rows of a table, and the HAVING clause is used to specify a condition to a retrieve group data.

Syntax:-

Select <columnname1>,<columnname2>, AGGREGET FUNCTION (<expression>) from <tablename> where <condition> GROUP BY<column1> HAVING <condition>;

Example:

SQL> select pname, sum(order_qty) from product group by pname having pname=”cpu”;