常见sql查询语句（二）---高级查询

• 查询哪个部门的平均工资是最高的

  首先，使用group by子句配合子查询，查询出平均工资最高的部门，SQL语句如下所示：

  
        
        
  select max(avg(sal)) from emp group by deptno；

  然后，将上述查询的结果作为主查询的条件，进一步查询出平均工资最高的部门的部门编码，SQL语句如下所示：

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

• 列出各个部门中工资最高的员工的信息

  首先，使用group by 子句配合组函数查询出各个部门的最高工资，SQL语句如下所示：

  
        
        
  select deptno , max(sal) from emp group by deptno；

  然后，将上述查询的结果作为where条件的值，即使用in操作符判断哪些员工与上述查询的结果集中的任一数据相等，SQL语句如下所示：

  
      
      
  select ename ,  sal , deptno from emp 
  where (deptno , sal) in 
  (select deptno , max(sal) from emp group by deptno);

• 查询管理者是“KING”的员工姓名（ename）和工资（sal）

  首先，查询员工“KING”的员工编号，SQL语句如下所示：

  
        
        
  select empno from emp where ename = 'KING'；

  然后，将上述查询的结果作为where条件的值，当员工的管理者ID（mgr）等于上述查询结果时，该员工的管理者即为“KING”，SQL语句如下所示：

  
      
      
  select ename, sal
  from emp
  where mgr = (select empno from emp where ename = 'KING');

• 查询部门所在地（loc）为“NEW YORK”的部门的员工信息

  首先，查询部门所在地为“NEW YORK”的部门编号，SQL语句如下所示：

  
        
        
  select deptno from dept where loc = 'NEW YORK'；

  然后，将上述查询的结果作为where条件的值，查询部门所在地为“NEW YORK”的部门里的所有员工的员工号（empno）、姓名（ename）和工资（sal）。SQL语句如下所示：

  
        
        
  select e.ename, d.dname, e.job
  from emp e join dept d
  on e.deptno = d.deptno
  where e.deptno = (select deptno from dept where loc = 'NEW YORK');

  由于需要显示部门名称（dname），因此在上述SQL语句中，使用join... on将表emp和表dept做内连接查询。

• 查询工资比公司平均工资高的所有员工信息

  首先，查询所有员工的平均工资，SQL语句如下所示：

  
        
        
  select avg(sal) from emp；

  然后，将上述查询的结果作为where条件的值，当职员表中的某条记录的工资（sal）大于上述查询结果时，该员工的工资即高于公司的平均工资，SQL语句如下所示：

  
      
      
  select empno, ename, sal
  from emp
  where  sal> (select avg(sal) from emp);

• 查询姓名中包含字母“u”的员工在相同部门的员工信息

  首先，使用like运算符查询姓名中包含字母“u”的员工的员工编号，SQL语句如下所示：

  
        
        
  select deptno from emp where ename like '%U%'；

  然后，将上述查询的结果作为where条件的值，当职员表中的某条记录的部门编号等于上述查询结果中的任意一个时，该员工所在的部门与姓名中包含字母“u”的员工的部门相同，SQL语句如下所示：

  
      
      
  select empno, ename
  from emp
  where deptno in (select deptno from emp where ename like '%U%');

• 查询哪些员工的薪水比本部门的平均薪水低

  本题可以使用关联子查询来解决，关联子查询的语法如下所示：

  
        
        
  SELECT  column1, column2, …
  FROM     table1 o
  WHERE   column1 operator
                       (SELECT column
                        FROM table2 i
  WHERE  i.expr1 = o.expr2)

  本题中要解决的问题为如何在主查询和子查询中来表示同一部门，SQL语句如下所示：

  
        
        
  select avg(sal)
  from emp b
  where b.deptno = a.deptno；

  其中a.deptno的a为主查询中emp表的别名。

  将上述查询作为主查询的条件，查询哪些员工的薪水比本部门的平均薪水低，SQL语句如下所示：

  
      
      
  selectename, sal, deptno
  from emp a
  where sal < ( select avg(sal)
  from emp b
  where b.deptno = a.deptno ) ;

•