1、Oracle 习题集 北京融信学唐科技有限公司第 1 页 共 32 页Oracle 习题集目 录第 1 章 简单查询 2第 2 章 多表查询 4第 3 章 分组查询 5第 4 章 子查询 7第 5 章 表、约束、数据插入、修改、删除、SEQUENCE .8第 6 章 视图 10第 7 章 用户角色权 限 11第 8 章 表空间、分区表 12第 9 章 PL/SQL 12第 10 章 存储过程与函数 13第 11 章 数据库高级 14第 12 章 数据库设计 15Oracle 习题集 北京融信学唐科技有限公司第 2 页 共 32 页第 1 章 简单查询使用 SQL PLUS 工具登陆，用户名用
2、scott。1. 在 emp 表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。select ename as 姓名, deptno as 部门编号, sal as 薪水 from emp;2. 在 emp 表中查询出薪水大于 1500 的记录，并且按照薪水的降序排列。select sal from emp where sal 1500 order by sal desc;3. 在 emp 表中查询出 comm 字段为空值的记录。select empno,ename,sal,comm from emp where comm is null;4. 查询出 emp 表中含有几个部门的
3、记录。 （用 DISTINCT 去除重复记录）select distinct deptno from emp;5. 在 emp 表中查询出部门编号为 10 或 20 的记录（请分别使用 OR 和 IN 关键字）select * from emp where deptno=10 or deptno=20;select * from emp where deptno in (10,20);6. 在 emp 表中查询出姓名的第二个字母为 A 的记录。select * from emp where ename like _A%;7. 查询出 emp 表中总共有多少条记录。select count(*)
4、 from emp;8. 查询 emp 表中 1981 年雇用的所有员工，或者是 1987 年 5 月雇用的员工。select ename,hiredatefrom empwhere to_char(hiredate,yyyy)=1981or to_char(hiredate,yyyymm)=198705;9. 选择部门 30 的所有员工。select ename,deptno from emp where deptno=30;10. 列出所有办事员（CLERK）的姓名，编号和部门编号。select empno,ename,deptno from emp where job=CLERK;Ora
5、cle 习题集 北京融信学唐科技有限公司第 3 页 共 32 页11. 找出佣金高于薪金的所有员工。select ename,sal,comm from emp where comm sal;12. 找出佣金高于薪金 30%的所有员工。select ename,sal,comm from emp where comm sal*0.3;13. 找出部门 10 中所有经理（MANAGER）和部门 20 中所有办事员（CLERK）的详细资料。select * from empwhere (deptno=10 and job=MANAGER)or (deptno=20 and job=CLERK);1
6、4. 找出部门 10 中所有经理（MANAGER） ，部门 20 中所有办事员（CLERK）,既不是经理又不是办事员（CLERK）但其薪金大于或等于 2000的所有员工的详细资料。select * from empwhere (deptno=10 and job=MANAGER)or (deptno=20 and job=CLERK) or (job not in (MANAGER,CLERK) and sal = 2000);15. 找出收取佣金的员工的不同的工作。select distinct job from emp where comm is not null;16. 找出不收取佣金或
7、收取佣金少于 100 的员工。select ename,comm from emp where comm is null or comm28;24. 以首字大写的方式显示所有员工的姓名。select initcap(ename) from emp;25. 显示员工姓名正好为 5 个字符的员工。select ename from emp where ename like _;SELECT * FROM emp WHERE length (ename)=5;26. 显示所有员工姓名的前三个字符。select substr(ename,1,3)from emp;27. 显示所有员工的姓名，用 a 替
8、换 A。select replace(ename,A,a) from emp;28. 显示满 10 年服务年限的员工的姓名和受雇日期。select ename,hiredatefrom empwhere to_char(sysdate,yyyymm)-to_char(hiredate,yyyymm) =10;29. 显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同，则将最早年份的员工排在最前面。select ename,to_char(hiredate,yyyy-mm)from empOracle 习题集 北京融信学唐科技有限公司第 5 页 共 32 页order b
9、y to_char(hiredate,mm),to_char(hiredate,yyyy);30. 显示所有员工的日薪金，忽略余数。每个月的天数都以 30 天计。select ename,trunc(sal/30) from emp ;31. 找出在（任何年份）2 月受聘的所有员工的姓名，对于每个员工，显示其加入公司的天数。select ename,hiredate,(sysdate-hiredate) 天数from empwhere to_char(hiredate,mm)=02;Oracle 习题集 北京融信学唐科技有限公司第 6 页 共 32 页第 2 章 多表查询1. 显示所有员工的姓
10、名 ename，部门号 deptno 和部门名称 dname。select ename,e.deptno,dnamefrom emp e,dept dwhere e.deptno=d.deptno;2. 查询 20 号部门员工的 job 和 20 号部门的 loc。select ename,job,locfrom emp e,dept dwhere e.deptno=d.deptno;3. 选择所有有奖金 comm 的员工的 ename , dname , loc。select ename,dname,loc,commfrom emp e,dept dwhere e.deptno=d.dept
11、no and comm is not null;4. 选择在工作地点 DALLAS 工作的员工的 ename , job , deptno, dname。select ename,job,e.deptno,dnamefrom emp e,dept dwhere e.deptno=d.deptno and loc=DALLAS;5. 选择所有员工的姓名 ename，员工号 deptno，以及他的管理者 mgr 的姓名ename 和员工号 deptno，结果类似于下面的格式：employees Emp# manager Mgr#SMITH 7369 FORD 7902select e.ename
12、employees,e.empno Emp#,m.ename manager,m.empno Mgr#from emp e,emp mwhere e.mgr=m.empno;6. 查询各部门员工姓名和他们所在位置，结果类似于下面的格式：Deptno Ename Loc20 SMITH DALLASselect e.deptno Deptno,ename Ename,loc LocOracle 习题集 北京融信学唐科技有限公司第 7 页 共 32 页from emp e,dept dwhere e.deptno=d.deptno;7. 显示：员工编号，员工名称，员工所在部门编号，员工所在部门名称
13、。select empno,ename,e.deptno,dnamefrom emp e,dept dwhere e.deptno=d.deptno;8. 显示：员工编号，员工名称，经理编号，经理名称。select e.empno,e.ename,m.empno,m.enamefrom emp e,emp mwhere e.mgr=m.empno;9. 显示：员工编号，员工名称，经理编号，经理名称，员工所在部门编号，员工所在部门名称。select e.empno,e.ename,m.empno,m.ename,e.deptno,d.dnamefrom emp e,emp m,dept dwhe
14、re e.mgr=m.empnoand e.deptno=d.deptno;10. 显示：员工编号，员工名称，经理编号，经理名称，员工所在部门编号，员工所在部门名称，经理所在部门编号，经理所在部门名称。select e.empno,e.ename,m.empno,m.ename,e.deptno,d.dname,md.deptno,md.dnamefrom emp e,emp m,dept d,dept mdwhere e.mgr=m.empnoand e.deptno=d.deptnoand m.deptno=md.deptno;Oracle 习题集 北京融信学唐科技有限公司第 8 页 共
15、32 页第 3 章 分组查询1. 列出至少有一个员工的所有部门。select deptno,count(ename)from empgroup by deptnohaving count(ename)=1;2. 查询 emp 表中出每个部门的部门代码、薪水之和、平均薪水。select deptno,sum(sal),avg(sal)from empgroup by deptno;3. 查询 emp 表中出部门人数大于等于 2 的部门的部门代码、部门人数。select deptno,count(empno)from empgroup by deptnohaving count(empno)=2;
16、4. 查询 emp 表中出部门平均薪水小于等于 20000 的部门的部门代码、平均薪水，并按平均薪水从大到小排序。select deptno,avg(sal)from empgroup by deptnohaving avg(sal)=1000Oracle 习题集 北京融信学唐科技有限公司第 9 页 共 32 页order by avg(sal) desc；6. 查询 emp 表中薪水最少，和薪水最大员工的姓名和薪水，并按薪水从大到小排序。select ename,salfrom empwhere sal=(select max(sal) from emp )or sal=(select mi
17、n(sal) from emp )order by sal desc;7. 列出薪金比 SMITH 高的所有员工。select ename,salfrom empwhere sal(select sal from emp where ename like SMITH);8. 列出所有员工的姓名及其直接上级领导的姓名。select e.ename,m.enamefrom emp e,emp mwhere e.mgr=m.empno;9. 列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称。select e.empno,e.ename,dname,e.hiredate,m.hiredate
18、from emp e,emp m,dept dwhere e.mgr=m.empnoand e.deptno=d.deptnoand to_char(e.hiredate,yyyymmdd)1500;13. 列出在部门 SALES(销售部)工作的员工的姓名，假定不知道销售部的部门编号。select ename,e.deptno,dnamefrom emp e,dept dwhere dname=SALESand e.deptno=d.deptno;14. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的等级工资。select e.ename,dname,m.ename,s.gra
19、de,e.salfrom emp e,emp m,dept d,salgrade swhere e.deptno=d.deptnoand e.mgr=m.empnoand e.sal between losal and hisaland e.sal(select avg(sal) from emp)；15. 列出与 SCOTT 从事相同工作的所有员工的编号，姓名，职位及其部门名称。select empno,ename,job,dnamefrom emp e,dept dOracle 习题集 北京融信学唐科技有限公司第 11 页 共 32 页where e.deptno=d.deptnoand
20、job=(select job from emp where ename=SCOTT)；16. 列出薪金等于部门 30 中员工的薪金的，所有员工的姓名和薪金。select ename,sal from emp where deptno=30;17. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名、薪金和部门名称。select ename,sal,dnamefrom emp e,dept dwhere e.deptno=d.deptnoand salall(select sal from emp where deptno=30);18. 列出在每个部门工作的员工数量、平均工资和平均服务
21、期限。select dname,count(*),avg(sal),avg(sysdate-hiredate)/365)from emp e,dept dwhere e.deptno=d.deptnogroup by dname；19. 列出所有员工的姓名、部门名称和工资。select ename,dname,salfrom emp e,dept dwhere e.deptno=d.deptno;20. 列出所有部门的详细信息和部门人数。select d.deptno,dname,loc,count(e.deptno)from emp e,dept dwhere e.deptno=d.dept
22、nogroup by d.deptno,dname,loc；21. 列出各种工作的最低工资及从事此工作的雇员姓名。SELECT * FROM EMP e,(SELECT job,MIN(sal) ssal FROM emp GROUP BY job)tWHERE e.job=t.jobAND e.sal=t.ssal;Oracle 习题集 北京融信学唐科技有限公司第 12 页 共 32 页22. 列出各个部门的 MANAGER(经理)的最低薪金。select dname,job,min(sal)from emp e,dept dwhere e.deptno=d.deptnoand job=MA
23、NAGERgroup by dname,job;23. 列出员工的年工资，按年薪从低到高排序。select ename,sal*12from emp eorder by sal*12 asc;24. 查出员工的信息，并要求这些员工的主管的薪水超过 3000。select e.*,m.sal from emp e,emp mwhere e.mgr=m.empnoand m.sal=3000;25. 求出部门名称中，带 S 字符的部门员工的、工资合计、部门人数。给任职日期超过 10 年的人加薪 10%。select dname,sum(sal),count(dname)from emp e,dep
24、t dwhere e.deptno=d.deptno and dname like %S% group by dname;26. 查询公司员工工资的最大值，最小值，平均值，总和。select max(sal),min(sal),sum(sal),avg(sal)from emp;27. 查询各 job 的员工工资的最大值，最小值，平均值，总和。select job,min(sal),max(sal),avg(sal),sum(sal)from empgroup by job;28. 选择具有各个 job 的员工人数(提示:对 job 进行分组)。select job,count(job)Ora
25、cle 习题集 北京融信学唐科技有限公司第 13 页 共 32 页from empgroup by job;29. 查询员工最高工资和最低工资的差距（DIFFERENCE） 。select max(sal)-min(sal) “DEFFERENCE“from emp;30. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 800，没有管理者的员工不计算在内。select m.ename,min(e.sal)from emp e,emp mwhere e.mgr=m.empno and e.sal=800group by m.ename;31. 查询所有部门的名字 dname，所在位置
26、loc，员工数量和工资平均值。select dname,loc,count(e.deptno),avg(sal)from emp e,dept dwhere e.deptno=d.deptnogroup by dname,loc;Oracle 习题集 北京融信学唐科技有限公司第 14 页 共 32 页第 4 章 子查询1. 查询和 scott 相同部门的员工姓名 ename 和雇用日期 hiredate。select ename,hiredatefrom emp where deptno=(select deptno from emp where ename=SCOTT);2. 查询工资比公司
27、平均工资高的所有员工的员工号 empno，姓名 ename 和工资 sal。select empno,ename,salfrom empwhere sal(select avg(sal) from emp);3. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号 empno 和姓名ename。select empno,enamefrom empwhere deptno=(select deptno from emp where ename like upper(%u%);4. 查询在部门的 loc 为 newYork 的部门工作的员工的员工姓名 ename，部门名称 dname 和岗位
28、名称 job。SELECT ename,dname,jobfrom emp e,dept dwhere e.deptno=d.deptnoand d.deptno=(select deptno from deptwhere loc=NEW YORK);5. 查询管理者是 king 的员工姓名 ename 和工资 sal。select e.ename,e.salfrom emp e,emp mOracle 习题集 北京融信学唐科技有限公司第 15 页 共 32 页where e.mgr=m.empnoand m.ename=KING;6. 查询没有员工的部门。select * from dept
29、where deptno not in (select distinct deptno from emp);Oracle 习题集 北京融信学唐科技有限公司第 16 页 共 32 页第 5 章 表、约束、数据插入、修改、删除、SEQUENCE1. 创建表 emp1，字段如下eno char(3),ename char(6),sex char(2),age number(2),deptno int使用 SQL 语句插入如下数据，要求编号使用 SEQUENCE 实现：1 ，TOM ， 男 ， 21 ，102 ，JERRY ，男 ，21 ， 203 ，KATE ， 女 ，21 ，304 ，MARY ，
30、女 ，21 ，405 ，JACK ， 男 ，21 ，50（1） 在 eno 字段上创建主键约束。（2） 在 ename 字段上创建非空约束。（3） 创建检查约束判断 age 在 18 到 60 岁之间的男性或者 age 在 18 到 55岁之间的女性。（4） 在 dno 字段上创建唯一性约束。（5） 创建和 emp1 表字段相同的 emp_bak 表，将 emp_bak 表的 deptno 字段与 dept 表的 deptno 字段创建外键约束。（6） 删除 JACK 员工。（7） 把 MARY 的年龄修改为 23。create table emp1(eno char(3) primary k
31、ey,ename char(6) not null,sex char(2),age number(2),deptno int unique,constraint sex_age check(age between 18 and 60) Oracle 习题集 北京融信学唐科技有限公司第 17 页 共 32 页and sex=男)or (age between 18 and 55) and sex=女);create sequence myseq1 increment by 1 start with 1;insert into emp1 values(myseq1.nextval,TOM,男,21
32、,10);insert into emp1 values(myseq1.nextval,JERRY,男,21,20);insert into emp1 values(myseq1.nextval,KATE,女,21,30);insert into emp1 values(myseq1.nextval,MARY,女,21,40);insert into emp1 values(myseq1.nextval,JACK,男,21,50);create table emp_bak as (select * from emp1 where 1=2);alter table emp_bak add con
33、straint emp_bak_dept_fk foreign key (deptno) references dept(deptno);delete emp1 where ename=JACK;update emp1 set age=23 where ename=MARY;2. 请创建一个表，表名为 phone,表结构如下电话号码（PHONENUM VARCHAR2（8） ） 电话费 （PAY number(8,2)）号码等级（NUMLEVEL VARCHAR2（4） ） 费用日期（PAYDATE varchar2(12)(1) 插入以下两条数据：123456， 600， pt04, 200
34、51220888888, 900, pt05, 20051019.(2) 创建一个备份表结构名为 phone_bak, 将 phone 中的数据插入phone_bak 中.(3) 将电话号码为 123456 的电话费改为 1000，执行回滚操作(4) 查询 phone 表中所有字段和所有数据，如果 PHONENUM 字段中的数据为123456 ，则返回Y否则返回N(5) 查询 phone 表中所有字段和所有数据，显示费用日期（PAYDATE）字段的日期转变成 YYYY/MM/DD 格式显示。Oracle 习题集 北京融信学唐科技有限公司第 18 页 共 32 页(6) 将电话号码为 88888
35、8 的号码等级改为 pt04 并提交(1)create table phone( phonenum varchar2(8),pay number(8,2) ,numlevel varchar2(4),paydate varchar2(12)(1)insert into phone values(123456,600,pt04,20051220);insert into phone values(888888,900,pt05,20051019);(2)create table phone_bak as (select * from phone);(3)update phone set pay=
36、1000where phonenum=123456rollback(4)SELECT p.*,decode(p.phonenum,123456,Y,N)FROM phone p;(5) SELECT phonenum,pay,numlevel,to_date(paydate,yyyy/mm/dd)FROM phone;(6) UPDATE phone SET numlevel=pt04 WHERE phonenum=888888;COMMIT;Oracle 习题集 北京融信学唐科技有限公司第 19 页 共 32 页第 6 章 视图1. 什么是视图。它有什么作用。视图是一个 虚拟表，其内容由查询
37、定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图实际上是一张或者多张表上的预定义查询，这些表称为基表。2. 创建一个视图，此视图要包括以下信息：员工编号，员工姓名。create view view1 select empno,ename from emp;3. 创建一个视图，此视图要包括以下信息：员工编号，员工姓名，部门编号，部门名称。create view view2 as select empno,ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;4. 创建一个视图，此视图要包括以下信息：员工编号
38、，员工姓名，经理编号，经理姓名。create view view3as select e.empno,e.ename,m.empno 经理编号,m.ename 经理姓名from emp e,emp m where e.mgr=m.empno；5. 创建一个视图，此视图要包括以下信息：部门编号，员工个数。create view view4as select deptno,count(ename) 人数from emp egroup by deptno；6. 创建一个视图，此视图要包括以下信息：部门编号，部门名称，员工个数。create view view5as select e.deptno,
39、d.dname,count(ename) 人数必须使用列别名命名此表达式，否则会出错在创建视图时，重复字段后面要使用别名，否则会出错Oracle 习题集 北京融信学唐科技有限公司第 20 页 共 32 页from emp e,dept dwhere e.deptno=d.deptnogroup by e.deptno, d.dname;7. 创建一个视图，此视图要包括以下信息：部门编号，部门名称，员工个数，部门内所有员工薪水的合计，部门内员工最高薪水，部门内员工最低薪水，部门内平均薪水。create view view6as select e.deptno, d.dname,count(ena
40、me) 人数,sum(sal) 总薪水,max(sal) 最高薪水 ,min(sal) 最低薪水,avg(sal) 平均薪水from emp e,dept dwhere e.deptno=d.deptnogroup by e.deptno, d.dname;Oracle 习题集 北京融信学唐科技有限公司第 21 页 共 32 页第 7 章 用户角色权限1 创建新用户 RXXT，密码 RXXT，使用的默认表空间是新建立的“CMSTS” ，临时表空间使用系统已经建立好的“TEMP” 。在 RXXT 用户下做存储过程作业.doc 作业。create user rxxt identified by r
41、xxtdefault tablespace CMSTStemporary tablespace TEMP2 创建用户“TEST1_自己姓名拼音”和“TEST2_自己姓名拼音”两个用户。使用第 1 个用户创建表 CUSTOMER，语句如下：CREATE TABLE CUSTOMER(ID INT,NAME VARCHAR(20);插入如下数据：INSERT INTO CUSTOMER(ID, NAME) VALUES(1, 王小明);INSERT INTO CUSTOMER(ID, NAME) VALUES(2, 张大明);然后使用第 2 个用户登录系统，并做如下操作：(1) 使用 SQL 查询
42、一下 CUSTOMER 表：SELECT * FROM TEST1_自己姓名拼音.CUSTOMER; /表名前需要带上第 1 个用户的用户名。(2) 使用 SQL 再插入一条数据：INSERT INTO TEST1_自己姓名拼音.CUSTOMER(ID, NAME) VALUES(3, 付海);(3) 使用 SQL 修改数据“王小明” ，修改成“王晓明” 。(4) 使用 SQL 删除所有数据。(5) 使用 SQL 把此表删除掉。create user TEST1_mapei identified by mapeidefault tablespace CMSTStemporary tablesp
43、ace TEMPOracle 习题集 北京融信学唐科技有限公司第 22 页 共 32 页create user TEST2_mapei identified by mapeidefault tablespace CMSTStemporary tablespace TEMPgrant create session to test1_mapei;grant create session to test2_ mapei;grant connect,resource to test1_ mapei;grant connect,resource to test2_ mapei;grant create
44、table to test1_ mapei;grant create table to test2_ mapei;grant select on test1_mapei.CUSTOMER to test2_mapei;grant insert on test1_mapei.CUSTOMER to test2_mapei;grant update on test1_ mapei.CUSTOMER to test2_ mapei;grant delete on test1_ mapei.CUSTOMER to test2_ mapei;grant drop any table to test2_
45、mapei;(1)select * from test1_mapei.CUSTOMER;(2)INSERT INTO test1_ mapei.CUSTOMER(ID, NAME) VALUES(3, 付海);(3) update test1_ mapei.CUSTOMERset name=王小明where name=王晓明;(4)delete from test1_ mapei.CUSTOMER;(5)drop table test1_ mapei.CUSTOMER;Oracle 习题集 北京融信学唐科技有限公司第 23 页 共 32 页第 8 章 表空间、分区表1. 什么是表空间。表空间是
46、一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，称作表空间。2. 什么是分区表。对表进行分区，有哪几种类型。分区表是将大表的数据分成称为分区的许多小的子集。分区表的类型： 范围分区 HASH 分区（散列分区） 列表分区 复合分区3. 创建订单表（订单 ID，订单标题，订单总金额） ，使用范围分区，对订单总金额字段值的范围进行分区。CREATE TABLE booking (b_ID NUMBER(20),b_title varchar2(20),b_money NUMBER(20) )PARTITION BY RANGE(b_money)(PARTITION
47、part_01 VALUES LESS THAN (100),PARTITION part_02 VALUES LESS THAN (200),PARTITION part_03 VALUES LESS THAN (300),PARTITION part_04 VALUES LESS THAN (MAXVALUE);4. 创建客户表（客户 ID，客户名称，行业） ，使用列表分区，对客户的行业字段值进行列表分区。Oracle 习题集 北京融信学唐科技有限公司第 24 页 共 32 页CREATE TABLE custom (c_id NUMBER(20),c_name varchar2(10),c_hang varchar2(50)PARTITION BY LIST(c_hang)(PARTITION part_c01 VALUES (金融),PARTITION part_c02 VALUES (IT),PARTITION part_c03 VALUE
