整理Oracle面試題及答案

一下題目根據(jù)此表變換

1、表：table1(FId,Fclass,Fscore),用最高效最簡單的sql列出各班成績最高的列表，顯示班級，成績兩個字段。

?select?stu_class,?max(stu_score)?from?core?group?by?stu_class?;

2、有一個表table1有兩個字段FID，F(xiàn)no，字都非空，寫一個SQL語句列出該表中一個FID對應(yīng)多個不同的Fno的紀錄。

select?t2.*?from?table1?t1,?table1?t2?where?t1.fid?=?t2.fid?and?t1.fno??t2.fno;

三種寫法：

select?*?from?core?co1?where?co1.STU_CLASS?in?(?select?co.STU_CLASS?from?CORE?co?group?by?co.STU_CLASS??having?count(co.STU_CLASS)?>1);? ?select?DISTINCT?c2.*?from?core?c1?,core?c2?where?c1.STU_CLASS?=?c2.STU_CLASS?and?c1.STU_SCORE??c2.STU_SCORE;?SELECT?*?FROM?core?c1?where?1=1?and??EXISTS?(select?1?from?core?c2?where?c1.STU_CLASS?=?c2.STU_CLASS?and?c1.STU_SCORE??c2.STU_SCORE);

?3、有員工表empinfo?

(? Fempno?varchar2(10)?not?null?pk,? Fempname?varchar2(20)?not?null,? Fage?number?not?null,? Fsalary?number?not?null? );

假如數(shù)據(jù)量很大約1000萬條；寫一個你認為最高效的SQL，用一個SQL計算以下四種人：?

fsalary&gt;9999?and?fage?&gt;?35? fsalary&gt;9999?and?fage??35? fsalary?<p>每種員工的數(shù)量；?<br></p><pre class="brush:php;toolbar:false;">select?sum(case?when?fsalary?&gt;?9999?and?fage?&gt;?35then?1else?0end)?as?"fsalary&gt;9999_fage&gt;35",sum(case?when?fsalary?&gt;?9999?and?fage?9999_fage?35then?1else?0end)?as?"fsalary35",sum(case?when?fsalary?<pre class="brush:php;toolbar:false;">select?sum(case?when?stu_score??60?and?stu_score??70?and?stu_score??80?and?stu_score?<p>4、表A字段如下?<br>month person income?<br>月份 人員 收入?<br>要求用一個SQL語句（注意是一個）的處所有人（不區(qū)分人員）每個月及上月和下月的總收入?<br>要求列表輸出為?<br>月份 當月收入 上月收入 下月收入?<br></p><pre class="brush:php;toolbar:false">MONTHS?PERSON?INCOME ----------?----------?----------200807?mantisXF?5000200806?mantisXF2?3500200806?mantisXF3?3000200805?mantisXF1?2000200805?mantisXF6?2200200804?mantisXF7?1800200803?8mantisXF?4000200802?9mantisXF?4200200802?10mantisXF?3300200801?11mantisXF?4600200809?11mantisXF?6800

11 rows selected

?months,?(incomes),?(prev_months),??((?),?),?),?lag(incomes)?(??months),?)??prev_months,?decode(lead(months)?(??months),?to_char(add_months(to_date(months,?),?),?),?lead(incomes)?(??months),?)??next_months??(?months,?(income)??incomes??a???months)?aa)?aaagroup?(INCOMES)?(PREV_MONTHS)?(NEXT_MONTHS)

5，表B?
C1 c2?
2005-01-01 1?
2005-01-01 3?
2005-01-02 5

要求的處數(shù)據(jù)?
2005-01-01 4?
2005-01-02 5?
合計 9?
試用一個Sql語句完成。

select?nvl(to_char(t02,'yyyy-mm-dd'),'合計'),sum(t01)from?test? group?by?rollup(t02)

6，數(shù)據(jù)庫1，2，3 范式的概念與理解。

關(guān)系數(shù)據(jù)庫設(shè)計之時是要遵守一定的規(guī)則的。尤其是數(shù)據(jù)庫設(shè)計范式?
簡單介紹1NF（第一范式），2NF（第二范式），3NF（第三范式），
第一范式（1NF）：在關(guān)系模式R中的每一個具體關(guān)系r中，如果每個屬性值 都是不可再分的最小數(shù)據(jù)單位，則稱R是第一范式的關(guān)系。

