Oracle练习题附答案

实验一

练习1、请查询表DEPT中所有部门的情况。 select * from dept;

练习2、查询表DEPT中的部门号、部门名称两个字段的所有信息。 select deptno,dname from dept;

练习3、请从表EMP中查询10号部门工作的雇员姓名和工资。 select ename,sal from emp where deptno=10;

练习4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。 select ename,sal from emp where job='CLERK' or job='MANAGER';

练习5、请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。 select ename,deptno,sal,job from emp where deptno between 10 and 30;

练习6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。 select ename,sal,job from emp where ename like 'J%';

练习7、请从表EMP中查找工资低于 2000的雇员的姓名、工作、工资，并按工资降序排列。

select ename,job,sal from emp where sal<=2000 order by sal desc;

练习8、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。

select ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and job=?CLERK?;

练习9、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。 select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+) and a.sal>=2000;

select a.ename 员工,b.ename 经理

from emp a left join emp b on a.mgr=b.empno where a.sal>=2000;

练习10、在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。 select ename,job,sal from emp where sal>(select sal from emp where ename=?JONES?);

练习11、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。 select ename,job,deptno from emp where deptno not in (select deptno from dept);

练习12、查找工资在1000～3000之间的雇员所在部门的所有人员信息

select * from emp where deptno in (select distinct deptno from emp where sal between 1000 and 3000);

select

练习13、雇员中谁的工资最高。

select ename from emp where sal=(select max(sal) from emp);

select ename from (select * from emp order by sal desc) where rownum<=1;

*练习14、雇员中谁的工资第二高（考虑并列第一的情况，如何处理）。

select ename from (select ename ,sal from (select * from emp order by sal desc) where rownum<=2 order by sal) where rownum<=1;

实验二

1． 查询所有雇员的姓名、SAL与COMM之和。 select ename,sal+nvl(comm,0) “sal-and-comm” from emp;

2． 查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字 select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and hiredate<=to_date(?1981-07-01?,?yyyy-mm-dd?);

3． 查询各部门中81年1 月1日以后来的员工数

select deptno,count(*) from emp where hiredate>=to_date(?1981-01-01?,?yyyy-mm-dd?) group by deptno; 4． 查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资 select ename,sal from emp where (job=?MANAGER? or job=?SALES?) and deptno in (select deptno from dept where loc=?CHICAGO?);

5． 查询列出来公司就职时间超过24年的员工名单

select ename from emp where hiredate<=add_months(sysdate,-288);

6． 查询于81年来公司所有员工的总收入（SAL和COMM）

select sum(sal+nvl(comm,0)) from emp where to_char(hiredate,?yyyy?)=?1981?;

7． 查询显示每个雇员加入公司的准确时间，按××××年 ××月××日 时分秒显示。 select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

8． 查询公司中按年份月份统计各地的录用职工数量 select to_char(hiredate,'yyyy-mm'),loc,count(*) from emp,dept

where emp.deptno=dept.deptno group by to_char(hiredate,'yyyy-mm'),loc;

9． 查询列出各部门的部门名和部门经理名字

select dname,ename from emp,dept where emp.deptno=dept.deptno and job=?MANAGER?;

10． 查询部门平均工资最高的部门名称和最低的部门名称

select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) ) where rownum<=1)

union all select dname from dept where deptno=(select deptno from (select deptno from emp group by deptno order by avg(sal) desc ) where rownum<=1); 11． *查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名 select ename,dname

from (select ename,deptno from

(select ename,deptno from emp where hiredate>(select hiredate from emp where empno=7521) order by hiredate ) where rownum<=1) e,dept where e.deptno=dept.deptno

实验三、

1． 建立一个表（表名自定），表结构与EMP相同，没有任何记录。 create table my_emp as select * from emp;

2． 用Insert语句输入5条记录，并提交。

3． 扩大该表的记录数到约40条，并使雇员号不重复；每个雇员都有所属部门，雇员在同一部门的经理是同一人。 insert …. update … commit

4． 建立一个与DEPT表结构和记录完全相同的新表，并与前项新表建立参照完整性约束。

alter table my_dept add( constraint s1 primary key(deptno));

alter table my_emp add(constraint s2 foreign key(deptno) references dept(deptno)); 5． 对在?NEW YORK?工作的雇员加工资，每人加200。

