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

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

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

2013上半年數(shù)據(jù)庫系統(tǒng)工程師考試真題及答案解析

下午卷

      試題二
閱讀以下說明，根據(jù)要求回答下列問題。
[說明]
某航空公司要開發(fā)一個訂票信息處理系統(tǒng)，該系統(tǒng)的部分關(guān)系模式如下：
  航班(航班編號，航空公司，起飛地，起飛時間，目的地，到達(dá)時間，票價)
      折扣(航班編號，開始日期，結(jié)束日期，折扣)
旅客(身份證號，姓名，性別，出生日期，電話，VIP折扣)
購票(購票單號，身份證號，航班編號，搭乘日期，購票金額)
有關(guān)關(guān)系模式的屬性及相關(guān)說明如下：
  4、航班表中的起飛時間和到達(dá)時間不包含日期，同一航班不會在一天出現(xiàn)兩次及兩次以上；
      5、各航空公司會根據(jù)旅客出行淡旺季適時調(diào)整機(jī)票的折扣，旅客購買機(jī)票的購票金額計算公式為：票價×折扣×VIP折扣，其中旅客的VIP折扣與該旅客已購買過的機(jī)票的購票金額總和相關(guān)，在旅客每次購票后被修改。VIP折扣值的計算由函數(shù)float vip_value(char[18]身份證號)完成。
根據(jù)以上描述，回答下列問題。
      4、請將如下創(chuàng)建購票關(guān)系的SQL語句的空缺部分補(bǔ)充完整，要求指定關(guān)系的主鍵、外鍵，以及購票金額大于零的約束。
      CREATE TABLE 購票(
      購票單號 CHAR(15) ______,
      身份證號 CHAR(18),
      航班編號 CHAR(6),
搭乘日期 DATE,
      購票金額 FLOAT ______, ______, ______, );
      5、(1)身份證號為210000196006189999的客戶購買了2013年2月18日CA5302航班的機(jī)票，購票單號由系統(tǒng)自動生成。下面的SQL語句將上述購票信息加入系統(tǒng)中，請將空缺部分補(bǔ)充完整。
  INSERT INTO 購票(購票單號,身份證號,航班編號,搭乘日期,購票金額)
  SELECT '201303105555','210000196006189999','CA5302','2013/2/18',
  ______
  FROM 航班,折扣,旅客
WHERE ______ AND 航班.航班編號='CA5302' AND
AND '2013/2/18' BETWEEN 折扣.開始日期 AND 折扣.結(jié)束日期
      AND 旅客.身份證號='210000196006189999';
      (2)需要用觸發(fā)器來實現(xiàn)VIP折扣的修改，調(diào)用函數(shù)vip_value()來實現(xiàn)。請將如下SQL語句的空缺部分補(bǔ)充完整。
      CREATE TRIGGER VIP_TRG AFTER ______ ON ______
RE FERENCING new  row AS  nrow
FOR EACH row
BEGIN
UPDATE 旅客
SET ______
WHERE ______;
      END
6、請將如下SQL語句的空缺部分補(bǔ)充完整。
(1)查詢搭乘日期在2012年1月1日至2012年12月31日之間，且合計購票金額大于等于10000元的所有旅客的身份證號、姓名和購票金額總和，并按購票金額總和降序輸出。
      SELECT 旅客.身份證號,姓名,SUM(購票金額)
      FROM 旅客,購票
      WHERE ______
GROUP BY ______;
      ORDER BY ______;
  (2)經(jīng)過中轉(zhuǎn)的航班與相同始發(fā)地和目的地的直達(dá)航班相比，會享受更低的折扣。查詢從廣州到北京，經(jīng)過一次中轉(zhuǎn)的所有航班對，輸出廣州到中轉(zhuǎn)地的航班編號、中轉(zhuǎn)地、中轉(zhuǎn)地到北京的航班編號。
      SELECT ______
      FROM 航班航班1,航班 航班2
      WHERE ______;
 

  參考答案及解析
      4、PRIMARY KEY(或NOT NULL UNIQUE)
      CHECK(購票金額＞0)
      FOREIGN KEY (身份證號) REFERENCES 旅客(身份證號)
      FOREIGN KEY (航班編號) REFERENCES 航班(航班編號)
  在創(chuàng)建“購票”關(guān)系模式的SQL語句時，由于屬性“購票單號”為“購票”關(guān)系模式的主鍵，即不能為空且標(biāo)識一條數(shù)據(jù)記錄，因此空缺處需要填入“PRIMARY KEY(或NOT NULL UNIQUE，或NOT NULL PRIMARY KEY)”對該屬性進(jìn)行主鍵約束。
      結(jié)合題干給出的關(guān)鍵信息“購票金額大于零”可知，空缺處應(yīng)填入“CHECK(購票金額＞0)”對屬性“購票金額”進(jìn)行約束。
