02 Oracle 习题集(V4).doc

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. 在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 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(*) from emp;8. 查询emp表中1981年雇用的所有员工，或者是1987年5月雇用的员工。select ename,hiredate from emp where to_char(hiredate,yyyy)=1981 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 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);14. 找出部门10中所有经理（MANAGER），部门20 中所有办事员（CLERK）,既不是经理又不是办事员（CLERK）但其薪金大于或等于2000的所有员工的详细资料。select * from emp where (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 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 替换A。select 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 emp 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. 显示所有员工的姓名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 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 Emp#,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.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. 显示：员工编号，员工名称，经理编号，经理名称，员工所在部门编号，员工所在部门名称，经理所在部门编号，经理所在部门名称。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 count(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 empgroup by deptnohaving avg(sal)=1000order by avg(sal) desc；6. 查询emp表中薪水最少，和薪水最大员工的姓名和薪水，并按薪水从大到小排序。select ename,salfrom empwhere sal=(select max(sal) 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 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)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.grade,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 dwhere e.deptno=d.deptnoand 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. 列出在每个部门工作的员工数量、平均工资和平均服务期限。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.deptnogroup 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;22. 列出各个部门的MANAGER(经理)的最低薪金。select dname,job,min(sal)from emp e,dept dwhere e.deptno=d.deptnoand job=MANAGERgroup 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,dept 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)from empgroup by job;29. 查询员工最高工资和最低工资的差距（DIFFERENCE）。select max(sal)-min(sal) DEFFERENCEfrom 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章 子查询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 empno,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的员工姓名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语句插入如下数据，要求编号使用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） 把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;insert 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 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;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表中所有字段和所有数据，如果PHONENUM字段中的数据为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,20051220);insert into phone values(888888,900,pt05,20051019);(2)create table phone_bak as (select * from phone);(3)update phone set pay=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;第6章 视图1. 什么是视图。它有什么作用。视图是一个 虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图实际上是一张或者多张表上的预定义查询，这些表称为基表。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. 创建一个视图，此视图要包括以下信息：员工编号，员工姓名，经理编号，经理姓名。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 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.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，密码RXXT，使用的默认表空间是新建立的“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, NAME) 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) 使用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 connect,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;grant 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_ 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), 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 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 VALUES (电子商务), PARTITION part_c04 VALUES (文员);5. 创建产品表（产品ID，产品名称，标准价），使用哈希分区，对产品的标准价字段值进行哈希分区。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 数据库查询语言，高级的非过程化编程语言，不需要用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式。Transact-SQL是具有批量与区块特性的SQL指令集合，数据库开发人员可以利用它来撰写数据部份的商业逻辑（Data-based Business Logic），以强制限制前端应用程序对数据的控制能力。同时，它也是数据库对象的主要开发语言。2. 有订单表ORDER，单号字段OrderNo VARCHAR(10)，OrderName VARCHAR(100)。需要实现自动编号，格式为YYYYMMXXXX，其中XXXX为序号，如：2004050001，20040500022004059999等。不使用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程序实现，查询员工编号为7788的员工薪水，然后判断，如果薪水小于1000，打印“薪水低”，如果1000到2000，打印“中等”，大于2000，打印“薪水不错”。DECLARE v_sal emp.sal%TYPE;v_s VARCHAR2(10);BEGIN SELECT sal,CASE WHEN sal1000 AND sal2000 THEN 薪水不错 END s INTO v_sal,v_s FROM emp WHERE 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块，以命名的数据库对象形式存储于数据库当中。区别：pl/sql块是没有存储过程名字的，写好后不能被其他模块调用，而存储过程可以用call 存储过程名 这种格式来进行调用，而且存储过程还能被其他的存储过程所调用。pl/sql 是指对标准 sql 的扩展，即加上 if/loop/break 等流程控制功能；pl/sq