>>>>>>>>>>點擊進入數據庫系統工程師考試網絡課堂

>>>>>>>>>>點擊進入數據庫系統工程師歷年真題題庫

>>>>>>>>>>點擊進入數據庫工程師考試大綱教材

2010年上半年數據庫系統工程師考試真題

下午卷

試題二
      閱讀下列說明，回答問題1至問題3，將解答填入答題紙的對應欄內。
[說明]
天津市某銀行信息系統的數據庫部分關系模式如下所示： 客戶 (客戶號，姓名，性別，地址，郵編，電話) 賬戶 (賬戶號，客戶號，開戶支行號，余額) 支行(支行號，支行名稱，城市，資產總額) 交易 (交易號，賬戶號，業務金額，交易日期)
      其中，業務金額為正值表示客戶向賬戶存款；為負值表示取款。
  [問題1]
  以下是創建賬戶關系的SQL語句，賬戶號識別一個賬戶，客戶號為客戶關系的標識，且不能為空。賬戶余額不能小于1.00元。請將空缺部分補充完整。
CREATE TABLE賬戶(
      賬戶號CHAR(19) (a) ,
客戶號CHAR(10) (b)
開戶支行號CHAR(6) NOT NULL,
余額NUMBER(8,2) (c) );
      [問題2]
  (1)現銀行決策者希望查看在天津市各支行開戶且2009年9月使用了銀行存取服務的所有客戶的詳細信息，請補充完整相應的查詢語句。
      (交易日期形式為'2000-01-01')
SELECT DISTINCT客戶.*
      FROM客戶,賬戶,支行,交易
WHERE客戶.客戶號=賬戶.客戶號 AND
賬戶.開戶支行號=支行.支行號AND
      (d) AND
  交易.賬戶號=賬戶.賬戶號 AND
(e)
  上述查詢優化后的語句如下，請補充完整。
SELECT DISTINCT客戶.*
  FROM 客戶,賬戶, (f) AS新支行， (g) AS新交易
  WHERE客戶.客戶號=賬戶.客戶號AND
      賬戶.開戶支行號=新支行.支行號AND
新交易.賬戶號=賬戶.賬戶號；
      (2)假定一名客戶可以申請多個賬戶，給出在該銀行當前所有賬戶余額之和超過百萬的客戶信息并按客戶號降序排列。
SELECT *
  FROM客戶
WHERE (h)
(SELECT客戶號FROM賬戶GROUP BY客戶號 (i) )
ORDER BY (j)
  [問題3]
(1)為賬戶關系增加一個屬性“賬戶標記”，缺省值為0，取值類型為整數；并將當前賬戶關系中所有記錄的“賬戶標記”屬性值修改為0。請補充相關SQL語句。
  ALTER TABLE 賬戶 (k) DEFAULT 0；
      UPDATE 賬戶 (l)
(2)對于每筆金額超過10萬元的交易，其對應賬戶標記屬性值加1，給出觸發器實現的方案。
      CREATE TRIGGER 交易_觸發器 (m) ON交易
  REFERENCING NEW ROW AS 新交易
FOR EACH ROW
WHEN (n) BEGIN ATOMIC
  UPDATE 賬戶 SET 賬戶標記=賬戶標記+1
      WHERE (o)
  COMMIT WORK;
END
 

  試題二分析
本題考查SQL語句的基本語法與結構知識。
      此類題目要求考生掌握SQL語句的基本語法和結構，認真閱讀題目給出的關系模式，針對題目的要求具體分析并解答。本試題已經給出了4個關系模式，需要分析每個實體的屬性特征及實體之間的聯系，補充完整SQL語句。
[問題1]
由于問題1中“賬戶號識別一個賬戶”可知賬戶號為賬戶關系的主鍵，即不能為空且標識一條賬戶信息，因此需要用PRIMARY KEY對該屬性進行主鍵約束；又由于“客戶號為客戶關系的標識，且不能為空”可知客戶號為客戶關系的主鍵，在賬戶關系中應作外鍵，用FOREIGN KEY對該屬性進行外鍵約束；由“賬戶余額不能小于1.00元”可知需要限制賬戶余額屬性值的范圍，通過CHECK約束來實現。從上分析可見，完整的SQL語句如下：
      CREATE TABLE賬戶(
      賬戶號CHAR(19) PRIMARY KEY,
客戶號CHAR(10) FOREIGN KEY(客戶號)REFERENcEs客戶(客戶號)， 開戶支行號CHAR(6) NOT NULL， 余額 NUMBER(8,2) CHECK(余額＞1.00));
      注：PRIMARY KEY可替換為NOT NULL UNIQUE或NOT NULL PRIMARY KEY。 [問題2]
      SQL查詢通過SELECT語句實現。
