SQL SERVER程序设计复习题.doc

一、填空题（每空1分，共10分）1、在select语句中 子句可以在分组后进行数据筛选。2、在定义表结构时使用 子句实现对数据合法性的检查。3、在DML触发器中，DML命令操作的数据保存在 和 中。4数据完整性包括 完整性、 完整性、 完整性和 完整性。5、使用 运算符可以判断查询结果是否为空。6、判断某值X是否为空应使用表达式 。7、在select语句中 子句可以让检索结果顺序显示。8、可以在from子句中使用 运算符或在where子句中使用 实现表的连接。9、在存储过程的形参中使用 或 定义输出参数。10、级联修改可以通过 或 实现。11、在select语句中 子句可以实现分组操作。12、在定义表结构时使用 子句定义字段的默认值。13、在函数定义中使用 定义返回值。14、级联修改可以通过 或 实现。二、名词解释（每小题2分，3小题，共6分）1、数据库2、实体完整性3、事务4、外键5、死锁6、主键三、简答题（每小题3分，3小题，共9分）1、索引的作用2、DML触发器的用途3、SQL分为几类，每类常用的命令有哪些？4、索引的设计原则5、事务的属性6、简述各类数据完整性及其对应的约束实现7、存储过程的优点四、操作题（共45分）所有操作假设由sa账号用SQL语句完成。1、创建一个数据库：数据库名为“教学”，数据文件的逻辑名为“教学_data”、物理文件名为“教学_data.mdf”，大小为10MB，增量为10MB，最大容量为200MB。日志文件的逻辑名为“教学_log”、物理文件名为”“教学_log.ldf”。文件请保存在文件夹“e:sqltestdata”中。2、在SQL Serve中添加一个登录名“teacher”,密码为“abcd”。3、为“teacher”在“教学”数据库中添加数据库用户“sql_teacher”。4、在“教学”数据库中添加一个角色“visitor”。5、将安全账户“sql_teacher”添加到角色“visitor”中。6、禁止“visitor”创建数据表。7、在“教学”数据库中创建三个数据表： （1）学生（学号（长度为10的字符串，学号必须符合“08081”或“08082”模式(约束名为xh)，主键），姓名（长度为20的可变长度Unicode字符串，不允许为空），生日（日期时间）； （注：“”为任意数字） （2）课程（课程号（长度为4的固定长度字符串，主键），课程名（长度为20的可变长度Unicode字符串，不允许为空）； （3）成绩（学号（长度为10的字符串，外键），课程号（长度为4的字符串，外键），分数（小数，有效位数4，小数点后1位，取值范围在0100之间，默认值为0），主键为（学号+课程号）。 8、修改“学生”表：添加一列“性别”（只能取值“男”或“女”），并在“生日”上添加不对已有数据进行验证的数据校验约束“bircheck”，要求输入的数据需满足的条件是学生的年龄必须不小于14岁。 9、创建一个显示所有10级计算机系学生的SQL课程的成绩的视图V_SQL，其中包括学号，姓名，课程名，分数四列。 10、在课程表的课程号列上建一个降序索引kch_idx。 11、定义触发器xct：当在学生表中删除记录时，将成绩表中相应学生的记录删除 五、设计题（30分）在“教学”数据库中创建一个存储过程cj_proc，实现按照输入的课程名称打印此门课程的成绩报表（如不给定课程名称，则输出“SQL”的成绩），输出结果按照分数降序排列： 例如：exec cj_proc 数据结构结果如下。 数据结构成绩表* 名次 学号 姓名 成绩1 0808044126 李军 95 2 0808044124 李明 85 3 0808044125 王刚 75 *如不指定课程名，如：exec cj_proc结果如下。 SQL成绩表* 名次 学号 姓名 成绩1 0808044124 李明 95 2 0808044126 李军 85 3 0808044125 王刚 80 *2、阅读下列说明，回答问题1至问题3。 说明天津市某银行信息系统的数据库部分关系模式如下所示：客户 (客户号，姓名，性别，地址，邮编，电话)账户 (账户号，客户号，开户支行号，余额)支行(支行号，支行名称，城市，资产总额)交易 (交易号，账户号，业务金额，交易日期)其中，业务金额为正值表示客户向账户存款；为负值表示取款。问题1以下是创建账户关系的SQL语句，账户号唯一识别一个账户，客户号为客户关系的唯一标识，且不能为空。账户余额不能小于1.00元。请将空缺部分补充完整。CREATE TABLE账户(账户号CHAR(19) (a) ,客户号CHAR(10) (b) ;开户支行号CHAR(6) NOT NULL,余额NUMBER(8,2) (c) );问题2(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(1)为账户关系增加一个属性“账户标记”，缺省值为0，取值类型为整数；并将当前账户关系中所有记录的“账户标记”属性值修改为0。请补充相关SQL语句。ALTER TABLE 账户 (k) DEFAULT 0；UPDATE 账户 (l) ;(2)对于每笔金额超过10万元的交易，其对应账户标记属性值加1，给出触发器实现的方案。CREATE TRIGGER 交易_触发器 (m) ON交易REFERENCING NEW ROW AS 新交易FOR EACH ROWWHEN (n) BEGIN ATOMICUPDATE 账户 SET 账户标记=账户标记+1WHERE (o) ;COMMIT WORK;END3、定义一个函数TF，返回某门课程的课程编号、课程名称、最高分、最低分、平均分。 4、阅读下列说明，回答问题1至问题3。 说明 某网上书店后台数据库的部分关系模式如下： 会员(会员编号，用户名，密码，姓名；地址，邮编，电话，消费额，积分) 图书(图书编号，类型名称，图书名称，作者，出版社，出版日期，ISBN，价格) 订单(订单编号，用户名，销售额，订购日期，出货日期) 订单明细(订单明细编号，订单编号，图书编号，数量) 问题1 下面是创建订单关系的SQL语句，订单编号唯一识别一个订单，用户名为订购图书的会员用户名，且不能为空。要求订购日期不能大于出货日期。请将空缺部分补充完整。 CREATE TABLE 订单( 订单编号 CHAR(6) 用户名VARCHAR(40)NOT NULL ， 销售额FLOAT, 订购日期DATE NOT NULL， 出货日期DATE )； 问题2 请完成下列查询的SQL语句。 (1)查询名称中包含“数据库”的图书的图书名称，作者，出版社和出版日期。 SELECT FROM 图书 WHERE 图书名称 ； (2)查询提供销售(图书表中有)但没有销售过(没在订单明细表中出现)的图书名称和出版社。 SELECT 图书名称，出版社 FROM 图书 WHERE NOT EXISTS ( SELECT FROM 订单明细 WHERE )； (3)查询订购图书数量最多的会员名及其订购的数量。 SELECT 用户名, FROM订单，订单明细 WHERE GROUP BY 用户名 HAVING (SELECT SUM (数量) FROM 订单，订单明细 WHERE 订单.订单编号=订单明细订单编号 GROUP BY 用户名)； (4)为了统计会员的购买行为信息，实施有意义的客户关怀策略，查询会员的平均订购间隔时间，考虑多次购买图书和一次购买图书的情况(其中，DATEDIFF函数表示两个日期之间的天数)。 SELECT 用户名，CASE WHEN （a） THEN DATEDIFF (MAX (订购日期)，MIN (订购日期) / 12 (b) ELSE DATEDIFF(CURRENT_TIMESTAMP，MIN(订购日期) END AS AVG GAP FROM 订单 (c) ； 问题3 会员订购图书后，将本次订购的销售额累加到该会员的消费额中，并按照本次订单的销售额计算积分累加到该会员的积分中(每20元增加1个积分，不足20元不计入积分)。下面用触发器实现该需求，请填充空缺部分。 CREATE TRIGGER会员积分TRIGGER AFTER (d) REFERENCING NEW ROW AS NROW BEGIN UPDATE会员 SET消费额=消费额+NROW.销售额, (e) WHERE用户名=NROW用户名 END5、利用第四题中的课程和成绩表，定义一个函数TF，用于返回选修人数最多的n门课程的课程编号、课程名称和选修人数，结果按人数的降序排列。 6、定义一个函数TF，返回指定班级的平均分前十名的学生的名次、学号、姓名和平均分，结果按平均分的降序排列。 7、阅读以下说明，回答问题1至问题4。 【说明】某商场客户商品数据库中各关系模式如下：客户(客户号, 姓名, 性别, 年龄)商品(商品号, 名称, 型号, 品牌, 单价, 库存)销售(流水号, 客户号, 商品号, 数量, 日期)采购(商品号，数量)其中：1）一种品牌的同一名称商品可以有多个型号，商品的库存有大于等于0约束；2）销售表记录每一笔销售，每销售一件商品，其库存都要做相应的修改。现假定已经建立了该数据库及上述四个基本表。【问题1】（5分）(1) 客户关系中的年龄取值在15岁到60岁之间（包含15岁和60岁），增加该约束的SQL语句如下，请将空缺部分补充完整。ALTER TABLE 客户 ADD CONSTRAINT CONSTRAINT con_age CHECK (a) )(2) 如下用SQL语句创建的畅销商品视图包含商品号、商品名称、型号、品牌和销售量，该视图中商品的销售量大于等于1000件。请将空缺部分补充完整。CREATE VIEW 畅销商品 (b) ASSELECT 商品.商品号, 名称, 型号, 品牌, 销售量FROM 商品, ( SELECT 商品号, (c) AS 销售量FROM 销售GROUP BY 商品号HAVING SUM(数量) = 1000 ) AS 商品销售量WHERE (d) ; (3) 将视图畅销商品的查询权限赋予销售经理李华，请将空缺部分补充完整。GRANT (e) ON TABLE 畅销商品TO 李华;【问题2】（3分）查询购买“新飞”品牌的任一型号“冰箱”的客户姓名及购买日期。实现该查询的SQL语句如下，请将空缺部分补充完整。SELECT 姓名, 日期FROM (f) WHERE (g) AND 商品号 (h) (SELECT 商品号 FROM 商品WHERE 品牌 = 新飞 AND 名称 = 冰箱 )【问题3】（3分）实现销售业务的嵌入式SQL代码段(嵌入C语言)如下，假设销售表的流水号由系统自动生成。请将空缺部分补充完整。 EXEC SQL BEGIN DECLARE SECTION; /* 销售：商品号，客户号，数量，日期 */ char pno6; char cno6; int quantity; char date10;EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO DEFAULT; EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;EXEC SQL INSERT INTO 销售(商品号,客户号,数量,日期) VALUES( (i) ); EXEC SQL UPDATE 商品 SET 库存 =(j) WHERE 商品号 = :pno; if(SQLCA.SQLCODE != 0) printf(“商品%s库存不满足本次购买数量，交易失败！”, pno); EXEC SQL ROLLBACK WORK; else EXEC SQL (k) ; EX