6． *如果雇员姓名与部门名称中有一个或一个以上相同的字母，则该雇员的COMM增加 500。

update my_emp a

set comm=NVL(comm,0)+500 where a.ename<>(

select translate(a.ename,b.dname,CHR(27)) from my_dept b where b.deptno=a.deptno );

--a.deptno与 b.deptno必须有主外键连接，否则可能出错，为什么？ commit;

7． 删除部门号为30的记录，并删除该部门的所有成员。 delete from emp where deptno=30; delete from dept where deptno=30; commit

8． 新增列性别SEX，字符型。 alter table emp add(sex char(2));

9． 修改新雇员表中的MGR列，为字符型。 该列数据必须为空

alter table emp modify(mgr varchar2(20));

10． 试着去删除新表中的一个列。 alter table my_emp drop (comm);

实验四、

1． 查询部门号为30的所有人员的管理层次图。 select level,ename from emp connect by mgr=prior empno

start with deptno=30 and job='MANAGER';

2． 查询员工SMITH的各个层次领导。 select level,ename from emp connect by prior mgr= empno start with ENAME='SMITH';

3． 查询显示EMP表各雇员的工作类型，并翻译为中文显示 用decode函数

4． * 查询显示雇员进入公司当年是什么属相年（不考虑农历的年份算法） 用decode函数

5． 建立一个视图myV_emp，视图包括myEMP表的empno、ename、sal，并按sal从大到小排列。

create view myV_EMP as select empno,ename,sal from emp; 6． 定义一个mySeq，对select mySeq.nextval,my_emp.* from my_emp的执行结果进行说明。

7． 定义序列mySeq、myEMP、 myV_emp的同义词，能否用同义词对上述对象进行访问。

8． 在myEMP表中建立ename的唯一性索引。

9． 如何在sql*plus中，运行sql的脚本（即后缀为.sql的文件）

实验五、

1． 观察下列PL/SQL的执行结果 declare

s emp%rowtype; begin

select * into s from emp

where ename='KING';

DBMS_OUTPUT.PUT_LINE(s.empno||s.ename||s.job||s.sal); END;

2． 编写一个PL/SQL，显示ASC码值从32至120的字符。 begin

for i in 32..120 loop

dbms_output.put_line(chr(i)); end loop; end;

3． 计算 myEMP表中COMM最高与最低的差值，COMM值为空时按0计算。 declare

var1 number; var2 number;

val_comm number; begin

select max(nvl(comm,0)) into var1 from myemp; select min(nvl(comm,0)) into var2 from myemp; val_comm:=var1-var2;

dbms_output.put_line(val_comm); end;

4． 根据表myEMP中deptno字段的值，为姓名为?JONES?的雇员修改工资；若部门号为10，则工资加100；部门号为20，加200；其他部门加 400。 declare c1 number; c2 number; begin

select deptno into c1 from emp where ename=?JONES?; if c1=10 then c2:=100; elsif c1=20 then c2:=200; else c2:=400; end if;

update emp set sal=sal+c2 where ename=?JONES?; commit; end;

5． 计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部门号、人数、工资总和。

6． 计算myEMP中所有雇员的所得税总和。假设所得税为累进税率，所得税算法为：工资收入为0－1000为免税；收入1000－2000者，超过1000的部分税率10％；2000－3000者超过 2000部分按20％税率计算；3000－4000者超过3000部分按30％税率计算；4000以上收入，超过4000部分按40％税率计算。（请查阅累进税率的概念） declare

sum_xx number:=0; xx number;

begin

-- 计算收入为1000－2000的所得税总额

select sum((sal-1000)*0.1) into xx from emp where sal >1000 and sal<=2000; sum_xx:=sum_xx+xx;

--计算收入为 2000－3000的所得税总额

select sum((sal-2000)*0.2+100) into xx from emp where sal >2000 and sal<=3000; sum_xx:=sum_xx+xx;

--计算收入为 3000－4000的所得税总额

select sum((sal-3000)*0.3+300) into xx from emp where sal >3000 and sal<=4000; sum_xx:=sum_xx+xx;

--计算收入为4000以上的所得税总额

select sum((sal-4000)*0.4+600) into xx from emp where sal >4000; sum_xx:=sum_xx+xx;

dbms_output.put_line(sum_xx); end;

