本文提供了一系列 Oracle 数据库 SQL 查询实训题目及其解答方案，包括获取特定条件下的员工信息、部门信息等，通过具体实例帮助读者掌握 SQL 语句的编写技巧。
一、建表
create table empgj(empno number(4,0) primary key,ename varchar2(10),
job varchar2(9),mgr number(4,0),hiredate date,sal number(7,2),comm
number(7,2),deptno number(2,0));
--1
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7369,'smith','clerk',7902,to_date('1980-12-17','yyyy/mm/dd'),
800.00,'',20);
commit;
--2
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7499,'allen','salesman',7698,to_date('1981-2-20','yyyy/mm/dd'),
1600.00,'300.00',20);
commit;
--3
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7521,'ward','salesman',7698,to_date('1981-2-22','yyyy/mm/dd'),
1250.00,'500.00',30);
commit;
--4
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7566,'jones','manager',7839,to_date('1981-4-2','yyyy/mm/dd'),
2975.00,'',20);
commit;
--5
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7654,'martin','salesman',7698,to_date('1981-9-28','yyyy/mm/dd'),
1250,'1400.00',30);
commit;
--6
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7698,'blake','manager',7839,to_date('1981-5-1','yyyy/mm/dd'),
2850,'',30);
commit;
--7
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7782,'clark','manager',7839,to_date('1981-6-9','yyyy/mm/dd'),
2450,'',10);
commit;
--8
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7788,'scott','analyst',7566,to_date('1987-4-19','yyyy/mm/dd'),
3000,'',20);
commit;
--9
insert into empgj(empno,ename,job,hiredate,sal,comm,deptno) values
(7839,'king','president',to_date('1981-11-17','yyyy/mm/dd'),
5000,'',10);
commit;
--10
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7844,'turner','salesman',7698,to_date('1981-9-8','yyyy/mm/dd'),
5000,'0.00',30);
commit;
--11
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7876,'adams','clerk',7788,to_date('1987-5-23','yyyy/mm/dd'),
1100,'',20);
commit;
--12
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7900,'james','clerk',7698,to_date('1981-12-3','yyyy/mm/dd'),
950,'',30);
commit;
--13
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7902,'ford','analyst',7566,to_date('1981-12-3','yyyy/mm/dd'),
3000,'',20);
commit;
--14
insert into empgj(empno,ename,job,mgr,hiredate,sal,comm,deptno) values
(7934,'miller','clerk',7782,to_date('1982-1-23','yyyy/mm/dd'),
1300,'',10);
commit;
select empno as "员工ID",ename as "员工姓名", job as "职位",mgr as "员工管理者ID",
hiredate as "入职日期",sal as "薪资",comm as "绩效",deptno as "员工所在部门的ID"
from empgj;
alter session set nls_date_format='yyyy/mm/dd';
create table deptgj(deptno number(2,0),dname varchar2(14),loc varchar2(13));
--1
insert into deptgj(deptno,dname,loc) values(10,'accounting','new york');
commit;
--2
insert into deptgj(deptno,dname,loc) values(20,'research','dallas');
commit;
--3
insert into deptgj(deptno,dname,loc) values(30,'sales','chicago');
commit;
--4
insert into deptgj(deptno,dname,loc) values(40,'operations','boston');
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 exists7.取得薪水最高的前五名员工
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语句的书写很灵活，有时一个条件会有多种写法的查询语句，除了及时复盘，更应经常训练，才会运用熟练
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