例：如職工號，姓名，電話號碼組成一個表（一個人可能有一個辦公室電話 和一個家里電話號碼） 規(guī)范成為1NF有三種方法：?
　　一是重復(fù)存儲職工號和姓名。這樣，關(guān)鍵字只能是電話號碼。?
　　二是職工號為關(guān)鍵字，電話號碼分為單位電話和住宅電話兩個屬性?
　　三是職工號為關(guān)鍵字，但強制每條記錄只能有一個電話號碼。?
　　以上三個方法，第一種方法最不可取，按實際情況選取后兩種情況。?

　第二范式（2NF）：如果關(guān)系模式R（U，F(xiàn)）中的所有非主屬性都完全依賴于任意一個候選關(guān)鍵字，則稱關(guān)系R 是屬于第二范式的。?
　　例：選課關(guān)系 SCI（SNO，CNO，GRADE，CREDIT）其中SNO為學(xué)號， CNO為課程號，GRADEGE 為成績，CREDIT 為學(xué)分。 由以上?
條件，關(guān)鍵字為組合關(guān)鍵字（SNO，CNO）?
　　在應(yīng)用中使用以上關(guān)系模式有以下問題：?

a.數(shù)據(jù)冗余，假設(shè)同一門課由40個學(xué)生選修，學(xué)分就 重復(fù)40次。?b.更新異常，若調(diào)整了某課程的學(xué)分，相應(yīng)的元組CREDIT值都要更新，有可能會出現(xiàn)同一門課學(xué)分不同。?c.插入異常，如計劃開新課，由于沒人選修，沒有學(xué)號關(guān)鍵字，只能等有人選修才能把課程和學(xué)分存入。?d.刪除異常，若學(xué)生已經(jīng)結(jié)業(yè)，從當前數(shù)據(jù)庫刪除選修記錄。某些門課程新生尚未選修，則此門課程及學(xué)分記錄無法保存。?

　　原因：非關(guān)鍵字屬性CREDIT僅函數(shù)依賴于CNO，也就是CREDIT部分依賴組合關(guān)鍵字（SNO，CNO）而不是完全依賴。?

　　解決方法：分成兩個關(guān)系模式 SC1（SNO，CNO，GRADE），C2（CNO，CREDIT）。新關(guān)系包括兩個關(guān)系模式，它們之間通過SCN中
的外關(guān)鍵字CNO相聯(lián)系，需要時再進行自然聯(lián)接，恢復(fù)了原來的關(guān)系?
　第三范式（3NF）：如果關(guān)系模式R（U，F(xiàn)）中的所有非主屬性對任何候選關(guān)鍵字都不存在傳遞信賴，則稱關(guān)系R是屬于第三范式的。?

例：如S1（SNO，SNAME，DNO，DNAME，location） 各屬性分別代表學(xué)號，?
姓名，所在系，系名稱，系地址。?
關(guān)鍵字SNO決定各個屬性。由于是單個關(guān)鍵字，沒有部分依賴的問題，肯定是2NF。但這關(guān)系肯定有大量的冗余，有關(guān)學(xué)生所在的幾個
屬性DNO，DNAME，LOCATION將重復(fù)存儲，插入，刪除和修改時也將產(chǎn)生類似以上例的情況。?
原因：關(guān)系中存在傳遞依賴造成的。即SNO -> DNO。 而DNO -> SNO卻不存在，DNO -> LOCATION, 因此關(guān)鍵遼 SNO 對 LOCATIO

N 函數(shù)決定是通過傳遞依賴 SNO -> LOCATION 實現(xiàn)的。也就是說，SNO不直接決定非主屬性LOCATION。?
解決目地：每個關(guān)系模式中不能留有傳遞依賴。?
解決方法：分為兩個關(guān)系 S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）?
注意：關(guān)系S中不能沒有外關(guān)鍵字DNO。否則兩個關(guān)系之間失去聯(lián)系。

7，簡述oracle行觸發(fā)器的變化表限制表的概念和使用限制，行觸發(fā)器里面對這兩個表有什么限制。

變化表mutating table
被DML語句正在修改的表
需要作為delete CASCADE參考完整性限制的結(jié)果進行更新的表也是變化的

限制:對于Session本身，不能讀取正在變化的表

限制表constraining table
需要對參考完整性限制執(zhí)行讀操作的表

限制:如果限制列正在被改變，那么讀取或修改會觸發(fā)錯誤，但是修改其它列是允許的。

8、oracle臨時表有幾種。?
臨時表和普通表的主要區(qū)別有哪些，使用臨時表的主要原因是什么？

