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;