查询

查询工资为 2500 到 4000 的人的数量（用不同方式查询）

select count(*) from emp where sal >= 2500 and sal <= 4000;
-- vs.
select count(*) from emp where sal between 2500 and 4000;
    

-- 初始化数据
create table salgrade as select * from scott.salgrade;

-- 分别查看数据
select * from emp;
select * from salgrade;

-- 杂交
select * from emp, salgrade;

-- 过滤掉不合适的
select ename, grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
    
查询每个部门的平均工资的等级
   
   

    
    
-- 分析题目
-- 1. 需要先查询出每个部门的平均工资
-- 2. 根据 salgrade 表中的数据，获取每个部门平均工资的等级

-- 这是所有的人
select * from emp;
-- 按照部门分组
select deptno, avg(sal) from emp group by deptno;
-- 结果：
-- | DEPTNO |   AVG(SAL)    |
-- |     30  | 1566.66667  |
-- |     20 |  2258.33333  |
-- |     10 |  2916.66667  |

-- 跟 salgrade 表，杂交，总共 15 条结果
select * from
  (select deptno, avg(sal) sal from emp group by deptno) t,
  salgrade s;

-- 过滤掉工资范围不合适的数据
select * from
  (select deptno, avg(sal) sal from emp group by deptno) t,
  salgrade s
where
  t.sal between s.losal and s.hisal;
-- 结果就是这样，就对了
-- | DEPTNO |  SAL        |  GRADE |  LOSAL |  HISAL  |
-- |     10 |  2916.66667 |      4 |   2001 |   3000  |
-- |     20 |  2258.33333 |      4 |   2001 |   3000  |
-- |     30 |  1566.66667 |      3 |   1401 |   2000  |


-- 需要将部门名称显示出来，再杂交->过滤一次就可以了
select * from
  (select deptno, avg(sal) sal from emp group by deptno) t,
  salgrade s,
  dept d
where
  t.deptno = d.deptno and
  t.sal between s.losal and s.hisal;


-- 最后的最后，设置显示字段
select dname, grade from
  (select deptno, avg(sal) sal from emp group by deptno) t,
  salgrade s,
  dept d
where
  t.deptno = d.deptno and
  t.sal between s.losal and s.hisal;
-- 最终结果，完美
--| DNAME      |  GRADE  |
--| ACCOUNTING |      4  |
--| RESEARCH   |      4  |
--| SALES      |      3  |
   
   
第一步：找到来自芝加哥的所有人。下面两种写法等价：

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的部门的信息及员工信息
最核心的问题： 查询出符合条件的部门编号。
第一种思路
   
   
查询出所有的部门编号
select * from emp;
    
查询出所有工资少于 1000 的人， 我们要把它所在的部门，从上面的结果中排除掉。
select deptno from emp where sal < 1000;
    
将上面查询出的不符合条件的部门排除掉
select distinct deptno from emp
 where deptno not in 
  (select deptno from emp where sal < 1000);
    
修改上面语句，增加最终的条件，查询所有的其他信息
     
     

      
      
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 的分组  
   
   
那我们的题目的解决思路就是：
   
   
按照部门分组
     
     

      
      
select deptno from emp group by deptno;
    
     
     
筛选，排除最低工资小于 1000 的部门。 即：得到符合条件的部门的编号。
     
     

      
      
select deptno from emp group by deptno having min(sal) > 1000;
    
     
     
完善最终语句，得到最终结果。
     
     

      
      
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);
    
     
     
查询当月总共有多少个周五
首先，第一步，得到本月所有日期的结果集，两种方式:
   
   
使用已有表的 rownum 构建
使用 oracle 的 connect by level 语句（结构化查询）
得到有 n 条记录的虚表：
   
   

    
    
select rownum from dba_objects where rownum < 32;
select level from dual connect by level < 32;
   
   
将虚表转化为我们需要的日期表：
   
   

    
    
select trunc(sysdate, 'MON') + rownum - 1 from dba_objects where rownum < 32;
select trunc(sysdate, 'MON') + level - 1 from dual connect by level < 32;
   
   
其次，在上面结果集的基础上进行筛选：
   
   

    
    
-- 1.
select *
  from (select trunc(sysdate, 'MON') + rownum - 1 d
          from dba_objects
         where rownum < 32)
 where to_char(d, 'day') = '星期五'
   and d <= last_day(sysdate);

-- 2.
select *
  from (select trunc(sysdate, 'MON') + level - 1 d
          from dual
        connect by level < 32)
 where to_char(d, 'day') = '星期五'
   and d <= last_day(sysdate);
   
   
当然，你也可以将 last_day 这一段放到里面：
   
   

    
    
select *
  from (select trunc(sysdate, 'MON') + level - 1 d
          from dual
        connect by level <= extract(day from last_day(sysdate)))
 where to_char(d, 'd') = 6;