--查询工资最高的人的名字

select ename ,sal from emp where sal=(select max(sal) from emp );

--求出员工的工资在所有人的平均工资之上的人

select ename ,sal from emp where sal >(select avg(sal) from emp );

--求出每个部门中工资最高的那个人的名字

select ename,sal from emp join (select deptno,max(sal) max_sal from emp group by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);

--求出每个部门的平均薪水

select deptno, avg(sal) from emp group by deptno;

--求出每个部门的平均薪水的等级是多少( 每个部门的平均薪水 ---的等级这个是求等级)

select deptno,grade from salgrade join (select deptno,avg(sal) avg_sal from emp group by deptno) t on t.avg_sal between losal and hisal;

--求部门平均的薪水等级（薪水等级的平均）

select avg(grade) from

(select deptno,grade from salgrade join (select deptno,avg(sal) avg_sal from emp group by deptno) t on t.avg_sal between losal and hisal);

--求雇员中哪些是经理人

select ename from emp where empno in (select mgr from emp);

--求雇员所对应的经理人

select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;

--查询平均薪水最高部门的部门编号

select deptno,avg_sal from

(select deptno,avg(sal) avg_sal from emp group by deptno)--当做一张表，否则没有表可以from

where avg_sal =

(select max(avg_sal)from (select deptno,avg(sal) avg_sal from emp group by deptno ))--当做一个值;

--查询平均薪水最高部门的部门名称

select dname from dept where deptno=

(select deptno from --当做一个值

(select deptno,avg(sal) avg_sal from emp group by deptno)

where avg_sal =

(select max(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 on avg_sal between losal and hisal;

--查询平均薪水等级最低的部门的名称--利用视图可以简化程序代码

select dname from dept where deptno=

( select deptno from

((select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t

join salgrade on avg_sal between losal and hisal))

where grade=

(select min(grade) from

(select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t

join salgrade on avg_sal between losal and hisal)));

--利用视图简化代码

create view my_view as (select deptno ,avg_sal, grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t

join salgrade on avg_sal between losal and hisal);

--创建视图要用sys用户来给scott用户授权

用sys用户登录成功后，

grant create table,create view to scott;

授权成功后才可以利用scott普通用户创建视图；

--用视图来简化上面的代码

select dname from dept where deptno=

( select deptno from

(my_view )

where grade=

(select min(grade) from

my_view ));

--求比普通员工最高薪水还要高的经理人的名称

普通员工：即不在mgr这个里面的就是普通员工

经理人：在mgr这个里面的就是经理人

--普通员工的最高薪水

select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);--对于有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 dname from dept d join (

select deptno,avg(sal) avg_sal from (select ename,sal,deptno from emp where empno in(select mgr from emp )) group by deptno ) s

on d.deptno =s.deptno

where avg_sal=

(select min(avg_sal) from (select deptno,avg(sal) avg_sal from (select ename,sal,deptno from emp where empno in(select mgr from emp )) group by deptno));

--求薪水最高的前5名的雇员

select ename,sal from (select ename,sal from emp order by sal desc) where rownum <=5;

mysql的分页是limit a,b oracle的分页比较特殊，没有limit只有rownum，这个是不显示的一个伪字段。这个字段只能使用小于或者小于等于，不能使用大于或者大于等于。

如果分页只能先查询出rownum这列 select rownum from emp，然后从这里面再rownum>或者<哪个数字。

select rownum ,ename from (select rownum no from emp) t where no>3 and no<10;

--求薪水最高的第6名到第10名的雇员

select ename,sal from (select ename,sal,rownum r from (select ename,sal from emp order by sal desc) ) where r>=6 and r<=10;

--求最后入职的5名员工(按照入职时间降序排列，取出前五个即可)

select ename,hiredate,rownum from(select ename,hiredate from emp order by hiredate desc ) where rownum<=5;

--比较效率

select * from emp where deptno=10 and ename like '%A%' --这个效率高些，跟java的短路与类似

select * from emp where ename like '%A% and deptno=10