>>>>>>>>>>點(diǎn)擊進(jìn)入數(shù)據(jù)庫系統(tǒng)工程師考試網(wǎng)絡(luò)課堂

>>>>>>>>>>點(diǎn)擊進(jìn)入數(shù)據(jù)庫系統(tǒng)工程師歷年真題題庫

>>>>>>>>>>點(diǎn)擊進(jìn)入數(shù)據(jù)庫工程師考試大綱教材

2010年上半年數(shù)據(jù)庫系統(tǒng)工程師考試真題

下午卷

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

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

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

>>>>>>>>>>點(diǎn)擊繼續(xù)答題

>>>>>>>>>>點(diǎn)擊返回目錄

  相關(guān)鏈接：

  數(shù)據(jù)庫系統(tǒng)工程師考試知識點(diǎn)分析與真題詳解（第4版）
  數(shù)據(jù)庫系統(tǒng)工程師考試歷年試題分析與解答（第4版）
  數(shù)據(jù)庫系統(tǒng)工程師考試下午知識點(diǎn)精講與考前必練
  數(shù)據(jù)庫系統(tǒng)工程師考試考前串講