oracle sql 练习题及答案.docx
- 文档编号:10842819
- 上传时间:2023-02-23
- 格式:DOCX
- 页数:18
- 大小:22.33KB
oracle sql 练习题及答案.docx
《oracle sql 练习题及答案.docx》由会员分享,可在线阅读,更多相关《oracle sql 练习题及答案.docx(18页珍藏版)》请在冰豆网上搜索。
oraclesql练习题及答案
Oracle_sql练习题及答案
实验一
练习1、请查询表DEPT中所有部门的情况。
select*fromdept;
练习2、查询表DEPT中的部门号、部门名称两个字段的所有信息。
selectdeptno,dnamefromdept;
练习3、请从表EMP中查询10号部门工作的雇员姓名和工资。
selectename,salfromempwheredeptno=10;
练习4、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。
selectename,salfromempwherejob='CLERK'orjob='MANAGER';
练习5、请在EMP表中查找部门号在10-30之间的雇员的姓名、部门号、工资、工作。
selectename,deptno,sal,jobfromempwheredeptnobetween10and30;
练习6、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。
selectename,sal,jobfromempwhereenamelike'J%';
练习7、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资,并按工资降序排列。
selectename,job,salfromempwheresal<=2000orderbysaldesc;
练习8、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。
selectename,sal,emp.deptno,dname,locfromemp,deptwhereemp.deptno=dept.deptnoandjob=’CLERK’;
练习9、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。
selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno(+)anda.sal>=2000;
练习10、在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。
selectename,job,salfromempwheresal>(selectsalfromempwhereename=’JONES’);
练习11、列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。
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;
实验二
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,'yyyy-mm'),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;
commit
8.新增列性别SEX,字符型。
altertableempadd(sexchar
(2));
9.修改新雇员表中的MGR列,为字符型。
该列数据必须为空
altertableempmodify(mgrvarchar2(20));
10.试着去删除新表中的一个列。
altertablemy_empdrop(comm);
实验四、
1.查询部门号为30的所有人员的管理层次图。
selectlevel,enamefromemp
connectbymgr=priorempno
startwithdeptno=30andjob='MANAGER';
2.查询员工SMITH的各个层次领导。
selectlevel,enamefromemp
connectbypriormgr=empno
startwithENAME='SMITH';
3.查询显示EMP表各雇员的工作类型,并翻译为中文显示
用decode函数
4.*查询显示雇员进入公司当年是什么属相年(不考虑农历的年份算法)
用decode函数
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的字符。
begin
foriin32..120
loop
dbms_output.put_line(chr(i));
endloop;
end;
3.计算myEMP表中COMM最高与最低的差值,COMM值为空时按0计算。
declare
var1number;
var2number;
val_commnumber;
begin
selectmax(nvl(comm,0))intovar1frommyemp;
selectmin(nvl(comm,0))intovar2frommyemp;
val_comm:
=var1-var2;
dbms_output.put_line(val_comm);
end;
4.根据表myEMP中deptno字段的值,为姓名为‘JONES’的雇员修改工资;若部门号为10,则工资加100;部门号为20,加200;其他部门加400。
declare
c1number;
c2number;
begin
selectdeptnointoc1fromempwhereename=’JONES’;
ifc1=10then
c2:
=100;
elsifc1=20then
c2:
=200;
elsec2:
=400;
endif;
updateempsetsal=sal+c2whereename=’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_xxnumber:
=0;
xxnumber;
begin
--计算收入为1000-2000的所得税总额
selectsum((sal-1000)*0.1)intoxxfromempwheresal>1000andsal<=2000;
sum_xx:
=sum_xx+xx;
--计算收入为2000-3000的所得税总额
selectsum((sal-2000)*0.2+100)intoxxfromempwheresal>2000andsal<=3000;
sum_xx:
=sum_xx+xx;
--计算收入为3000-4000的所得税总额
selectsum((sal-3000)*0.3+300)intoxxfromempwheresal>3000andsal<=4000;
sum_xx:
=sum_xx+xx;
--计算收入为4000以上的所得税总额
selectsum((sal-4000)*0.4+600)intoxxfromempwheresal>4000;
sum_xx:
=sum_xx+xx;
dbms_output.put_line(sum_xx);
end;
7.*(可选做,难题)假设有个表如myEMP,未建立主键,含有多条记录重复(列值完全相同),试编制一个PL/SQL,将多余的重复记录删除。
实验六、
1.用外部变量,实现两个PL/SQL程序间的数据交换。
SQL>variablea1number;
SQL>begin
2:
a1:
=1000;
3end;
4/
PL/SQL过程已成功完成。
SQL>begin
2dbms_output.put_line(:
a1);
3end;
4/
1000
PL/SQL过程已成功完成。
2.插入myEMP表中的数据记录,考虑可能出现的例外,并提示。
主要的例外提示:
唯一性索引值重复DUP_VAL_ON_INDEX
3.删除myDEPT表中的数据记录一条,考虑例外情况,并提示。
主要的例外提示:
违反完整约束条件
4.将下列PL/SQL改为FOR游标
declare
cursorcur_myempisselect*fromemp;
remp%rowtype;
begin
opencur_myemp;
fetchcur_myempintor;
whilecur_myemp%found
loop
dbms_output.put_line(r.ename);
fetchcur_myempintor;
endloop;
closecur_myemp;
end;
5.工资级别的表salgrade,列出各工资级别的人数。
(用游标来完成)
declare
v1number;
cursorcur1isselect*fromsalgrade;
begin
forc1incur1
loop
selectcount(*)intov1fromempwheresalbetweenc1.losalandc1.hisal;
dbms_output.put_line('grade'||c1.grade||''||v1);
endloop;
end;
实验七、
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;
begin
selectenameintox1fromempwhereempno=in_empno;
selectcount(*)intox2fromempwhereempno=in_empnoandemppass=in_emppass;
ifx2=1then
out_code:
=0;
out_desc:
=x1;
else
out_code:
=2;
out_desc:
=’用户登陆密码错误!
’;
endif;
exception
whenNO_DATA_FOUNDthen
out_code:
=1;
out_desc:
=’该用户号存在!
’;
whenTOO_MANY_ROWSthen
out_code:
=3;
out_desc:
=’该用户号有重复值!
’;
whenothersthen
out_code:
=100;
out_desc:
=’其他错误!
’;
end;
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:
=0;
OUT_DESC:
=’修改口令成功’;
ELSE
OUT_CODE:
=1;
OUT_DESC:
=’修改口令不成功’;
ENDIF;
exception
whenothersthen
out_code:
=100;
out_desc:
=’其他错误’;
END;
4.建立一个函数,输入一个雇员号,返回该雇员的所在同一部门的最高级别上司姓名。
createorreplacefunctionf_leader(
in_empnoinemp.empno%type)returnvarchar2
is
v1number;
v2number;
v3emp.ename%type;
v4emp.deptno%type;
begin
v1:
=in_empno;
v3:
='未找到';
selectdeptnointov4fromempwhereempno=v1;
loop
selectmgrintov2fromempwhereempno=v1;
selectenameintov3fromempwhereempno=v2anddeptno=v4;
v1:
=v2;
endloop;
exception
whenothersthen
returnv3;
end;
5.试用上题函数,实现各雇员的同一部门最高级别上司的Select查询。
selectf_leader(7521)fromdual;
6.*编写实验五中第六题,关于各雇员工资的所得税计算函数
实验八、
1.建立一个触发器,当myEMP表中部门号存在时,该部门不允许删除。
createorreplacetriggerdept_line_delete
beforedeleteondeptforeachrow
declare
v1number;
begin
selectcount(*)intov1fromempwheredeptno=:
old.deptno;
ifv1>=1thenRAISE_APPLICATION_ERROR(-20000,’错误’);
endif;
end;
实验九、
1.建立一个示例包emp_mgmt中,新增一个修改雇员所在部门的过程。
createorreplacepackageemp_mgmtas
procedurechange_dept(
in_newdeptinemp.deptno%type,
out_codeoutnumber,
out_descoutvarchar2);
mgmt_empnoemp.empno%type;
proceduremgmt_login(
in_empnoinemp.empno%type,
in_emppassinemp.emppass%type,
out_codeoutnumber,
out_descoutvarchar2);
e
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle sql 练习题及答案 练习题 答案