Following
is employee Table where I will demonstrate the Where,
Group by and HAVING clause.
EMPNO
|
ENAME
|
JOB
|
SAL
|
DEPT
|
DEPTNO
|
DNAME
|
LOC
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
1
|
ACCOUNTING
|
ST LOUIS
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
2
|
RESEARCH
|
NEW YORK
|
2
|
Anil
|
Manager
|
12000
|
3
|
3
|
SALES
|
ATLANTA
|
3
|
Adam
|
Developer
|
6700
|
3
|
3
|
SALES
|
ATLANTA
|
5
|
Tina
|
Developer
|
3000
|
3
|
3
|
SALES
|
ATLANTA
|
1
|
Alok
|
Admin
|
18000
|
4
|
4
|
OPERATIONS
|
SEATTLE
|
4
|
Anit
|
Developer
|
4500
|
4
|
4
|
OPERATIONS
|
SEATTLE
|
Where
Clause-
WHERE can be used clause with SELECT, INSERT, UPDATE
and DELETE statement to filtering rows and it applies on each and every row.
- WHERE clause is used before GROUP BY clause.
- We can't use aggregate functions in the where clause
SELECT * FROM EMP WHERE EMP.DEPT=3
OutPut:-
EMPNO
|
ENAME
|
JOB
|
SAL
|
DEPT
|
DEPTNO
|
DNAME
|
LOC
|
2
|
Anil
|
Manager
|
12000
|
3
|
3
|
SALES
|
ATLANTA
|
3
|
Adam
|
Developer
|
6700
|
3
|
3
|
SALES
|
ATLANTA
|
5
|
Tina
|
Developer
|
3000
|
3
|
3
|
SALES
|
ATLANTA
|
Group
by Clause-
Group by
is used to group the result set or we can say that gather all the rows together
on the based on the one or more columns. It allows aggregate functions to be
performed on the one or more columns.
- To use Group by Clause, we need to use at least one aggregate function
- We can use Group by Clause with or without Where Clause.
SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM GROUP BY DNAME
OutPut:-
DNAME
|
TotalEmployee
|
ACCOUNTING
|
0
|
OPERATIONS
|
2
|
RESEARCH
|
0
|
SALES
|
3
|
HAVING
Clause-
HAVING is used to trying to filter based on one of
the grouping fields. It can be used in a SELECT statement to filter the records
that a GROUP BY.
- HAVING clause is used to filter grouping fields.
- HAVING clause is used after GROUP BY clause.
SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM EMP GROUP BY DNAME HAVING COUNT(EMPNO)>1
OutPut:-
DEPTNO
|
DNAME
|
TotalEmployee
|
4
|
OPERATIONS
|
2
|
3
|
SALES
|
3
|
Fetching department that have no employee
by using HAVING clause example
SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM EMP GROUP BY DNAME HAVING COUNT(EMPNO)<1
OutPut:-
DEPTNO
|
DNAME
|
TotalEmployee
|
1
|
ACCOUNTING
|
0
|
2
|
RESEARCH
|
0
|