oracle一些常见题目
发布时间:2020-12-12 14:34:27 所属栏目:百科 来源:网络整理
导读:对于嵌套子查询的练习 –1.求部门中薪水最高的人 select * from emp e join ( select max (e.sal) max from emp e group by e.deptno) m on m. max = e.sal; –2.求部门平均薪水的等级 select m.*,sg.grade from ( select avg (e.sal) sal,e.deptno from emp
对于嵌套子查询的练习 –1.求部门中薪水最高的人 select * from emp e join (select max(e.sal) max from emp e group by e.deptno) m on m.max = e.sal;
–2.求部门平均薪水的等级 select m.*,sg.grade from (select avg(e.sal) sal,e.deptno from emp e group by e.deptno) m join salgrade sg on m.sal between sg.losal and sg.hisal;
–3.求部门平均的薪水等级 select m.*,sg.grade from (select avg(m.avg) sal from (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) m) m join salgrade sg on m.sal between sg.losal and sg.hisal;
–4.雇员中有哪些人是经理人 select * from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no;
–5.不准用组函数,求薪水的最高值 select e1.* from emp e1 where e1.sal >= all (select e.sal sal from emp e);
–6.求平均薪水最高的部门的部门编号 select e.dp from (select max(m.avg) max from (select avg(e.sal) avg from emp e group by e.deptno) m) m join (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) e on e.avg = m.max;
–组函数嵌套写法(对多可以嵌套一次,group by 只对内层函数有效) –7.求平均薪水最高的部门的部门名称 select d.dname from (select e.dp dp from (select max(m.avg) max from (select avg(e.sal) avg from emp e group by e.deptno) m) m join (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) e on e.avg = m.max) m join dept d on d.deptno = m.dp ;
–8.求平均薪水的等级最低的部门的部门名称 select d.dname from (select n.dp dp from (select max(m.avg) max from (select avg(e.sal) avg from emp e group by e.deptno) m) m join (select avg(e.sal) avg,e.deptno dp from emp e group by e.deptno) n on m.max = n.avg) m join dept d on d.deptno = m.dp;
–9.求部门经理人中平均薪水最低的部门名称 select d.dname from (select n.dp dp from (select min(m.avg) min from (select avg(m.sal) avg from (select e.sal sal,e.deptno dp from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no) m group by m.dp) m) m join (select avg(m.sal) avg,m.deptno dp from (select e.sal sal,e.deptno deptno from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no) m group by m.deptno) n on m.min = n.avg) m join dept d on d.deptno = m.dp;
–10.求比普通员工的最高薪水还要高的经理人名称(not in) select e.ename from (select m.no no from (select e.sal sal,e.empno no from (select distinct e.mgr no from emp e where e.mgr is not null) m join emp e on e.empno = m.no) m join (select max(m.sal) max from (select e.sal sal from emp e where e.empno not in (select distinct e.mgr no from emp e where e.mgr is not null)) m) n on m.sal > n.max) m join emp e on e.empno = m.no;
–11.求薪水最高的前5名雇员 select m.* from (select rownum r,m.* from (select * from emp e order by e.sal desc) m) m where m.r <= 5;
–12.求薪水最高的第6到第10名雇员(important) select m.* from (select rownum r,m.* from (select * from emp e order by e.sal desc) m) m where m.r > 5 and m.r <= 10;
–13.求最后入职的5名员工 select m.* from (select rownum r,m.* from (select * from emp e order by e.hiredate desc) m) m where m.r <= 5;
对于以上习题,肯定还有更好和更简便的实现方式,本人只是练习使用嵌套子查询的使用,个人觉得这种查询很基础。 下面再多写个行转列吧 题目: 建表 create table STUDENT_SCORE ( name VARCHAR2(20),subject VARCHAR2(20),score NUMBER(4,1) )
– 添加数据 insert into student_score (NAME,SUBJECT,SCORE) values ('张三','语文',78.0);
insert into student_score (NAME,'数学',88.0);
insert into student_score (NAME,'英语',98.0);
insert into student_score (NAME,SCORE) values ('李四',89.0);
insert into student_score (NAME,76.0);
insert into student_score (NAME,90.0);
insert into student_score (NAME,SCORE) values ('王五',99.0);
insert into student_score (NAME,66.0);
insert into student_score (NAME,91.0);
– 希望得到下面的结果 使用case when then end select ss.name,max(case when ss.subject = '语文' then ss.score end) 语文,max(case when ss.subject = '数学' then ss.score end) 数学,max(case when ss.subject = '英语' then ss.score end) 英语 from student_score ss group by ss.name;
使用decode select ss.name,max(decode(ss.subject,ss.score)) 语文,ss.score)) 数学,ss.score)) 英语 from student_score ss group by ss.name;
使用多条子查询 select m1.name,m1.sc,m2.sc,m3.sc from (select ss.name,ss.score sc from student_score ss where ss.subject = '语文') m1 join (select ss.name,ss.score sc from student_score ss where ss.subject = '数学') m2 on m1.name = m2.name join (select ss.name,ss.score sc from student_score ss where ss.subject = '英语') m3 on m2.name = m3.name; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |