Oracle习题答案课后题.docx
《Oracle习题答案课后题.docx》由会员分享，可在线阅读，更多相关《Oracle习题答案课后题.docx（13页珍藏版）》请在冰豆网上搜索。
Oracle习题答案课后题
/articles/330494.html第一章
略
第二章
5、已知一个关系数据库的模式如下：
S（SNO,SNAME,SCITY）
P（PNO,PNAME,COLOR,WEIGHT）
J（JNO,JNAME,JCITY）
SPJ（SNO,PNO,JNO,QTY）
供应商S由供应商代码SNO、供应商姓名SNAME、供应商所在城市SCITY组成；零件P由零件代码PNO、零件名PNAME、颜色COLOR、重量WEIGHT组成；工程项目J由工程项目代码JNO、工程项目名JNAME、和所在城市JCITY组成；供应情况SPJ由供应商代码SNO、零件代码PNO、工程项目代码JNO、供应数量QTY组成。
用关系代数表达式表示下面的查询要求：
（1）求供应工程J1零件的供应商代码SNO
（2）求供应工程J1零件P1的供应上代码SNO
（3）求供应工程J1零件为红色的供应商代码SNO
（4）求没有使用天津供应商生产的红色零件的工程项目代码JNO
（5）求至少用了供应商S1所供应的全部零件的工程项目代码JNO
答：
（1）
（2）
（3）
（4）
（5）
第三章
22．建立一个关于系、学生、班级、学会等诸信息的关系数据库。
学生：
学号、姓名、出生年月、系名、班号、宿舍区。
班级：
班号、专业名、系名、人数、入校年份。
系：
系名、系号、系办公地点、人数。
学会：
学会名、成立年份、办公地点、人数。
语义如下：
一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。
一个系的学生住在同一宿舍区。
每个学生可参加若干学会，每个学会有若干学生。
学生参加某学会有一个入会年份。
请给出关系模式，写出每个关系模式的极小函数依赖集，指出是否存在传递函数依赖，对于函数依赖左部是多属性的情况讨论函数依赖是完全函数依赖，还是部分函数依赖。
指出各关系模式的候选码、外部码，有没有全码存在?
解：
（1）关系模式如下：
学生：
S（Sno，Sname，Sbirth，Dept，Class，Sloc）
班级：
C（Class，Major，Dept，Cnum，Cyear）
系：
D（Dept，Dno，Office，Dnum）
学会：
P（Pname，Pyear，Paddr，Pnum）
学生--学会SP（Sno，Pname，SPyear）
（2）每个关系模式的最小函数依赖集如下：
（a）、学生S（Sno，Sname，Sbirth，Dept，Class，Sloc）的最小函数依赖集如下:
Sno→Sname，Sno→Sbirth，Sno→Class，Class→Dept，Dept→Sloc
传递依赖如下：
由于Sno→Class，Class→Sno，Class→Dept
所以Sno与Dept之间存在着传递函数依赖。
由于Sno→Dept，Dept→Sno，Dept→Sloc
所以Sno与Sloc之间存在着传递函数依赖。
由于Class→Dept，Dept→Class，Dept→Sloc
所以Class与Sloc之间存在着传递函数依赖。
（b）、班级C（Class，Major，Dept，Cnum，Cyear）的最小函数依赖集如下:
Class→Major，Class→Cnum，Class→Cyear，Major→Dept,
（Major,Cyear）→Class
由于Class→Major，Major→Class，Major→Dept
所以Class与Dept之间存在着传递函数依赖。
（c）、系D（Dept，Dno，Office，Dnum）的最小函数依赖集如下：
Dept→Dno，Dno→Dept，Dno→Office，Dno→Dnum
根据上述函数依赖可知，Dept与Office，Dept与Dnum之间不存在传递依赖。
（d）、学会P（Pname，Pyear，Paddr，Pnum）的最小函数依赖集如下：
Pname→Pyear，Pname→Paddr，Pname→Pnum
该模式不存在传递依赖。
（e）、学生--学会SP的最小函数依赖集如下：
（Sno，Pname）→SPyear
（3）各关系模式的候选码、外部码，全码如下：
A、学生S候选码：
Sno；外部码：
Dept、Class；无全码
B、班级C候选码：
Class和（Major,Cyear）；外部码：
Dept；无全码
C、系D候选码：
Dept或Dno；无外部码；无全码
D、学会P候选码：
Pname；无外部码；无全码
学生--学会SP候选码：
（Sno，Pname）外部码：
Sno，Pname；无全码
23．试由amstrong公理系统推导出下面三条推理规则：
（1）合并规则：
若X→Z，X→Y，则有X→YZ
（2）伪传递规则：
由X→Y，WY→Z有XW→Z
（3）分解规则：
X→Y，Z包含于Y，有X→Z
证明:
（1）已知X→Z，由增广律知XY→YZ，又因为X→Y，可得XX→XY→YZ，最后根据传递律得X→YZ。
（2）已知X→Y，据增广律得XW→WY，因为WY→Z，所以XW→WY→Z，通过传递律可知XW→Z。
（3）已知Z包含于Y，根据自反律知Y→Z，又因为X→Y，所以由传递律可得X→Z。
第四章
5．请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。
selectename,job,salfromempwheresal<2000orderbysaldesc;
6．请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。
selectename,sal,emp.deptno,dname,locfromemp,deptwhereemp.deptno=dept.deptnoandjob=’CLERK’;
7．查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。
selecta.ename,b.enamefromempa,empbwherea.mgr=b.empnoanda.sal>=2000;
8．在表EMP中查询所有工资高于JONES的所有雇员姓名、工作和工资。
selectename,job,salfromempwheresal>（selectsalfromempwhereename=’JONES’）;
9．列出没有对应部门表信息的所有雇员的姓名、工作以及部门号。
selectename,job,deptnofromempwheredeptnonotin（selectdeptnofromdept）;
10．查找工资在1000～3000之间的雇员所在部门的所有人员信息
select*fromempwheredeptnoin（selectdistinctdeptnofromempwheresalbetween1000and3000）;
11．查询列出来公司就职时间超过24年的员工名单
selectenamefromempwherehiredate<=add_months（sysdate,-288）;
12．查询显示每个雇员加入公司的准确时间，按××××年××月××日时分秒显示。
selectename,to_char（hiredate,'yyyy-mm-ddhh24:
mi:
ss'）fromemp;
13．查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字
selectename,sal,dnamefromemp,deptwhereemp.deptno=dept.deptnoandhiredate<=to_date（‘1981-07-01’,’yyyy-mm-dd’）;
14．查询公司中按年份月份统计各地的录用职工数量
selectto_char（hiredate,'yyyy-mm'）,loc,count（*）fromemp,dept
whereemp.deptno=dept.deptno
groupbyto_char（hiredate,'yyyy-mm'）,loc;
15．查询部门平均工资最高的部门名称和最低的部门名称
selectdnamefromdeptwheredeptno=（selectdeptnofrom（selectdeptnofromempgroupbydeptnoorderbyavg（sal））whererownum<=1）
unionallselectdnamefromdeptwheredeptno=（selectdeptnofrom（selectdeptnofromempgroupbydeptnoorderbyavg（sal）desc）whererownum<=1）;
第五章
5.对每一个雇员，显示 employee_id、last_name、salary 和 salary 增加 15%，并且表示成整数，列标签显示为 New_Salary
SELECTemployee_id,last_name,salary,
ROUND（salary*1.15,0）"NewSalary"
FROMemployees;
6.写一个查询用首字母大写，其它字母小写显示雇员的last_names，显示名字的长度，对所有名字开始字母是J、A或M的雇员，给每列一个适当的标签。
用雇员的last_names排序结果。
SELECTINITCAP（last_name）"Name",
LENGTH（last_name）"Length"
FROMemployees
WHERElast_nameLIKE'J%'
ORlast_nameLIKE'M%'
ORlast_nameLIKE'A%'
ORDERBYlast_name;
7.对每一个雇员，显示其last_name，并计算从雇员受雇日期到今天的月数，列标签MONTHS_WORKED。
按受雇月数排序结果，四舍五入月数到最靠近的整数月。
SELECTlast_name,ROUND（MONTHS_BETWEEN
（SYSDATE,hire_date））"MONTHS_WORKED"
FROMemployees
ORDERBYMONTHS_BETWEEN（SYSDATE,hire_date）;
8.显示last_name、hire_date和雇员开始工作的周日，列标签为DAY,以星期一作为周的起始日排序结果。
SELECTlast_name,hire_date,
TO_CHAR（hire_date,'DAY'）"DAY"
FROMemployees
ORDERBYTO_CHAR（hire_date-1,'d'）;
第八章
5.
CREATEVIEWVW_DEPT_SUM（DEPT_NAME,EMP_COUNT,MINSAL,MAXSAL,AVGSAL）
AS
SELECTdname,count（empno）,min（sal）,max（sal）,avg（sal）
FROMdeptdleftjoinempeone.deptno=d.deptno
GROUPBYdname
6.
CREATEVIEWVW_EMP_AVG
AS
SELECTe.ename,e.sal,d.dname,s.avgsal
FROMempe,（selectdeptno,AVG（sal）avgsalfromempGROUPBYdeptno）s,deptd
WHEREe.deptno=s.deptnoANDs.deptno=d.deptnoANDe.sal>s.avgsal
第九章
10、
declare
typeemp_table_typeistableofemp%rowtype;
emp_tableemp_table_type;
begin
selecte.*bulkcollectintoemp_table
fromempe,deptd
wheree.deptno=d.deptnoandd.dname='&dname';
foriin1..emp_table.countloop
dbms_output.put_line（emp_table（i）.empno||','||emp_table（i）.ename||','||emp_table（i）.hiredate||','||emp_table（i）.deptno）;
ENDLOOP;
end;
11、
declare
v_salaryemp.sal%type;
v_addsalnumber;
begin
selectsalintov_salaryfromemp
whereempno=&empno;
dbms_output.put_line（'原工资：
'||v_salary）;
ifv_salary>10000then
v_addsal:
=0.1;
elsifv_salary>5000then
v_addsal:
=0.2;
else
v_addsal:
=0.3;
endif;
updateemp
setsal=sal*（1+v_addsal）
whereempno=&empno;
selectsalintov_salaryfromemp
whereempno=&empno;
dbms_output.put_line（'增长后工资：
'||v_salary）;
end;
12、创建表：
createtabletest（
column1number
（2）,
column2char
（2）,
column3date
）;
简单循环：
declare
inumber:
=0;
begin
loop
insertintotestvalues（1+i,chr（97+i）,sysdate+i）;
i:
=i+1;
exitwheni=10;
endloop;
commit;
end;
WHILE循环：
declare
inumber:
=0;
begin
whilei<10loop
insertintotestvalues（1+i,chr（97+i）,sysdate+i）;
i:
=i+1;
endloop;
commit;
end;
FOR循环：
declare
inumber:
=0;
begin
foriin0..9loop
insertintotest
values（1+i,chr（97+i）,sysdate+i）;
endloop;
commit;
end;
第十一章
9、编写一个数据包，它有两个函数和两个过程以操作"emp"表。
该数据包要执行的任务为：
a）插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水（薪水+佣金）；显示指定雇员所在部门名称。
createorreplacepackageemppackas
procedureinsrec（pempnoemp.empno%type,penameemp.ename%type,
pjobemp.job%type,pmgremp.mgr%type,
phiredateemp.hiredate%type,psalemp.sal%type,
pcommm%type,pdeptnoemp.deptno%type）;
proceduredelrec（pempnoinnumber）;
functionselsal（pempnonumber）returnnumber;
functionseldname（pempnonumber）returnvarchar2;
end;
/
createorreplacepackagebodyemppackas
procedureinsrec（pempnoemp.empno%type,penameemp.ename%type,
pjobemp.job%type,pmgremp.mgr%type,
phiredateemp.hiredate%type,psalemp.sal%type,
pcommm%type,pdeptnoemp.deptno%type）
is
begin
insertintoempvalues（pempno,pename,pjob,pmgr,phiredate,
psal,pcomm,pdeptno）;
dbms_output.put_line（1recordiscreated.）;
endinsrec;
proceduredelrec（pempnoinnumber）
is
begin
deletefromempwhereempno=pempno;
dbms_output.put_line（1recordisdeleted.）;
enddelrec;
functionselsal（pempnonumber）returnnumber
is
vtotalsalnumber;
begin
selectnvl（sal,0）+nvl（comm,0）intovtotalsal
fromemp
whereempno=pempno;
returnvtotalsal;
endselsal;
functionseldname（pempnonumber）returnvarchar2
is
vdnamedept.dname%type;
begin
selectdnameintovdname
fromemp,dept
whereempno=pempnoandemp.deptno=dept.deptno;
returnvdname;
endseldname;
end;
/
--执行包中的过程和函数
executeemppack.insrec（1111,goldens,manager,7698,2003-01-18,2000,400,30）;
executeemppack.delrec（1111）;
declare
salarynumber;
begin
salary:
=emppack.selsal（7369）;
dbms_output.put_line（totalsalaryis||salary）;
end;
/
declare
departmentvarchar2（30）;
begin
department:
=emppack.seldname（7369）;
dbms_output.put_line（departmentnameis||department）;
end;
/
b）编写一个函数以检查所指定雇员的薪水是否有效范围内。
不同职位的薪水范围为：
designation raise
clerk 1500-2500
salesman 2501-3500
analyst 3501-4500
others 4501andabove.
如果薪水在此范围内，则显示消息"salaryisok"，否则，更新薪水为该范围内的最水值。
createorreplacefunctionsal_level（noemp.empno%type）returncharas
vjobemp.job%type;
vsalemp.sal%type;
vmesgchar（50）;
begin
selectjob,salintovjob,vsalfromempwhereempno=no;
ifvjob=clerkthen
ifvsal>=1500andvsal<=2500then
vmesg:
=salaryisok.;
else
vsal:
=1500;
vmesg:
=haveupdatedyoursalaryto||to_char（vsal）;
endif;
elsifvjob=salesmanthen
ifvsal>=2501andvsal<=3500then
vmesg:
=salaryisok.;
else
vsal:
=2501;
vmesg:
=haveupdatedyoursalaryto||to_char（vsal）;
endif;
elsifvjob=analystthen
ifvsal>=3501andvsal<=4500then
vmesg:
=salaryisok.;
else
vsal:
=3501;
vmesg:
=haveupdatedyoursalaryto||to_char（vsal）;
endif;
else
ifvsal>=4501then
vmesg:
=salaryisok.;
else
vsal:
=4501;
vmesg:
=haveupdatedyoursalaryto||to_char（vsal）;
endif;
endif;
updateempsetsal=vsalwhereempno=no;
returnvmesg;
end;
/
declare
vmesgchar（50）;
vempnoemp.empno%type;
begin
vempno:
=&empno;
vmesg:
=sal_level（vempno）;
dbms_output.put_line（vmesg）;
end;
/
--selectempno,ename,sal,comm,hiredatefromempwhereempno=:
no;
升级会员 