Oracle数据库练习题(2)

标签: Oracle数据库查询语句

一、表展示




二、练习题及答案

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

那么求日期差,月份差,年差就都出来了:

日期差: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);



版权声明:本文为GuanJdoJ原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/GuanJdoJ/article/details/79630664