|
oracle数据库子查询与连接查询的一些例子，正在研究oracle子查询、连接查询 oracle中子查询与连接查询的综合实例。
1, 求部门中那些人的薪水最高
复制代码代码示例:
select e.deptno, e.ename, e.sal
from (select deptno, max(sal) max_sal from emp group by deptno) t join emp e on (e.deptno = t.deptno and e.sal = t.max_sal)
2, 求部门平均薪水的等级
复制代码代码示例:
select d.deptno, avg_sal, grade from dept d
join (select deptno, avg(sal) avg_sal from emp group by deptno) t on (t.deptno = d.deptno) join salgrade s on (t.avg_sal between losal and hisal)
3, 求部门平均的薪水等级
复制代码代码示例:
select e.deptno, avg(s.grade) from emp e
join salgrade s on (e.sal between s.losal and s.hisal) group by e.deptno
4, 雇员中有哪些人是经理人
复制代码代码示例:
select ename
from emp where empno in (select distinct mgr from emp)
② 连接查询
复制代码代码示例:
select e1.empno, e1.ename, e2.empno, e2.mgr
from emp e1 join emp e2 on (e1.empno = e2.mgr)
5, 不用组函数, 求薪水的最高值
复制代码代码示例:
select e1.ename, e1.sal from emp e1
left join emp e2 on (e1.sal < e2.sal) where e2.sal is null and e1.sal is not null
② 先求出能比别人小的薪水集合, 不在该集合的薪水即为最大薪水
复制代码代码示例:
select ename, sal from emp
where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal))
6, 平均薪水最高的部门的编号
复制代码代码示例:
select deptno, avg(sal) from emp
group by deptno having avg(sal) = ( select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno) t )
②
复制代码代码示例:
select deptno, avg(sal) from emp
group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno)
7, 平均薪水的等级最低的部门的部门名称
复制代码代码示例:
select d.deptno, d.dname, tt.grade, tt.avg_sal from dept d
join -- 部门平均薪水对应的等级 (select t.deptno, t.avg_sal, s.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) ) tt on (d.deptno = tt.deptno) where tt.grade = ( -- 最低的薪水等级 select min(tt.grade) from -- 部门平均薪水对应的等级 (select t.deptno, t.avg_sal, s.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) ) tt )
8, 求部门经理人中平均薪水最低的部门名称
复制代码代码示例:
select d.dname, t2.avg_sal from
( -- 经理人按部门分组的平均薪水 select t.deptno, avg(sal) avg_sal from ( -- 经理人的编号,工资,部门 select distinct e1.empno, e1.sal, e1.deptno from emp e1 join emp e2 on e1.empno = e2.mgr ) t group by t.deptno ) t2 join dept d on t2.deptno = d.deptno where t2.avg_sal = ( select min(t2.avg_sal) from ( -- 经理人按部门分组的平均薪水 select t.deptno, avg(sal) avg_sal from ( -- 经理人的编号,工资,部门 select distinct e1.empno, e1.sal, e1.deptno from emp e1 join emp e2 on e1.empno = e2.mgr ) t group by t.deptno ) t2 )
9, 求比普通员工的最高薪水还高的经理人名称
复制代码代码示例:
select empno, ename, sal from emp
where empno in (select distinct mgr from emp) and sal > ( --普通员工的最高薪水 select max(sal) from emp where empno not in ( -- 经理人的编号 select distinct mgr from emp where mgr is not null ) )
10, 求薪水最高的前5名雇员
复制代码代码示例:
select ename, sal
from (select ename, sal from emp order by sal desc) where rownum <= 5
11, 求薪水最高的第6到第10名雇员
复制代码代码示例:
select r, ename, sal from
( select rownum r, ename, sal from (select ename, sal from emp order by sal desc) t1 ) t2 where r >=6 and r <= 10
12, 比较效率
复制代码代码示例:
①select * from emp where deptno = 10 and ename like '%A%';
②select * from emp where ename like '%A%' and deptno = 10;
解:
13, 有三个表
复制代码代码示例:
select S.SNAME
from S join SC on (S.SNO = SC.SNO) join C on (SC.CNO = C.CNO) where C.CTEACHER <> '黎明'
2, 列出2门以上(含2门)不及格学生 姓名及平均成绩
复制代码代码示例:
select S.SNAME, avg(SC.SCGRADE) avg_grade
from S join SC on (S.SNO = SC.SNO) where SC.SNO in ( -- 2门及以上不及格的学生编号 select SC.SNO from SC where SC.SCGRADE < 60 group by SC.SNO having count(*) >= 2 ) group by S.SNO, S.SNAME
3, 既学过1号课程又学过2号课程所有学生的姓名
复制代码代码示例:
select S.SNAME from S
where S.SNO in (-- 学过 1 和 2 课程的学生编号 select sc1.SNO from SC sc1 join SC sc2 on (sc1.CNO = sc2.CNO) where sc1.CNO = 1 and sc2.CNO = 2 )
14,一个简单的表TABLE 有100条以上的信息，其中包括：
复制代码代码示例:
create table product
create view v$product select * from v$product;
NAME RED_AMOUNT BLUE_AMOUNT
select name, (red_amount - blue_amount) difference_amount
NAME DIFFERENCE_AMOUNT (责任编辑：IT) |