solution to aggregate functions

aggregate.sql

Aggregate Function

1.	Find the maximum,minimum, average salary and also the number of employees assigned to department 20.

>>  select max(sal),min(sal),avg(sal),count(empno) from emp where deptno=20;

2.	Find the maximum,minimum, average salary and also the number of employees employed under designation ‘Salesman’.
>>  select max(sal),min(sal),avg(sal),count(empno) from emp group by job having job='SALESMAN';


3.	Find the maximum,minimum, average salary and commission for employees those who have at least 0 commission.
>>select max(sal),min(sal),avg(sal),comm from emp group by comm having comm>=0;
4.	Find out the number of employees from each department.
>> select count(ename) from emp group by deptno;
5.	Find out the average number of employees in each department.
>> select avg(count(ename)) from emp group by deptno;
6.	Find the minimum and maximum joining date for each department according to their job.
 
7.	Count how many employees get no commission

>> select count(ename) from emp where comm is null;

8.	Calculate and show average commission of all the employees working in the institute0
   >> select avg(comm) from emp;