数据库系统工程师认证之二_SQL应用与数据库操作_试题实例

《数据库系统工程师》认证近年试题实例By jhsun厦门理工学院计算机科学与技术系目录2012年软考数据库系统工程师试题 (3)2011年软考数据库系统工程师试题 (6)【参考答案】 (8)2010年软考数据库系统工程师试题 (9)【参考答案】 (11)2009年软考数据库系统工程师试题 (12)【参考答案】 (13)2008年软考数据库系统工程师试题 (15)试题二（共15分） (15)【参考答案】 (17)2012年软考数据库系统工程师试题阅读下列说明，回答问题1至问题5，将解答填入答题纸的对应栏内。

[说明]某企业网上销售管理系统的数据库部分关系模式如下所示：客户(客户号，姓名，性别，地址，邮编)产品(产品号，名称，库存，单价)订单(订单号，时间，金额，客户号)订单明细(订单号，产品号，数量)关系模式的主要属性及约束如表2-1所示。

表2-1关系模式的主要属性及约束关系名约束客户客户号唯一标识一位客户，客户性别取值为“男”或者“女’产品产品号唯一标识一个产品订单订单号唯一标识一份订单。

一份订单必须且仅对应一位客户，一份订单可由一到多条订单明细组成。

一位客户可以有多份订单。

订单明细一条订单明细对应一份订单中的一个产品客户、产品、订单和订单明细关系及部分数据分别如表2-2、2-3、2-4、2-5所示。

表2-2客户关系客户号姓名性别地址邮编Ol 王晓丽女南京路2号20000502 林俊杰男北京路18号200010表2-3产品关系产品号名称库存单价01 产品A 20 298.0002 产品B 50 168.00表2-4订单关系订单号时间金额客户号1001 2006.02.03 1268.00 01 1002 2006.02.03 298.00 02 表2-5订单明细关系订单号产品号数量1001 01 2 1001 02 4 1002 01 1[问题1](3分)以下是创建部分关系表的SQL语句，请将空缺部分补充完整。

