#### 查询比本部门平均薪水高的员工信息

###### 解法一：

select d.deptno,e.empno,e.ename,e.sal,d.avgsal
from emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno = d.deptno and e.sal > d.avgsal
order by 1;

###### 解法二：相关子查询：将主查询中的某些值作为参数传递给子查询

select e.empno,e.ename,e.sal,(select avg(sal) from emp where deptno = e.deptno) avgsal
from emp e
where e.sal > (select avg(sal) from emp where deptno = e.deptno);

#### 查询每年入职的员工数

select count(*) total,
sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",
sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",
sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",
sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"
from emp

#### 涨工资问题,总裁1000 经理800 其他400

###### 解法一：

select ename,job,sal 涨前，
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal + 400
end 涨后
from emp;

###### 解法二：

select ename,job,sal 涨前,
decode(job, 'PRESIDENT', sal+1000,
'MANAGER', sal+800,
sal+400) 涨后
from emp;

#### 查询员工信息：员工号 姓名 月薪工资级别（不等值连接）

where e.sal between s.losal and s.hisal

#### 按部门统计员工数：部门号 部门名称 人数

select d.deptno,d.dname,count(e.empno)
from emp e,dept d
where e.deptno（+） = d.deptno
group by d.deptno,d.dname

#### 层次查询

select level,empno,ename,mgr
from emp
connect by prior empno=mgr