Oracle练习2

1. 求emp表中工资总和、工资平均值、最小工资、最大工资

   select sum(sal) from emp;
   select avg(sal) from emp;
   select min(sal) from emp;
   select max(sal) from emp;
   

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

   select count(*) from emp;
   select sum(distinct deptno) from emp;
   

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

   select count(comm) from emp;
   

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

   select avg(sal) from emp;
   

5. 求每个部门的工资总和、平均工资、最小工资、最大工资

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

6. 求每个部门中从事不同工作的员工的工资总和、平均工资、最小工资、最大工资

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

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

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

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

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

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

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

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

    select * from emp e where e.sal=(select max(sal) from emp where deptno=e.deptno);
    

11. 按工作种类进行分组，显示各个组内雇员工资的最高、最低、合计和平均值，并对各值去掉小数四舍五入

    select round(max(sal),0),round(min(sal),0),round(sum(sal),0),round(avg (sal),0)from emp group by job;
    

12. 求EMP表中1980、1981、1982年加入公司的员工数量

    select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy') having to_char(hiredate,'yyyy') in ('1980','1981','1982');