由于屬性“身份證號”、“航班編號”是“購票”關(guān)系模式的外鍵，因此空缺處需要使用FOREIGN KEY對這兩個屬性進(jìn)行外鍵約束，即應(yīng)填入“FOREIGN KEY(身份證號)REFERENCES旅客(身份證號)”、“FOREIGN KEY(航班編號)REFERENCES航班(航班編號)”。
      5、票價*折扣*VIP折扣
航班.航班編號=折扣.航班編號
INSERT
  購票
VIP折扣=vip_value(nrow.身份證號)
      旅客.身份證號=nrow.身份證號
      (1)基于題干給出的關(guān)鍵信息“旅客購買機(jī)票的購票金額計算公式為：票價×折扣×VIP折扣”可知，(空缺處對應(yīng)填入INSERT INTO語句中“購票金額”的計算公式，即“票價*折扣*VIP折扣”。
      結(jié)合題干給出的“其中旅客的VIP折扣與該旅客已購買過的機(jī)票的購票金額總和相關(guān)，在旅客每次購票后被修改”等關(guān)鍵信息可知，該SELECT查詢語句中將涉及到“航班”、“折扣”兩個關(guān)系模式，因此空缺處應(yīng)對這二者進(jìn)行關(guān)聯(lián)，即應(yīng)填入“航班.航班編號=折扣.航班編號”。
      (2)使用T-SQL語句來創(chuàng)建觸發(fā)器的基本語句如下。
create trigger trigger_name on {table_name | view_name}  {for | After | Instead of}
      [insert，update，delete] as sql_statement
  在“購票”關(guān)系模式中插入一條數(shù)據(jù)記錄時，觸發(fā)器應(yīng)能夠自動執(zhí)行，因此需要創(chuàng)建基于INSERT類型的觸發(fā)器，即空缺處應(yīng)依次填入“INSERT”、“購票”。
根據(jù)題干給出的“VIP折扣值的計算由函數(shù)float vip_value(char[18]身份證號)完成”等關(guān)鍵信息可知，空缺處應(yīng)填入的觸發(fā)器執(zhí)行動作是“VIP折扣=vip_value(nrow.身份證號)”。
      空缺處應(yīng)添加表的連接條件——“旅客.身份證號=nrow.身份證號”。
      6、旅客.身份證號=購票.身份證號 AND
搭乘日期 BETWEEN '2012/1/1' AND '2012/12/31'
      旅客.身份證號,姓名 HAVING SUM(購票金額)＞=10000
      SUM(購票金額) DESC
航班1.航班編號,
航班1.目的地,
航班2.航班編號
      航班1.起飛地='廣州' AND 航班2.目的地='北京' AND
      航班1.目的地=航班2.起飛地;
(1)查詢搭乘日期在2012年1月1日至2012年12月31日之間，且合計購票金額大于等于10000元的所有旅客的身份證號、姓名和購票金額總和，并按購票金額總和降序輸出。
SELECT 旅客.身份證號,姓名,SUM(購票金額)
FROM 旅客,購票
      WHERE 旅客.身份證號=購票.身份證號 AND
  搭乘日期 BETWEEN '2012/1/1' AND '2012/12/31'
      GROUP BY 旅客.身份證號,姓名 HAVING SUM(購票金額)＞=10000;
      ORDER BY SUM(購票金額) DESC;
      (2)經(jīng)過中轉(zhuǎn)的航班與相同始發(fā)地和目的地的直達(dá)航班相比，會享受更低的折扣。查詢從廣州到北京，經(jīng)過一次中轉(zhuǎn)的所有航班對，輸出廣州到中轉(zhuǎn)地的航班編號、中轉(zhuǎn)地、中轉(zhuǎn)地到北京的航班編號。
      SELECT 航班1.航班編號,航班1.目的地,航班2.航班編號
      FROM 航班航班1,航班航班2
      WHERE 航班1.起飛地='廣州' AND 航班2.目的地='北京' AND
    航班1.目的地=航班2.起飛地

>>>>>>>>>>點擊繼續(xù)答題

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

相關(guān)鏈接：

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