数据库复习三

                                                                                数据库复习三---用习题来复习知识点

接上篇文章，继续来看题。

4.查询平均薪水等级最低的部门名称。

  1>这个和上一题非常的类似，那就是求出各部门的平均薪水。

   select dname,avg(sal) avg_sal from emp,dept where emp.deptno(+)=dept.deptno group by dname;

  2>然后对上面的结果集取别名为t ,求出各部门的薪水等级

   select salgrade.grade t.dname from t,salgarde s where t.avg_sal between s.losal and s.hisal;

  3>这一步就要注意，有人会选择使用排序，取出第一或最后一行，这种做法不完全正确，因为在同一等级的部门可能有多个，单单取第一个是不正确的，当然只有一个的情况下就是正确的。这里推荐使用第二种方法是：

     再多一步查询，查询出各部门的薪水等级最低的等级数，通过等级数选择出部门名称。

   select min(grade) from (select salgrade.grade t.dname from t,salgarde s where t.avg_sal between s.losal and s.hisal);

    上面查询的结果返回的就是一个等级数。

至于排序的那种方法就是使用伪列，再排序，取首或尾。

   4> 这是最后一步，就是根据薪水等级数选择出薪水最低的部门名称

select dname from(
    select salgrade.grade,t.dname from(
       select dname,avg(sal)  avg_sal from emp,dept where emp.deptno(+)=dept.deptno  group by dname
         )t,salgrade 
         where t.avg_sal between salgrade.losal and salgrade.hisal)t2 where t2.grade =(
          select min(grade) from (
            select salgrade.grade,t.dname from(select dname,avg(sal)  avg_sal from emp,dept where                     emp.deptno(+)=dept.deptno  group by dname
 )t,salgrade 
where t.avg_sal between salgrade.losal and salgrade.hisal));

至此我们这次求出平均薪水最低等级部门名称的SQL语句写完了。

这个写起来确实有点麻烦。那到底是哪里比较麻烦呢：

   select grade,dname from(select dname,avg(sal) avg_sal from emp,dept where emp.deptno(+)=dept.deptno group by dname)t,salgrade where t.avg_sal between salgrade.losal and salgrade.hisal;

首先能够非常熟练的写出这个查出各部门平均薪水等级及部门名称，三表联合查询，就是有点难度的了，但是还有点难度的是求出位于最低等级的部门，这与求最低等级是不一样的，前者是部门名称后者是薪水的等级数。

还有这个题如果改成求最高等级，那么排序就会出现问题了：

在上图中我们就会发现两个部门是同一等级，都是最高等级。这个时候，用推荐的方法会查询出正确的结果。

总结一下使用的知识点：

               1.分组查询  2.分组函数  3.子查询 4.多表连接查询（难点）

5.查询员工的姓名和工资数，条件限定为工资数必须大于1200，并且查询结果按入职时间进行排序。早入职的员工排在前面。

    1>明确要查询的信息有什么？

       员工的姓名，工资大于1200的员工工资数---简单为就是姓名、工资--->对应表格只有emp表

      select ename,sal from emp where sal > 12000;

    2>对第一步查询的结果集的操作是什么？

     按时间排序，早进入的员工排在前面。

     order by hiredare asc;

两步合并：

select ename,sal from emp where sal > 12000 order by hiredare asc;

这个非常简单，单表查询就搞定了，只有排序，where的应用。

6.查询不是领导的员工信息

   这道题查询的内容非常简单就员工信息，难点在于条件的选择，即“不是领导的员工”对应的条件是什么？可以通过研究一下emp表的特点来选择条件。

通过查看emp表的信息可知，mgr是每个员工对应的领导，那么应当以此为依据来判断不是领导的员工信息。

select * from emp where empno not in(select distinct nvl(mgr,0) from emp);

分析原因：选择出是领导的员工号，select mgr from emp;  再以emp为集合取该集合的补集即可。

总结知识点：

1.对于emp表格结构的理解  2.not in 可以完成取补集的功能。

7.查询员工的平均奖金。

    这道题很简单，考查点在分组函数中不能有空值及nvl()函数的应用情况，因为不是所有的员工都有奖金的。

