1. Show jobs with average salary of >2000
Select Job,avg (SAL) from EMP Group BY job have avg (SAL) >2500;
2. Calculate the salary above 2000, the average salary of various positions is more than 3000 of the position and average salary
Select Job,avg (SAL) from EMP where sal>2000 GROUP BY job has avg (SAL) >3000;
3. Find the highest and lowest wages for each department
Select Deptno,max (Sal), Min (sal) from the EMP group by DEPTNO;
4. Find the highest and lowest wages for each position in each department
Select Deptno,max (Sal), Min (sal) from the EMP group by Job,deptno;
Select deptno,job,Max(sal),min(sal) from emp where job is notnull Group by Job,deptno;
5. Show the employee's average salary, maximum wage, minimum wage and wage, and
Select AVG (SAL), Max (Sal), Min (sal), sum (SAL) from the EMP where job like '%man% ';
6. Show number of employees in department 20th
Select COUNT (*) from EMP where deptno=20;
7. Show the name and average salary of the department with average salary greater than 2000
Select Dname,avg (SAL) from Dept d,emp E where E.deptno=d.deptno GROUP by Dname have avg (SAL) >2000;
8. Show departments and jobs for each department with a average wage greater than 2500 per job
Select deptno,job from EMP GROUP by Deptno,job have avg (SAL) >2500;
9. Work name and average salary with "man" in the job name and average salary greater than 1000
Select Job,avg (SAL) from the EMP group by job has the AVG (SAL) >1000 and job like '%man% ';
10. Show the highest average wage in the department
11. List of jobs with a minimum wage greater than 1500
Select job from EMP GROUP by Job have min (sal) >1500;
12. List the number of employees and average working life of each department
Select Deptno,count (*), AVG ((sysdate-hiredate)/365) from the EMP group by DEPTNO;
Select Deptno,avg ((Months_between (sysdate,hiredate))/12) from the EMP group by DEPTNO;
The grouping function for Oracle intensive exercises