oracle一些常见题目

对于嵌套子查询的练习

–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, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

– 希望得到下面的结果
– 姓名 语文 数学 英语
– 王五 89 56 89

使用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)) 语文,
max(decode(ss.subject, '数学', ss.score)) 数学,
max(decode(ss.subject, '英语', 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;