多表连接
来源:互联网 发布:apache 开启虚拟主机 编辑:程序博客网 时间:2024/05/18 02:09
----求各个部门里薪水最高的人
SQL> select ename, sal from emp
2 join (select deptno, max(sal) max_sal from emp group by deptno) t
3 on (emp.deptno = t.deptno and emp.sal = t.max_sal);
----求各个部门平均薪水的等级
SQL> select deptno, avg_sal, grade from
2 (select deptno, avg(sal) avg_sal from emp group by deptno) t
3 join salgrade s on (t.avg_sal between s.losal and s.hisal);
----求各个部门平均的薪水等级(没人薪水等级的平均数)
SQL> select deptno, avg(grade) from
2 (select deptno, grade from emp join salgrade on (sal between losal and hisal)) t
3 group by deptno;
----求雇员中谁是经理
SQL> select distinct ename from emp where empno in (select mgr from emp);
—---不用组函数求薪水的最大值
SQL> select distinct sal from emp
2 where sal not in (select distinct e1.sal from emp e1 join emp e2
3 on (e1.sal < e2.sal));
----求平均薪水最高的部门的部门编号
SQL> select deptno from
2 (select deptno, avg(sal) avg_sal from emp group by deptno)
3 where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno));// = 部门平均薪水的最大值
或者:
SQL> select deptno from
2 (select deptno, avg(sal) avg_sal from emp group by deptno)
3 where avg_sal = (select max(avg(sal)) from emp group by deptno);
----求平均薪水最高的部门的部门名称
//SQL> select dname from dept where deptno = 平均薪水最高的部门的部门编号;
SQL> select dname from dept where deptno =
2 (select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno)
3 where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno)));
----求平均薪水等级最低的部门的部门名称
SQL> select deptno, avg(sal) avg_sal from emp group by deptno;//求部门平均薪水
DEPTNO AVG_SAL
---------- ----------
30 1566.66667
20 2175
10 2916.66667
SQL> select deptno, grade gra from//求部门平均薪水等级
2 ( select deptno, avg(sal) avg_sal from emp group by deptno )
3 join salgrade on (avg_sal between losal and hisal);
DEPTNO GRA
---------- ----------
10 4
20 4
30 3
SQL> select min(gra) from
(select deptno, grade gra from ( select deptno, avg(sal) avg_sal from emp group by deptno )
join salgrade on (avg_sal between losal and hisal));//求部门平均薪水等级的最小值
MIN(GRA)
----------
3
SQL> select deptno from (select deptno, grade gra from ( select deptno, avg(sal
) avg_sal from emp group by deptno ) join salgrade on (avg_sal between losal and
hisal)) where gra = (select min(gra) from (select deptno, grade gra from ( sel
ect deptno, avg(sal) avg_sal from emp group by deptno ) join salgrade on (avg_sa
l between losal and hisal)));//求平均薪水等级最低的部门的部门编号
DEPTNO
----------
30
SQL> select dname from dept where deptno =
(
select deptno from
(
select deptno, grade gra from
(
select deptno, avg(sal) avg_sal from emp group by deptno
)
join salgrade on (avg_sal between losal and hisal)
)
where gra =
(
select min(gra) from
(
select deptno, grade gra from
(
select deptno, avg(sal) avg_sal from emp group by deptno
)
join salgrade on (avg_sal between losal and hisal)
)
)
);//求平均薪水等级最低的部门的部门名称
DNAME
--------------
SALES
////////////////////////创建视图
SQL> create view v$_dept_avg_sal_info as
2 select deptno, grade gra from ( select deptno, avg(sal) avg_sal from emp g
roup by deptno ) join salgrade on (avg_sal between losal and hisal);
SQL> select dname from dept where deptno =
(
select deptno from
(
v$_dept_avg_sal_info
)
where gra =
(
select min(gra) from
(
v$_dept_avg_sal_info
)
)
);//求平均薪水等级最低的部门的部门名称
----求部门经理平均薪水最低的部门名称
SQL> select distinct e1.ename, e1.deptno, e1.sal from emp e1 join emp e2 on (e2.
mgr = e1.empno);//求出所以经理的部门编号和薪水
SQL> select deptno, avg(sal) avg_sal from (select distinct e1.ename, e1.deptno,
e1.sal from emp e1 join emp e2 on (e2.mgr = e1.empno)) group by deptno;//求出各部门经理的平均薪水
SQL> create view v$_dept_mgr_avg_sal as
2 select deptno, avg(sal) avg_sal from (select distinct e1.ename, e1.deptno,e1.sal
from emp e1 join emp e2 on (e2.mgr = e1.empno)) group by deptno;//创建视图
SQL> select min(avg_sal) from v$_dept_mgr_avg_sal;求出部门经理平均薪水的最小值
SQL> select deptno from v$_dept_mgr_avg_sal where avg_sal = (
2 select min(avg_sal) from v$_dept_mgr_avg_sal);//部门经理平均薪水最低的部门编号
SQL> select dname from dept join
2 (select deptno from v$_dept_mgr_avg_sal where avg_sal = (
3 select min(avg_sal) from v$_dept_mgr_avg_sal))
4 t on dept.deptno = t.deptno;//部门经理平均薪水最低的部门名称
----求薪水比普通雇员的最高薪水还要高的部门经理的名字
SQL> select t.ename from (select distinct e1.ename, e1.sal from emp e1 join emp
e2 on (e1.empno = e2.mgr)) t where t.sal > ( select max(sal) from emp where empn
o not in (select distinct mgr from emp where mgr is not null));
----薪水最高的前5名雇员
SQL> select ename, sal from
2 (select ename, sal from emp order by sal desc) where rownum <= 5;
----求薪水最高的6到10名雇员
SQL> select r, ename, sal from
2 (select rownum r, ename, sal from
3 (select ename, sal from emp order by sal desc)) where r >= 6 and r <= 10;
- 【Oracle】多表连接查询——内连接、左连接、右连接、全连接(外连接)、完全连接
- Mysql连接查询(自然连接、自身连接、外连接、多表连接)总结
- 数据库多表连接查询(外连接和内连接)
- mysql 左右连接 以及内连接 多表连接
- 数据库多表连接查询(外连接和内连接)
- oracle 多表连接 内连接 外连接
- 多表连接查询-内连接-外连接
- 多表连接查询
- 多表连接查询
- 多表连接查询
- 多表连接查询
- 多表连接Update
- 多表连接查询
- 多表连接原理!
- 多表连接
- SQL多表连接
- oracle多表连接
- 多表连接查询
- 动作编辑器
- 【转】java web项目改名之后tomcat服务器项目自动重命名
- 传智播客成都中心——JavaEE+3G精品就业班
- 中科院分词系统ICTCLAS50 (Linux)
- WEB应用使用过滤器解决编码乱码问题
- 多表连接
- 学习《数据结构》有感之以字符串形式输入链表实现多项式相加
- java序列化—Serializable 小结
- 公共语言运行时(CLR),它负责管理和执行由.NET 编译器编译产生的中间语言代码
- EBS在后台取消请求
- linux wall命令详解
- android ListView监听滚动条滚动到底部时继续加载
- 第一篇博客
- android项目窗口布局属性介绍