ORACLE試題2011年信息管理與信息系統(tǒng)演示教學(xué)

1、Good is good, but better carries it.精益求精，善益求善。ORACLE試題2011年信息管理與信息系統(tǒng)-ORACLE試題（50分）一、判斷題（9分）（每小題1分）1SGA是針對(duì)某一服務(wù)器進(jìn)程而保留的內(nèi)存區(qū)域，它是不可以共享的。（對(duì)）2用戶進(jìn)程可以直接操縱數(shù)據(jù)庫。（錯(cuò)）3Orderby子句僅對(duì)檢索數(shù)據(jù)的顯示有影響，并不改變表中行的內(nèi)部順序。（對(duì)）4從一個(gè)表中刪除元組時(shí)，delete和droptable命令可以起到同樣的效果。（對(duì)）5%rowtype用來保證數(shù)據(jù)類型和變量的長度與數(shù)據(jù)庫中的數(shù)據(jù)列相一致。（錯(cuò)）6與主鍵不同，惟一鍵可以是null值。（錯(cuò)）7Delet

2、e可以有選擇地刪除記錄，使用rollback語句可以取消這個(gè)命令的執(zhí)行。（對(duì)）8Createtableasselect能夠創(chuàng)建一個(gè)復(fù)制表對(duì)象。（對(duì)）9使用having子句是為了限制groupby子句返回的行的個(gè)數(shù)。（對(duì)）一、填空（4分）（每小題1分）（1）數(shù)據(jù)庫系統(tǒng)由數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)、數(shù)據(jù)庫管理員、用戶和應(yīng)用程序組成。（2）Oracle10g產(chǎn)品數(shù)據(jù)庫組件的安裝盤、CompanionCD、客戶端、CRS和Gateway等組成。（3）數(shù)據(jù)庫的啟動(dòng)方式有：startupnomount非安裝啟動(dòng)、startupmountdbname安裝啟動(dòng)、startuprestrict約束方式啟動(dòng)、star

3、tupforce強(qiáng)制啟動(dòng)方式、startuppfile=參數(shù)文件名。（4）數(shù)據(jù)庫的關(guān)閉模式有：shutdownnorma正常方式關(guān)閉數(shù)據(jù)庫、shutdownabort直接關(guān)閉數(shù)據(jù)庫、shutdownimmediate立即方式關(guān)閉數(shù)據(jù)庫。二、簡(jiǎn)答題（7分）（每小題1分）（1）如何查看已定義表的方案、表空間、存儲(chǔ)參數(shù)、塊空間管理參數(shù)等信息？selectschemefromtable_nameanalyzetableempcomputestatisticsselecttable_name,num_rows,avg_row_len,num_rows*avg_row_lenfromuser_tables

4、wheretable_name=selecttable_name,initial_extent,next_extent,min_extents,max_extents,pct_increasefromuser_tables;（2）ORACLE中數(shù)據(jù)類型CHAR、VARCHAR、VARCHAR2有什么不同？答：char(n)長度為n的定長字符串。varchar(b)最大長度為n的變長字符串。varchar2(n)變長字符型數(shù)據(jù)類型，最小長度為一個(gè)字節(jié)，最大長度為4000字節(jié)，應(yīng)用時(shí)必須指定它的長度。區(qū)別有以下三點(diǎn)：1CHAR的長度是固定的，而VARCHAR2的長度是可以變化的，比如，存儲(chǔ)字符串“

5、abc，對(duì)于CHAR(20)，表示你存儲(chǔ)的字符將占20個(gè)字節(jié)(包括17個(gè)空字符)，而同樣的VARCHAR2(20)則只占用3個(gè)字節(jié)的長度，20只是最大值，當(dāng)你存儲(chǔ)的字符小于20時(shí)，按實(shí)際長度存儲(chǔ)。2CHAR的效率比VARCHAR2的效率稍高。3目前VARCHAR是VARCHAR2的同義詞。工業(yè)標(biāo)準(zhǔn)的VARCHAR類型可以存儲(chǔ)空字符串，但是oracle不這樣做，盡管它保留以后這樣做的權(quán)利。Oracle自己開發(fā)了一個(gè)數(shù)據(jù)類型VARCHAR2，這個(gè)類型不是一個(gè)標(biāo)準(zhǔn)的VARCHAR，它將在數(shù)據(jù)庫中varchar列可以存儲(chǔ)空字符串的特性改為存儲(chǔ)NULL值。如果你想有向后兼容的能力，Oracle建議使用V

