直擊資料庫面試題：資料庫查詢語句
Student（S#，Sname，Sage，Ssex） 學生表
Course（C#，Cname，T#） 課程表
SC（S#，C#，score） 成績表
Teacher（T#，Tname） 教師表
問題：
1、查詢“001”課程比“002”課程成績高地所有學生的學號
select a。S#
from （select s#，score from SC where C#=’001′） a，
（select s#，score from SC where C#=’002′） b
where a。score>b。score and a。s#=b。s#；
2、查詢平均成績大於60分的同學的學號和平均成績
select S#，avg（score）
from sc
group by S# having avg（score） >60；
3、查詢所有同學的學號、姓名、選課數、總成績
select Student。S#，Student。Sname，count（SC。C#），sum（score）
from Student left Outer join SC on Student。S#=SC。S#
group by Student。S#，Sname
4、查詢姓“李”的老師的個數
select count（distinct（Tname））
from Teacher
where Tname like ‘李%’；
5、查詢沒學過“葉平”老師課的同學的學號、姓名
select Student。S#，Student。Sname
from Student
where S# not in （select distinct（ SC。S#） from SC，Course，Teacher where SC。C#=Course。C# and Teacher。T#=Course。T# and Teacher。Tname=’葉平’）；
6、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名
select Student。S#，Student。Sname
from Student，SC
where Student。S#=SC。S# and SC。C#=’001′and exists（ Select * from SC as SC_2 where SC_2。S#=SC。S# and SC_2。C#=’002′）；
7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名
select S#，Sname
from Student
where S# in
（select S#
from SC ，Course ，Teacher
where SC。C#=Course。C# and Teacher。T#=Course。T# and Teacher。Tname=’葉平’ group by S# having count（SC。C#）=（select count（C#） from Course，Teacher where Teacher。T#=Course。T# and Tname=’葉平’））；
8、查詢所有課程成績小於60分的同學的學號、姓名
select S#，Sname
from Student
where S# not in （select Student。S# from Student，SC where S。S#=SC。S# and score>60）；
9、查詢沒有學全所有課的同學的學號、姓名
select Student。S#，Student。Sname
from Student，SC
where Student。S#=SC。S#
group by Student。S#，Student。Sname having count（C#） <（select count（C#） from Course）；
10、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名
select S#，Sname
from Student，SC
where Student。S#=SC。S# and C# in （select C# from SC where S#=‘1001’）；
11、刪除學習“葉平”老師課的SC表記錄
Delect SC
from course ，Teacher
where Course。C#=SC。C# and Course。T#= Teacher。T# and Tname=‘葉平’；
12、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
SELECT L。C# 課程ID，L。score 最高分，R。score 最低分
FROM SC L ，SC R
WHERE L。C# = R。C#
and
L。score = （SELECT MAX（IL。score）
FROM SC IL，Student IM
WHERE IL。C# = L。C# and IM。S#=IL。S#
GROUP BY IL。C#）
and
R。Score = （SELECT MIN（IR。score）
FROM SC IR
WHERE IR。C# = R。C#
GROUP BY IR。C# ）；
13、查詢學生平均成績及其名次
SELECT 1+（SELECT COUNT（ distinct 平均成績）
FROM （SELECT S#，AVG（score） 平均成績
FROM SC
GROUP BY S# ） T1
WHERE 平均成績 > T2。平均成績） 名次， S# 學生學號，平均成績
FROM （SELECT S#，AVG（score） 平均成績 FROM SC GROUP BY S# ） T2
ORDER BY 平均成績 desc；
14、查詢各科成績前三名的記錄：（不考慮成績並列情況）
SELECT t1。S# as 學生ID，t1。C# as 課程ID，Score as 分數
FROM SC t1
WHERE score IN （SELECT TOP 3 score
FROM SC
WHERE t1。C#= C#
ORDER BY score DESC）
ORDER BY t1。C#；
15、查詢每門功課成績最好的前兩名
SELECT t1。S# as 學生ID，t1。C# as 課程ID，Score as 分數
FROM SC t1
WHERE score IN （SELECT TOP 2 score
FROM SC
WHERE t1。C#= C#
ORDER BY score DESC ）
ORDER BY t1。C#；
補充：
已經知道原表
year salary
——————
2000 1000
2001 2000
2002 3000
2003 4000
解：
select b。year，sum（a。salary）
from salary a，salary b
where a。year<=b。year
group by b。year
order by b。year；
在面試過程中多次碰到一道SQL查詢的題目，查詢A（ID，Name）表中第31至40條記錄，ID作為主鍵可能是不是連續增長的列，完整的查詢語句如下：
方法一：
select top 10 *
from A
where ID >（select max（ID） from （select top 30 ID from A order by ID ） T） order by ID
方法二：
select top 10 *
from A
where ID not In （select top 30 ID from A order by ID）
order by ID
資料庫面試題
1。 在一個查詢中，使用哪一個關鍵字能夠除去重複列值？
答案：使用distinct關鍵字
2。 什麼是快照？它的作用是什麼？
答案：快照Snapshot是一個檔案系統在特定時間裡的映象，對於線上實時資料備份非常有用。快照對於擁有不能停止的應用或具有常開啟檔案的檔案系統的備份非常重要。對於只能提供一個非常短的備份時間而言，快照能保證系統的完整性。
3。 解釋儲存過程和觸發器
儲存過程是一組Transact-SQL語句，在一次編譯後可以執行多次。因為不必重新編譯Transact-SQL語句，所以執行儲存過程可以提高效能。
觸發器是一種特殊型別的儲存過程，不由使用者直接呼叫。建立觸發器時會對其進行定義，以便在對特定表或列作特定型別的資料修改時執行。
4。 SQL Server是否支援行級鎖，有什麼好處？
支援動態行級鎖定。
SQL Server 2000動態地將查詢所引用的每一個表的鎖定粒度調整到合適的級別。當查詢所引用的少數幾行分散在一個大型表中時，最佳化資料並行訪問的最佳辦法是使用粒度鎖，如行鎖。但是，如果查詢引用的是一個表中的大多數行或所有行，最佳化資料並行訪問的最佳辦法可以是鎖定整個表，以儘量減少鎖定開銷並儘快完成查詢。
SQL Serve 2000透過為每個查詢中的每個表選擇適當的鎖定級別，在總體上優化了資料併發訪問。對於一個查詢，如果只引用一個大型表中的幾行，則資料庫引擎可以使用行級鎖定；如果引用一個大型表的幾頁中的多行，則使用頁級鎖定；如果引用一個小型表中的所有行，則使用表級鎖定。
5。 資料庫日誌幹什麼用，資料庫日誌滿的時候再查詢資料庫時會出現什麼情況？
每個資料庫都有事務日誌，用以記錄所有事務和每個事務對資料庫所做的修改。
6。 儲存過程和函式的區別？
儲存過程是使用者定義的一系列SQL語句的集合，涉及特定表或其它物件的任務，使用者可以呼叫儲存過程，而函式通常是資料庫已定義的方法，它接收引數並返回某種型別的值並且不涉及特定使用者表。
7。 事務是什麼？
事務是作為一個邏輯單元執行的一系列操作，一個邏輯工作單元必須有四個屬性，稱為 ACID（原子性、一致性、隔離性和永續性）屬性，只有這樣才能成為一個事務：
(1) 原子性
事務必須是原子工作單元；對於其資料修改，要麼全都執行，要麼全都不執行。
(2) 一致性
事務在完成時，必須使所有的資料都保持一致狀態。在相關資料庫中，所有規則都必須應用於事務的修改，以保持所有資料的完整性。事務結束時，所有的內部資料結構（如 B 樹索引或雙向連結串列）都必須是正確的。
(3) 隔離性
由併發事務所作的修改必須與任何其它併發事務所作的修改隔離。事務檢視資料時資料所處的狀態，要麼是另一併發事務修改它之前的狀態，要麼是另一事務修改它之後的狀態，事務不會檢視中間狀態的資料。這稱為可序列性，因為它能夠重新裝載起始資料，並且重播一系列事務，以使資料結束時的狀態與原始事務執行的狀態相同。
(4) 永續性
事務完成之後，它對於系統的影響是永久性的。該修改即使出現系統故障也將一直保持。
8。 遊標的作用？如何知道遊標已經到了最後？
遊標用於定位結果集的行，透過判斷全域性變數@@FETCH_STATUS可以判斷是否到了最後，通常此變數不等於0表示出錯或到了最後。
9。 觸發器分為事前觸發和事後觸發，這兩種觸發有何區別？語句級觸發和行級觸發有何區別？
事前觸發器運行於觸發事件發生之前，而事後觸發器運行於觸發事件發生之後。通常事前觸發器可以獲取事件之前和新的欄位值。
語句級觸發器可以在語句執行前或後執行，而行級觸發在觸發器所影響的每一行觸發一次。
10。 解決死鎖的方法有哪些？
SQL Server自動檢測和消除死鎖
設定死鎖優先順序
設定鎖定超時
使用更新鎖避免死鎖
11。 提高資料庫執行效率的辦法有哪些？
在給定的系統硬體和系統軟體條件下，提高資料庫系統的執行效率的辦法是：
（1） 在資料庫物理設計時，降低正規化，增加冗餘， 少用觸發器， 多用儲存過程。
（2） 當計算非常複雜、而且記錄條數非常巨大時（例如一千萬條），複雜計算要先在資料庫外面，以檔案系統方式用C++語言計算處理完成之後，最後才入庫追加到表中去。這是電信計費系統設計的經驗。
（3） 發現某個表的記錄太多，例如超過一千萬條，則要對該表進行水平分割。水平分割的做法是，以該表主鍵PK的某個值為界線，將該表的記錄水平分割為兩個表。若發現某個表的欄位太多，例如超過八十個，則垂直分割該表，將原來的一個表分解為兩個表。
（4） 對資料庫管理系統DBMS進行系統最佳化，即最佳化各種系統引數，如緩衝區個數。
（5） 在使用面向資料的SQL語言進行程式設計時，儘量採取最佳化演算法。總之，要提高資料庫的執行效率，必須從資料庫系統級最佳化、資料庫設計級最佳化、程式實現級最佳化，這三個層次上同時下功夫。
12。 通俗地理解三個正規化
通俗地理解三個正規化，對於資料庫設計大有好處。在資料庫設計中，為了更好地應用三個正規化，就必須通俗地理解三個正規化（通俗地理解是夠用的理解，並不是最科學最準確的理解）
第一正規化：1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解；
第二正規化：2NF是對記錄的惟一性約束，要求記錄有惟一標識，即實體的唯一性；
第三正規化：3NF是對欄位冗餘性的約束，即任何欄位不能由其他欄位派生出來，它要求欄位沒有冗餘。沒有冗餘的資料庫設計可以做到。但是，沒有冗餘的資料庫未必是最好的資料庫，有時為了提高執行效率，就必須降低正規化標準，適當保留冗餘資料。具體做法是：在概念資料模型設計時遵守第三正規化，降低正規化標準的工作放到物理資料模型設計時考慮。降低正規化就是增加欄位，允許冗餘。
13。 簡述儲存過程的優缺點
優點：
1。 更快地執行速度：儲存過程只在創造時進行編譯，以後每次執行儲存過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次，所以使用儲存過程可提高資料庫執行速度；
2。 與事務的結合，提供更好的解決方案：當對資料庫進行復雜操作時（如對多個表進行Update、Insert、Query和Delete時），可將此複雜操作用儲存過程封裝起來與資料庫提供的事務處理結合一起使用；
3。 支援程式碼重用：儲存過程可以重複使用，可減少資料庫開發人員的工作量；
4。 安全性高：可設定只有某此使用者才具有對指定儲存過程的使用權。
缺點：
1。 如果更改範圍大到需要對輸入儲存過程的引數進行更改，或者要更改由其返回的資料，則你仍需要更新程式集中的程式碼以新增引數、更新 GetValue（） 呼叫，等等，這時候估計比較繁瑣了。
2。 可移植性差由於儲存過程將應用程式繫結到 SQL Server，因此使用儲存過程封裝業務邏輯將限制應用程式的可移植性。如果應用程式的可移植性在您的環境中非常重要，則將業務邏輯封裝在不特定於 RDBMS 的中間層中可能是一個更佳的選擇。
14。 主鍵和唯一索引有什麼區別？
相同點：
它們都屬於實體完整性約束。
不同點：
（1） 唯一性約束所在的列允許空值，但是主鍵約束所在的列不允許空值。
（2） 可以把唯一性約束放在一個或者多個列上，這些列或列的組合必須有唯一的。但是，唯一性約束所在的列並不是表的主鍵列。
（3） 唯一性約束強制在指定的列上建立一個唯一性索引。在預設情況下，建立唯一性的非聚簇索引，但是，也可以指定所建立的索引是聚簇索引。
（4） 建立主鍵的目的是讓外來鍵來引用。
（5） 一個表最多隻有一個主鍵，但可以有很多唯一鍵。
15。 簡述索引存取的方法的作用和建立索引的原則
作用：加快查詢速度。
原則：
（1） 如果某屬性或屬性組經常出現在查詢條件中，考慮為該屬性或屬性組建立索引；
（2） 如果某個屬性常作為最大值和最小值等聚集函式的引數，考慮為該屬性建立索引；
（3） 如果某屬性經常出現在連線操作的連線條件中，考慮為該屬性或屬性組建立索引；
16。 簡述資料庫的設計過程
資料庫設計分為五個階段：
需求分析：主要是準確收集使用者資訊需求和處理需求，並對收集的結果進行整理和分析，形成需求說明。
概念結構設計：對使用者需求進行綜合、歸納、抽象，形成一個與與具體的DBMS無關概念模型（一般為ER模型）。
邏輯結構設計：將概念結構設計的概念模型轉化為某個特定的DBMS所支援的資料模型，建立資料庫邏輯模式，並對其進行最佳化，同時為各種使用者和應用設計外模式。
物理結構設計：為設計好的邏輯模型選擇物理結構，包括儲存結構和存取方法等，建立資料庫物理模式。
實施和維護：實施就是使用DLL語言建立資料庫模式，將實際資料載入資料庫，建立真正的資料庫。維護階段是對執行中的資料庫進行評價、調整和修改。
17。 什麼是記憶體洩漏？
一般我們所說的記憶體洩漏指的是堆記憶體的洩漏。堆記憶體是程式從堆中為其分配的，大小任意的，使用完後要顯示釋放記憶體。當應用程式用關鍵字new等建立物件時，就從堆中為它分配一塊記憶體，使用完後程序呼叫free或者delete釋放該記憶體，否則就說該記憶體就不能被使用，我們就說該記憶體被洩漏了。
18。 什麼是基本表？什麼是檢視？
基本表是本身獨立存在的表，在 SQL 中一個關係就對應一個表。
檢視是從一個或幾個基本表匯出的表。檢視本身不獨立儲存在資料庫中，是一個虛表
19。 試述檢視的優點
（1） 檢視能夠簡化使用者的操作
（2） 檢視使使用者能以多種角度看待同一資料；
（3） 檢視為資料庫提供了一定程度的邏輯獨立性；
（4） 檢視能夠對機密資料提供安全保護。
20。 所有的檢視是否都可以更新？為什麼？
不是。檢視是不實際儲存資料的虛表，因此對檢視的更新，最終要轉換為對基本表的更新。因為有些檢視的更新不能惟一有意義地轉換成對相應基本表的更新，所以，並不是所有的檢視都是可更新的。
21。 哪類檢視是可以更新的？哪類檢視是不可更新的？
基本表的行列子集檢視一般是可更新的。若檢視的屬性來自集函式、表示式，則該檢視肯定是不可以更新的。
22。 維護資料庫的完整性、一致性、你喜歡用觸發器還是自寫業務邏輯？為什麼？
儘可能用約束（包括CHECK、主鍵、唯一鍵、外來鍵、非空欄位）實現，這種方式的效率最好；其次用觸發器，這種方式可以保證無論何種業務系統訪問資料庫都能維持資料庫的完整性、一致性；最後再考慮用自寫業務邏輯實現，但這種方式效率最低、程式設計最複雜，當為下下之策。
23。 在為檢視建立索引前，檢視本身必須滿足哪些條件？
（1） 檢視以及檢視中引用的所有表都必須在同一資料庫中，並具有同一個所有者
（2） 索引檢視無需包含要供最佳化器使用的查詢中引用的所有表。
（3） 必須先為檢視建立唯一群集索引，然後才可以建立其它索引。
（4） 建立基表、檢視和索引以及修改基表和檢視中的資料時，必須正確設定某些 SET 選項（在本文件的後文中討論）。另外，如果這些 SET 選項正確，查詢最佳化器將不考慮索引檢視。
（5） 檢視必須使用架構繫結建立，檢視中引用的任何使用者定義的函式必須使用 SCHEMABINDING 選項建立。
（6） 另外，還要求有一定的磁碟空間來存放由索引檢視定義的資料。
24。 什麼是SQL Server的確定性函式和不確定性函式？
只要使用特定的輸入值集並且資料庫具有相同的狀態，不管何時呼叫，始終都能範圍相同結果的函式叫確定性函式。幾十訪問的資料庫的狀態不變，每次書用特定的輸入值都可能範圍不同結果的函式叫非確定性函式。
