Subqueries with Group by and Having Clause

Subqueries with Group by and Having Clause

select * from XX_EMP_TL

EMPNO	ENAME	JOB	SAL	DEPTNO	LOCID
7369	SMITH	CLERK	800	20	100
7499	ALLEN	SALESMAN	1600	30	101
7521	WARD	SALESMAN	1250	30	100
7566	JONES	MANAGER	2975	20	101
7654	MARTIN	SALESMAN	12000	30	100
7698	BLAKE	MANAGER	10000	30	101
7782	CLARK	MANAGER	2450	10	100
7788	SCOTT	ANALYST	3000	20	102
7839	KING	PRESIDENT	5000	10	100
7844	TURNER	SALESMAN	1500	30	102
7876	ADAMS	CLERK	1100	20	100
7900	JAMES	CLERK	950	30	100
7902	FORD	ANALYST	3000	20	100
7934	MILLER	CLERK	1300	10	100

select * from XX_DEPT_TL

DEPTNO	DNAME
10	ACCOUNTING
20	RESEARCH
30	SALES
40	OPERATIONS
50	FINANCE

select * from XX_LOC_TL

LOCID	LOCATION
100	NEW DELHI
101	NOIDA
102	FRIDABAD

--====================================================

--Aggregate Function

--====================================================

select  COUNT(*) from XX_EMP_TL

--====================================================

-- Group by

--====================================================

select JOB, COUNT(*)

from XX_EMP_TL

GROUP BY JOB

--====================================================

-- Having

--====================================================

select JOB, COUNT(*)

from XX_EMP_TL

GROUP BY JOB

HAVING COUNT(*) = 1

--====================================================

-- Sub Query

--====================================================

select JOB, COUNT(*)

from XX_EMP_TL

WHERE LOCID = ( select LOCID from XX_LOC_TL WHERE LOCATION = 'NEW DELHI' )

GROUP BY JOB

HAVING COUNT(*) = 1