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就不会在执行后面的了