Oracle——函数及分组练习

Oracle——函数及分组练习

1.求emp表中工资总和

select sum(sal) from emp;

2.求emp表中工资平均值

select avg(sal) from emp;

3.求emp表中最小工资

select min(sal) from emp;

4.求emp表中最大工资

select max(sal) from emp;

5.求emp表的总行数,再求出部门编号去重后的总行数

select count(empno),count(distinct deptno) from emp;

6.求有奖金的员工的行数

select count(comm) from emp where comm!=0;

7.求所有员工的平均奖金

select avg(nvl(comm,0)) from emp;

8.求每个部门的工资总和

select deptno,sum(sal) from emp group by deptno;

9.求每个部门的平均工资

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

10.求每个部门的最小工资

select deptno,min(sal) from emp group by deptno;

11.求每个部门的最大工资

select deptno,max(sal) from emp group by deptno;

12.求每个部门中从事不同工作的员工的工资总和

select deptno,job,sum(sal) from emp group by deptno,job;

13.求每个部门中从事不同工作的员工的平均工资

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

14.求每个部门中从事不同工作的员工的最小工资

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

15.求每个部门中从事不同工作的员工的最大工资

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

16.查出平均工资大于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

17. 查询各个部门的平均工资

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

18. 显示各种职位的最低工资

select job,min(sal) from emp group by job;

19. 查询每个部门的最高工资

select deptno,max(sal) from emp group by deptno;

20. 查询每个部门,每种职位的最高工资

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

21. 查询各部门工资最高的员工信息

select * from emp where sal in(select max(sal) from emp group by deptno);