2017-07-19

select avg(sal) from emp where sal>500 group by deptno;
select deptno,round(avg(sal)) from emp group by deptno having avg(sal)>500;
select max(comm) from emp where deptno=30;
select ename,comm from emp where comm in(select max(comm) from emp where deptno=30);
select job, count(empno),min(sal) from emp group by job;

select * from emp;
select avg(sal),avg(nvl(comm,0)),
case
when
avg(nvl(comm,0))>300 then '奖金不错'
when avg(nvl(comm,0))>=100 and avg(nvl(comm,0))<=300 then '奖金一般'
when avg(nvl(comm,0))<100 then '基本没有奖金'
end 奖金状况
From emp
group by job order by job desc,avg(sal) desc;


SELECT AVG(sal) 岗位平均工资,AVG(NVL(comm,0)) 岗位平均奖金,
(CASE
WHEN AVG(NVL(comm,0))>300 THEN '奖金不错'
WHEN AVG(NVL(comm,0))>=100 AND AVG(NVL(comm,0))<=300 THEN '奖金不错'
WHEN AVG(NVL(comm,0))<100 THEN '基本没有奖金'
END ) 岗位奖金状况
FROM emp
GROUP BY job ORDER BY job DESC,AVG(sal) DESC;