SQL Group By Clause Exercise

Q1.display total number of employees work in each departments?
SELECT DEPARTMENT_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Q2.DISPLAY DUPLICATES OF EMAIL?
SELECT EMAI,COUNT(*)
FROM MEPLOYEES
GROUP BY EAIL
HAVING COUNT(*)>1;

Q3. DISPLAY HOW MANY EMPLOYEES WORK UNDER ACH MANAGER?
SELECT MANAGER_ID,COUNT(*)
FROM EMPLOYEES
GROUP BY MANAGER_ID;

Q4.DISPLAY SUM OF SALRY OF EACH DEPARTMENT?
SELECT DEPARTMENT_ID,SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Q4. HOW MAY EMPLOYEES WORK AS ‘IT_PROGRAMMER’?
SELECT JOB_ID,COUNT(*)
FROM EMPLOYEES
WHERE JOB_ID=’IT_PROG’
GROUP BY JOB_ID;

SELECT COUNT(*)
FROM EMPLOYEES
WHERE JOB_ID=’IT_PROG’;

Q5.DISPLAY TOTAL SUM OF SALARY OF EACH DEPARTMENTS?
SELECT DEPARTMENT_ID,SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Q6.DISPLAY TOTAL AVERAGE OF SALARY OF EACH DEPARTMENTS?
SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Q7.DISPLAY MAXIMUM SALARY OF EACH DEPARTMENTS?
SELECT DEPARTMENT_ID,MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Q8.DISPLAY MINUMUM SALARY OF EACH DEPARTMENTS?
SELECT DEPARTMENT_ID,MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Q9.DISPLAY TOTAL NUMBER EMPLOYEES WORK IN 10TH DEPARTMENTS?
SELECT DEPARTMENT_ID,COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID=10
GROUP BY DEPARTMENT_ID;

Q10. DISPLAY DUPLICATES OF FIRST_NAME IN EMPLOYEES?
SELECT FIRST_NAME,COUNT(*)
FROM EMPLOYEES
GROUP BY FIRST_NAME;

Q11. DISPLAY DUPLICATES OF LAST_NAME IN EMPLOYEES?
SELECT LAST_NAME,COUNT(*)
FROM EMPLOYEES
GROUP BY LAST_NAME;

Q12.DISPLAY TOTAL NUMBER EMPLOYEES ,MAXIMUM SALARY,MINIMUM SALARY,AVERAGE SALARY,TOTAL SALARY OF EMPLOYEES?
SELECT DEPARTMENT_ID,MAX(SALARY),MIN(SALARY),AVG(SALARY),SUM(SALARY)
FROM EMPLOYEES;