Having Clause :-

Select deptno,max(sal),maxsal from emp where deptno in (10,30) group by deptno having max(sal)>=3000

Where vs having clause :—

  • To filter data be fine group by use where clause
  • To filter data after group by use having clause.
  • In condition, if there is no aggregate function their use where clause.
  • In condition if there is aggregate function, then use having clause.

From emp                      where             group by dept no       having       select

Deptno (10,30)  maxsal >=(Graterthan and Equal to) 3000

Display number of employee joined in each year

When year=1981 (or) 82 and no of employee≥2

Select year (hiredate) year,count(*) no of emps from emp where year (hiredate) in(1981,1982) group by year(hiredate) having count (*)>2

Output :-

   Year                 no of emps

   1981                       10

Display department wise sum salaries &with in the department job wise

Select deptno,job,sum(sal) sumsal from emp group by deptno,job order by deptno,job

Output :→  deptno                  job                  sumsal        

                            10                          manager                5000

                            10                          president               2450

                            20                          analyst                   30000