在Oracle中，可以創(chuàng)建以下兩種臨時表：?
a。會話特有的臨時表?

CREATE?GLOBAL?TEMPORARY?(?)? ON?COMMIT?PRESERVE?ROWS；

b。事務(wù)特有的臨時表?

CREATE?GLOBAL?TEMPORARY?(?)? ON?COMMIT?DELETE?ROWS；? CREATE?GLOBAL?TEMPORARY?TABLE?MyTempTable

所建的臨時表雖然是存在的，但是你試一下insert 一條記錄然后用別的連接登上去select，記錄是空的，明白了吧。
下面兩句話再貼一下：?

• ON COMMIT DELETE ROWS 說明臨時表是事務(wù)指定，每次提交后ORACLE將截斷表（刪除全部行）?

• ON COMMIT PRESERVE ROWS 說明臨時表是會話指定，當中斷會話時ORACLE將截斷表。

9，怎么實現(xiàn)：使一個會話里面執(zhí)行的多個過程函數(shù)或觸發(fā)器里面都可以訪問的全局變量的效果，并且要實現(xiàn)會話間隔離？

--個人理解就是建立一個包,將常量或所謂的全局變量用包中的函數(shù)返回出來就可以了,摘抄一短網(wǎng)上的解決方法Oracle數(shù)據(jù)庫程序包中的變量，在本程序包中可以直接引用，但是在程序包之外，則不可以直接引用。對程序包變量的存取，可以為每個變量配套相應(yīng)的存儲過程和函數(shù)來實現(xiàn)。? 　　 　　3.2?實例? 　　--定義程序包?　　create?or?replace?package?PKG_System_Constant?is?　　 　　　?C_SystemTitle?nVarChar2(100):='測試全局程序變量';　--定義常數(shù)?　　　?--獲取常數(shù)?　　　?Function?FN_GetSystemTitle? 　　　　　Return?nVarChar2;? 　　 　　　?G_CurrentDate?Date:=SysDate;?--定義全局變量?　　　?--獲取全局變量?　　　?Function?FN_GetCurrentDate? 　　　　　Return?Date;? 　　　?--設(shè)置全局變量?　　　?Procedure?SP_SetCurrentDate? 　　　　　(P_CurrentDate?In?Date);? 　　End?PKG_System_Constant;? 　　/?　　create?or?replace?package?body?PKG_System_Constant?is?　　　?--獲取常數(shù)?　　　?Function?FN_GetSystemTitle? 　　　　　Return?nVarChar2? 　　　　　Is?　　　　　Begin?　　　　　　?Return?C_SystemTitle;? 　　　　　End?FN_GetSystemTitle;? 　　 　　　?--獲取全局變量?　　　?Function?FN_GetCurrentDate? 　　　　　Return?Date? 　　　　　Is?　　　　　Begin?　　　　　　?Return?G_CurrentDate;? 　　　　　End?FN_GetCurrentDate;? 　　　?--設(shè)置全局變量?　　　?Procedure?SP_SetCurrentDate? 　　　　　(P_CurrentDate?In?Date)? 　　　　　Is?　　　　　Begin?　　　　　　?G_CurrentDate:=P_CurrentDate;? 　　　　　End?SP_SetCurrentDate;? 　　End?PKG_System_Constant;? 　　/?　　　　 　　3.3?測試? 　　--測試讀取常數(shù)?　　Select?PKG_System_Constant.FN_GetSystemTitle?From?Dual;　　　? 　　--測試設(shè)置全局變量?　　Declare　 　　Begin?　　　?PKG_System_Constant.SP_SetCurrentDate(To_Date('2001.01.01','yyyy.mm.dd'));? 　　End;? 　　/?　　--測試讀取全局變量?　　Select?PKG_System_Constant.FN_GetCurrentDate?From?Dual;

10，aa，bb表都有20個字段，且記錄數(shù)量都很大，aa，bb表的X字段（非空）上有索引，?
請用SQL列出aa表里面存在的X在bb表不存在的X的值，請寫出認為最快的語句，并解譯原因。

select?aa.x?from?aa where?not?exists?(select?'x'?from?bb?where?aa.x?=?bb.x)?;

以上語句同時使用到了aa中x的索引和的bb中x的索引

11，簡述SGA主要組成結(jié)構(gòu)和用途？

