Oracle Clauses

Following are the important clauses in Oracle.

Group BY: Group by is used to group the data when group function used in select statement. Columns specified after select, must be used after group by. Otherwise, Oracle returns an error.

Syntax:

SELECT COLUMN_name1,….
FROM TABLE
GROUP BY column_name1;

Example:
Query: To display number of employees in each department.

SELECT DEPTNO, COUNT(*)
FROM emp
GROUP BY DEPTNO;

Query: Display number of employees jobwise from emp table.

SELECT JOB, COUNT(*)
FROM emp;
GROUP BY JOB;

Query: Display the minimum and maximum salaries in each department.

SELECT DEPTNO, MIN(SAL), MAX(SAL)
FROM emp
GROUP BY DEPTNO;

HAVING Clause:

  1. After group by clause, we are not allowed to use where clause.
  2. If we want restrict rows in table, then we are using where clause. Where as, if we want to restrict groups after group by then we must use having clause.
  3. We are not allowed to use group by function in where clause. Where as in a having clause, we can also use group functions(aggregate functions).
  4. We can use Having clause without group by clause.

Examples:
Query: Display the job wise average salary and average salary having more than 3000

SELECT JOB, AVG(SAL)
FROM emp
HAVING AVG(SAL)>3000;

Query: Display total salary department wise from emp table and also those departments having more than two employees.

SELECT DEPTNO, SUM(SAL)
FROM emp
GROUP BY DEPTNO
HAVING COUNT(EMPNO)>2

ORDER BY: Used to arrange the data in ascending order or descending order. By default order by clause having ascending order.

Example:

SELECT empno, ename, sal, deptno FROM emp
order by deptno;

SELECT empno, ename, sal, deptno FROM emp
order by 2 desc;

Powered by k2schools