sql语句中一些容易犯错的地方

(1)  Group by语句    
       求薪水值最高的人的名字.
           select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
       应如下求:
           select ename from emp where sal=(select max(sal) from emp);

       Group by语句应注意,

       出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.
        
(2)  Having 对分组结果筛选
      Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
          select avg(sal),deptno from emp  group by deptno  having avg(sal)>2000;
      查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
          select * from emp  where sal>1200   group by deptno   having avg(sal)>1500   order by avg(sal) desc;
        
(3):子查询
      谁挣的钱最多(谁:这个人的名字,  钱最多)
      select 语句中嵌套select 语句,可以在where,from后      
      问那些人工资,在平均工资之上.
          select ename,sal from emp where sal>(select avg(sal) from emp);

      查找每个部门挣钱最多的那个人的名字.
          select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.

      应该如下:
          select  max(sal),deptno from emp group by deptno;当成一个表.语句如下:
          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); 
      每个部门的平均薪水的等级. 
      分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.
(4)  join on

      三张表连接：
          slect ename,dname, grade from
          emp e join dept d on(e.deptno=d.deptno)
          join salgrade s on(e.sal between s.losal and s.hisal)
          where ename not like '_A%';
      把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。
     
          select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);

      左外连接：会把左边这张表多余数据显示出来。
          select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
      右外连接：
          select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
       
      即把左边多余数据，也把右边多余数据拿出来，全外连接。
          select ename,dname from emp e full join dept d on(e.deptno =d.deptno);

(5) 面试中可能出现的问题:

      a.求部门平均薪水的等级。

          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)
       
      b.求部门平均的薪水等级
          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

      c.那些人是经理
          select ename from emp where empno in(select mgr from emp);
          select ename from emp where empno in(select distinct mgr from emp);
       
      d.不准用组函数，求薪水的最高值（面试题）
          select distinct sal from emp where sal not in

          (
             select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal)

          );
       
      e.平均薪水最高的部门编号
          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)
          )
      
     f.平均薪水最高的部门名称
          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) )
          )
      
      g.求平均薪水的等级最低的部门的部门名称
       
        组函数嵌套
        如：平均薪水最高的部门编号，可以E.更简单的方法如下：
        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)
       
        组函数最多嵌套两层
       
        分析：
        首先求
        1.平均薪水： select avg(sal) from group by deptno;

        2.平均薪水等级：  把平均薪水当做一张表，需要和另外一张表连接salgrade
        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)
       
        上面结果又可当成一张表。
       
        DEPTNO    GRADE    AVG_SAL
      --------  -------  ----------
        30           3   1566.66667
        20           4   2175
        10           4   2916.66667

        3.求上表平均等级最低值
       
        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.hisa)
         )

        4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
         
          select dname ,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 s.hisal)
             ) t1
            join dept on (t1.deptno = dept.deptno)
            where t1.grade =
            (
              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)
               )
            )
         结果如下：
        
        DNAME    DEPTNO     GRADE    AVG_SAL
      --------  -------  --------   --------
        SALES        30        3    1566.6667 
     
         
       h: 视图（视图就是一张表，一个字查询）
       
       G中语句有重复，可以用视图来简化。
       conn sys/bjsxt as sysdba;
       grant create table,create view to scott;
       conn scott/tiger
       创建视图：
       create view v$_dept_avg-sal_info as
       select deptno,grade,avg_sal from
        ( select deptno,avg(sal) avg_sal from emp group by deptno)t
       join salgrade s on 9t.avg_sal between s.losal and s.hisal)
     
       然后
       select * from v$_dept_avg-sal_info
      
       结果如下：
       DEPTNO      GRADE    AVG_SAL
      --------  -------  ----------
        30           3   1566.66667
        20           4   2175
        10           4   2916.66667

       然后G中查询可以简化成：
       select  dname,t1.deptno,grade,avg_sal from
       v$_dept_avg-sal_info t1
       join dept on9t1.deptno =dept.deptno)
       where t1.grade=
       (
           select min(grade) from v$_dept_avg-sal_info t1
       )