oracle的应用（面试题目示例）：重点

（一）ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。

1.部门中那些人的薪水最高
select ename, sal from emp 
join ( select max(sal) max_sal ,deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno)


2.部门平均薪水的等级
select deptno ,avg_sal,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)


3.部门平均的薪水等级
select deptno ,avg(grade) from 
(select deptno ,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t
group by deptno


4.雇员中那些是 经理人
select ename from emp where empno in (select distinct mgr from emp)


5.不准用组函数，求薪水的最高值
select distinct 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
(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))


第二种写法：聚合函数的嵌套


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


7.求平均薪水最高的部门的部门名称
select dname from dept where deptno=
( 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)
)
)
8.求平均薪水的等级最低的部门的部门名称
select dname,t1.deptno ,grade,avg_sal from 
  (
     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 hisal)
   ) t1
 join dept on ( t1.deptno =dept.deptno)
where t1.grade=
  (
     select min(grade) from 
      (
         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)
       )
   )


有重复的sql语句   可以通过创建view视图来代替子查询简化语句


9.求部门经理人中平均薪水最低的部门名称




10.求比普通员工的最高薪水还要高的经理人的名称
select ename 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)
)
11.求薪水最高的前5名雇员


12求薪水最高的第6到第10名雇员


比较效率
 select * from emp where deptno =10 and ename like '%A%';
 select * from emp where ename like '%A%' and deptno =10;
第一条执行效率更高，先精确匹配，如果部门编号不等于10的直接就不查看了。
但是，要真的放入到oracle中去执行时，oracle内部可能就已经将这条语句优化了，也很难区分那条语句效率更高。




1.找出没选过黎明老师的所有学生姓名
s(sno,sname) 学号 姓名
c(cno,cname,cteacher) 课号 课名 教师
sc(sno,cno,sccgrade) 学号  课号 成绩


select sname from s join sc on (s.sno=sc.sno) join c (c.cno=sc.cno) where c.cteacher <>'liming';


2.列出2门以上（含2门）不及格学生姓名及平均成绩


select sname where sno in (select sno from sc where scgrade <60 group by sno having count(*)>=2);


3.既学过1号课程又学过2号课程所有学生的姓名


select sname from s where sno in (select sno from sc where cno = 1 and cno in (select distinct sno from sc where cno = 2));