oracle多表连接查询、条件查询
&求部门平均薪水的等级
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);

&求部门中那些人薪水最高
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);

&求部门平均薪水等级
select deptno,avg(grade) from(
select deptno,ename,grade from emp e join salgrade s on(
e.sal between s.losal and s.hisal)) t group by deptno;
&雇员中那些人是经理人
select empno,ename from emp where empno in(
select distinct mgr  from emp)
&不准用分组函数，求出薪水最高值（面试题）
select ename,sal from emp where sal not in(
select distinct e.sal from emp e join emp e1 on(
e.sal < e1.sal));
&求平均薪水最高的部门的部门编号
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)) ;
2种写法：
select deptno,avg_sal from(
select avg(sal) avg_sal,deptno from emp group by deptno)where avg_sal=(
select max(avg(sal)) from emp group by deptno);
&求平均薪水最高的部门的部门名称
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)));
&求部门经理的等级最低的部门的部门名称
先求平均薪水：
select deptno, avg(sal) avg_sal from emp group by deptno;
再求平均薪水的等级：
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);
再求平均薪水的最低的等级是：
select min(grade) from(
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));
最终：
select dname,t1.deptno,avg_sal,grade from(
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))t1
join dept on(t1.deptno=dept.deptno)where t1.grade=(
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)));

里面有两段代码是完全重复的  哪怎么解决呢？ 下面我们可以创建一个视图；
create view v$_dept_avg_sal_info as
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 dname,t1.deptno,avg_sal,grade from
v$_dept_avg_sal_info t1
join dept on(t1.deptno=dept.deptno)
where t1.grade=(
select min(grade) from v$_dept_avg_sal_info);

&求部门经经理人中平均薪水最低的部门名称

&求比普通员工的最高薪水还高的经理人名称
第一步：先求普通员工的最高薪水
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 ename,sal from (
select ename,sal from emp order by sal desc)where rownum<=5

&求薪水最高的第六列第十名雇员
&面试题：比较效率
select * from emp where deptno=10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno=10;
相比之下第一条语句效率高些  因为先执行数字  如果deptno不等于10就不会在执行后面的了