Oracle数据库练习题实例及答案分享
程序员文章站
2023-11-23 14:43:22
一、建表
1. create table empgj(empno number(4,0) primary key,ename varchar2(10),
2. job varchar2(9...
一、建表
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语句的书写很灵活,有时一个条件会有多种写法的查询语句,除了及时复盘,更应经常训练,才会运用熟练
上一篇: jquery改变tr背景色的示例代码
下一篇: js数据类型的区别和使用讲解