试题二（共 15 分）阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。【说明】天津市某银行信息系统的数据库部分关系模式如下所示：客户 （客户号，姓名，性别，地址，邮编，电话）账户 （账户号，客户号，开户支行号，余额）支行 （支行号，支行名称，城市，资产总额）交易 （交易号，账户号，业务金额，交易日期）其中，业务金额为正值表示客户向账户存款；为负值表示取款。【问题1】（3 分）以下是创建账户关系的SQL语句，账户号唯一识别一个账户，客户号为客户关系的唯一标识，且不能为空。账户余额不能小于1.0

正确答案：

 
试题二(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