题库

    题库

• 在芝加哥工作的人中，谁的工资最高
• 查询每个部门下有多少员工
• 查询除去 salesman 所有平均工资超过 1500 的部门
• 查询在 new york 工作的所有员工的姓名，部门名称和工资信息
• 查询姓名为 King 的员工的编号，名称跟部门
• 查询各种工作的最低工资
• 查询工龄大于10年的所有员工信息
• 查询每个部门员工数量，平均工资和平均工作年限
• 统计各部门每个工种的人数，平均工资。
• 查询从事同一种工作但不属于同一部门的员工信息。
• 查询所有员工工资都大于1000的部门的信息及员工信息
• 查询入职日期早于其直接上级的所有员工信息。
• 列出雇员中（除去mgr为空的人)工资第二高的人。
• 列出1981年来公司所有员工的总收入（包括sal和comm）

查询各种工作中工资最低的人

看到最低、最大、平均之类的题目，首先想到的是分组函数的使用。

也就是 group by, having。

select job, min(sal) from emp group by job;

在芝加哥工作的人中，谁的工资最高

[题目] 从 scott 用户的 emp/dept 表中，找到 “来自芝加哥最有钱的那个人” 。

首先，我们需要理清思路。

这里总共有两个条件：

1. 这个人来自芝加哥
2. 这个人是最有钱的，而且是芝加哥最有钱的

我们可以看出，第二个条件是依赖第一个条件的。

所以，分两步查询：

1. 找出所有来自芝加哥的人
2. 从这些人中，找到最有钱的那个。这一步，可以通过 max 函数或者 order by 方式实现。

下面是语句示例：

---- 第一步：找到来自芝加哥的所有人。下面两种写法等价：

select e.* from emp e
  join dept d on (e.deptno=d.deptno)
  where d.loc='CHICAGO';

select e.* from emp e, dept d
  where d.deptno = e.deptno
        and d.loc='CHICAGO';


---- 第二步，基于上面结果，筛选出最有钱的那个

-- 可以通过 max 函数
select e.* from emp e, dept d
  where e.deptno = d.deptno
        and d.loc='CHICAGO'
        and sal = 
            (select max(sal) from emp e, dept d
              where e.deptno = d.deptno
                    and d.loc='CHICAGO');

-- 可以通过 order by 方式
select ename from
  (select e.*, d.* from emp e, dept d
    where e.deptno = d.deptno
          and d.loc='CHICAGO'
    order by sal desc)
where rownum = 1;

注意，实现的方式，不止上面的那些。但总体 思路 是一样的。

所以，思路永远是最重要的。

查询所有员工工资都大于1000的部门的信息及员工信息

最核心的问题： 查询出符合条件的部门编号。

第一种思路

1. 查询出所有的部门编号

   select * from emp;
   

2. 查询出所有工资少于 1000 的人， 我们要把它所在的部门，从上面的结果中排除掉。

   select deptno from emp where sal < 1000;
   

3. 将上面查询出的不符合条件的部门排除掉

   select distinct deptno from emp
    where deptno not in 
     (select deptno from emp where sal < 1000);
   

4. 修改上面语句，增加最终的条件，查询所有的其他信息

   select d.dname, e.* from emp e join dept d on(e.deptno = d.deptno)
     where e.deptno not in
       (select deptno from emp where sal < 1000)
   

第二种思路

使用分组函数(group by / having)。

分组函数主要用来统计分析。

一个完整的查询语句如下，其中 group by 和 having 是用来分组和筛选分组。

select [字段] from [表名]
  where [条件]
  group by [分组字段]
  having [对分组结果进行筛选]
  order by [字段]

示例：

select deptno,     -- 分组字段
       count(*),   -- 人数
       sum(sal),   -- 工资总和
       avg(sal),   -- 平均工资
       max(sal),   -- 最高工资
       min(sal)    -- 最低工资
  from emp group by deptno         -- 按照部门分组，进行统计
           having avg(sal) > 2000; -- 只显示平均工资大于 2000 的分组

那我们的题目的解决思路就是：

1. 按照部门分组

   select deptno from emp group by deptno;
   

2. 筛选，排除最低工资小于 1000 的部门。 即：得到符合条件的部门的编号。

   select deptno from emp group by deptno having min(sal) > 1000;
   

3. 完善最终语句，得到最终结果。

   select * from emp e, dept d
     where e.deptno = d.deptno
           and e.deptno in (select deptno from emp group by deptno having min(sal) > 1000);