SGA是Oracle為一個實例分配的一組共享內(nèi)存緩沖區(qū)，它包含該實例的數(shù)據(jù)和控制信息。SGA在實例啟動時被自動分配，當實例關(guān)閉時被收回。數(shù)據(jù)庫的所有數(shù)據(jù)操作都要通過SGA來進行。?
SGA中內(nèi)存根據(jù)存放信息的不同，可以分為如下幾個區(qū)域：
a.Buffer Cache：存放數(shù)據(jù)庫中數(shù)據(jù)庫塊的拷貝。它是由一組緩沖塊所組成，這些緩沖塊為所有與該實例相鏈接的用戶進程所共享。緩沖塊的數(shù)目由初始化參數(shù)DB_BLOCK_BUFFERS確定，緩沖塊的大小由初始化參數(shù)DB_BLOCK_SIZE確定。大的數(shù)據(jù)塊可提高查詢速度。它由DBWR操作。?
b. 日志緩沖區(qū)Redo Log Buffer：存放數(shù)據(jù)操作的更改信息。它們以日志項（redo entry）的形式存放在日志緩沖區(qū)中。當需要進行數(shù)據(jù)庫恢復(fù)時，日志項用于重構(gòu)或回滾對數(shù)據(jù)庫所做的變更。日志緩沖區(qū)的大小由初始化參數(shù)LOG_BUFFER確定。大的日志緩沖區(qū)可減少日志文件I/O的次數(shù)。后臺進程LGWR將日志緩沖區(qū)中的信息寫入磁盤的日志文件中，可啟動ARCH后臺進程進行日志信息歸檔。?
c. 共享池Shared Pool：包含用來處理的SQL語句信息。它包含共享SQL區(qū)和數(shù)據(jù)字典存儲區(qū)。共享SQL區(qū)包含執(zhí)行特定的SQL語句所用的信息。數(shù)據(jù)字典區(qū)用于存放數(shù)據(jù)字典，它為所有用戶進程所共享。

12什么是分區(qū)表？簡述范圍分區(qū)和列表分區(qū)的區(qū)別，分區(qū)表的主要優(yōu)勢有哪些？

使用分區(qū)方式建立的表叫分區(qū)表

范圍分區(qū)?
每個分區(qū)都由一個分區(qū)鍵值范圍指定（對于一個以日期列作為分區(qū)鍵的表，“2005 年 1 月”分區(qū)包含分區(qū)鍵值為從“2005 年 1 月 1 日”?
到“2005 年 1 月 31 日”的行）。

列表分區(qū)?
每個分區(qū)都由一個分區(qū)鍵值列表指定（對于一個地區(qū)列作為分區(qū)鍵的表，“北美”分區(qū)可能包含值“加拿大”“美國”和“墨西哥”）。

分區(qū)功能通過改善可管理性、性能和可用性，從而為各式應(yīng)用程序帶來了極大的好處。通常，分區(qū)可以使某些查詢以及維護操作的性能大大提高。此外,分區(qū)還可以極大簡化常見的管理任務(wù)。通過分區(qū),數(shù)據(jù)庫設(shè)計人員和管理員能夠解決前沿應(yīng)用程序帶來的一些難題。分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用性系統(tǒng)的關(guān)鍵工具。

13，背景：某數(shù)據(jù)運行在archivelog，且用rman作過全備份和數(shù)據(jù)庫的冷備份，?
且所有的歸檔日志都有，現(xiàn)控制文件全部損壞，其他文件全部完好，請問該怎么恢復(fù)該數(shù)據(jù)庫，說一兩種方法。

回復(fù)的方法:
一.使用冷備份,直接將冷備份的文件全部copy到原先的目錄下,在從新啟動數(shù)據(jù)庫就可以
二.使用歸檔日志,

• 啟動數(shù)據(jù)庫NOMOUNT

• 創(chuàng)建控制文件,控制文件指定數(shù)據(jù)文件和重做日志文件的位置.

• 使用RECOVER database using backup controlfile until cancel 命令回復(fù)數(shù)據(jù)庫,這時可以使用歸檔日志

• ALETER DATABASE OPEN RESETLOGS;

• 重新備份數(shù)據(jù)庫和控制文件

14，用rman寫一個備份語句：備份表空間TSB，level 為2的增量備份。

15，有個表a(x number(20),y number(20))用最快速高效的SQL向該表插入從1開始的連續(xù)的1000萬記錄。

相關(guān)學(xué)習(xí)推薦：oracle數(shù)據(jù)庫學(xué)習(xí)教程

【專題推薦】：oracle數(shù)據(jù)庫學(xué)習(xí)教程