group by and having clause

GROUP BY AND HAVING CLAUSE

1.	Find the number of employees Department wise.

Ans : 

Select deptno, count(*) no_of_employees from employee group by deptno;

2.	Find the number of employees Job wise.

Ans : 

Select job, count(*) no_of_employees from employee group by job;


3.	Find the total salary distribution job wise in the year 1981. 

Ans : 

select job,sum(12*sal) from emp where to_char(hiredate,'YYYY') = '1981'
group by job ;


4.	Display the number of employees for each job group and department number wise. 

Ans : 

select d.deptno,e.job,count(e.job) from emp e,dept d where e.deptno(+)=d.deptno group by e.job,d.deptno;


5.	List the number of employees in each department where the number is more than 3. 

Ans : 

select deptno,count(*) from emp group by deptno  having count(*) > 3;


6.	List the names of departments where at least 3 are working in that department. 

Ans : 

select d.dname,count(*) from emp e ,dept d where e.deptno = d.deptno 
      group by d.dname 
                           having count(*) >= 3  ;





7.	List the department details where at least two employees are working 

Ans : 

select deptno ,count(*) from emp group by deptno 
       having count(*) >= 2;


8.	List the details of the department where maximum number of employees are working 

Ans : 

select * from dept where deptno in 
      (select deptno from emp group by deptno         
      having count(*) in 
      (select max(count(*)) from emp group by deptno) ); 
(OR) 

select d.deptno,d.dname,d.loc,count(*) from emp e ,dept d 
     where e.deptno = d.deptno group by d.deptno,d.dname,d..loc 
     having count(*) = (select max(count(*) ) from emp group by deptno);


9.	List the name of the department where more than average numbers of employees are working.

Ans : 

select d.dname from dept d, emp e where e.deptno = d.deptno 
group by d.dname 
having count(*) > (select avg(count(*)) from emp  group by deptno);

10.	List the manager name having maximum number of employees working under him.

Ans : 

select m.ename,count(*) from emp w, emp m 
where w.mgr = m.empno  
group by m.ename
having count(*) = (select max(count(*)) from emp group by mgr);      




11.	Check whether all the employee numbers are indeed unique.

Ans : 

select   empno,count(*)  from emp group by empno;

12.	Find all the employees who earn the minimum Salary for each job wise in ascending order. 

Ans : 

select * from emp where sal in 
     (select min(sal) from emp group by job) 
     order by sal asc;


13.	Find out all the employees who earn highest salary in each job type. Sort in descending salary order. 

Ans : 

select * from emp where sal in 
     (select max(sal) from emp group by job) 
     order by sal desc;


14.	Find out the most recently hired employees in each department order by joining date. 

Ans : 

select * from emp  e where hiredate in 
     (select max(hiredate) from emp where e.deptno =  deptno ) 
      order by hiredate;


15.	List the number of employees and Average salary within each department for each job 

Ans : 

select count(*),avg(sal),deptno,job from emp 
      group by deptno,job;


16.	Find the maximum average salary drawn for each job except for ‘President’. 

Ans : 

select max(avg(sal)) from emp  where job != 'PRESIDENT' group by job;


17.	List the department number and their average salaries for department with the average salary less than the averages for all departments.

Ans : 

select deptno,avg(sal) from emp group by deptno
having avg(sal) <(select avg(Sal) from emp);