select avg(nvl(comm,0)) from emp;

8.查询所有领导的信息，要求使用exits关键字

   这里首先要注意一点是：select * from emp where ename='scott' 这是错误的，正确的应当是：select * from emp where ename ='SCOTT';

查询领导的信息，若不要求使用exits 关键字 则可以写为：select * from where job ='MANAGER';
   exits关键字的用法是：

      EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

select * from emp outer where exists (
                   select * from emp inner
                   where outer.empno = inner.mgr
           );

exits关键字的查询效率要比in的查询效率要高。

9、创建一个从20开始，每次递增2的序列。

<span style="font-size:18px;"> create sequence emno1 start with 20 increment by 2 nocache;</span>

nocache表示没有缓存序列。cache第一序列的内存块 cycle 循环 nocycle 不循环

创建序列的语法规则是：

create sequence 序列名 start with 初始值 increment by 步长 nocache/cache/cycle/nocycle;

10.按照薪水即按需排列，每页只显示5条，显示分页内容。

   本题查询的信息很简单就是职员的工资信息，为了增加显示的信息可加上一条员工姓名，主要考察的内容就是分页伪列的使用及子查询。

思路非常简单：

先显示前十条数据，再该前十条数据的基础上再显示后五条数据，即可实现显示第二页的内容。

 select ename,sal,rownum from emp where rownum<=10 order by sal desc;  显示前十条数据，为该结果集取别名为t

select t.ename,t.sal,t.r1,rownum  from (
   select ename,sal,rownum r1 from emp where rownum<=10 order by sal desc
)t where t.r1>5;

通过对第一步查询结果集的进一步条件限制选择出了目标数据。记得初学数据库时感觉写这个分页都好难，但是写完上面的三表连接查询的SQL语句后，顿时认为这个分页其实没有想象的那么难，在解决较难的题目后会发现自己之前的问题会很简单。其实这次也是抱着尝试态度去写的，结果真是出来了，这也是做软件行业的最大的体会，任何事情，你刚开始拿着可能没有一点思路，但是自己尝试着来，也许就出结果了。

总结知识点：

伪列rownum的应用，排序order by 

我又随手做了一些题目，很简单，不再分析了，如下：

      查询薪水等级为4的员工信息
    select * from (select losal,hisal from salgrade where grade='4')t,emp where emp.sal between t.losal and t.hisal;
   查询‘SMITH’的领导姓名
      select * from emp where empno=(select mgr from emp where ename='SMITH');

 查询所有薪水不在2000-4000范围内的员工信息
  select * from emp where sal not in(2000,4000);

查询平均工资比10部门低的部门编号
 

 select deptno from (
 select avg(sal) avg,emp.deptno from emp,dept where emp.deptno(+)=dept.deptno group by emp.deptno
)a where a.avg >(
select avg(sal) from emp where deptno='20'
);

第二种解法是：

select a.deptno from(
select avg(sal) avg,emp.deptno from emp,dept where emp.deptno(+)=dept.deptno group by emp.deptno
)a,(
select avg(sal) avg10 from emp where deptno='10'
)b where b.avg10> a.avg;

就这些吧，在最后一道题上（查询平均工资比10部门低的部门编号）还是有点意思的，这个题目当时没有对平均薪资取别名导致两个结果集的平均薪资为无效。这里也是需要注意的，如果引用分组函数的结果，必须为其取别名。

在做完上面几道练习题后，总结的是：

第一：明确查询的内容。

第二：对查询的结果集有什么样的处理。

第三：在前两步基础上再来考虑怎么查。

这是我个人总结的写SQL语句的步骤，写多了就会发现，难点在多表连接查询以及如何应用子查询的结果集的，分步考虑是经常用到的，所以子查询是非常重要的，也是一种思考的方式。

重点的知识点也就出来了：

左、右连接，等值连接，分组函数，分组查询，in，not in，and，between and，any，some，all，union，union all ，exits， 这些是经常会用到的。

后续还会有关于数据库的理论知识：

比如：事务，表，视图，序列，索引，Oracle漏洞注入等知识点的复习。