Oracle学习笔记8

一． 常见问题

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.lowsal and s.highsal);

3） 求部门平均的薪水等级

Select deptno, avg(grade) from

(select deptno, ename, grade from emp join salgrade s on (emp.sal between s. lowsal and s.highsal)) 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 from (select avg(sal) avgsal, deptno from emp group by deptno)

Where avgsal = (select max(avgsal) from (select avg(sal) avgsal, deptno from emp group by deptno));

7） 求平均薪水最高的部门名称

Select dname from dept where deptno =

         (

         Select deptno from (select avg(sal) avgsal, deptno from emp group by deptno)

Where avgsal = (select max(avgsal) from

(select avg(sal) avgsal, deptno from emp group by deptno)

);

);

8） 求平均薪水的等级最低的部门的部门名称

select deptname

from (

  select deptno, grade

  from (

select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on

                       (t.avg_sal between s.lowsal and s.highsal)

     ) t1 join dept d on (t1.deptno = d.deptno);

where t1.grade =

         (

       select min(grade)

         from (

                select deptno, grade

                from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on

                       (t.avg_sal between s.lowsal and s.highsal)

      )

  );

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

Create view v_mgr_avgsal_by_dept as

Select avg(salary) avg_sal, deptno

from emp

where empno in (select distinct mgr from emp)

group by deptno;

 

         select dname

         from v_mgr_avgsal_by_dept t join dept d on (t.deptno = d.deptno)

         where t.avg_sal =

                            (

                                   Select min(avg_sal) from v_mgr_avgsal_by_dept

);

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）              求薪水最高的前五名雇员

Select ename, sal

From (select ename, sal from emp order by sal desc) where rownum<=5

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

方法一：

select salary

from (

              select salary, rownum r

              from

                      (select salary from employees order by salary desc)

)

where r between 6 and 10

方法二：

select salary from

(

  select salary, rownum r from

  (select salary from employees order by salary desc)

  where rownum <= 10

)

where r between 6 and 10

13）              比较效率

Select * from emp where deptno = 10 and ename like ‘%A%’;            // 理论上效率稍高

Select * from emp where ename like ‘%A%’ and deptno = 10;            // 因为判断数字比字符快

14）              有三个表 S ， C ， SC

S （ SNO ， SNAME ）代表（学号，姓名）

C （ CNO ， CNAME ， CTEACHER ）代表（课程号，课名，老师）

SC （ SNO ， CNO ， GRADE ）代表（学号，课程号，成绩）

问题：

1， 找出没选过“黎明”老师的所有学生姓名

select sname

from s

where sno not in (

   select s.sno

   from sc join s on(sc.sno = s.sno) join c on(sc.cno = c.cno)

   where c.cteacher = ‘liming’)

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

select sname

from s

where sno in

  (

   select sno

   from sc

   where grade < 60

   group by sno

   having count(*) >= 2

  )

3， 既学过一号课程又学过二号课程所有学生的姓名

select sname

from s

where sno in

  (

   select s.sno

   from sc join s on (sc.sno = s.sno)

   where cno = 1 or cno =2

   group by s.sno

   having count(*) = 2

  )

15）              树状结构的存储与展示

Create table article

(

         Id number primary key,             

         Context varchar2(4000),           -- 内容

         Pid number,                               -- 父 ID

         Isleaf number(1),                       --0 代表非叶子节点， 1 代表叶子节点

         aLevel number(2)                      -- 深度

);

Insert into article values (1, ‘ 蚂蚁大战大象 ’, 0, 0, 0);

Insert into article values (2, ‘ 大象被打趴了 ’, 1, 0, 1);

Insert into article values (3, ‘ 蚂蚁也不好过 ’, 2, 1, 2);

Insert into article values (4, ‘ 瞎说 ’, 2, 0, 2);

Insert into article values (5, ‘ 没有瞎说 ’, 4, 1, 3);

Insert into article values (6, ‘ 怎么可能 ’, 1, 0, 1);

Insert into article values (7, ‘ 怎么没有可能 ’, 6, 1, 2);

Insert into article values (8, ‘ 可能性是很大的 ’, 6, 1, 2);

Insert into article values (9, ‘ 大象进医院了 ’, 2, 0, 2);

Insert into article values (10, ‘ 护士是蚂蚁 ’, 9, 1, 3);

 

Commit;

 

-- 显示树状结构

create or replace procedure p

  (v_pid article.pid%type, v_level binary_integer)

is

  cursor c is select * from article where pid = v_pid;

  v_split varchar2(1024) := '';

begin

  for i in 1..v_level loop

    v_split := v_split || '*****';

  end loop;

  for v_article in c loop

    dbms_output.put_line(v_split || v_article.context);

    if (v_article.isleaf = 0) then

      p(v_article.id, v_level + 1);

    end if;

  end loop;

end;