te_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