Oracle数据库练习题（2）

一、表展示

二、练习题及答案

11.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

(1)select t.empno, t.empname, d.dname
  from (select a.empno,
               a.ename as empname,
               a.hiredate as emphiredate,
               a.deptno,
               b.ename as leadername,
               b.hiredate as leaderhiredate
          from empgj a
          left join empgj b on a.mgr = b.empno) t,
       deptgj d
 where emphiredate < leaderhiredate
   and t.deptno = d.deptno;

(2)select a.empno, a.ename, d.dname
  from empgj a
  left join empgj b on a.mgr = b.empno
  left join deptgj d on a.deptno = d.deptno
 where a.hiredate < b.hiredate;

12.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

select d.dname,
       e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.hiredate,
       e.sal,
       e.comm,
       e.deptno
  from deptgj d
  left join empgj e on d.deptno = e.deptno;

select d.dname,
       e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.hiredate,
       e.sal,
       e.comm,
       e.deptno
  from empgj e
  left join deptgj d on d.deptno = e.deptno;

select d.dname,
       e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.hiredate,
       e.sal,
       e.comm,
       e.deptno
  from empgj e, deptgj d
 where d.deptno = e.deptno;

可以看到只有第一种写法把没有员工的部门也输出出来了，后面两种并没有

ps：（1）注意left join,join与rhght join的区别：
left join是组合两表符合条件内容部分以及左表的全部内容（在取出如“取出所有部门并列出没有员工的部门”时很有用）；
join等于inner join，组合两表符合条件内容；
right join一般都转化为left join使用。
（2）表连接（join）在on后跟分组条件；
分组（group by）在having后跟分组条件；

select查询在where后跟查询条件。

13.列出薪金比"SMITH"多的所有员工信息

select empno, ename, job, mgr, hiredate, sal, comm, deptno
  from empgj
 where sal > (select sal from empgj where ename = 'smith')

14.列出至少有一个员工的所有部门

select distinct d.dname
  from empgj e
  inner join deptgj d on e.deptno = d.deptno

15.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

select e.ename, d.dname, t.numofpeople
  from empgj e
  left join deptgj d on e.deptno = d.deptno
 inner join (select deptno, count(1) as numofpeople
               from empgj
              group by deptno) t on e.deptno = t.deptno
 where e.job = 'clerk'

第一步查出各个部门的人数：

select deptno, count(1) as numofpeople from empgj group by deptno

第二步查出所有“clerk”的姓名及其部门名称：

select e.ename,d.dname
  from empgj e
  left join deptgj d on e.deptno = d.deptno
  where e.job='clerk'

第三步联系二者关联字段deptno组成新的查询语句

ps：从子表取count(1)的时候无法直接按“t.count(1)”的写法来取，需要为其取别名

16.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

select job, count(1) from empgj group by job having min(sal) > 1500

常见的几个聚合函数
① 求个数：count
② 求总和：sum
③ 求最大值：max
④ 求最小值：min
⑤ 求平均值：avg
聚合函数对一组值执行计算，并返回单个值。除了 COUNT 以外，聚合函数都会忽略空值。 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用
本条语句的count就是对group by后跟的job进行统计

17.列出在部门"SALES"（销售部）工作的员工的姓名,假定不知道销售部的部门编号

select e.ename, e.deptno, d.dname
  from empgj e
  left join deptgj d on e.deptno = d.deptno
 where d.dname = 'sales';

18.列出与"SCOTT"从事相同工作的所有员工及部门名称

（1）select e.ename, d.dname, e.job
  from empgj e
  left join deptgj d on e.deptno = d.deptno
 where e.job = (select job from empgj where ename = 'scott');

（2）select e.ename, d.dname, e.job
  from empgj e, deptgj d
 where e.job = (select job from empgj where ename = 'scott')
   and e.deptno = d.deptno;

19.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

select e.ename, e.sal, e.deptno
  from empgj e
  join (select sal from empgj where deptno = '30') t on e.sal = t.sal;

20.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

（1）select e.ename, e.sal, d.dname
  from empgj e
  left join deptgj d on e.deptno = d.deptno
 where e.sal > ALL (select sal from empgj where deptno = 20);

（2）select e.ename, e.sal, d.dname
  from empgj e
  left join deptgj d on e.deptno = d.deptno
 where e.sal > (select max(sal) from empgj where deptno = 20);

上面两条语句效果是对等的

any与all都用于子查询
any 代表任意一个 
all 代表所有

any表示有任何一个满足就返回true，all表示全部都满足才返回true

21.列出在每个部门工作的员工数量,平均工资和平均服务期限

select deptno,
       count(1),
       avg(sal),
       avg(months_between(sysdate,hiredate)/12) year
  from empgj
 group by deptno;

