02Oracle习题集V4

1、oracle习题集 北京融信学唐科技有限公司oracle习题集目 录第1章简单查询2第2章多表查询4第3章分组查询5第4章子查询7第5章表、约束、数据插入、修改、删除、sequence8第6章视图10第7章用户角色权限11第8章表空间、分区表12第9章pl/sql12第10章存储过程与函数13第11章数据库高级14第12章数据库设计15第1章 简单查询使用sql plus工具登陆，用户名用scott。1. 在emp表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。select ename as 姓名, deptno as 部门编号, sal as 薪水 from emp;2. 在

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表中含有几个部门的记录。（用distinct去除重复记录）select distinct deptno from emp;5. 在emp表中查询出部门编号为10或20的记录（请分别使用or和in关键字）select * from emp w

3、here 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(*) from emp;8. 查询emp表中1981年雇用的所有员工，或者是1987年5月雇用的员工。select ename,hiredate from emp where to_char(hiredate,'yyyy')=19

4、81 or 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'11. 找出佣金高于薪金的所有员工。select ename,sal,comm from emp where comm > sal;12. 找出佣金高于薪金30%的所有员工。select enam

5、e,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');14. 找出部门10中所有经理（manager），部门20 中所有办事员（clerk）,既不是经理又不是办事员（clerk）但其薪金大于或等于2000的所有员工的详细资料。select * from emp wher

