oracle的應用（面試題目樣本）：重點，oracle樣本

oracle的應用（面試題目樣本）：重點，oracle樣本

（一）ROW_NUMBER() OVER(partition by col1 order by col2) 表示根據col1分組，在分組內部根據col2排序，而此Function Compute的值就表示每組內部排序後的順序編號（組內是連續且唯一的）。

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));

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。