select deptno,
       count(1),
       avg(sal),
       avg(months_between(to_date('2000-1-1','yyyy/mm/dd'),hiredate)/12) year
  from empgj
 group by deptno;

MONTHS_BETWEEN (date1, date2)用于计算date1和date2之间有几个月
    如果date1在日历中比date2晚，那么MONTHS_BETWEEN()就返回一个正数
    如果date1在日历中比date2早，那么MONTHS_BETWEEN()就返回一个负数
    如果date1和date2日期一样，那么MONTHS_BETWEEN()就返回一个0

sysdate（系统时间）与指定时间到受雇日期的月数

那么求日期差，月份差，年差就都出来了：

日期差：select to_date('05/30/2018','mm/dd/yyyy')-to_date('05/01/2018','mm/dd/yyyy') from dual;

月份差用MONTHS_BETWEEN (date1, date2)函数，年份差再除以12

详细用法参考：点击打开链接

22.列出所有部门的详细信息和人数

select d.deptno, d.dname, nvl(t.numofpeople, 0)
  from deptgj d
  left join (select deptno, count(1) as numofpeople
               from empgj
              group by deptno) t on d.deptno = t.deptno;

nvl(arg,n)函数：如果arg这个参数是null的话，那么就用n这个数代替arg

23.列出各种工作的最低工资及从事此工作的雇员姓名

select e.ename,t.job,t.minsal from empgj e join
(select job,min(sal）as minsal from empgj group by job) t
on e.job = t.job and e.sal = t.minsal;

24.列出各个部门的MANAGER(经理)的最低薪金

（1）select t2.minsal, d.dname
  from (select min(sal) as minsal, t.deptno
          from (select sal, deptno from empgj where job = 'manager') t
         group by t.deptno) t2
  join deptgj d on t2.deptno = d.deptno;

（2）select d.dname, t.minsal
  from (select deptno, min(sal) as minsal
          from empgj
         where job = 'manager'
         group by deptno) t
  join deptgj d on t.deptno = d.deptno;

最外层是把deptno转变成dname显示的查询，核心内容还是查询各个部门的经理的最低薪金，这里用了两种方法

方法（1）：

步骤一：查询不同部门经理的薪金，得出字段为经理和部门号的表t：

select sal, deptno from empgj where job = 'manager'

步骤二：对表t操作，查询表t中按部门号分组后每组的最低薪金，这里就得出了各个部门的经理的最低薪金：

select min(sal) as minsal, t.deptno
          from  t
         group by t.deptno

方法（2）：

对empgj单表操作，直接查询各个部门的经理的最低薪金：

select deptno, min(sal) as minsal
          from empgj
         where job = 'manager'
         group by deptno

刚开始看到这条语句我被惊艳到了，竟然有如此简洁的语句来查询一个比较复杂的内容；但是empgj表数据比较少，每个部门只有一个经理，可能展示出来的数据不准确，所以有必要多做几条数据；考虑到MySQL中也有group by的用法，而且MySQL的图形界面工具更易操作，我就用MySQL检查了这两条语句，发现并没有问题！

25.列出所有员工的年工资,按年薪从低到高排序

select empno, ename, (sal + nvl(comm, 0)) * 12 yearsal
  from empgj
 order by yearsal asc;

select empno, ename, (sal + nvl(comm, 0)) * 12 yearsal
  from empgj
 order by yearsal;

asc：按升序排列，也是默认排序可省略不写

desc：按降序排列

26.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数

select t.dname, sum(sal), count(1)
  from (select e.empno, e.sal, d.dname
          from empgj e
          join deptgj d on e.deptno = d.deptno
         where d.dname like '%s%') t
 group by t.dname;

Oracle中like的用法可参考：点击打开链接

27.列出所有办事员(CLERK)的姓名，编号和部门编号

select ename, empno, deptno from empgj where job = 'clerk';

28.找出佣金高于薪金的员工

select empno, ename, comm, sal from empgj where comm > sal;

29.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料

select empno, ename, job, mgr, hiredate, sal, comm, deptno
  from empgj
 where (deptno = 10 and job = 'manager')
    or (deptno = 20 and job = 'clerk')

这里有个and和or的优先级问题，and的优先级是高于or的，所以其实不要括号效果是一样的，但是我一直觉得跟写程序一样，代码是机器运行的，但更是写给人用的，所以代码的可读性与可维护性在我心中的地位是非常高的；写SQL语句同理，所以加括号方便阅读，更不需要我们去关心符号的优先级，我选择使用括号！

30.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料

select empno, ename, job, mgr, hiredate, sal, comm, deptno
  from empgj
 where (deptno = 10 and job = 'manager')
    or (deptno = 20 and job = 'clerk')
    or (job != 'manager' and job != 'clerk' and sal >= 2000);