GROUP CLAUSE QUESTIONS:
1. WAQTD NUMBER OF EMPLOYEES WORKING IN EACH DEPARTMENT EXCEPT
PRESIDENT.
o Ans:
o SELECT DEPTNO, COUNT(*)
o FROM EMP
o WHERE JOB NOT IN ('PRESIDENT')
o GROUP BY DEPTNO;
2. WAQTD TOTAL SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH JOB.
o Ans:
o SELECT JOB, SUM(SAL)
o FROM EMP
o GROUP BY JOB;
3. WAQTD NUMBER OF EMPLOYEES WORKING AS MANAGER IN EACH
DEPARTMENT.
o Ans:
o SELECT DEPTNO, COUNT(*)
o FROM EMP
o WHERE JOB IN ('MANAGER')
o GROUP BY DEPTNO;
4. WAQTD AVG SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH
DEPARTMENT EXCLUDING THE EMPLOYEES OF DEPTNO 20.
o Ans:
o SELECT DEPTNO, AVG(SAL)
o FROM EMP
o WHERE DEPTNO <> 20
o GROUP BY DEPTNO;
5. WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER 'A' IN THEIR NAMES
IN EACH JOB.
o Ans:
o SELECT JOB, COUNT(*)
o FROM EMP
o WHERE ENAME LIKE '%A%'
o GROUP BY JOB;
6. WAQTD NUMBER OF EMPLOYEES AND AVG SALARY NEEDED TO PAY THE
EMPLOYEES WHOSE SALARY IS GREATER THAN 2000 IN EACH DEPT.
o Ans:
o SELECT DEPTNO, COUNT(*), AVG(SAL)
o FROM EMP
o WHERE SAL > 2000
o GROUP BY DEPTNO;
7. WAQTD TOTAL SALARY NEEDED TO PAY AND NUMBER OF SALESMEN IN
EACH DEPT.
o Ans:
o SELECT DEPTNO, SUM(SAL), COUNT(*)
o FROM EMP
o WHERE JOB = 'SALESMAN'
o GROUP BY DEPTNO;
8. WAQTD NUMBER OF EMPLOYEES WITH THEIR MAXIMUM SALARIES IN EACH
JOB.
o Ans:
o SELECT JOB, COUNT(*), MAX(SAL)
o FROM EMP
o GROUP BY JOB;
9. WAQTD MAXIMUM SALARIES GIVEN TO AN EMPLOYEE WORKING IN EACH
DEPT.
o Ans:
o SELECT DEPTNO, MAX(SAL)
o FROM EMP
o GROUP BY DEPTNO;
10. WAQTD NUMBER OF TIMES THE SALARIES PRESENT IN EMPLOYEE TABLE.
o Ans:
o SELECT SAL, COUNT(*)
o FROM EMP
o GROUP BY SAL;
HAVING CLAUSE QUESTIONS:
1. WAQTD DNO AND NUMBER OF EMPLOYEES WORKING IN EACH DEPT IF
THERE ARE AT LEAST 2 CLERKS IN EACH DEPT.
o Ans:
o SELECT DEPTNO, COUNT(*)
o FROM EMP
o WHERE JOB = 'CLERK'
o GROUP BY DEPTNO
o HAVING COUNT(*) > 1;
2. WAQTD DNO AND TOTAL SALARY NEEDED TO PAY ALL EMPLOYEES IN EACH
DEPT IF THERE ARE AT LEAST 4 EMPLOYEES IN EACH DEPT.
o Ans:
o SELECT DEPTNO, SUM(SAL)
o FROM EMP
o GROUP BY DEPTNO
o HAVING COUNT(*) >3;
3. WAQTD NUMBER OF EMPLOYEES EARNING SAL MORE THAN 1200 IN EACH
JOB AND THE TOTAL SAL NEEDED TO PAY EMP OF EACH JOB MUST EXCEEDS
3800.
o Ans:
o SELECT JOB, COUNT(*), SUM(SAL)
o FROM EMP
o WHERE SAL = 1200
o GROUP BY JOB
o HAVING SUM(SAL) = 1200;
4. WAQTD DEPTNO AND NUMBER OF EMPLOYEES WORKING ONLY IF THERE
ARE 2 EMPLOYEES WORKING IN EACH DEPT AS MANAGER.
o Ans:
o SELECT DEPTNO, COUNT(*)
o FROM EMP
o WHERE JOB = 'MANAGER'
o GROUP BY DEPTNO
o HAVING COUNT(*) = 2;
5. WAQTD JOB AND MAX SALARY OF EMPLOYEES IN EACH JOB IF THE MAX
SALARY EXCEEDS 2600.
o Ans:
o SELECT JOB, MAX(SAL)
o FROM EMP
o GROUP BY JOB
o HAVING MAX(SAL) > 2600;
6. WAQTD THE SALARIES WHICH ARE REPEATED IN EMPLOYEE TABLE.
o Ans:
o SELECT SAL, COUNT(*)
o FROM EMP
o GROUP BY SAL
o HAVING COUNT(*) > 1;
7. WAQTD THE HIREDATE WHICH ARE DUPLICATED IN EMPLOYEE TABLE.
o Ans:
o SELECT HIREDATE, COUNT(*)
o FROM EMP
o GROUP BY HIREDATE
o HAVING COUNT(*) > 1;
8. WAQTD AVG SALARY OF EACH DEPT IF AVG SALARY IS LESS THAN 3000.
o Ans:
o SELECT DEPTNO, AVG(SAL)
o FROM EMP
o GROUP BY DEPTNO
o HAVING AVG(SAL) < 3000;
9. WAQTD DEPTNO IF THERE ARE AT LEAST 3 EMPLOYEES IN EACH DEPT
WHOSE NAMES HAVE CHARACTER 'A' OR 'S'.
o Ans:
o SELECT DEPTNO , COUNT(*)
o FROM EMP
o WHERE ENAME LIKE '%A%' OR ENAME LIKE '%S%'
o GROUP BY DEPTNO
o HAVING COUNT(*) >= 3;
10. WAQTD MIN AND MAX SALARIES OF EACH JOB IF MIN SALARY IS MORE THAN
1000 AND MAX SALARY IS LESS THAN 5000.
o Ans:
o SELECT JOB, MIN(SAL), MAX(SAL)
o FROM EMP
o GROUP BY JOB
o HAVING MIN(SAL) > 1000 AND MAX(SAL) < 5000;
