Oracle习题答案(课后题)

blogs.eom/zhenyulu/articles/3

30494.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)^SNO9jNO='Jl'(SPJ))

(2)ANO(bjNO二、ji、八PNO='PI、(SPJ))

兀SNO9JNO=，J1，八COLOR。!^(P><SPJ))

(3)

(4)

兀JNO(')一兀JNO(。5丫=天津'(S)><SPJ><分。乙。7?=‘红色’(0))

(5)兀PNO,JNO(SPJ)+兀PNO9SNO='S1,(SPJ))

第二早

22.建立一个关于系、学生、班级、学会等诸信息的关系数据库。

学生：学号、姓名、出生年月、系名、班号、宿舍区。

班级：班号、专业名、系名、人数、入校年份。

系：系名、系号、系办公地点、人数。

学会：学会名、成立年份、办公地点、人数。

语义如下：一个系有若干专业，每个专业每年只招一个班，

每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可

参加若干学会，每个学会有若干学生。学生参加某学会有一个入

会年份。

请给出关系模式，写出每个关系模式的极小函数依赖集，指

出是否存在传递函数依赖，对于函数依赖左部是多属性的情况讨

论函数依赖是完全函数依赖，还是部分函数依赖。指出各关系模

式的候选码、外部码，有没有全码存在？

解：(1)关系模式如下：

学生：S(Sno,Sname,Sbirth,Dept,Class,Sloe)

班级：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,Sloe)的

最小函数依赖集如下:Sno—Sname,Sno^Sbirth,Sno^Class,

Class—Dept,Dept—Sloe

传递依赖如下：

由于Sno-Class,ClassVSno,Class^Dept

所以Sno与Dept之间存在着传递函数依赖。

由于Sno-Dept,Dept，Sno,Dept—Sloc

所以Sno与Sloe之间存在着传递函数依赖。

由于Class^Dept,Dept，Class,Dept-Sloe

所以Class与Sloe之间存在着传递函数依赖。

(b)、班级C(Class,Major,Dept,Cnum,Cyear)的最小函

数依赖集如下：

Class—Major,Class^Cnum,ClassfCyear,Major

—Dept,

(Major,Cyear)—Class

由于Class-Major,Major'Class,Major^Dept

所以Class与Dept之间存在着传递函数依赖。

(c)、系D(Dept,Dno,Office,Dnum)的最小函数依赖集如下:

Dept—Dno,DnolDept,Dno^Office,Dno^Dnum

根据上述函数依赖可知，Dept与Office,Dept与Dnum之

间不存在传递依赖。

(d)、学会P(Pname,Pyear,Paddr,Pnum)的最小函数依赖

集如下：

Pname^Pyear,Pname^Paddr,Pname^Pnum

该模式不存在传递依赖。

(e)、学生-学会SP的最小函数依赖集如下：

(Sno,Pname)fSPyear

(3)各关系模式的候选码、外部码，全码如下：

A、学生S候选码：Sno；外部码：Dept、Class；无全码

B、班级C候选码:Class和(Major,Cyear)；外部码:Dept；

无全码

C、系D候选码：Dept或Dn。；无外部码；无全码

D、学会P候选码：Pname；无外部码；无全码

学生一学会SP候选码：(Sno,Pname)外部码：Sno,Pname；

无全码

23.试由amstrong公理系统推导出下面三条推理规则：

(1)合并规则：若X-乙X-Y,则有X-YZ

(2)伪传递规则：由X-Y,WY-Z有XW-Z

(3)分解规则：X-Y,Z包含于Y,有X-Z

证明：

(1)已知X-乙由增广律知XY-Y乙又因为X-Y,可得XX

一XY-YZ,最后根据传递律得X-YZ。

(2)已知X-Y,据增广律得XW-WY,因为WY-乙所以

XW-WY-乙通过传递律可知XW-Z。

(3)已知Z包含于丫，根据自反律知Y-Z,又因为X-Y,

所以由传递律可得X-Z。

第四章

5.请从表EMP中查找工资低于2000的雇员的姓名、工作、工

