SQL上机模拟习题.doc

数据库软件SQL SERVERA卷考试说明：在F：盘新建一个以考生的学号和姓名命名的文件夹（例如2007001张三），将所做的数据库以及所有的SQL文本文件均存放在此文件夹中。（第一题为数据库，第二、三、四和五题均存为一个SQL文本文件，请标明题目编号）一、（30分）操作题创建数据库：在该文件夹下创建一个为“library”的数据库，主文件的初始大小为3MB，增长方式为10增长，最大容量为10MB。日志文件初始大小为3MB，增长方式为1MB，最大容量为10MB。“library”中包含的数据“book”表有如下结构：（bookid为主键）字段名称数据类型字段大小允许空DEFAULTCHECK表达式约束名备注bookidchar20书编号bnamechar20书名authorchar20作者priceint大于零ck_price价钱classChar10类别publishchar30出版社editionchar10版本号contentchar200内容简介“reader”表结构：（readerid为主键）字段名称数据类型字段大小允许空备注readeridchar20读者编号rnamechar20读者姓名sexchar2性别departmentchar20所在部门“borrow”表结构（bookid和readerid为主键）字段名称数据类型字段大小允许空备注bookidchar20书编号readeridchar20读者编号statuschar4状态datedatetime8借阅日期（1） 将“book”表中的记录导入到“book.txt”文件中（2） 创建一视图view_borrow，要求包含借阅者的姓名、所借阅图书名和借阅日期 “library”表记录如下：bookidbnameauthorpriceclasspublisheditioncontent100001C程序设计王宇天24计算机中国水利出版社2003第1版使用通俗语言介绍C程序语言100002VB程序设计语言张大海19计算机清华大学出版社2004第2版100003大学英语宋美美34英语北大出版社2010第1版100004机床加工张小梅27建筑电子工业出版社2009第1版“reader”表记录readeridrnamesexdepartment1001陈芳女计算机1002罗军军女机电1003黎明男外语1004刘凡易男信息“borrow”表记录bookidreaderidstatusdate1000011001借阅2012-5-301000021004续借2013-2-131000031004借阅2013-1-191000041002借阅2013-10-22二、（40分）编写查询语句（要求所有命令存储于SQL的文本文件中，文件名为“SQL代码”）（1）向“book”表中添加一条记录，书号为100005，书名计算机网络基础，作者孙超（2）修改“reader”表中记录，将姓刘凡易的所在部门改为水建（3）查询所有未借阅图书的所有读者姓名（4）查询至少借阅了两门图书的读者的姓名（5）查询借阅了“100001”和未借阅“100002”图书的读者姓名（6）查询出借阅了图书的各个部门的人数（7）查询和“大学英语”出版社相同的图书名称（8）查询比所有图书平均价低的图书的编号（9）查询书名中包含有“计算机”的书名和作者（10）查询各个出版社中图书最高价格、最低价格和图书数目三、（10分）编写存储过程要求利用读者姓名查询出该读者所借阅图书的书名、出版社和价格，并给出“罗军军”读者的相关信息。四、（10分）编写触发器为“borrow”表创建一触发器，保证插入记录图书编号和读者编号必须存在于book和reader表中。五、（10分）编写一内嵌表值函数要求根据图书名，查询所有借阅该图书的读者信息，包括：readerid、rname、sex和department。6、 T-SQL程序题-2.1insert into book(bookid,bname,author) values(100005,计算机网络基础,孙超)-2.2update reader set department=水建 where rname=刘凡易-2.3select rname from reader left join borrow on reader.readerid=borrow.readerid where bookid is null-2.4select rname from borrow,reader where borrow.readerid=reader.readerid group by borrow.readerid,rname having count(*)=2-2.5select rname from borrow b1,borrow b2,reader where b1.readerid=b2.readerid and b1.bookid=100001 and b2.bookid=100002 and b1.readerid=reader.readerid-2.6select department,count(distinct borrow.readerid) as 借阅人数 from borrow,reader where borrow.readerid=reader.readerid group by department-2.7select bname from book b1,book b2 where b1.bname=大学英语 and b2.publish=b1.publish and b2.bname大学英语-2.8select bookid from book where price(select avg(price)from book)-2.9select bname,author from book where bname like %计算机%-2.10查询各个出版社中图书最高价格、最低价格和图书数目select publish,max(price) as 最高价格,min(price)as 最低价格,count(*)as 图书数目 from book where publish is not null group by publish-3.存储过程,要求利用读者姓名查询出该读者所借阅图书的书名、出版社和价格，并给出“罗军军”读者的相关信息。use libraryif object_id(pro_1,p) is not null drop procedure pro_1gocreate procedure pro_1(rname char(20)ASBEGINselect bname,publish,price from reader,borrow,book where reader.readerid=borrow.readerid and borrow.bookid=book.bookid and rname=rnameENDGOexec pro_1 罗军军-4.触发器，为“borrow”表创建一触发器，保证插入记录图书编号和读者编号必须存在于book和reader表中。if object_id(tri_1,tr)is not nulldrop trigger tri_1GOCREATE TRIGGER tri_1 on borrow for insert AS BEGINif exists (select * from insertedwhere inserted.bookid not in (select bookid from book)or inserted.readerid not in (select readerid from reader)begin rollback transactionPRINT 图书表或读者表中没有相关的记录endendgoinsert into borrow(bookid,readerid) values(991101,00000)-5.内嵌表值函数-要求根据图书名，查询所有借阅该图书的读者信息，包括：readerid、rname、sex和department。if object_id(fun_1,if)is not nulldrop function fun_1GOcreate function fun_1(bname char(20)RETURNS TABLE ASR