/*1.1*/
select * from emp where nvl(comm,0)<500;
/*1.2*/
select sum(decode(s.grade, 1, 1)) 等级一,
       sum(decode(s.grade, 2, 1)) 等级二,
       sum(decode(s.grade, 3, 1)) 等级三,
       sum(decode(s.grade, 4, 1)) 等级四,
       sum(decode(s.grade, 5, 1)) 等级五
from emp e, salgrade s
where e.sal >= losal and e.sal <= hisal;
/*1.3*/
select sal,sum(sal),avg(sal) 
from emp e
select round(123.345,2) from dual

select e.ename ,
       e.sal,
       sum(sal) over(partition by deptno) sum_sal,
       round(avg(sal) over(partition by deptno),2) avg_sal
  from emp e;
/*1.4*/
select e.ename,
       e.deptno,
       rank() over(partition by deptno order by sal desc)-1 高于工资人数,
       rank() over(partition by deptno order by sal)-1 低于工资人数
  from emp e ;
/*1.5*/
select  ename,deptno,sal,rn "工资排名" from 
(select deptno,ename,sal,row_number() over(partition by deptno order by sal desc) rn
from emp) e; 

select * from emp where ename='KING';
/*1.6*/
select ename,job,sal,avg_sal "工种平均工资"
from(select ename,job,sal,round(avg(sal) over(partition by job),2) avg_sal,empno from emp)
where sal<avg_sal 
order by empno;
/*1.7*/
/*1.8*/
select e_before.ename "时间点入职前ENAME",
e_before.job "时间点入职前JOB",
e_before.sal "时间点入职前SAL",
e_after.ename "时间点入职后ENAME",
e_after.job "时间点入职后JOB",
e_after.sal "时间点入职后SAL"
from
(select * from emp where to_char(hiredate,'YYYY-MM-DD')<'1981-09-09') e_before,
(select * from emp where to_char(hiredate,'YYYY-MM-DD')>'1981-09-09') e_after
where e_before.job=e_after.job;
/*1.9*/
select e.ename,e.job,d.dname
from emp e,dept d
where e.job=(select job from emp where ename='SCOTT') and e.deptno=d.deptno and e.ename!='SCOTT';  
/*1.10*/
select 
distinct  e1.deptno,
          e1.job,
          avg(sal) over(partition by e1.deptno,e1.job) "平均工资"
from emp e1,
(select e.deptno,e.job from emp e group by e.deptno,e.job having count(*)>=2) e2
where e1.deptno=e2.deptno and e1.job=e2.job
order by "平均工资";