6、ARCHAR2而不是VARCHAR。（3）什么是ROWID？有哪幾類？如何使用？答：ROWID是數(shù)據(jù)庫的一個(gè)偽列，建立表的時(shí)候數(shù)據(jù)庫會(huì)自動(dòng)為每個(gè)表建立ROWID列,用來唯一標(biāo)識(shí)一行記錄。Oracle的ROWID分為兩種：物理ROWID和邏輯ROWID。它主要記錄表內(nèi)各行的物理地址，以便能夠快速定位并用來唯一標(biāo)識(shí)數(shù)據(jù)行。Oracle的ROWID用來唯一標(biāo)識(shí)表中的一條記錄，是這條數(shù)據(jù)在數(shù)據(jù)庫中存放的物理地址。索引組織表使用邏輯ROWID，其他類型的表使用物理ROWID。（4）截?cái)啾砼c刪除表有何區(qū)別？答：表刪除包括表的定義和關(guān)聯(lián)對(duì)象（規(guī)則、索引、約、觸發(fā)器、主鍵，等）截?cái)啾砻顒t僅僅刪除了表中所有的

7、數(shù)據(jù)行。表的結(jié)構(gòu)和所有的索引仍然繼續(xù)存在，直到你輸入刪除表的命令。（5）對(duì)SQL語句進(jìn)行優(yōu)化的常用策略有那些？答：1、盡可能地減少多表查詢2、以不相關(guān)子查詢替代相關(guān)子查詢3、只檢索需要的列4、用帶IN的條件子句等價(jià)替換OR子句5、根據(jù)表出現(xiàn)在FROM中的順序,ORDERED使ORACLE依此順序?qū)ζ溥B接.6、將指定表與嵌套的連接的行源進(jìn)行連接,并把指定表作為內(nèi)部表.（6）當(dāng)元組含有空值時(shí)？空值是否參與聚集操作？答:空(NULL)值表示數(shù)值未知。空值不同于空白或零值。沒有兩個(gè)相等的空值。比較兩個(gè)空值或?qū)⒖罩蹬c任何其它數(shù)值相比均返回未知，這是因?yàn)槊總€(gè)空值均為未知?？罩低ǔ１硎疚粗⒉豢捎没?qū)⒃谝院?/p>

8、添加的數(shù)據(jù)。組函數(shù)忽略空值。（7）在什么情況下使用層次查詢？怎樣將表中存儲(chǔ)的具有層次關(guān)系的數(shù)據(jù)，以層次的結(jié)構(gòu)方式查詢出來?答：當(dāng)數(shù)據(jù)之間的關(guān)系是層次關(guān)系，如有零部件組成的汽車結(jié)構(gòu)和家族圖譜用oracle10g可以很方便的查詢各種層次關(guān)系。層次查詢的語法如下：ELECTLEVEL,column,expr.FROMtableWHEREconditionSTARTWITHconditionCONNECTBYPRIORcolumn1=column2|column1PRIORcolumn2;其中偽列LEVEL用于返回層次結(jié)構(gòu)的層次（1：根行；2：第二級(jí)行；3：第三級(jí)行）；STARTWITH子句用于指定層