资，并按工资降序排列。

selectename,job,salfromempwheresal<2000orderbysal

desc;

6.请从表中查询工作是CLERK的所有人的姓名、工资、部门

号、部门名称以及部门地址的信息。

selectename,sal,emp.deptno,dname,locfromemp.deptwhere

emp.deptno=dept.deptnoandjob='CLERK';

7.查询表EMP中所有的工资大于等于2000的雇员姓名和他的

经理的名字。

selecta.ename,b.enamefromempa,empbwhere

a.mgr=b.empnoanda.sal>=2000;

8.在表EMP中查询所有工资高于JONES的所有雇员姓名、工

作和工资。

selectename,job,salfromempwheresal>(selectsalfromemp

whereename=,JONES,);

9.列出没有对应部门表信息的所有雇员的姓名、工作以及部门

号。

selectename,job,deptnofromempwheredeptnonotin(select

deptnofromdept);

10.查找工资在1000〜3000之间的雇员所在部门的所有人员信

息

select*fromempwheredeptnoin(selectdistinctdeptnofrom

empwheresalbetween1000and3000);

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

selectenamefromempwhere

hiredate<=add_months(sysdate,-288);

12.查询显示每个雇员加入公司的准确时间，按XXXX年XX月XX

日时分秒显示。

selectename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss')from

emp;

13.查询所有81年7月1日以前来的员工姓名、工资、所属部

口的名字

selectename,sal,dnamefromemp,deptwhere

emp.deptno=dept.deptnoand

hiredate<=to_date(l1981-07-01'/yvyy-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=(selectdeptno

from(selectdeptnofromempgroupbydeptnoorderby

avg(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

WHERElastnameLIKE'J%'

ORlastnameLIKE'M%'

ORlast_nameLIKE'A%'

ORDERBYlast_name;

7.对每一个雇员，显示其last_name,并计算从雇员受雇日期到今

天的月数，列标签M0NTHS_W0RKEDo按受雇月数排序结果,

四舍五入月数到最靠近的整数月。

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-

LAST_NAMEHIRE_DATEDAY

Grant24-MAY-99MONDAY

Ernst21-MAY-91TUESDAY

Mourgos16-NOV-99TUESDAY

Taylor24-MAR-98TUESDAY

Rajs17-OCT-95TUESDAY

Gietz07-JUN-94TUESDAY

Higgins07-JUN-94TUESDAY

King17-JUN-87WEDNESDAY

DeHaan13-JAN-93WEDNESDAY

Davies29-JAN-97WEDNESDAY

Hunold03-JAN-90WEDNESDAY

Kochhar21-SEP-89THURSDAY

Whalen17-SEP-87THURSDAY

Vargas09-JUL-98THURSDAY

Matos15-MAR-98SUNDAY

第八章

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)avgsalfromempGROUP

BYdeptno)s,deptd

WHEREe.deptno=s.deptnoANDs.deptno=d.deptnoAND

e.sal>s.avgsal

第九章

10、

declare

typeemp_table_typeistableofemp%rowtype;

emp_tableemp_tab1e_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|I'II

emp_table(i).ename|I'IIemp_table(i).hiredate

Ill'llemp_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*(l+v_addsal)

whereempno=&empno;

selectsalintov_salaryfromemp

whereempno=&empno;

dbms_output.put_line「增长后工资：*||v_salary);

end;

12、创建表：

createtabletest(

columnlnumber(2),

column2char(2),

columnsdate

)；

简单循环：

declare

inumber:=0;

begin

loop

insertintotest

values(1+iAchr(97+i),sysdate+i);

i:=i+l;

exitwheni=10;

endloop;

commit;

end;

WHILE循环：

declare

inumber:=0;

begin

whilei<10loop

insertintotest

values(1+izchr(97+i),sysdate+i);

i:=i+l;

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,pename

emp.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,pename

emp.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;

/

--执行包中的过程和函数

execute

emppack.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)编写一个函数以检查所指定雇员的薪水是否有效范围内。不

同职位的薪水范围为：

designationraise

clerk1500-2500

salesma