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