子查询

就是在一个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));