(1)根據問題2要求應在表連接條件的基礎上，需要添加兩個條件：①支行關系的城市屬性值為“天津市”，即支行城市='天津市'；②在2009年9月存在交易記錄，由于交易日期形式為'2000-01-01'，所以需要通過模糊匹配來實現，用LIKE關鍵詞和通配符表示，即交易．交易日期LIKE'2009-09-%'。
WHERE子句中條件的先后順序會對執行效率產生影響。假如解析器是按照先后順序依次解析并列條件，優化的原則是：表之間的連接必須出現在其他WHERE條件之后，那些可以過濾掉最多條記錄的條件盡可能出現在WHERE子句中其他條件的前面。要實現上述優化過程，可以重新組織WHERE條件的順序或者通過嵌套查詢以縮小連接記錄數目的規模來實現。
根據問題2要求，考生需要添加兩個子查詢以縮小參與連接的記錄的數目，即篩選出天津市的所有支行(SELECT+FROM支行WHERE城市='天津市')，而且找到2009年9月發生的交易記錄(SELECT+FROM交易WHERE交易日期LIKE'2009-09-%')，然后再做連接查詢。
      (2)根據問題2要求，可通過予查詢實現“所有賬戶余額之和超過百萬的客戶信息”的查詢；對SUM函數計算的結果應通過HAVING條件語句進行約束；降序通過DESC關鍵字來實現。
SELECT * FROM 客戶 WHERE 客戶號 IN
(SELECT 客戶號 FROM 賬戶 GROUP BY客戶號HAVING SUM(余額)＞1000000.00) ORDER BY客戶號 DESC; [問題3]
  (1)關系模式的修改通過ALTER語句來實現，使用ADD添加屬性；使用SET修改屬性值。
ALTER TABLE 賬戶 ADD 賬戶標記 INT DEFAULT 0; UPDATE 賬戶 SET 賬戶標記 =0;
      (2)創建觸發器可通過CREATE TRIGGER語句實現，問題3要求考生掌握該語句的基本語法結構。按照問題3要求，在交易關系中插入一條記錄時觸發器應自動執行，故需要創建基于INSERT類型的觸發器，其觸發條件是新插入交易記錄的金額屬性值＞100000.00；最后添加表連接條件。完整的觸發器實現方案如下：
CREATE TRIGGER交易觸發器AFTER INSERT ON交易 REFERENCING NEW ROW AS新交易 FOR EACH ROW
      WHEN新交易.金額＞100000.00 BEGIN ATOMIC
      UPDATE 賬戶 SET 賬戶標記=賬戶標記+1 WHERE 賬戶.賬戶號=新交易.賬戶號; COMMIT WORK; END
 

  參考答案
      [問題1]
(a)PRIMARY KEY/NOT NULL UNIQUE/NOT NULL PRIMARY KEY

  (b)FOREIGN KEY(客戶號)REFERENCES客戶(客戶號)

  (C)CHECK(余額＞1.00)
[問題2]
      (d)支行.城市='天津市'
(e)交易.交易日期 LIKE'2009-09-%'或等價表示
注：(d)(e)次序無關
      (f)(SELECT*FROM 支行 WHERE城市='天津市')
(g)(SELECT*FROM 交易 WHERE交易日期LIKE'2009-09%')或等價表示
(h)客戶號IN
      (i)HAVING SUM(余額)＞1000000.00或等價表示 (i)客戶號 DESC
[問題3]
      (k)ADD賬戶標記INT (1)SET賬戶標記=0;
      (m)AFTER INSERT
(n)新交易.金額＞100000.00或等價表示

  (o)賬戶.賬戶號=新交易.賬戶號

>>>>>>>>>>點擊繼續答題

>>>>>>>>>>點擊返回目錄

  相關鏈接：

  數據庫系統工程師考試知識點分析與真題詳解（第4版）
  數據庫系統工程師考試歷年試題分析與解答（第4版）
  數據庫系統工程師考試下午知識點精講與考前必練
  數據庫系統工程師考試考前串講