9、次查詢的根行；CONNECTBY子句用于執(zhí)行父行和子行之間的關(guān)系，當(dāng)定義父行和子行的關(guān)系時(shí)，必須使用PRIOR關(guān)鍵字，并且column1和column2對(duì)應(yīng)于父鍵列或者子鍵列。當(dāng)使用層次查詢顯示層次結(jié)構(gòu)的表行數(shù)據(jù)時(shí)，可以采用從頂向下或者從底向上兩種方式顯示數(shù)據(jù)。三、操作題（寫出相關(guān)操作的SQL語句）（20分）（每小題5分）1.表的創(chuàng)建學(xué)生成績(jī)管理項(xiàng)目中每個(gè)關(guān)系的屬性可作以下定義：部門信息DEPT部門編號(hào)：varchar2(8)PK部門名稱：varchar2(40)部門經(jīng)理：varchar2(8)地址：varchar2(80)部門人數(shù)：number答：Createtabledept(Dept_i

10、dvarchar2(8)primarykey,Dept_namevarchar2(40),Dept_mangervarchar2(8),Dept_adsvarchar2(80),Dept_numnumber(5);學(xué)生基本信息STUDENT部門編號(hào)：varchar2(8)FK學(xué)號(hào)：varchar2(12)PK姓名：varchar2(20)notnull性別：varchar2(1)orchar學(xué)生類型：varchar2(1)orchar入學(xué)日期：date籍貫：varchar2(20)身份證號(hào)：varchar2(18)詳細(xì)地址：varchar2(60)出生日期：date所學(xué)專業(yè)：varchar2(

11、20)民族：varchar2(20)電話：varchar2(20)繳費(fèi)總計(jì)：number備注：varchar2(80)答：Createtablestudent(Dept_idvarchar2(8),Foreignkey(dept_id)referencesdept(dept_id),Stu_numvarchar2(12)primarykey,Stu_namevarchar2(20)notnull,Stu_gendervarchar2(1),Stu_typevarchar2(1),Stu_startdatedate,Stu_nativevarchar2(20),Stu_idvarchar2(18

12、),Stu_adsvarchar2(60),Stu_borndate,Stu_majorvarchar2(20),Stu_nationvarchar2(20),Stu_telvarchar2(20),Stu_costnumber(5),Stu_psvarchar2(80);課程信息COURSE課程號(hào)：varchar2(8)PK課程名稱：varchar2(40)notnull開設(shè)年級(jí)：varchar2(8)學(xué)時(shí)數(shù)：number答：Createtablecourse(Course_idvarchar2(8)primarykey,Course_namevarchar2(40)notnull,Cour

13、se_classvarchar2(8),Course_timenumber(5);成績(jī)單REPORT_CARD學(xué)號(hào)：varchar2(12)FK課程號(hào)：varchar2(8)FK學(xué)期：varchar2(8)成績(jī)：number(5,2)備注：varchar2(80)答：Createtablereport_card(Stu_numvarchar2(12),Foreignkey(stu_num)referencesstudent(stu_num),Course_idvarchar2(8),foreignkey(course_id)referencescourse(course_id),Termvar

14、char2(8),Marknumber(5,2),Card_psvarchar2(80);(學(xué)號(hào)，課程號(hào))為PK寫出完成下列操作的正確的SQL語句：（1）創(chuàng)建學(xué)生成績(jī)管理項(xiàng)目中四個(gè)基表：部門信息表dept、學(xué)生基本信息表student、課程信息表courses和成績(jī)單表report_card。（2）修改courses表修改表名為courses1Altertablecourserenametocourses1增加字段pcourses_no類型varchar2，長度為10Insertintocourses1Values(pcourses_no,varchar2,10)修改字段pcourses_no

15、，長度為8Altertablecourses1modifypcourses_novarchar2(8)刪除字段pcourses_noAltertablecourse1droppcourses_no（3）刪除表STUDENTDroptablestudent2.表記錄的操作在DEPT表中插入數(shù)據(jù)（寫出SQL語句）101,信息工程系,null,校3號(hào)教學(xué)樓,48102,電子商務(wù)系,null,校4號(hào)教學(xué)樓,null103,管理系,null,校5號(hào)教學(xué)樓,null201,校長辦公室,null,校辦公北樓4樓,null202,校人事處,null,校辦公北樓3樓,null203,校教務(wù)處,null,校辦公南

16、樓2樓,null答：Insertintodept(dept_id,dept_name,dept_manger,dept_ads,dept_num)Values(101,信息工程系,null,校3號(hào)教學(xué)樓,48)Insertintodept(dept_id,dept_name,dept_manger,dept_ads,dept_num)Values(102,電子商務(wù)系,null,校4號(hào)教學(xué)樓,null)Insertintodept(dept_id,dept_name,dept_manger,dept_ads,dept_num)Values(103,管理系,null,校5號(hào)教學(xué)樓,null)Ins

17、ertintodept(dept_id,dept_name,dept_manger,dept_ads,dept_num)Values(201,校長辦公室,null,校辦公北樓4樓,null)Insertintodept(dept_id,dept_name,dept_manger,dept_ads,dept_num)Values(202,校人事處,null,校辦公北樓3樓,null)Insertintodept(dept_id,dept_name,dept_manger,dept_ads,dept_num)Values(203,校教務(wù)處,null,校辦公北樓2樓,null)在STUDENT表中插

18、入數(shù)據(jù)（寫出SQL語句）101,2002101001,李麗,1,1,sysdate,山西,14240119840101001,山西太原,19840101,信息管理與信息系統(tǒng),漢null,null101,2002101002,王娜,1,1,sysdate,山西,14240119840501001,山西大同,19840501,信息管理與信息系統(tǒng),漢null,null101,2002101003,張大虎,1,1,sysdate,山西,14240119840815021,山西運(yùn)城,19840815,計(jì)算機(jī)科學(xué)與技術(shù),漢

19、null,null答：Insertintostudent(dept_id,stu_num,stu_num,stu_gender,stu_type,stu_stardate,stu_native,stu_id,stu_ids,stu_born,stu_major,stu_nation,stu_tel,stu_cost,stu_ps)Values(101,2002101001,李麗,1,1,sysdate,山西,14240119840101001,山西太原,19840101,信息管理與信息系統(tǒng),漢null,null)Insertintostudent(dept_id,s

20、tu_num,stu_num,stu_gender,stu_type,stu_stardate,stu_native,stu_id,stu_ids,stu_born,stu_major,stu_nation,stu_tel,stu_cost,stu_ps)Values(101,2002101002,王娜,1,1,sysdate,山西,14240119840501001,山西大同,19840501,信息管理與信息系統(tǒng),漢null,null)Insertintostudent(dept_id,stu_num,stu_num,stu_gender,stu_type,stu

21、_stardate,stu_native,stu_id,stu_ids,stu_born,stu_major,stu_nation,stu_tel,stu_cost,stu_ps)Values(101,2002101003,張大虎,1,1,sysdate,山西,14240119840815021,山西運(yùn)城,19840815,計(jì)算機(jī)科學(xué)與技術(shù),漢null,null)在COURSES表中插入數(shù)據(jù)（寫出SQL語句）101001,數(shù)據(jù)庫系統(tǒng)設(shè)計(jì),48,101000101002,軟件需求工程,36,101000;101003,軟件工程導(dǎo)論,36,101000;答：Insert

22、intocourses(couse_id,course_name,course_class,course_time)Values(101001,數(shù)據(jù)庫系統(tǒng)設(shè)計(jì),48,101000)Insertintocourses(couse_id,course_name,course_class,course_time)Values(101002,軟件需求工程,36,101000)Insertintocourses(couse_id,course_name,course_class,course_time)Values(101003,軟件工程導(dǎo)論,36,101000)在report_card表中插入數(shù)據(jù)（寫

23、出SQL語句）2002101001,101001,null,88,null2002101001，101002,null,78,null2002101001,101003,null,94,null2002101002,101003,null,92,null2002101002,101001,null,84,null2002101003,101002null,74,null答：Insertintoreport_card(stu_num,course_id,term,mark,card_ps)Values(2002101001,101001,null,88,null)Insertintoreport

24、_card(stu_num,course_id,term,mark,card_ps)Values(2002101001，101002,null,78,null)Insertintoreport_card(stu_num,course_id,term,mark,card_ps)Values(2002101001,101003,null,94,null)Insertintoreport_card(stu_num,course_id,term,mark,card_ps)Values(2002101002,101003,null,92,null)Insertintoreport_card(stu_nu

25、m,course_id,term,mark,card_ps)Values(2002101002,101001,null,84,null)Insertintoreport_card(stu_num,course_id,term,mark,card_ps)Values(2002101003,101002null,74,null)將課程號(hào)為101002課程的成績(jī)普提5分。（寫出SQL語句）答：Updatereport_cardSetmark=mark+5Wherecourse_id=101002將report_card中學(xué)期為空的記錄全部設(shè)置為20060701（寫出SQL語句）答：Updaterep

26、ort_cardSetterm=20060701Wheretermisnull刪除掉系代號(hào)為101并且姓名為張大虎的學(xué)生記錄。（寫出SQL語句）答：DeletefromstudentWheredept_id=101andstu_name=張大虎刪除student表中的全部數(shù)據(jù)。（寫出SQL語句）答：Droptablestudent3.表的查詢（寫出SQL語句）（1）簡(jiǎn)單查詢查詢部門信息表DEPT的信息。Select*fromdept查詢部門信息表DEPT中部門編號(hào)與部門名稱。Selectdept_id,dept_nameFromdept查詢學(xué)生基本信息表STUDENT中學(xué)生的學(xué)號(hào)、姓名和年齡。

27、Selectstu_num,stu_name,stu_ageFromstudent查詢部門編號(hào)為201或202的部門部門名稱、部門經(jīng)理、地址。Selectdept_name,dept_manger,dept_adsFromdeptWheredept_id=201ordept_id=202查詢1984年出生的學(xué)生的學(xué)生信息。Select*FromstudentWherestu_born=1984查詢山西學(xué)生的學(xué)號(hào)、姓名、年齡及所在系。Selectstu_num,stu_name,stu_age,stu_majorFromstudentWherestu_native=山西查詢學(xué)生的學(xué)號(hào)、姓名、出生

28、日期及所在系，輸出結(jié)果先按照部門編號(hào)升序排序，部門編號(hào)相同按照出生日期降序排序。Selectstu_num,stu_name,stu_born,stu_majorFromstudentGroupbydept_idOrderbydept_iddesc查詢各院系人數(shù)。Selectcount(stu_num)Fromstudent查詢?cè)合等藬?shù)多于500的院系及其人數(shù)。Selectdept_name,count(stu_num)FromstudentWherecount(stu_num500)(2)子查詢與連接查詢查詢信息工程系的學(xué)生的學(xué)號(hào)、姓名、性別、入學(xué)日期及籍貫。Selectstu_num,st

29、u_name,stu_gender,stu_stardate,stu_nativeFromstudent,deptWheredept_namein（selectdept_nameFromdeptWheredept_name=信息工程系）查詢其他系比101系某一學(xué)生年齡小的學(xué)生學(xué)號(hào)、姓名、性別、入學(xué)日期和年齡。Selectstu_num,stu_name,stu_gender_stu_stardate,stu_ageFromstudentWherestu_ageany(selectstu_ageFromstudentWheredept_id=101)查詢其他系比101系所有學(xué)生年齡都要小的學(xué)生學(xué)

30、號(hào)、姓名、性別、入學(xué)日期和年齡。Selectstu_num,stu_name,stu_gender_stu_stardate,stu_ageFromstudentWherestu_ageSELECTstudent_no,101FROMSTUDENTWHEREDEPT_NO=101UNIONSELECTstudent_no,101001FROMreport_cardWHEREcourse_no=101001;INTERSECTSQLSELECTstudent_no,101FROMSTUDENTWHEREDEPT_NO=101INTERSECTSELECTstudent_no,101001FROM

31、report_cardWHEREcourse_no=101001;4.數(shù)據(jù)庫的復(fù)雜查詢本實(shí)驗(yàn)以SCOTT方案中的表為例來實(shí)現(xiàn)。（1）橫向小計(jì)與縱向小計(jì)查詢查詢雇員表emp，統(tǒng)計(jì)不同部門(deptno)、不同崗位(job)的平均工資,同時(shí)生成橫向小計(jì)統(tǒng)計(jì)（部門的平均工資）。SELECTdeptno,job,avg(sal)fromscott.empGROUPBYROLLUP(deptno,job);在基礎(chǔ)上生成縱向小計(jì)，統(tǒng)計(jì)崗位的平均工資。SELECTdeptno,job,avg(sal)fromscott.empGROUPBYCUBE(deptno,job);有些統(tǒng)計(jì)結(jié)果只會(huì)用到deptno列

32、，有些統(tǒng)計(jì)結(jié)過只會(huì)用到j(luò)ob列，查看每項(xiàng)統(tǒng)計(jì)結(jié)果到底使用了哪些列。（2）外連接查詢查詢部門名稱以及該部門中的人員姓名（有些部門沒有雇員）。selectdept_name,enamefromscott.emp,scott.deptwheree.dept_id=d.dept_id(+);查詢數(shù)據(jù)庫中是否存在部門沒有雇員，或者雇員不曾分配到各部門的情況。selectdept_name,enamefromscott.emp,scott.deptwheree.dept_id(+)=d.dept_id;（3）多列子查詢查詢與SMITH部門和崗位完全相同的所有雇員信息。Select*Fromscott.em

33、pWherejob=(selectjobFromscott.deptWheredept_name=SMITH)顯示工資、補(bǔ)助與SCOTT完全一致的所有雇員名、工資、補(bǔ)助。Selectename,sal,supplyFromscott.empWheredept_namein(Selectdept_nameFromscott.deptWheredept_name=SCOTT)（4）層次查詢通過層次查詢顯示除CLERK崗位外其他雇員之間的上下級(jí)關(guān)系selectename,jobfromscott.empstartwithmgrisnullwithoutdept_name=clerkconnectby

34、mgr=priorempno;通過層次查詢MARTIN的所有領(lǐng)導(dǎo)姓名。SELECTLPAD(,3*(LEVEL-1)|enameename,LPAD(,3*(LEVEL-1)|jobjobFromscott.empSTARTWITHename=MARTINCONNECTBYempno=PRIORmgr;查詢課程表中各課程之間的先修關(guān)系。Selectlpad(,3*(level-1)|course_no課程編號(hào),lpad(,3*(level-1)|course_name課程名稱fromcoursesSTARTWITHperiodISNULLCONNECTBYPERIOD=PRIORCOURSE_NO;（