Oracle实战练习(续一)
来源:互联网 发布:php xpath html 编辑:程序博客网 时间:2024/06/16 18:25
#非等值连接
#查询出员工的薪水等级
select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal) where ename not like'_A%';
#查出上司是谁,采用自连接
select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr=e2.empno);
#外连接, 将左边的king也显示出来
select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.empno);
#查询出员工姓名,所属部门名称
select ename, dname from emp e join dept d on(e.deptno = d.deptno);
#运用右外连接将右边的表的数据显示出来
select ename, dname from emp e right outer join dept d on (e.deptno=d.deptno);
#full join
select ename, dname from emp e full join dept d on (e.deptno=d.deptno);
#求部门平均薪水的等级
#1.首先求部门的平均薪水
select deptno, avg(sal) from emp group by deptno;
#2.将上面的SQL语句看成是一张表,取别名为t
select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from em
p group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal);
#求部门平均薪水等级
#1.求出每个员工的薪水等级
select deptno,ename, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal);
#2.求出每个部门的薪水等级,并按部门编号分组
select deptno,avg(grade) from (select deptno, ename, grade from emp join sa
lgrade s on(emp.sal between s.losal and s.hisal) )group by deptno;
#雇员中有哪些人是经理人
#1.首先查询出哪些人是经理
select distinct mgr from emp;
#2.
select ename from emp where empno in(select distinct mgr from emp);
#不准用组函数,求薪水的最高值(面试题) (使用自连接)
#1.采用自连接, 记录都小于e2表中的每一个记录,那么肯定有一个是连接不上条件的,那么这个就是最大值
select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal);
select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
#求出来的最高薪水值是5000
#求平均薪水最高的部门的部门编号
#1.求平均薪水,按部门编号分组
select deptno, avg(sal) from emp group by deptno;
#2.求平均薪水最高的
select max(avg_sal) from (select avg(sal) as avg_sal from emp group by dep
tno); # 2916.66667
#3.求平均薪水最高的部门编号(平均薪水in最高薪水)
select deptno, avg_sal from (select avg(sal) avg_sal, deptno from emp group
by deptno) where avg_sal =(select max(avg_sal) from (select avg(sal) avg_sal, d
eptno from emp group by deptno));
#求平均薪水最高的部门的部门名称
#1.求平均薪水最高的
select max(avg_sal) from (select avg(sal) avg_sal, deptno from emp group by deptno);
#2.再把部门编号求出来
select deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno));
#3.求出部门名称
select dname, deptno from dept where deptno in(select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal,deptno from emp group by deptno)));
#视图就是一张虚表
create view v$dept_avg_sal_info as (select deptno, grade, avg_sal 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 dname, t1.deptno, grade, avg_sal from v$dept_avg_sal_info t1 join dept on(t1.deptno = dept.deptno) where t1.grade = (select min(grade) from v$dept_avg_sal_info);
#求部门经理人中平均薪水最低的部门名称
select deptno, empno, ename, mgr from emp where mgr is not null;
select ename, mgr from emp;
select e1.empno, e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
#1.查出谁是经理人
select e2.deptno, e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno);
create view v$_mgr_sal_info as select e2.deptno,e2.ename, e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno);
#2.求平均薪水
select deptno, avg(sal) avg_sal from emp group by deptno;
#求经理人的平均薪水
select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno;
#3.求平均薪水最低的
select min(avg_sal) from (select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno);
#4.求平均薪水最低的部门名称
select deptno, avg_sal from (select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno) where avg_sal = (select min(avg_sal) from (select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno));
#比普通员工的最高薪水还要高的经理人名称
#1.经理人不为空的
select distinct mgr from emp where mgr is not null;
#2.选出不是经理人的薪水最高的()
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
#
select ename,sal 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));
- Oracle实战练习(续一)
- Oracle实战练习(续二)
- Oracle实战练习(续三)
- Oracle实战练习(续四)
- Oracle实战练习
- oracle存储过程实战练习
- Erlang实战练习(一)
- Oracle练习(一)
- Oracle练习(一)
- 【笔记】oracle练习<一>
- Oracle练习总结一
- oracle数据库 练习一
- ORACLE 实战一
- sparkStreaming+kafka+hbase实战练习一
- MapReduce实战练习一:手机流量统计
- ORACLE基础练习(一)
- oracle基础练习(一)
- Oracle RAC 实战(一)
- 火焰算法(模糊算法运用)
- 基础知识——线程和进程的定义及区别
- 日食前再次发布新版MyUML090801_2
- 什么是嵌入式系统?(
- 详细讲解Java中log4j的使用方法
- Oracle实战练习(续一)
- 性能测试之:操作系统性能指标观测及分析
- JAVA高效学习方法的陈词滥调,给机会一个机会
- 修改Linux命令提示符
- 脱壳,反编译 ,汇编工具集合
- log4net
- Protocol Buffers Language Guide之proto文件类型格式分析[关键点翻译]
- linux网络配置(fc、ubuntu版本)
- SQL Server使用Merge语句当源表数据集为空时,无法进行查询的问题