Oracle表连接与子查询示例

7cf3xdbkq9 · 发表于 2016-8-8 06:58:44

　　--把雇员按部门分组，求最高薪水，部门号，过滤掉名字中第二个字母是'A'的，要求分组后的平均薪水
　　>1500，按照部门编号倒序排列
　　--求每个人的名字和他的经理人的名字，用外连接把KING也取出来
　　--哪些人的薪水在部门的平均薪水之上
　　--求部门中哪些人的薪水最高
　　--求部门平均薪水的等级
　　--求部门平均的薪水等级
　　--雇员中有哪些人是经理人
　　--不准用组函数,求薪水的最高值(面试题)
　　--求平均薪水最高的部门的部门编号
　　--求平均薪水最高的部门的部门名称
　　--求平均薪水的等级最低的部门的部门名称
　　--求部门经理人中平均薪水最低的部门名称 (思考题)
　　--求比普通员工的最高薪水还要高的经理人名称
　　--求薪水最高的前5名雇员
　　--求薪水最高的第6到第10名雇员(重点掌握)
　　--练习: 求最后入职的5名员工
　　--求每个部门中薪水最高的前两名雇员
　　--面试题: 比较效率
　　--求分段显示薪水的个数
如：
scale  total
<800  0
800-1000 2
1001-2000 3
2001-5000 6
>5000  8
　　
或者显示成为
800-1000 1001-2000 2000-5000
2  3  6
　　
或者显示成为
DEPTNO   800-2000 2001-5000
------ ---------- ----------
    30          5          1
    20          2          3
    10          1          2
--
　　========================第一份答案=======================
　　--把雇员按部门分组，求最高薪水，部门号，过滤掉名字中第二个字母是'A'的，
要求分组后的平均薪水>1500，按照部门编号倒序排列

select e1.ename, e2.ename
from emp e1 left join emp e2 on (e1.mgr = e2.empno)

　　
--哪些人的薪水在部门的平均薪水之上

select ename, sal
from emp join
(select deptno, max(sal) max_sal
from emp
group by deptno) t
on (emp.deptno = t.deptno and emp.sal = t.max_sal)

　　
　　--求部门平均薪水的等级

select deptno, avg_sal, grade
from salgrade s join
(select deptno, avg(sal) avg_sal
from emp
group by deptno) t
on (t.avg_sal between s.losal and s.hisal)

sql 代码
　　
　　--求部门平均的薪水等级

select ename from emp where empno in (select distinct mgr from emp)

　　
　　--不准用组函数,求薪水的最高值(面试题)

sql 代码

select ename from emp where empno not in
(select distinct e1.empno from emp e1 join emp e2 on (e1.sal < e2.sal))

　　
排序，取第一个
　　
--求平均薪水最高的部门的部门编号

select deptno from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
where avg_sal =
(select max(avg_sal) from
(select deptno, avg(sal) avg_sal from emp group by deptno) t)
select max(avg(sal)) from emp group by deptno －－组函数嵌套

　　
--求平均薪水最高的部门的部门名称

select dname from dept where deptno =
(select deptno from
(select deptno, avg(sal) avg_sal from emp group by deptno) t
where avg_sal =
(select max(avg_sal) from
(select deptno, avg(sal) avg_sal from emp group by deptno) t)
)

　　
　　--求平均薪水的等级最低的部门的部门名称

select dept.deptno,dname from
(
select deptno, avg_sal, grade from salgrade s join
(select deptno, avg(sal) avg_sal from emp group by deptno) t
on (t.avg_sal between s.losal and s.hisal)
) t1 join dept on
(t1.deptno = dept.deptno)
where grade =
(select min(grade) from
(
select deptno, avg_sal, grade from salgrade s join
(select deptno, avg(sal) avg_sal from emp group by deptno) t
on (t.avg_sal between s.losal and s.hisal)
)
)
reate view v$_dept_info as
select deptno, avg_sal, grade from salgrade s join
(select deptno, avg(sal) avg_sal from emp group by deptno) t
on (t.avg_sal between s.losal and s.hisal)
elect deptno from v$_dept_info where grade = (select min(grade) from v$_dept_info);

　　
　　--求部门经理人中平均薪水最低的部门名称 (思考题)
//....
　　
--求比普通员工的最高薪水还要高的经理人名称

