Grouping functions of oracle reinforcement exercises

Grouping functions of oracle reinforcement exercises

1. Show jobs with an average salary of> 2000

Select job, avg (sal) from emp group by job having avg (sal)> 2500;

2. Calculate the average salary of over 2000 jobs and the average salary of over 3000 jobs

Select job, avg (sal) From emp where sal> 2000 group by job having avg (sal)> 3000;

3. Find the highest and lowest salaries for each department

Select deptno, max (sal), min (sal) From emp group by deptno;

4. Find the highest and lowest salaries for each position in each department

Select deptno, max (sal), min (sal) from emp group by job, deptno;

SelectDeptno, job,Max(Sal ),Min(Sal)FromEmpWhereJobIs notnull groupJob, deptno;

5. display the sum of the average salary, highest salary, minimum wage and salary of employees whose job name contains "MAN"

Select avg (sal), max (sal), min (sal), sum (sal) from emp where job like '% MAN % ';

6. The number of employees in department 20 is displayed.

Select count (*) from emp where deptno = 20;

7. Show the names and average salaries of departments with an average salary greater than 2000

Select dname, avg (sal) From dept d, emp e where e. deptno = d. deptno group by dname having avg (sal)> 2000;

8. shows the average salary of each department over 2500 of all departments and jobs

Select deptno, job From emp group by deptno, job having avg (sal)> 2500;

9. The name of a job that contains "MAN" and whose average salary is greater than 1000 is displayed.

Select job, avg (sal) From emp group by job having avg (sal)> 1000, and job like '% MAN % ';

10. shows the average salary of the Department with the highest average salary

11. List jobs with a minimum wage of more than 1500

Select job From emp group by job having min (sal)> 1500;

12. List the number of employees and the average working life of each department

Select deptno, count (*), avg (sysdate-hiredate)/365) From emp group by deptno;

Select deptno, avg (months_between (sysdate, hiredate)/12) from emp group by deptno;