Oracle作业5——多表查询、子查询

一、基础练习：

1.查询和scott相同部门的员工姓名ename和雇用日期hiredate

select ename,hiredate from emp where deptno=(select deptno from emp where ename='scott');

2.查询在部门的loc为new york的部门工作的员工的员工姓名ename，部门名称dname和岗位名称job

select e.ename,d.dname,e.job,d.loc from emp e,dept d where e.deptno=d.deptno and d.loc='new york';

3.查询上司是king的员工姓名(ename)和工资(sal)

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

4.查询与姓名中包含字母u的员工在相同部门的员工信息

select * from emp where deptno in(select deptno from emp where ename like '%u%');

5.查询所有雇员姓名和部门名称(使用left join，inner join, right join)

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
select e.ename,d.dname from dept d right join emp e on e.deptno=d.deptno;

6.显示每个员工的员工姓名、部门名称、职务、工资、和工资等级信息(使用left join，inner join, right join)

select e.ename,d.dname,e.job,e.sal,s.grade from emp e inner join dept d on e.deptno=d.deptno inner join salgrade s on e.sal between s.losal and s.hisal;

select e.ename,d.dname,e.job,e.sal,s.grade from emp e left join dept d on e.deptno=d.deptno left join salgrade s on e.sal between s.losal and s.hisal;

select e.ename,d.dname,e.job,e.sal,s.grade from dept d right join emp e on e.deptno=d.deptno right join salgrade s on e.sal between s.losal and s.hisal;

二、综合练习

1.取得每个部门最高薪水的人员名称2.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

--使用相关子查询
select empno,ename,sal,deptno from emp e where e.sal=(select max(sal) from emp m where m.deptno=e.deptno) order by deptno;
--使用多表连接查询（渔舟唱晚同学的）
select empno,ename,sal,deptno from emp q, (select e.deptno 部门, max(e.sal) 最高薪资 from emp e group by e.deptno) 
r where r.部门 = q.deptno and q.sal = r.最高薪资 order by q.deptno;
--使用dense_rank()函数结合order by
select * from(select empno,ename,sal,deptno,dense_rank() over(partition by deptno order by sal desc)rn from emp) where rn=1 order by deptno;
--使用in子查询（有bug）
select empno,ename,deptno,sal from emp where sal in(select max(sal) from emp group by deptno) order by deptno;

--测试上面的in子查询bug：发现10部门的nullun也显示出来了，但其并非10部门最高工资，10部门最高工资为5000
insert into emp(empno,ename,deptno,sal) values(1015,'nullun',10,3000);
select empno,ename,deptno,sal from emp where sal in(select max(sal) from emp group by deptno) order by deptno;

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

--左自连接和多表查询
select e.empno 员工编号,e.ename 员工姓名,m.ename 主管姓名,e.hiredate 员工受雇日期, m.hiredate 上级雇用日期,d.dname 部门名称
from emp e,emp m,dept d where m.empno(+)=e.mgr and e.hiredate<m.hiredate and e.deptno=d.deptno order by e.empno;

--相关子查询和多表查询
select e.empno,e.ename,d.dname from emp e,dept d where e.hiredate
<(select hiredate from emp m where m.empno=e.mgr) and e.deptno=d.deptno order by e.empno;

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

思路：1.先查询job为clerk的所有部门编号，将该子查询结果命名为a；2.再从emp表查询与a查询中部门编号相同的员工所在的部门人数，这一步的查询结果命名为b；3.最后从emp表、dept表和b查询中进行多表查询获取job为clerk的所有员工的姓名、部门名称和所在部门人数。

select e.ename,d.dname,t.部门人数,e.job from emp e,dept d,(select deptno,count(1) 部门人数 from emp where deptno in(
select distinct deptno from emp where job='clerk') group by deptno)t 
where e.deptno=d.deptno and e.job='clerk' and t.deptno=e.deptno;

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

