栏目导航
热点推荐
- oracle查询表名、表的列名方法
- Oracle数据库迁移介绍
- 快速掌握删除Oracle 10g垃圾表的
- Oracle数据库如何处理临时数据?
- Oracle教程:如误添加数据文件如
- Oracle数据库安全基础知识
- Oracle新手最常碰到的6个错误及
- Oracle数据库的使用技巧五则
- 将Oracle中的数据更改后出错的解
- 在不安装Oracle客户端的情况下进
- 讲解Oracle数据库冷备份恢复的具
- 当数据积累到一定时间后执行会越
阅览排行
Oracle实际应用时常用的基本功
www.jz123.cn 2010-09-29 来源: 中国建站 责任编辑(袁袁) 我要投递新闻
以下的文章,主要为大家在实际工作中提供一种解决方法。
---用户名:scott
---密 码:tiger
---*********Oracle表连接与子查询示例************
---求部门中哪些人的薪水最高
- 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_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 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;
---雇员中哪些人是经理人
- select ename from emp where empno in (select distinct mgr from emp);
---不用组函数,求薪水的最高值
- select sal from emp where sal not in
- (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
---用组函数,求薪水的最高值
- select max(sal) from emp;
---求平均薪水最高的部门的部门编号
- select deptno , avg_sal from
- (select avg(sal) avg_sal,deptno from emp group by deptno) t
- where avg_sal =
- (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t);
----组函数嵌套的写法
- select deptno , avg_sal from
- (select avg(sal) avg_sal,deptno from emp group by deptno) t
- 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) t
- where avg_sal =
- (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t)
- );
---求平均薪水的等级最低的部门的部门名称
- select avg(sal) avg_sal,deptno from emp group by deptno
--部门平均薪水
- select min(avg_sal) from
- (
- select avg(sal) avg_sal,deptno from emp group by deptno
- )
--平均工资的最小值
- select avg_sal,deptno from
- (select avg(sal) avg_sal,deptno from emp group by deptno) t
- where avg_sal =
- (
- select min(avg_sal) from
- (
- select avg(sal) avg_sal,deptno from emp group by deptno
- )
- )
--平均工资的最小值及部门编号
- select t.avg_sal,t.deptno,s.grade from
- (select avg(sal) avg_sal,deptno from emp group by deptno) t
- join salgrade s on (t.avg_sal between s.losal and s.hisal)
- where avg_sal =
- (
- select min(avg_sal) from
- (
- select avg(sal) avg_sal,deptno from emp group by deptno
- )
- )
--平均工资的最小值及部门编号和工资等级
- select d.dname,t.avg_sal,t.deptno,s.grade from
- (select avg(sal) avg_sal,deptno from emp group by deptno) t
- join salgrade s on (t.avg_sal between s.losal and s.hisal)
- join dept d on (t.deptno = d.deptno)
- where avg_sal =
- (
- select min(avg_sal) from
- (
- select avg(sal) avg_sal,deptno from emp group by deptno
- )
- )
--平均工资的最小值及部门编号和工资等级及部门名称
----Another 按照题意的写法
- select t1.deptno,t1.avg_sal,grade,d.dname 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 d on (t1.deptno = d.deptno)
- 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 s on (t.avg_sal between s.losal and s.hisal)
- )
- );
---创建视图或者表,如果没有权限
- conn sys/sys as sysdba;
--已连接。
- grant create table, create view to scott;
--授权成功。
---创建视图
- create view v
0
上一篇:ORACLE里的set和col命令整理 下一篇:oracle中SQL递归语句应用实例