以下的文章,主要为大家在实际工作中提供一种解决方法。
---用户名: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
- )