7． * （可选做，难题）假设有个表如myEMP，未建立主键，含有多条记录重复（列值完全相同），试编制一个PL/SQL，将多余的重复记录删除。 实验六、

1． 用外部变量，实现两个PL/SQL程序间的数据交换。 SQL> variable a1 number; SQL> begin 2 :a1:=1000; 3 end; 4 /

PL/SQL 过程已成功完成。

SQL> begin

2 dbms_output.put_line(:a1); 3 end; 4 / 1000

PL/SQL 过程已成功完成。

2． 插入myEMP表中的数据记录，考虑可能出现的例外，并提示。 主要的例外提示：唯一性索引值重复 DUP_VAL_ON_INDEX

3． 删除myDEPT表中的数据记录一条，考虑例外情况，并提示。 主要的例外提示：违反完整约束条件

4． 将下列PL/SQL改为FOR游标 declare

cursor cur_myemp is select * from emp; r emp%rowtype; begin

open cur_myemp;

fetch cur_myemp into r; while cur_myemp%found loop

dbms_output.put_line(r.ename); fetch cur_myemp into r; end loop;

close cur_myemp; end;

5． 工资级别的表salgrade，列出各工资级别的人数。（用游标来完成） declare v1 number;

cursor cur1 is select * from salgrade; begin

for c1 in cur1 loop

select count(*) into v1 from emp where sal between c1.losal and c1.hisal; dbms_output.put_line('grade'||c1.grade||' '||v1); end loop; end;

实验七、

1． 在myEMP表中增加一个字段，字段名为EMPPASS，类型为可变长字符。 2． 建立一个存储过程，用于操作用户登录的校验，登录需要使用EMPNO和EMPPASS，并需要提示登录中的错误，如是 EMPNO不存在，还是EMPNO存在而是EMPPASS错误等。

create or replace procedure p_login( in_empno in emp.empno%type, in_emppass in emp.emppass%type, out_code out number, out_desc out varchar2) is

x1 emp.ename%type; x2 number; begin

select ename into x1 from emp where empno=in_empno;

select count(*) into x2 from emp where empno=in_empno and emppass=in_emppass; if x2=1 then out_code:=0; out_desc:=x1; else

out_code:=2;

out_desc:=?用户登陆密码错误！?; end if; exception

when NO_DATA_FOUND then out_code:=1;

out_desc:=?该用户号存在!?; when TOO_MANY_ROWS then out_code:=3;

out_desc:=?该用户号有重复值!?; when others then out_code:=100;

out_desc:=?其他错误!?; end;

3． 建立一个存储过程，实现myEMP表中指定雇员的EMPPASS字段的修改，修改前必须进行EMPPASS旧值的核对。

Create or REPLACE PROCEDURE P_CHANGEPASS( IN_EMPNO IN EMP.EMPNO%TYPE, IN_OLDPASS IN EMP.EMPPASS%TYPE, IN_NEWPASS IN EMP.EMPPASS%TYPE, OUT_CODE OUT NUMBER, OUT_DESC OUT VARCHAR2) IS

X1 NUMBER; BEGIN

Select COUNT(*) INTO X1 FROM EMP Where EMPNO=IN_EMPNO AND EMPPASS=IN_OLDPASS; IF X1=1 THEN

update emp set emppass=in_newpass where empno=in_empno; commit;

OUT_CODE:=0;

OUT_DESC:=?修改口令成功?; ELSE

OUT_CODE:=1;

OUT_DESC:=?修改口令不成功?; END IF; exception

when others then out_code:=100;

out_desc:=?其他错误?; END;

4． 建立一个函数，输入一个雇员号，返回该雇员的所在同一部门的最高级别上司姓名。

create or replace function f_leader(

in_empno in emp.empno%type) return varchar2 is

v1 number; v2 number;

v3 emp.ename%type; v4 emp.deptno%type; begin

v1:=in_empno; v3:=' 未找到';

select deptno into v4 from emp where empno=v1; loop

select mgr into v2 from emp where empno=v1;

select ename into v3 from emp where empno=v2 and deptno=v4; v1:=v2; end loop; exception

when others then return v3; end;

5． 试用上题函数，实现各雇员的同一部门最高级别上司的Select查询。 select f_leader(7521) from dual;

6． *编写实验五中第六题，关于各雇员工资的所得税计算函数

实验八、