CREATE TABLE 客户(客户号CHAR(5)___ NOT NULL UNIQUE ___姓名CHAR(30)，性别CHAR(2)___ CHECK（VALUE IN（'男'，'女'））___地址CHAR(30)，邮编CHAR(6));CREATE TABLE 订单(订单号CHAR(4)，时间 CHAR(10)，金额 NUMBER(6,2)，客户号 CHAR(5) NOT NULL，PRIMARY KEY(订单号)，___ FOREIGN KEY（客户号） REFERENCES 客户（客户号）___;[问题2](4分)请根据如下查询语句，回答问题(d)，(e)和(f)SELECT 客户号FROM 订单，订单明细WHERE 订单明细.订单号 = 订单.订单号AND 产品号 = '02’AND 数量>10；(d)上述查询语句的功能是什么?请简要回答。

(30个字以内)答：查询一次订购（或购买）产品号为02的数量大于10的客户号(e)将上述查询语句转换成对应的关系代数表达式。

答：π客户号（订单∞σ产品号='02'^数量>10（订单明细））(f)上述SQL查询语句是否可以进一步优化?如可以，给出优化后的SQL查询语句。

可以优化。

优化的SQL语句为：SELECT客户号FROM订单WHERE订单号IN（SELECT 订单号FROM 订单明细WHERE 产品号 = '02 ' AND数量>10）请按题意将下述SQL查询语句的空缺部分补充完整。

[问题3](3分)按客户购买总额的降序，输出每个客户的客户名和购买总额。

SELECT 客户.客户名，___ SUM（金额）AS总额___FROM 客户，订单WHERE 客户.客户号 = 订单.客户号_ GROUP BY 客户.客户号____ ORDER BY 总额 DESC ___；[问题4](3分)用SQL语句完成下述要求。

(1)定义一个描述订单的客户号和对应订单明细中产品号关系的视图，客户产品(客户号，产品号)。

CREATE VIEW 客户产品AS（SELECT 客户号，产品号FROM 订单，订单明细WHERE 订单明细.订单号=订单.订单号）(2)借助(1)所定义的视图，查询至少购买了01号客户购买的所有产品的客户号。

SELECT 客户号FROM 客户产品客户产品1WHERE___ NOT EXISTS ___( SELECT*FROM客户产品客户产品2WHERE___客户号='01' AND NOT EXISTS ___( SELECT*FROM客户产品客户产品3WHERE 客户产品1.客户号=客户产品3.客户号AND客户产品2.产品号=客户产品3.产品号))；[问题5](2分)当—个订单和对应的订单明细数据入库时，应该减少产晶关系中相应的产品库存，为此应该利用数据库管理系统的什么机制实现此功能?请用100字以内的文字简要说明。

2011年软考数据库系统工程师试题阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。

【说明】某法院要开发一个诉讼案件信息处理系统，该信息系统的部分关系模式如下：职工（职工编号，姓名，岗位）律师（律师编号，姓名）被告（被告编号，姓名，地址）案件（案件编号，案件类型，案件描述，被告，律师，主审法官，立案日期，状态，结案日期，结案摘要）审理（审理编号，案件编号，审理日期，摘要）有关关系模式的属性及相关说明如下：(1)职工关系模式的岗位有“法官”、“书记员”和“其他”。

(2)诉讼立案后，即在案件关系中插入一条相应记录。

案件关系模式的状态有“待处理”、“审理中”、“结案”和“撤销”，一个案件开始立案时其案件状态为“待处理”。

(3)案件关系模式的案件类型有“偷窃”、“纵火”等。

(4) -个案件自立案到结案的整个过程由一位法官和一位律师负责，一个案件通常经过一次到多次审理。

【问题1】（4分）假设案件编号唯一标识一个案件，且立案日期小于等于结案日期。

请将如下创建案件关系的SQL语句的空缺部分补充完整。

CREATE TABLE案件(案件编号CHAR(6) (a) ，案件类型VARCHAR(10),案件描述VARCHAR (200)，立案日期DATE ，被告VARCHAR (6) REFERENCES被告（被告编号），律师VARCHAR (6) REFERENCES律师（律师编号），主审法官VARCHAR (6) (b) ，状态VARCHAR (6) (c) DEFAULT‘待处理’，结案日期DATE,结案摘要VARCHAR (200)，(d) )；【问题2】（8分）请完成下列查询的SQL语句。

(1)查询当前待处理的诉讼案件，显示案件的案件编号、立案日期、被告姓名、被告地址、案件描述、律师姓名和主审法官姓名。

SELECT案件编号，立案日期，被告．姓名AS被告姓名，地址AS被告地址，案件描述，律师．姓名AS律师姓名， (e)FROM (f)WHERE案件.被告=被告.被告编号AND案件.律师=律师.律师编号AND (g) ；(2)查询2009年立案的各类案件数，并按案件数降序排序。

（日期格式举例：2009年1月1日表示为01-JAN-2009，2009年12月31日表示为31-DEC-2009）SELECT类型，count(*) AS案件数FROM 案件WHERE (h)GROUP BY类型(i) ;(3)查询立案次数超过5次的被告姓名和地址。

SELECT姓名，地址，count(*)FROM 案件，被告WHERE (j)GROUP BY (k) (1) ;【问题3】（3分）当插入一个审理记录时，检查案件的状态，若状态为“未处理”，则将其修改为“审理中”。

下面是用触发器实现该需求的SQL语句，请将空缺部分补充完整。

CREATE TRIGGER 审理TRIGGER AFTER (m)ON审理REFERENCING new row AS nrowFOR each rowWHEN‘未处理’=（SELECT状态 FROM案件 WHERE案件编号- nrow.案件编号）BEGINUPDATE案件 (n)WHERE (o) ；END【参考答案】2010年软考数据库系统工程师试题试题二（共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。