AGGREGATE FUN/GROUP BY SOLVED EXAMPLE

Q1) Conside The following table

EID	FNAME	LNAME	EMAIL	PHONENO	HIREDATE	JOB_ID	SALARY	COMMISSION	MANAGER_ID	DEPTID
100	Steven	King	SKING	9811234567	17-Jun-87	PRES	24000	null	null	90
101	Neena	Kochhar	NKOCHHAR	9711124568	21-Sep-89	VP	17000	null	100	90
102	Lex	De Haan	LDEHAAN	9345124569	13-Jan-93	VP	17000	null	100	90
103	Alexander	Hunold	AHUNOLD	9823424567	3-Jan-90	IT	9000	null	102	60

·         displays number of employees work in each department

SELECT COUNT(*) FROM EMPLOYEE GROUP BY DEPTID;

·         DISPLAY total salary paid to employees work in each department.

SELECT SUM(SALARY),DEPTID FROM EMPLOYEE GROUP BY DEPTID;

·         displays number of employees, total salary paid to employees work in each department

SELECT COUNT(EID),SUM(SALARY),DEPTID FROM EMPLOYEE GROUP BY DEPTID;

·         displays the total salary paid to employees IN EACH DEPARTMENT department_id AND WITHIN IT FOR EACH RESPECTIVE JOB.

SELECT SUM(SALARY),JOBID,DEPTID  FROM  EMPLOYEE GROUP BY JOBID,DEPTID;

·         displays the department id, number of employees of those groups that have more than 2                employees

SELECT COUNT(EID),DEPTID FROM EMPLOYEE GROUP BY DEPTID HAVING COUNT(EID)>2;

·         Calculate the number of employee in all departments except DEPTNO 30, show ONLY departments with more than ten employees, and sort by DEPTNO

         SELECT COUNT(EID), DEPTID FROM EMPLOYEE WHERE DEPTID<>30 GROUP BY DEPTID ORDER BY DEPTID;

·         Display only those Departments where Average salary is greater than 80.

SELECT DEPTID, AVG(SALARY) FROM EMPLOYEE GROUP BY DEPTID HAVING AVG(SALARY)>80;

Display the difference of highest and lowest salary of each department having maximum  

    salary>400.

SELECT MAX(SALARY)-MIN(SALARY) ,DEPTID FROM EMPLOYEE GROUP BY DEPTID HAVING MAX(SALARY)>400;

Display the jobs where the number of employees is less than 3.

SELECT COUNT(EID) ,JOBID FROM EMPLOYEE GROUP BY JOBID HAVING COUNT(EID)<3;

     

Display average sal of each department for the jobs ‘’ VP’ only.

SELECT AVG(SALARY) ,DEPTID FROM EMPLOYEE WHERE JOBID=”VP” GROUP BY DEPTID;

DISPLAY NAME OF EMPLOYEES WHOSE NAME CONATINS EITHER “TT” OR “LL”

SELECT * FROM EMPLOYEE WHERE ENAME LIKE “%TT%” OR ENAME LIKE “%LL%”;

INSERT A RECORD WITH SUITABLE DATA IN THE TABLE HAVING SYSTEM DATE AS THE HIRE DATE.

INSERT INTO EMPLOYEE VALUES(106,”RAHUL””SOOD”,RAHUL@GMSAIL.COM, 9847754555,SYSDATE(),”CLERK”,NULL,102,80);

DISPLAY ALL THE EMPLOYEES WHO WERE HIRED IN YEAR 1995

SELECT * FROM EMPLOYEE WHERE YEAR(HIREDATE)=1995;

FIND ALL THE EMPLOYEES WHO HAVE NO MANAGER

SELECT * FROM EMPLOYEE WHERE MGRID IS NULL;

FIND OUT NUMBER OF EMPLOYEES HAVING “ VP” AS JOB

SELECT  COUNT(EID) FROM EMPLOYEE GROUP BY JOBID HAVING JOBID=”VP”;

LIST MINIMUM AND MAXIMUM SALARY FOR EACH JOB TYPE

SELECT MIN(SALARY),MAX(SALARY) FROM EMPLOYEE GROUP BY JOBID;