«
»
以下的文章,主要为大家在实际工作中提供一种解决方法。
---用户名: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
Oracle实际应用时常用的基本功 - 站长学院 最新更新 | 加入收藏 | 设为首页首页- 互联网软件
- IDC产品
- 站长交易
- 项目外包
- 商业信息
- 招聘
- 公司库
- 资 讯
- 下 载
- 学 院
- 工 具
- 论 坛
热门关键字:CSS布局配色参考Dreamweaver8 apacheApacheIIS6AjaxWMLOracle实际应用时常用的基本功
2010-9-28 18:43:00查看学习心得{GetProperty(Content)}
«
»