sql 代码

select empno, ename, sal from emp where sal >
(
select max(sal) from
(select empno,ename, sal
from emp where empno not in (select distinct nvl(mgr, -1) from emp ))
) and empno in (select distinct mgr from emp)
select distinct mgr from emp where mgr is not null

　　
　　
--求薪水最高的前5名雇员

--求薪水最高的第6到第10名雇员(重点掌握)

select ename, sal from
(
select ename, sal, rownum r from
(
select ename, sal from emp order by sal desc
)
where rownum <=10
)
where r > 5 and r <= 10

　　
　　--求每个部门中薪水最高的前两名雇员

select deptno, ename, sal from emp order by deptno, sal desc
select deptno, ename, sal , rownum r from
(select deptno, ename, sal from emp order by deptno, sal desc);
create view v as select deptno, ename, sal , rownum r from
(select deptno, ename, sal from emp order by deptno, sal desc);
select deptno, min(r) min_r from v group by deptno;
select v.deptno, ename, sal , r from v join
(
select deptno, min(r) min_r from v group by deptno
) t
on (v.deptno = t.deptno and v.r >= t.min_r and v.r <= t.min_r + 1)
order by v.deptno, sal desc;

　　
--练习: 求最后入职的5名员工

select ename, hiredate from
(
select ename ,hiredate from emp order by hiredate desc
)
where rownum <= 5

　　
　　
　　--面试题: 比较效率

select * from emp where deptno = 10 and ename like '%A%';--这种是最高的
select * from emp where ename like '%A%' and deptno = 10;

　　
　　
--求分段显示薪水的个数
如：

scale total
<800 0
801-1000 2
1001-2000 3
2001-5000 6
>5000 8
select '<800' as scale , count(*) as total from emp where sal < 800
union
select '800-1000' as scale , count(*) as total from emp where sal between 800 and 1000
union
select '2000-5000' as scale , count(*) as total from emp where sal between 2000 and 5000;
...

　　
　　或者显示成为
800-1000 1001-2000 2000-5000
2 3 6
sql 代码

select * from
( select count(*) as "800-1000" from emp where sal between 800 and 1000 )
,
( select count(*) as "1001-2000" from emp where sal between 1001 and 2000 );
...

　　或者显示成为
DEPTNO   800-2000 2001-5000
------ ---------- ----------
    30          5          1
    20          2          3
    10          1          2
sql 代码

select t1.deptno, "800-2000", "2001-5000" from
(
select deptno , count(*) as "800-2000" from emp where sal between 800 and 2000
group by deptno
) t1
join
(
select deptno , count(*) as "2001-5000" from emp where sal between 2001 and 5000
group by deptno
) t2
on
t1.deptno = t2.deptno

select ename, sal
from
(select ename, sal from emp order by sal desc)
where rownum <=5;

　　
sql 代码

select deptno, avg(grade) from
(select deptno, sal , grade
from emp e join salgrade s on
(e.sal between s.losal and s.hisal)
) t
group by deptno

　　--雇员中有哪些人是经理人

sql 代码

select ename, sal
from emp join
(select deptno, avg(sal) avg_sal
from emp
group by deptno) t
on (emp.deptno = t.deptno and emp.sal > t.avg_sal)

　　--求部门中哪些人的薪水最高

sql 代码

select max(sal), deptno from emp
where ename not like '_A%'
group by deptno
having avg(sal) > 1500
order by deptno desc;

　　--求每个人的名字和他的经理人的名字，用外连接把KING也取出来
sql 代码

账号		自动登录	找回密码
密码			立即注册

Centos6.5×64安装配置openmeetings3.0.3详

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

[经验分享] Oracle表连接与子查询示例

浏览过的版块

扫码加入运维网微信交流群