Oracle子查询语句
就是在一个select语句中套了另一个子查询语句：
在emp中工资最高的人的名字：
SQL>select ename ,sal from emp where sal=(select max(sal) from emp);
工资高于平均工资的人：
SQL>select ename,sal from where sal>(select avg(sal) from emp);
查询每个部门中工资最高的人：
SQL>select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on(emp.sal = t.max_sal and emp.deptno = t.deptno);
查询部门的平均工资：
SQL>select avg(sal),deptno from emp group by deptno;
查询雇员编号，和姓名以及雇员的经理：
SQL>select empno,ename,mgr from emp;
查询每一个雇员的经理是谁：
SQL>select e1.ename ,e2.ename from emp e1 ,emp e2 where e1.mgr = e2.empno;
对于雇员的工资进行等级划分：
SQL>select ename ,dname,grade from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal and job<>'CLERK';
部门平均薪水的等级：
SQL>select deptno ,avg_sal,grade from,
(select deptno ,avg(sal) avg_sal from emp group by deptno )t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
部门平均的薪水等级：
SQL>select avg(grade) from
(select deptno ,ename,grade from emp join salgrade s
on (emp.sal between s.losal and s.hisal)) t group by deptno;
)
SQL>select deptno,avg(grade) from
(select deptno,ename,grade from emp join salgrade) s
on (emp.sal between s.losal and s.hisal))t group by deptno;
雇员中那些人是经理人：
SQL>select ename from emp where empno in(select mgr from emp);
不准用组函数，求薪水的最高值：
（这个是中没有最大值：）
SQL>select distinct e1.sal from emp e1 join emp e2
on(e1.sal < e2.sal);
（除去没有最大值的就是最大值）
SQL>select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2)
on(e1.sal <e2.sal>);
求平均薪水最高的部门的部门编号：
求出每一个部门的平均工资：
SQL>select deptno,avg(sal) from emp group by deptno;
求出所有部门中平均工资最高的：
SQL>select max(sal_avg) from
(select avg(sal) sal_avg ,deptno from emp group by deptno);
求出平均最高工资的部门号：
SQL>select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =(select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno));
求出最高平均工资的部门名称：
SQL>select dname from dept where deptno=
(
select deptno from
(select avg(sal)avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno)));
求出平均薪水的等级最低的部门的名称：
求出了每个部门的平均薪水：
SQL>select deptno,grade,avg_sal from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal);
求出了最低薪水的部门：
SQL>select min(grade) from
(
select deptno,grade,avg_sal from
(select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
);
求出平均薪水的等级最低的部门的名称：
创建视图可以简化这个过程：
部门经理人中平均薪水最低的部门
比普通员工的最高薪水还要高的经理人名称：
（求出普通员工中的最高工资）
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null);
select ename from emp
where empno in(select distinct mgr from emp where mgr is not null)
and
sal >
(
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null)
);
面试题：比较这两个语句的效率：
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno=10;
第一个效率会高点（通过数字就可以排除很多项）；
薪水最高的前五名雇员：
select empno,ename from emp;
rownum :相当于每一行的行号：只能和小于和小于等于一起使用；不能和大于或等于使用：
select empno,ename from emp where rownum<5;
select empno.ename from emp where rownum<=5;
(查询大于行号大于10的)
select rownum r,ename from emp;
select ename from (select rownum r,ename from emp) where r>10;
(查询工资最高的人，按倒序排列)
select ename,sal from emp order by sal desc;
select ename,sal from
(select ename,sal from emp order by sal desc )where rownum <=5;
薪水最高的第六个人到第十个人：
（查看rownum）
select ename ,sal ,rownum r from emp order by sal desc;
(对rownum 进行排序)
select ename ,sal ,rownum r from (select ename,sal from emp order by sal desc);
select ename,sal from(select ename ,sal ,rownum r from (select ename,sal from emp order by sal desc));
