一、基础练习：
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;