Oracle習(xí)題答案(課后題)

blogs.eom/zhenyulu/articles/3

30494.html第一章

略

第二章

5、已知個(gè)關(guān)系數(shù)據(jù)庫的模式如下：

S(SNO,SNAME,SCITY)

P(PNO,PNAME,COLOR,WEIGHT)

J(JNO,JNAME,JCITY)

SPJ(SNO,PNO,JNO,QTY)

供應(yīng)商S由供應(yīng)商代碼SNO、供應(yīng)商姓名SNAME、供應(yīng)商所在

城市SCITY組成；零件P由零件代碼PNO、零件名PNAME、

顏色COLOR、重量WEIGHT組成；工程項(xiàng)目J由工程項(xiàng)目代碼

JNO、工程項(xiàng)目名JNAME、和所在城市JCITY組成；供應(yīng)情況

SPJ由供應(yīng)商代碼SNO、零件代碼PNO、工程項(xiàng)目代碼JNO、

供應(yīng)數(shù)量QTY組成。

用關(guān)系代數(shù)表達(dá)式表示下面的查詢要求：

(1)求供應(yīng)工程J1零件的供應(yīng)商代碼SNO

(2)求供應(yīng)工程J1零件P1的供應(yīng)上代碼SNO

(3)求供應(yīng)工程J1零件為紅色的供應(yīng)商代碼SNO

(4)求沒有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程項(xiàng)目代碼

JNO

(5)求至少用了供應(yīng)商S1所供應(yīng)的全部零件的工程項(xiàng)目代碼

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.建立一個(gè)關(guān)于系、學(xué)生、班級(jí)、學(xué)會(huì)等諸信息的關(guān)系數(shù)據(jù)庫。

學(xué)生：學(xué)號(hào)、姓名、出生年月、系名、班號(hào)、宿舍區(qū)。

班級(jí)：班號(hào)、專業(yè)名、系名、人數(shù)、入校年份。

系：系名、系號(hào)、系辦公地點(diǎn)、人數(shù)。

學(xué)會(huì)：學(xué)會(huì)名、成立年份、辦公地點(diǎn)、人數(shù)。

語義如下：一個(gè)系有若干專業(yè)，每個(gè)專業(yè)每年只招一個(gè)班，

每個(gè)班有若干學(xué)生。一個(gè)系的學(xué)生住在同一宿舍區(qū)。每個(gè)學(xué)生可

參加若干學(xué)會(huì)，每個(gè)學(xué)會(huì)有若干學(xué)生。學(xué)生參加某學(xué)會(huì)有一個(gè)入

會(huì)年份。

請(qǐng)給出關(guān)系模式，寫出每個(gè)關(guān)系模式的極小函數(shù)依賴集，指

出是否存在傳遞函數(shù)依賴，對(duì)于函數(shù)依賴左部是多屬性的情況討

論函數(shù)依賴是完全函數(shù)依賴，還是部分函數(shù)依賴。指出各關(guān)系模

式的候選碼、外部碼，有沒有全碼存在？

解：(1)關(guān)系模式如下：

學(xué)生：S(Sno,Sname,Sbirth,Dept,Class,Sloe)

班級(jí)：C(Class,Major,Dept,Cnum,Cyear)

系：D(Dept,Dno,Office,Dnum)

學(xué)會(huì)：P(Pname,Pyear,Paddr,Pnum)

學(xué)生--學(xué)會(huì)SP(Sno,Pname,SPyear)

(2)每個(gè)關(guān)系模式的最小函數(shù)依賴集如下：

(a)、學(xué)生S(Sno,Sname,Sbirth,Dept,Class,Sloe)的

最小函數(shù)依賴集如下:Sno—Sname,Sno^Sbirth,Sno^Class,

Class—Dept,Dept—Sloe

傳遞依賴如下：

由于Sno-Class,ClassVSno,Class^Dept

所以Sno與Dept之間存在著傳遞函數(shù)依賴。

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

所以Sno與Sloe之間存在著傳遞函數(shù)依賴。

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

所以Class與Sloe之間存在著傳遞函數(shù)依賴。

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

數(shù)依賴集如下：

Class—Major,Class^Cnum,ClassfCyear,Major

—Dept,

(Major,Cyear)—Class

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

所以Class與Dept之間存在著傳遞函數(shù)依賴。

(c)、系D(Dept,Dno,Office,Dnum)的最小函數(shù)依賴集如下:

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

根據(jù)上述函數(shù)依賴可知，Dept與Office,Dept與Dnum之

間不存在傳遞依賴。

(d)、學(xué)會(huì)P(Pname,Pyear,Paddr,Pnum)的最小函數(shù)依賴

集如下：

Pname^Pyear,Pname^Paddr,Pname^Pnum

該模式不存在傳遞依賴。

(e)、學(xué)生-學(xué)會(huì)SP的最小函數(shù)依賴集如下：

(Sno,Pname)fSPyear

(3)各關(guān)系模式的候選碼、外部碼，全碼如下：

A、學(xué)生S候選碼：Sno；外部碼：Dept、Class；無全碼

B、班級(jí)C候選碼:Class和(Major,Cyear)；外部碼:Dept；

無全碼

C、系D候選碼：Dept或Dn。；無外部碼；無全碼

D、學(xué)會(huì)P候選碼：Pname；無外部碼；無全碼

