oraclesql练习题及答案Word格式.docx
《oraclesql练习题及答案Word格式.docx》由会员分享，可在线阅读，更多相关《oraclesql练习题及答案Word格式.docx（14页珍藏版）》请在冰豆网上搜索。
selectename,job,deptnofromempwheredeptnonotin（selectdeptnofromdept）;
练习12、查找工资在1000～3000之间的雇员所在部门的所有人员信息
select*fromempwheredeptnoin（selectdistinctdeptnofromempwheresalbetween1000and3000）;
练习13、雇员中谁的工资最高。
selectenamefromempwheresal=（selectmax（sal）fromemp）;
selectenamefrom（select*fromemporderbysaldesc）whererownum<
=1;
*练习14、雇员中谁的工资第二高（考虑并列第一的情况，如何处理）。
selectenamefrom（selectename,salfrom（select*fromemporderbysaldesc）whererownum<
=2orderbysal）whererownum<
实验二
1．查询所有雇员的姓名、SAL与COMM之和。
selectename,sal+nvl（comm,0）“sal-and-comm”fromemp;
2．查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字
selectename,sal,dnamefromemp,deptwhereemp.deptno=dept.deptnoandhiredate<
=to_date（‘1981-07-01’,’yyyy-mm-dd’）;
3．查询各部门中81年1月1日以后来的员工数
selectdeptno,count（*）fromempwherehiredate>
=to_date（‘1981-01-01’,’yyyy-mm-dd’）groupbydeptno;
4．查询所有在CHICAGO工作的经理MANAGER和销售员SALESMAN的姓名、工资
selectename,salfromempwhere（job=’MANAGER’orjob=’SALES’）anddeptnoin（selectdeptnofromdeptwhereloc=’CHICAGO’）;
5．查询列出来公司就职时间超过24年的员工名单
selectenamefromempwherehiredate<
=add_months（sysdate,-288）;
6．查询于81年来公司所有员工的总收入（SAL和COMM）
selectsum（sal+nvl（comm,0））fromempwhereto_char（hiredate,’yyyy’）=’1981’;
7．查询显示每个雇员加入公司的准确时间，按×
×
年×
月×
日时分秒显示。
selectename,to_char（hiredate,'
yyyy-mm-ddhh24:
mi:
ss'
）fromemp;
8．查询公司中按年份月份统计各地的录用职工数量
selectto_char（hiredate,'
yyyy-mm'
）,loc,count（*）fromemp,dept
whereemp.deptno=dept.deptnogroupbyto_char（hiredate,'
）,loc;
9．查询列出各部门的部门名和部门经理名字
selectdname,enamefromemp,deptwhereemp.deptno=dept.deptnoandjob=’MANAGER’;
10．查询部门平均工资最高的部门名称和最低的部门名称
selectdnamefromdeptwheredeptno=（selectdeptnofrom（selectdeptnofromempgroupbydeptnoorderbyavg（sal））whererownum<
=1）
unionallselectdnamefromdeptwheredeptno=（selectdeptnofrom（selectdeptnofromempgroupbydeptnoorderbyavg（sal）desc）whererownum<
=1）;
11．*查询与雇员号为7521员工的最接近的在其后进入公司的员工姓名及其所在部门名
selectename,dname
from（selectename,deptnofrom
（selectename,deptnofromempwherehiredate>
（selecthiredatefromempwhereempno=7521）orderbyhiredate）whererownum<
=1）e,dept
wheree.deptno=dept.deptno
实验三、
1．建立一个表（表名自定），表结构与EMP相同，没有任何记录。
createtablemy_empasselect*fromemp;
2．用Insert语句输入5条记录，并提交。
3．扩大该表的记录数到约40条，并使雇员号不重复；
每个雇员都有所属部门，雇员在同一部门的经理是同一人。
insert….
update…
commit
4．建立一个与DEPT表结构和记录完全相同的新表，并与前项新表建立参照完整性约束。
altertablemy_deptadd（constraints1primarykey（deptno））;
altertablemy_empadd（constraints2foreignkey（deptno）referencesdept（deptno））;
5．对在‘NEWYORK’工作的雇员加工资，每人加200。
6．*如果雇员姓名与部门名称中有一个或一个以上相同的字母，则该雇员的COMM增加500。
updatemy_empa
setcomm=NVL（comm,0）+500
wherea.ename<
>
（
selecttranslate（a.ename,b.dname,CHR（27））
frommy_deptbwhereb.deptno=a.deptno
）;
--a.deptno与b.deptno必须有主外键连接，否则可能出错，为什么？
commit;
7．删除部门号为30的记录，并删除该部门的所有成员。
deletefromempwheredeptno=30;
deletefromdeptwheredeptno=30;
8．新增列性别SEX，字符型。
altertableempadd（sexchar
（2））;
9．修改新雇员表中的MGR列，为字符型。
该列数据必须为空
altertableempmodify（mgrvarchar2（20））;
10．试着去删除新表中的一个列。
altertablemy_empdrop（comm）;
实验四、
1．查询部门号为30的所有人员的管理层次图。
selectlevel,enamefromemp
connectbymgr=priorempno
startwithdeptno=30andjob='
2．查询员工SMITH的各个层次领导。
connectbypriormgr=empno
startwithENAME='
SMITH'
3．查询显示EMP表各雇员的工作类型，并翻译为中文显示
用decode函数
4．*查询显示雇员进入公司当年是什么属相年（不考虑农历的年份算法）
5．建立一个视图myV_emp，视图包括myEMP表的empno、ename、sal，并按sal从大到小排列。
createviewmyV_EMPasselectempno,ename,salfromemp;
6．定义一个mySeq，对selectmySeq.nextval,my_emp.*frommy_emp的执行结果进行说明。
7．定义序列mySeq、myEMP、myV_emp的同义词，能否用同义词对上述对象进行访问。
8．在myEMP表中建立ename的唯一性索引。
9．如何在sql*plus中，运行sql的脚本（即后缀为.sql的文件）
实验五、
1．观察下列PL/SQL的执行结果
declare
semp%rowtype;
begin
select*intos
fromemp
whereename='
KING'
DBMS_OUTPUT.PUT_LINE（s.empno||s.ename||s.job||s.sal）;
END;
2．编写一个PL/SQL，显示ASC码值从32至120的字符。
foriin32..120
loop
dbms_output.put_line（chr（i））;
endloop;
end;
3．计算myEMP表中COMM最高与最低的差值，COMM值为空时按0计算。
var1number;
var2number;
val_commnumber;
selectmax（nvl（comm,0））intovar1frommyemp;
selectmin（nvl（comm,0））intovar2frommyemp;
val_comm:
=var1-var2;
dbms_output.put_line（val_comm）;
4．根据表myEMP中deptno字段的值，为姓名为‘JONES’的雇员修改工资；
若部门号为10，则工资加100；
部门号为20，加200；
其他部门加400。
c1number;
c2number;
selectdeptnointoc1fromempwhereename=’JONES’;
ifc1=10then
c2:
=100;
elsifc1=20then
c2:
=200;
elsec2:
=400;
endif;
updateempsetsal=sal+c2whereename=’JONES’;
5．计算显示部门人数最多的部门号、人数、工资总和，以及部门人数最少的部门号、人数、工资总和。
6．计算myEMP中所有雇员的所得税总和。
假设所得税为累进税率，所得税算法为：
工资收入为0－1000为免税；
收入1000－2000者，超过1000的部分税率10％；
2000－3000者超过2000部分按20％税率计算；
3000－4000者超过3000部分按30％税率计算；
4000以上收入，超过4000部分按40％税率计算。
（请查阅累进税率的概念）
sum_xxnumber:
=0;
xxnumber;
--计算收入为1000－2000的所得税总额
selectsum（（sal-1000）*0.1）intoxxfromempwheresal>
1000andsal<
sum_xx:
=sum_xx+xx;
--计算收入为2000－3000的所得税总额
selectsum（（sal-2000）*0.2+100）intoxxfromempwheresal>
2000andsal<
=3000;
--计算收入为3000－4000的所得税总额
selectsum（（sal-3000）*0.3+300）intoxxfromempwheresal>
3000andsal<
=4000;
--计算收入为4000以上的所得税总额
selectsum（（sal-4000）*0.4+600）intoxxfromempwheresal>
4000;
dbms_output.put_line（sum_xx）;
7．*（可选做，难题）假设有个表如myEMP，未建立主键，含有多条记录重复（列值完全相同），试编制一个PL/SQL，将多余的重复记录删除。
实验六、
1．用外部变量，实现两个PL/SQL程序间的数据交换。
SQL>
variablea1number;
begin
2:
a1:
=1000;
3end;
4/
PL/SQL过程已成功完成。
2dbms_output.put_line（:
a1）;
1000
2．插入myEMP表中的数据记录，考虑可能出现的例外，并提示。
主要的例外提示：
唯一性索引值重复DUP_VAL_ON_INDEX
3．删除myDEPT表中的数据记录一条，考虑例外情况，并提示。
违反完整约束条件
4．将下列PL/SQL改为FOR游标
cursorcur_myempisselect*fromemp;
remp%rowtype;
opencur_myemp;
fetchcur_myempintor;
whilecur_myemp%found
dbms_output.put_line（r.ename）;
closecur_myemp;
5．工资级别的表salgrade，列出各工资级别的人数。
（用游标来完成）
v1number;
cursorcur1isselect*fromsalgrade;
forc1incur1
loop
selectcount（*）intov1fromempwheresalbetweenc1.losalandc1.hisal;
dbms_output.put_line（'
grade'
||c1.grade||'
'
||v1）;
endloop;
实验七、
1．在myEMP表中增加一个字段，字段名为EMPPASS，类型为可变长字符。
2．建立一个存储过程，用于操作用户登录的校验，登录需要使用EMPNO和EMPPASS，并需要提示登录中的错误，如是EMPNO不存在，还是EMPNO存在而是EMPPASS错误等。
createorreplaceprocedurep_login（
in_empnoinemp.empno%type,
in_emppassinemp.emppass%type,
out_codeoutnumber,
out_descoutvarchar2）
is
x1emp.ename%type;
x2number;
selectenameintox1fromempwhereempno=in_empno;
selectcount（*）intox2fromempwhereempno=in_empnoandemppass=in_emppass;
ifx2=1then
out_code:
out_desc:
=x1;
else
=2;
=’用户登陆密码错误！
’;
exception
whenNO_DATA_FOUNDthen
out_code:
out_desc:
=’该用户号存在!
whenTOO_MANY_ROWSthen
=3;
=’该用户号有重复值!
whenothersthen
=’其他错误!
3．建立一个存储过程，实现myEMP表中指定雇员的EMPPASS字段的修改，修改前必须进行EMPPASS旧值的核对。
CreateorREPLACEPROCEDUREP_CHANGEPASS（
IN_EMPNOINEMP.EMPNO%TYPE,
IN_OLDPASSINEMP.EMPPASS%TYPE,
IN_NEWPASSINEMP.EMPPASS%TYPE,
OUT_CODEOUTNUMBER,
OUT_DESCOUTVARCHAR2）
IS
X1NUMBER;
BEGIN
SelectCOUNT（*）INTOX1FROMEMPWhereEMPNO=IN_EMPNOANDEMPPASS=IN_OLDPASS;
IFX1=1THEN
updateempsetemppass=in_newpasswhereempno=in_empno;
commit;
OUT_CODE:
OUT_DESC:
=’修改口令成功’;
ELSE
=’修改口令不成功’;
ENDIF;
=’其他错误’;
4．建立一个函数，输入一个雇员号，返回该雇员的所在同一部门的最高级别上司姓名。
createorreplacefunctionf_leader（
in_empnoinemp.empno%type）returnvarchar2
v2number;
v3emp.ename%type;
v4emp.deptno%type;
v1:
=in_empno;
v3:
='
未找到'
selectdeptnointov4fromempwhereempno=v1;
selectmgrintov2fromempwhereempno=v1;
selectenameintov3fromempwhereempno=v2anddeptno=v4;
=v2;
returnv3;
5．试用上题函数，实现各雇员的同一部门最高级别上司的Select查询。
selectf_leader（7521）fromdual;
6．*编写实验五中第六题，关于各雇员工资的所得税计算函数
实验八、
1．建立一个触发器，当myEMP表中部门号存在时，该部门不允许删除。
createorreplacetriggerdept_line_delete
beforedeleteondeptforeachrow
selectcount（*）intov1fromempwheredeptno=:
old.deptno;
ifv1>
=1thenRAISE_APPLICATION_ERROR（-20000,’错误’）;
实验九、
1．建立一个示例包emp_mgmt中，新增一个修改雇员所在部门的过程。
createorreplacepackageemp_mgmtas
procedurechange_dept（
in_newdeptinemp.deptno%type,
out_descoutvarchar2）;
mgmt_empnoemp.empno%type;
proceduremgmt_login（
out_codeou
升级会员 