1． 建立一个触发器，当myEMP表中部门号存在时，该部门不允许删除。 create or replace trigger dept_line_delete before delete on dept for each row declare v1 number; begin

select count(*) into v1 from emp where deptno=:old.deptno; if v1>=1 then RAISE_APPLICATION_ERROR(-20000,?错误?); end if; end;

实验九、

1． 建立一个示例包emp_mgmt中，新增一个修改雇员所在部门的过程。 create or replace package emp_mgmt as procedure change_dept(

in_newdept in emp.deptno%type, out_code out number,

out_desc out varchar2);

mgmt_empno emp.empno%type; procedure mgmt_login(

in_empno in emp.empno%type, in_emppass in emp.emppass%type, out_code out number, out_desc out varchar2); end;

create or replace package body emp_mgmt as procedure change_dept(

in_newdept in emp.deptno%type, out_code out number, out_desc out varchar2) is begin

update emp set deptno=in_newdept where empno=mgmt_empno; commit; out_code:=0; out_desc:=?ok?; end;

procedure mgmt_login(

in_empno in emp.empno%type, in_emppass in emp.emppass%type, out_code out number, out_desc out varchar2) is begin

--登陆过程见实验七第2题 mgmt_empno:=in_empno; out_code:=0; out_desc:=?ok?; end; end;

2． 假设myEMP表中有口令字段password，试在包emp_mgmt中建立一个登录的过程，并将登录成功的雇员号存入包变量。 见前一题

3． 示例包emp_mgmt中，将remove_emp操作设限，只有本部门经理操作才能删除本部门雇员记录，只有公司头头PRESIDENT才能删除部门经理的雇员记录。 --

procedure remove_emp(

remove_empno emp.empno%type,

out_code number, out_desc varchar2) is

x emp.job%type; y number; begin

select job,deptno into x,y from emp where empno=mgmt_empno; if x=?PRESIDENT? then

delete from emp where empno=remove_empno and job=?MANAGER?; else

delete from emp where empno=remove_empno and deptno=y and x=?MANAGER?; end if

if sql%found then out_code:=0; out_desc:=?ok?; else

out_code:=1;

out_desc:=?未删除记录?; end if; commit; end;

4． *用 JAVA＋ORACLE实现上题的软件功能。

实验十

1． 编写一段PL/SQL，利用系统工具包，实现对SERVER 端数据文件D:\\DATA\\A.TXT的读取输出至缓冲区。

2． 编写一个存储过程，就myEMP表，输入参数为字段名和匹配值（字符型），对符合匹配条件的工资加100。

3． 编写一个存储过程，输入参数为一个表名，通过存储过程处理将该表删除Drop，并返回是否成功的信息。

实验十一

1． 以雇员作为对象类型，试根据myEMP表结构设计其属性，方法主要有雇员更换部门、更换工种、 MAP排序的定义。

2． 编制一个雇员类型的对象表myOBJ_EMP。 3． 添加对象表myOBJ_EMP的数据10条。 4． 试对对象表排序输出。

5． 给对象表中部门号为20的记录的工资增加10％。 6． 显示每个雇员所在的雇员名、部门名称。

select '销售等级' || decode(grade,1, '一',2, '二',3, '三',4, '四',5, '五')销售等级,

decode(grade,1,'[' || losal ||',' || hisal || ']', 2,'[' || losal ||',' || hisal || ']', 3,'[' || losal ||',' || hisal || ']', 4,'[' || losal ||',' || hisal || ']',

5,'[' || losal ||',' || hisal || ']')销售等级要求 from salgrade

select grade 销售等级,

case when losal=700 and hisal=1200 then '700,1200' when losal=1201 and hisal=1400 then '1201,1400' when losal=1401 and hisal=2000 then '1401,2000' when losal=2001 and hisal=3000 then '2001,3000' else '3001,9999' end 销售等级要求 from salgrade

select grade 销售等级, case when grade=1,then '一' when grade=2,then '二' when grade=3,then '三' when grade=4,then '四' else '五' end 销售等级

case when losal=700 and hisal=1200 then '700,1200' when losal=1201 and hisal=1400 then '1201,1400' when losal=1401 and hisal=2000 then '1401,2000' when losal=2001 and hisal=3000 then '2001,3000' else end 销售等级要求 from salgrade

本文来源：https://www.bwwdw.com/article/vp95.html