學(xué)生一學(xué)會(huì)SP候選碼：(Sno,Pname)外部碼：Sno,Pname；

無全碼

23.試由amstrong公理系統(tǒng)推導(dǎo)出下面三條推理規(guī)則：

(1)合并規(guī)則：若X-乙X-Y,則有X-YZ

(2)偽傳遞規(guī)則：由X-Y,WY-Z有XW-Z

(3)分解規(guī)則：X-Y,Z包含于Y,有X-Z

證明：

(1)已知X-乙由增廣律知XY-Y乙又因?yàn)閄-Y,可得XX

一XY-YZ,最后根據(jù)傳遞律得X-YZ。

(2)已知X-Y,據(jù)增廣律得XW-WY,因?yàn)閃Y-乙所以

XW-WY-乙通過傳遞律可知XW-Z。

(3)已知Z包含于丫，根據(jù)自反律知Y-Z,又因?yàn)閄-Y,

所以由傳遞律可得X-Z。

第四章

5.請(qǐng)從表EMP中查找工資低于2000的雇員的姓名、工作、工

資，并按工資降序排列。

selectename,job,salfromempwheresal<2000orderbysal

desc;

6.請(qǐng)從表中查詢工作是CLERK的所有人的姓名、工資、部門

號(hào)、部門名稱以及部門地址的信息。

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

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

7.查詢表EMP中所有的工資大于等于2000的雇員姓名和他的

經(jīng)理的名字。

selecta.ename,b.enamefromempa,empbwhere

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

8.在表EMP中查詢所有工資高于JONES的所有雇員姓名、工

作和工資。

selectename,job,salfromempwheresal>(selectsalfromemp

whereename=,JONES,);

9.列出沒有對(duì)應(yīng)部門表信息的所有雇員的姓名、工作以及部門

號(hào)。

selectename,job,deptnofromempwheredeptnonotin(select

deptnofromdept);

10.查找工資在1000?3000之間的雇員所在部門的所有人員信

息

select*fromempwheredeptnoin(selectdistinctdeptnofrom

empwheresalbetween1000and3000);

11.查詢列出來公司就職時(shí)間超過24年的員工名單

selectenamefromempwhere

hiredate<=add_months(sysdate,-288);

12.查詢顯示每個(gè)雇員加入公司的準(zhǔn)確時(shí)間，按XXXX年XX月XX

日時(shí)分秒顯示。

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.查詢公司中按年份月份統(tǒng)計(jì)各地的錄用職工數(shù)量

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.對(duì)每一個(gè)雇員，顯示employee_id、last_name、salary利salary增

加15%,并且表示成整數(shù)，列標(biāo)簽顯示為New_Salary

SELECTemployee_id,last_name,salary,

ROUND(salary*1.15,0)"NewSalary"

FROMemployees;

6.寫一個(gè)查詢用首字母大寫，其它字母小寫顯示雇員的

last_names,顯示名字的長度，對(duì)所有名字開始字母是J、A或M

的雇員，給每列一個(gè)適當(dāng)?shù)臉?biāo)簽。用雇員的last_names排序結(jié)

果。

SELECTINITCAP(last_name)"Name",

LENGTH(last_name)"Length"

FROMemployees

WHERElastnameLIKE'J%'

ORlastnameLIKE'M%'

ORlast_nameLIKE'A%'

ORDERBYlast_name;

7.對(duì)每一個(gè)雇員，顯示其last_name,并計(jì)算從雇員受雇日期到今

天的月數(shù)，列標(biāo)簽M0NTHS_W0RKEDo按受雇月數(shù)排序結(jié)果,

四舍五入月數(shù)到最靠近的整數(shù)月。

SELECTlast_name,ROUND(MONTHS_BETWEEN

(SYSDATE,hire_date))"MONTHS_WORKED"

FROMemployees

ORDERBYMONTHS_BETWEEN(SYSDATE,hire_date);

8.顯示last_name、hire_date和雇員開始工作的周日，列標(biāo)簽為

DAY,以星期一作為周的起始日排序結(jié)果。

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、創(chuàng)建表：

createtabletest(

columnlnumber(2),

column2char(2),

columnsdate

)；

簡單循環(huán)：

declare

inumber:=0;

begin

loop

insertintotest

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

i:=i+l;

exitwheni=10;

endloop;

commit;

end;

WHILE循環(huán)：

declare

inumber:=0;

begin

whilei<10loop

insertintotest

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

i:=i+l;

endloop;

commit;

end;

FOR循環(huán)：

declare

inumber:=0;

begin

foriin0..9loop

insertintotest

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

endloop;

commit;

end;

第十一章

9、編寫一個(gè)數(shù)據(jù)包，它有兩個(gè)函數(shù)和兩個(gè)過程以操作"emp"表。

該數(shù)據(jù)包要執(zhí)行的任務(wù)為：

a)插入一個(gè)新雇員；刪除一個(gè)現(xiàn)有雇員；顯示指定雇員的整體

薪水(薪水+傭金)；顯示指定雇員所在部門名稱。

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;

/

--執(zhí)行包中的過程和函數(shù)

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)編寫一個(gè)函數(shù)以檢查所指定雇員的薪水是否有效范圍內(nèi)。不

同職位的薪水范圍為：

designationraise

clerk1500-2500

salesma