试题二(共 15 分) 阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。 【说明】 天津市某

Question

试题二（共 15 分）

阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。

【说明】

天津市某银行信息系统的数据库部分关系模式如下所示：

客户 （客户号，姓名，性别，地址，邮编，电话）

账户 （账户号，客户号，开户支行号，余额）

支行 （支行号，支行名称，城市，资产总额）

交易 （交易号，账户号，业务金额，交易日期）

其中，业务金额为正值表示客户向账户存款；为负值表示取款。

【问题1】（3 分）

以下是创建账户关系的SQL语句，账户号唯一识别一个账户，客户号为客户关系的唯一标识，且不能为空。账户余额不能小于1.00元。请将空缺部分补充完整。

CREATE TABLE 账户(

账户号 CHAR(19) （a） ,

客户号 CHAR(10) （b） ,

开户支行号 CHAR(6) NOT NULL ,

余额 NUMBER(8,2) （c） );

【问题2】（7 分）

（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】（5 分）

（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 ;

Answer 1

正确答案：

 
试题二(15 分)
问题 1（3 分，各1 分）
（a）  PRIMARY KEY/NOT NULL UNIQUE/NOT NULL PRIMARY KEY
（b）  FOREIGN KEY(客户号)REFERENCES 客户(客户号)
（c）  CHECK(余额>1.00)
问题 2（7 分，各1 分）
（1）
（d）  支行.城市=’天津市’
（e）  交易.交易日期 LIKE  ‘2009-09%’
注（d）(e)次序无关
（f）  (SELECT*FROM  支行  WHERE  城市=’天津市’)
（g）  (SELECT*FROM  交易  WHERE  交易日期  LIKE  ‘2009-09%’)
（2）
(h)客户号  IN
(i) HAVING SUM(余额)>1000000.00
(j)客户号  DESC
问题 3（5 分，各1 分）
(1)
（k）ADD 账户标记  INT
(l)SET  账户标记=0；
(2)
(m)AFTER INSERT
(n)新交易.金额>1000000.00
（o）账户.账户号=新交易.账户号
试题二分析
    本题考查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条件语句进行约束；降序通过DES（关键字来实现。
      SELECT*
      FROM客户
      WHERE客户号工N
      （SELECT客户号FROM账户GROUP BY客户号HAVTNG SUM（余额）＞1000000.00）
      ORDER BY客户号DES；
【问题3】
    （l）关系模式的修改通过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