oracle sql语句训练,oracle的sql语句训练

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

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

原文：http://www.cnblogs.com/fengli9998/p/7017898.html