select e.*,d.dname from emp e,dept d where e.job=(select job from emp where ename='scott') and e.deptno=d.deptno;

5.查出某个员工的上级主管，并要求出这些主管中的薪水超过3000

select e.empno 员工编号,e.ename 员工姓名,m.ename 主管姓名,m.sal 主管工资 from emp e,emp m where m.empno(+)=e.mgr and m.sal>3000;

6.找出部门10中所有经理(manager)和部门20中所有办事员(clerk)的详细资料

select e.*,d.dname,d.loc,s.* from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal 
and (e.deptno=10 and e.job='manager' or e.deptno=20 and e.job='clerk');
--注意：e.deptno=10 and e.job='manager' or e.deptno=20 and e.job='clerk' 要用括号括起来，不然会与前面的and条件混淆造成错误！

7.找出早于12年前受雇的员工. 并且按受雇年份倒序排序

思路一：用months_between比较当前系统时间和受雇日期之前相差的月份，然后除以12，如果值大于12，则是早于12前受雇的员工。

--有错误的语句
select e.*,to_char(hiredate,'yyyy') 受雇年份,round((months_between(sysdate,hiredate)/12),2) 受雇年限 from emp e where 受雇年限>12 order by 受雇年份 desc; 
/*为什么“受雇年限”会是无效的标识符呢？因为select语句在where语句后面才执行，而列的别名（受雇年限）是在select时才生成的，故在where子句中看不到这个别名（受雇年限），自然无法引用这个别名了。*/

--排错后的正确语句
select e.*,to_char(hiredate,'yyyy') 受雇年份,round((months_between(sysdate,hiredate)/12),2) 受雇年限 from emp e 
where (months_between(sysdate,hiredate)/12)>12 order by 受雇年份 desc; 

思路二：用add_months判断，(受雇日期+12*12)得出的日期如果小于当前系统时间，则是早于12前受雇的员工。

select e.*,to_char(hiredate,'yyyy') 受雇年份,add_months(hiredate,12*12) 受雇十二周年日,round((months_between(sysdate,hiredate)/12),2) 受雇年限 from emp e 
where add_months(hiredate,12*12)<sysdate order by 受雇年份 desc;
--注意：离当前日期越远的日期越小，反之，离当前日期越近的日期越大。

8.列出从事同一种工作但属于不同部门的员工的一种组合

--不算完美但算比较接近题意的sql语句
select distinct e.empno,e.ename,e.job,e.deptno from emp e,emp p where e.deptno!=p.deptno and e.job=p.job order by job,deptno;
--其它两种不等于的写法
select distinct e.empno,e.ename,e.job,e.deptno from emp e,emp p where e.deptno<>p.deptno and e.job=p.job order by job,deptno;
select distinct e.empno,e.ename,e.job,e.deptno from emp e,emp p where e.deptno^=p.deptno and e.job=p.job order by job,deptno;
/*精妙之处：使用distinct！如果不使用distinct，查询结果会出现很多一样的重复数据！*/

分析：为什么说上面的sql语句不算完美呢？因为从上图可看出job为clerk，且deptno=20的记录有两条，即分别是第2和第3条查询记录，这就与题目要求的“从事同一种工作但属于不同部门的员工”不一致了，故最理想的查询结果应该如下：

    

9.查询有奖金的所有员工的姓名、奖金以及所在部门名称

--如果奖金等于0也算有奖金，那如下实现：
select ename,comm,dname from emp e,dept d where comm is not null and e.deptno=d.deptno;

--如果奖金等于0不算有奖金，则如下实现：
select ename,comm,dname from emp e,dept d where comm is not null and comm<>0 and e.deptno=d.deptno;

10.给任职日期超过25年的员工加薪10%

select e.ename,e.sal 原薪水,e.sal*1.1 加薪后薪水,round((months_between(sysdate,hiredate)/12),2) 受雇年限 from emp e 
where (months_between(sysdate,hiredate)/12)>25;