一、建表
1. create table empgj(empno number(4,0) primary key,ename varchar2(10),
2. job varchar2(9),mgr number(4,0),hiredate date,sal number(7,2),comm
3. number(7,2),deptno number(2,0));
4. --1
5. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
6. (7369,'smith','clerk',7902,to_date('1980-12-17','yyyy/mm/dd'),
7. 800.00,'',20);
8. commit;
9. --2
10. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
11. (7499,'allen','salesman',7698,to_date('1981-2-20','yyyy/mm/dd'),
12. 1600.00,'300.00',20);
13. commit;
14. --3
15. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
16. (7521,'ward','salesman',7698,to_date('1981-2-22','yyyy/mm/dd'),
17. 1250.00,'500.00',30);
18. commit;
19. --4
20. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
21. (7566,'jones','manager',7839,to_date('1981-4-2','yyyy/mm/dd'),
22. 2975.00,'',20);
23. commit;
24. --5
25. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
26. (7654,'martin','salesman',7698,to_date('1981-9-28','yyyy/mm/dd'),
27. 1250,'1400.00',30);
28. commit;
29. --6
30. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
31. (7698,'blake','manager',7839,to_date('1981-5-1','yyyy/mm/dd'),
32. 2850,'',30);
33. commit;
34. --7
35. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
36. (7782,'clark','manager',7839,to_date('1981-6-9','yyyy/mm/dd'),
37. 2450,'',10);
38. commit;
39. --8
40. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
41. (7788,'scott','analyst',7566,to_date('1987-4-19','yyyy/mm/dd'),
42. 3000,'',20);
43. commit;
44. --9
45. insert into empgj(empno,ename,job,hiredate,sal,comm,deptno) values
46. (7839,'king','president',to_date('1981-11-17','yyyy/mm/dd'),
47. 5000,'',10);
48. commit;
49. --10
50. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
51. (7844,'turner','salesman',7698,to_date('1981-9-8','yyyy/mm/dd'),
52. 5000,'0.00',30);
53. commit;
54. --11
55. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
56. (7876,'adams','clerk',7788,to_date('1987-5-23','yyyy/mm/dd'),
57. 1100,'',20);
58. commit;
59. --12
60. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
61. (7900,'james','clerk',7698,to_date('1981-12-3','yyyy/mm/dd'),
62. 950,'',30);
63. commit;
64. --13
65. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
66. (7902,'ford','analyst',7566,to_date('1981-12-3','yyyy/mm/dd'),
67. 3000,'',20);
68. commit;
69. --14
70. insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
71. (7934,'miller','clerk',7782,to_date('1982-1-23','yyyy/mm/dd'),
72. 1300,'',10);
73. commit;
74. select empno as "员工id",ename as "员工姓名", job as "职位",mgr as "员工管理者id",
75. hiredate as "入职日期",sal as "薪资",comm as "绩效",deptno as "员工所在部门的id"
76. from empgj;
77.
78. alter session set nls_date_format='yyyy/mm/dd';
79.
80. create table deptgj(deptno number(2,0),dname varchar2(14),loc varchar2(13));
81. --1
82. insert into deptgj(deptno,dname,loc) values(10,'accounting','new york');
83. commit;
84. --2
85. insert into deptgj(deptno,dname,loc) values(20,'research','dallas');
86. commit;
87. --3
88. insert into deptgj(deptno,dname,loc) values(30,'sales','chicago');
89. commit;
90. --4
91. insert into deptgj(deptno,dname,loc) values(30,'operations','boston');
92. commit;
二、表展示
三、练习题及答案
1.取得每个部门最高薪水的人员名称
select e.ename,t.maxsal, t.deptno from empgj e join (select max(sal) as maxsal, deptno from empgj group by deptno) t on t.deptno = e.deptno and e.sal= t.maxsal;
–>
select e.ename,t.maxsal, d.dname from empgj e join (select max(sal) as maxsal, deptno from empgj group by deptno) t on t.deptno = e.deptno and e.sal= t.maxsal ,deptgj d where e.deptno = d.deptno;（加上部门名称）
<+>取得sales部门中薪资最高的人名
select ename
from empgj
where sal = (select max(e.sal)
from empgj e
join deptgj d
on e.deptno = d.deptno
where d.dname = 'sales');
2.哪些人的薪水在部门的平均薪水之上
select e.ename, e.sal, t.avgsal from empgj e join (select avg(sal) as avgsal, deptno from empgj group by deptno) t on t.deptno = e.deptno and e.sal > t.avgsal;
3.不准用组函数（max），取得最高薪水
select sal from (select sal from empgj order by sal desc) where rownum = 1;
4.取得平均薪水最高的部门的部门编号
select deptno
from (select avg(sal), deptno
from empgj
group by deptno
order by avg(sal) desc)
where rownum = 1;
（ps:这种方法只能查询最高平均薪资只有一个部门的情况，如果两个部门平均薪资一样且都是最高，执行此查询则会漏查，希望有能解决此问题的朋友不吝赐教）
5.取得平均薪水最高的部门的部门名称
(1)select dname
from deptgj
where deptno = (select deptno
from (select avg(sal), deptno
from empgj
group by deptno
order by avg(sal) desc)
where rownum = 1);
(2)select t2.avgsal, t2.deptno, d.dname
from (select t.avgsal, t.deptno
from (select avg(sal) as avgsal, deptno
from empgj
group by deptno
order by avgsal desc) t
where rownum = 1) t2
join deptgj d on t2.deptno = d.deptno;
6.取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要低的经理人姓名
select ename
from empgj
where sal <
(select max(sal)
from empgj
where empno not in
(select distinct mgr from empgj where mgr is not null))
and job = 'manager';
ps：这里注意not in的用法，其后不能跟有null的数据，注意去除；一般使用not exists
7.取得薪水最高的前五名员工
select ename, sal from (select ename, sal from empgj order by sal desc) where rownum < 6;
8.取得薪水最高的第六到第十名员工
select ename, sal
from (select ename, sal, rownum rn
from (select ename, sal from empgj order by sal desc))
where rn >= 6
and rn <= 10;
ps：有人可能会想，我直接查询rownum大于5小于11不就使查询语句简单些吗，比如： select ename,sal from (select ename, sal from empgj order by sal desc) where rownum >= 6 and rownum <=10; 但是这样尝试的查询并没有结果，这是因为不能对rownum使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果 这是因为： （1）rownum是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个rownum数值； （2）返回结果记录的rownum是从1开始排序的，因此第一条始终是1; 这样，当查询到第一条记录时，该记录的rownum为1，但条件要求rownum>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其rownum还是为1，如此循环，就不会产生结果 按照我所写查询语句是将rownum伪列加入到新表中成为实际列，就可进行某个范围的查询。rownum的其他用法可参考此篇文章进行深入了解点击打开链接
9.取得最后入职的5名员工
select ename from (select ename, hiredate from empgj order by hiredate desc) where rownum <= 5;
10.列出所有员工及直接上级的姓名
(1)select a.ename, b.ename from empgj a left join empgj b on a.mgr = b.empno;
(2)select t.ename, e.ename from (select ename, mgr from empgj) t left join empgj e on e.empno = t.mgr;
sql语句的书写很灵活，有时一个条件会有多种写法的查询语句，除了及时复盘，更应经常训练，才会运用熟练