The grouping function for Oracle intensive exercises

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, including "man" in the job name. 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 salary greater than 2500 per job

Select deptno,job from EMP GROUP by Deptno,job have avg (SAL) >2500;

9. Show that the job name includes "man". And the average wage is more than 1000 of the job name and average wage

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