6、e (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. 找出不收取佣金或收取佣金少于100的员工。select ename,comm from emp where comm is null or comm

7、<100;17. 显示姓名不带有r的员工的姓名。 select ename from emp where ename not like '%r%'18. 显示员工的详细资料，按姓名排序。select * from emporder by ename desc19. 显示员工的姓名和受雇日期，根据服务年限，将最老的员工排在最前面。select ename,hiredate from emp order by to_char( hiredate,'yyyy-mm-dd');20. 显示所有员工的姓名，工作和薪金，按工作降序排序，若工作相同则按薪金排序。sele

8、ct ename,job,sal from emp order by job desc , sal desc;21. 显示姓名字段的任何位置包含a的所有员工的姓名。select ename from emp where ename like '%a%'22. 找出各月倒数第三天受雇的所有员工。select ename,hiredate from emp where last_day(hiredate)-2=hiredate;23. 找出早于28年前受雇的所有员工。 select ename ,hiredate from emp where to_char(sysdate,

9、9;yyyy')-to_char(hiredate,'yyyy')>28;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 替换a。select

10、replace(ename,'a','a') from emp;28. 显示满10年服务年限的员工的姓名和受雇日期。select ename,hiredate from emp where to_char(sysdate,'yyyymm')-to_char(hiredate,'yyyymm') >=10;29. 显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同，则将最早年份的员工排在最前面。select ename,to_char(hiredate,'yyyy-mm') from e

11、mp order by 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;第2章 多表查询1. 显示所有员工的姓

12、名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.deptno and comm is

13、 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，结果类似于下面的格式：employeesemp#managermgr#smith7369ford7902select e.ename employees,e.empno em

14、p#,m.ename manager,m.empno mgr#from emp e,emp mwhere e.mgr=m.empno;6. 查询各部门员工姓名和他们所在位置，结果类似于下面的格式：deptnoenameloc20smithdallasselect e.deptno deptno,ename ename,loc locfrom emp e,dept dwhere e.deptno=d.deptno;7. 显示：员工编号，员工名称，员工所在部门编号，员工所在部门名称。select empno,ename,e.deptno,dnamefrom emp e,dept dwhere e.

15、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 dwhere e.mgr=m.empnoand e.deptno=d.deptno;10. 显示：员工编号，员工名称，经理编号

16、，经理名称，员工所在部门编号，员工所在部门名称，经理所在部门编号，经理所在部门名称。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;第3章 分组查询1. 列出至少有一个员工的所有部门。 select deptno,count(ename) from emp group by deptno having coun

17、t(ename)>=1;2. 查询emp表中出每个部门的部门代码、薪水之和、平均薪水。select deptno,sum(sal),avg(sal)from empgroup by deptno;3. 查询emp表中出部门人数大于等于2的部门的部门代码、部门人数。select deptno,count(empno) from emp group by deptno having count(empno)>=2;4. 查询emp表中出部门平均薪水小于等于20000的部门的部门代码、平均薪水，并按平均薪水从大到小排序。select deptno,avg(sal)from empgrou

18、p by deptnohaving avg(sal)<=20000order by avg(sal) desc;5. 查询emp表中出部门合计薪水大于等于1000的部门的部门代码、平均薪水，并按平均薪水从大到小排序。select deptno,avg(sal),sum(sal)from empgroup by deptnohaving sum(sal)>=1000order by avg(sal) desc；6. 查询emp表中薪水最少，和薪水最大员工的姓名和薪水，并按薪水从大到小排序。select ename,salfrom empwhere sal=(select max(sa

19、l) from emp )or sal=(select min(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

20、e.empno,e.ename,dname,e.hiredate,m.hiredatefrom emp e,emp m,dept dwhere e.mgr=m.empnoand e.deptno=d.deptnoand to_char(e.hiredate,'yyyymmdd')<to_char(m.hiredate,'yyyymmdd');10. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。select dname,e.*from emp e,dept dwhere e.deptno(+)=d.deptno;11. 列出所有clerk(

21、办事员)的姓名，及其部门名称，部门人数。select *from (select deptno,enamefrom empwhere job='clerk') t,(select e.deptno,dname,count(e.deptno)from emp e,dept dwhere e.deptno=d.deptno and job='clerk'group by e.deptno,dname) mwhere t.deptno=m.deptno;12. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数。select job,count(job)fr

22、om empgroup by jobhaving min(sal)>1500;13. 列出在部门sales(销售部)工作的员工的姓名，假定不知道销售部的部门编号。select ename,e.deptno,dnamefrom emp e,dept dwhere dname='sales'and e.deptno=d.deptno;14. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的等级工资。select e.ename,dname,m.ename,s.grade,e.salfrom emp e,emp m,dept d,salgrade swhere e

23、.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 dwhere e.deptno=d.deptnoand job=(select job from emp where ename='scott')；16. 列出薪金等于部门30中员工的薪金的，所有员工的姓名

24、和薪金。select ename,sal from emp where deptno=30;17. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名、薪金和部门名称。 select ename,sal,dnamefrom emp e,dept dwhere e.deptno=d.deptnoand sal>all(select sal from emp where deptno=30);18. 列出在每个部门工作的员工数量、平均工资和平均服务期限。select dname,count(*),avg(sal),avg(sysdate-hiredate)/365)from emp e,d

25、ept 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.deptnogroup by d.deptno,dname,loc；21. 列出各种工作的最低工资及从事此工作的雇员姓名。select * from e

26、mp e,(select job,min(sal) ssal from emp group by job)twhere e.job=t.joband e.sal=t.ssal;22. 列出各个部门的manager(经理)的最低薪金。select dname,job,min(sal)from emp e,dept dwhere e.deptno=d.deptnoand job='manager'group by dname,job;23. 列出员工的年工资，按年薪从低到高排序。 select ename,sal*12from emp eorder by sal*12 asc;24

27、. 查出员工的信息，并要求这些员工的主管的薪水超过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,dept dwhere e.deptno=d.deptno and dname like '%s%' group by dname;26. 查询公司员工工资的最大值，最小值，平均值，总和。s

28、elect 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)from empgroup by job;29. 查询员工最高工资和最低工资的差距（difference）。select max(sal)-min(sal) "defference&quo

29、t;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，所在位置loc，员工数量和工资平均值。select dname,loc,count(e.deptno),avg(sal)from emp e,dept dwhere e.deptno=d.deptnogroup by dname,loc;第4章 子查询

30、1. 查询和scott相同部门的员工姓名ename和雇用日期hiredate。select ename,hiredatefrom emp where deptno=(select deptno from emp where ename='scott');2. 查询工资比公司平均工资高的所有员工的员工号empno，姓名ename和工资sal。select empno,ename,salfrom empwhere sal>(select avg(sal) from emp);3. 查询和姓名中包含字母u的员工在相同部门的员工的员工号empno和姓名ename。select e

31、mpno,enamefrom empwhere deptno=(select deptno from emp where ename like upper('%u%');4. 查询在部门的loc为newyork的部门工作的员工的员工姓名ename，部门名称dname和岗位名称job。select ename,dname,job from emp e,dept d where e.deptno=d.deptno and d.deptno= (select deptno from dept where loc='new york');5. 查询管理者是king的员工

32、姓名ename和工资sal。select e.ename,e.salfrom emp e,emp mwhere e.mgr=m.empnoand m.ename='king'6. 查询没有员工的部门。select * from deptwhere deptno not in (select distinct deptno from emp);第5章 表、约束、数据插入、修改、删除、sequence1. 创建表 emp1，字段如下eno char(3), ename char(6), sex char(2), age number(2),deptno int使用sql语句插入如下

33、数据，要求编号使用sequence实现：1 ，tom ， 男 ， 21 ，102 ，jerry ，男 ，21 ，203 ，kate ， 女 ，21 ，304 ，mary ，女 ，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） 把

34、mary的年龄修改为23。create table emp1(eno char(3) primary key, ename char(6) not null, sex char(2), age number(2), deptno int unique, constraint sex_age check(age between 18 and 60) and sex='男') or (age between 18 and 55) and sex='女') );create sequence myseq1 increment by 1 start with 1;ins

35、ert into emp1 values(myseq1.nextval,'tom','男',21,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

36、 emp1 values(myseq1.nextval,'jack','男',21,50);create table emp_bak as (select * from emp1 where 1=2);alter table emp_bak add constraint emp_bak_dept_fk foreign key (deptno) references dept(deptno);delete emp1 where ename='jack'update emp1 set age=23 where ename='mary'

37、2. 请创建一个表，表名为phone,表结构如下电话号码（phonenum varchar2（8） 电话费 （pay number(8,2)）号码等级（numlevel varchar2（4） 费用日期（paydate varchar2(12)(1) 插入以下两条数据： 123456， 600， pt04, 20051220 888888, 900, pt05, 20051019.(2) 创建一个备份表结构名为phone_bak, 将phone中的数据插入phone_bak中.(3) 将电话号码为123456的电话费改为1000，执行回滚操作(4) 查询phone表中所有字段和所有数据，如果p

38、honenum字段中的数据为123456，则返回y否则返回n(5) 查询phone表中所有字段和所有数据，显示费用日期（paydate）字段的日期转变成yyyy/mm/dd格式显示。(6) 将电话号码为888888的号码等级改为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','200

39、51220');insert into phone values('888888',900,'pt05','20051019');(2)create table phone_bak as (select * from phone);(3)update phone set pay=1000where phonenum='123456'rollback (4)select p.*,decode(p.phonenum,123456,'y','n')from phone p;(5) select p

40、honenum,pay,numlevel,to_date(paydate,'yyyy/mm/dd')from phone; (6) update phone set numlevel='pt04' where phonenum=888888;commit;第6章 视图1. 什么是视图。它有什么作用。视图是一个 虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图实际上是一张或者多张表上的预定义查询，这些表称为基表。2. 创建一个视图，此视图要包括以下信息：员工编号，员工姓名。create view view1 select empn

41、o,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. 创建一个视图，此视图要包括以下信息：员工编号，员工姓名，经理编号，经理姓名。create view view3as select e.empno,e.ename,m.empno 经理编号,m.ename 经理姓名from emp e

42、,emp m where e.mgr=m.empno；5. 创建一个视图，此视图要包括以下信息：部门编号，员工个数。create view view4 必须使用列别名命名此表达式，否则会出错as select deptno,count(ename) 人数from emp egroup by deptno；6. 创建一个视图，此视图要包括以下信息：部门编号，部门名称，员工个数。create view view5as select e.deptno, d.dname,count(ename) 人数from emp e,dept dwhere e.deptno=d.deptnogroup by e.

43、deptno, d.dname;7. 创建一个视图，此视图要包括以下信息：部门编号，部门名称，员工个数，部门内所有员工薪水的合计，部门内员工最高薪水，部门内员工最低薪水，部门内平均薪水。create view view6as select e.deptno, d.dname,count(ename) 人数,sum(sal) 总薪水,max(sal) 最高薪水,min(sal) 最低薪水,avg(sal) 平均薪水from emp e,dept dwhere e.deptno=d.deptnogroup by e.deptno, d.dname;第7章 用户角色权限1 创建新用户rxxt，密码r

44、xxt，使用的默认表空间是新建立的“cmsts”，临时表空间使用系统已经建立好的“temp”。在rxxt用户下做存储过程作业.doc作业。create user rxxt identified by rxxtdefault tablespace cmsts temporary tablespace temp2 创建用户“test1_自己姓名拼音”和“test2_自己姓名拼音”两个用户。使用第1个用户创建表customer，语句如下：create table customer( id int, name varchar(20);插入如下数据：insert into customer(id, na

45、me) values(1, '王小明');insert into customer(id, name) values(2, '张大明');然后使用第2个用户登录系统，并做如下操作：(1) 使用sql查询一下customer表：select * from test1_自己姓名拼音.customer; /表名前需要带上第1个用户的用户名。(2) 使用sql再插入一条数据：insert into test1_自己姓名拼音.customer(id, name) values(3, '付海');(3) 使用sql修改数据“王小明”，修改成“王晓明”。(4)

46、 使用sql删除所有数据。(5) 使用sql把此表删除掉。create user test1_mapei identified by mapei default tablespace cmsts temporary tablespace tempcreate user test2_mapei identified by mapei default tablespace cmsts temporary tablespace temp grant create session to test1_mapei;grant create session to test2_ mapei;grant con

47、nect,resource to test1_ mapei;grant connect,resource to test2_ mapei;grant create 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;gra

48、nt delete on test1_ mapei.customer to test2_ mapei;grant drop any table to test2_ 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_

49、mapei.customer;(5)drop table test1_ mapei.customer;第8章 表空间、分区表1. 什么是表空间。表空间是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，称作表空间。2. 什么是分区表。对表进行分区，有哪几种类型。分区表是将大表的数据分成称为分区的许多小的子集。分区表的类型：u 范围分区u hash分区（散列分区）u 列表分区u 复合分区3. 创建订单表（订单id，订单标题，订单总金额），使用范围分区，对订单总金额字段值的范围进行分区。create table booking ( b_id number(20),

50、b_title varchar2(20), b_money number(20) )partition by range(b_money)(partition 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，客户名称，行业），使用列表分区，对客户的行业字段值进行列表分区。create table cu

51、stom (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 values ('电子商务'), partition part_c04 values ('文员');5. 创建产品表（产品id，产品名称，标准价），使用哈希分区，对产品的标准价字段值

52、进行哈希分区。create table product (p_id number(20), p_name varchar2(10), p_price number(10) )partition by hash(p_price)( partition part_p01 , partition part_p02 , partition part_p03 , partition part_p04 )第9章 pl/sql1. 什么是pl/sql，pl/sql和sql有什么区别。什么是transact-sql。pl/sql 过程化语言，对sql的补充，可以用来实现比较复杂的业务逻辑;sql 数据库查询语

53、言，高级的非过程化编程语言，不需要用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式。transact-sql是具有批量与区块特性的sql指令集合，数据库开发人员可以利用它来撰写数据部份的商业逻辑（data-based business logic），以强制限制前端应用程序对数据的控制能力。同时，它也是数据库对象的主要开发语言。2. 有订单表order，单号字段orderno varchar(10)，ordername varchar(100)。需要实现自动编号，格式为yyyymmxxxx，其中xxxx为序号，如：2004050001，20040500022004059999等。不使

54、用oracle的sequence实现，而是自己定义一个管理序列的表，存放序号。使用pl/sql程序，向订单表中增加数据，订单编号能自动生成。create table order1 (orderno varchar2(10), ordername varchar(100)beginfor v_count in 1 . 9999loopinsert into order1(orderno)values(to_char(sysdate,'yyyymm')|trim(to_char(v_count,'0000');end loop;end;3. 使用pl/sql程序实现

55、，查询员工编号为7788的员工薪水，然后判断，如果薪水小于1000，打印“薪水低”，如果1000到2000，打印“中等”，大于2000，打印“薪水不错”。declare v_sal emp.sal%type;v_s varchar2(10);begin select sal,case when sal<1000 then '薪水低' when sal>1000 and sal<2000 then '中等' when sal>2000 then '薪水不错' end s into v_sal,v_s from emp whe

56、re empno=7788; dbms_output.put_line(v_sal|' '|v_s);end ;4. 查询出所有员工信息，打印出所有员工的编号和姓名。declare v_emprecord emp%rowtype; cursor c_emp is select * from emp ;begin for v_emprecord in c_emp loop dbms_output.put_line('empno='|v_emprecord.empno|' '|'ename='|v_emprecord.ename); end loop;end ;第10章 存储过程与函数1. 什么是存储过程，存储过程和pl/sql有什么区别。存储过程是一种pl/sql块，以命名