SQL Server 练习题3

<iframe align="top" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog01.html" frameborder="0" width="728" scrolling="no" height="90"></iframe>

题目3

问题描述：
本题用到下面三个关系表：
CARD借书卡。CNO卡号，NAME姓名，CLASS班级
BOOKS图书。BNO书号，BNAME书名,AUTHOR作者，PRICE单价，QUANTITY库存册数
BORROW借书记录。CNO借书卡号，BNO书号，RDATE还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：
1．写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
2．找出借书超过5本的读者,输出借书卡号及所借图书册数。
3．查询借阅了"水浒"一书的读者，输出姓名及班级。
4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
5．查询书名包括"网络"关键词的图书，输出书号、书名、作者。
6．查询现有图书中价格最高的图书，输出书名及作者。
7．查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。
8．将"C01"班同学所借图书的还期都延长一周。
9．从BOOKS表中删除当前无人借阅的图书记录。
10．如果经常按书名查询图书信息，请建立合适的索引。
11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
12．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。
13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
15．对CARD表做如下修改：
a.将NAME最大列宽增加到10个字符（假定原为6个字符）。
b.为该表增加1列NAME（系名），可变长，最大20个字符。

1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束

--实现代码：
CREATETABLEBORROW(
CNOintFOREIGNKEYREFERENCESCARD(CNO),
BNOintFOREIGNKEYREFERENCESBOOKS(BNO),
RDATEdatetime,
PRIMARYKEY(CNO,BNO))

2. 找出借书超过5本的读者,输出借书卡号及所借图书册数

--实现代码：
SELECTCNO,借图书册数=COUNT(*)
FROMBORROW
GROUPBYCNO
HAVINGCOUNT(*)>5

3. 查询借阅了"水浒"一书的读者，输出姓名及班级

--实现代码：
SELECT*FROMCARDc
WHEREEXISTS(
SELECT*FROMBORROWa,BOOKSb
WHEREa.BNO=b.BNO
ANDb.BNAME=N'水浒'
ANDa.CNO=c.CNO)

4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期

--实现代码：
SELECT*FROMBORROW
WHERERDATE<getdate><p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">5. 查询书名包括"网络"关键词的图书，输出书号、书名、作者</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>SELECTBNO,BNAME,AUTHORFROMBOOKS<br>WHEREBNAMELIKEN'%网络%' </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">6. 查询现有图书中价格最高的图书，输出书名及作者</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>SELECTBNO,BNAME,AUTHORFROMBOOKS<br>WHEREPRICE=(<br>SELECTMAX(PRICE)FROMBOOKS) </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>SELECTa.CNO<br>FROMBORROWa,BOOKSb<br>WHEREa.BNO=b.BNOANDb.BNAME=N'计算方法'<br>ANDNOTEXISTS(<br>SELECT*FROMBORROWaa,BOOKSbb<br>WHEREaa.BNO=bb.BNO<br>ANDbb.BNAME=N'计算方法习题集'<br>ANDaa.CNO=a.CNO)<br>ORDERBYa.CNODESC </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">8. 将"C01"班同学所借图书的还期都延长一周</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>UPDATEbSETRDATE=DATEADD(Day,7,b.RDATE)<br>FROMCARDa,BORROWb<br>WHEREa.CNO=b.CNO<br>ANDa.CLASS=N'C01' </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">9. 从BOOKS表中删除当前无人借阅的图书记录</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>DELETEAFROMBOOKSa<br>WHERENOTEXISTS(<br>SELECT*FROMBORROW<br>WHEREBNO=a.BNO) </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">10. 如果经常按书名查询图书信息，请建立合适的索引</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>CREATECLUSTEREDINDEXIDX_BOOKS_BNAMEONBOOKS(BNAME)</p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">11. 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>CREATETRIGGERTR_SAVEONBORROW<br>FORINSERT,UPDATE<br>AS<br>IF@@ROWCOUNT&gt;0<br>INSERTBORROW_SAVESELECTi.*<br>FROMINSERTEDi,BOOKSb<br>WHEREi.BNO=b.BNO<br>ANDb.BNAME=N'数据库技术及应用' </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">12. 建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>CREATEVIEWV_VIEW<br>AS<br>SELECTa.NAME,b.BNAME<br>FROMBORROWab,CARDa,BOOKSb<br>WHEREab.CNO=a.CNO<br>ANDab.BNO=b.BNO<br>ANDa.CLASS=N'力01'</p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>SELECTa.CNO<br>FROMBORROWa,BOOKSb<br>WHEREa.BNO=b.BNO<br>ANDb.BNAMEIN(N'计算方法',N'组合数学')<br>GROUPBYa.CNO<br>HAVINGCOUNT(*)=2<br>ORDERBYa.CNODESC </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>ALTERTABLEBOOKSADDPRIMARYKEY(BNO) </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>ALTERTABLECARDALTERCOLUMNNAMEvarchar(10) </p> <p><span style="BORDER-RIGHT: #999999 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #999999 1px solid; PADDING-LEFT: 5px; FONT-WEIGHT: bold; FONT-SIZE: 12px; BACKGROUND-IMAGE: url(http://blog.csdn.net/images/blog_csdn_net/zjcxc/36941/o_xgart_bg.gif); PADDING-BOTTOM: 5px; MARGIN: 0px; BORDER-LEFT: #999999 1px solid; WIDTH: auto; LINE-HEIGHT: 16px; PADDING-TOP: 5px; BORDER-BOTTOM: #999999 1px solid; BACKGROUND-REPEAT: repeat-x; HEIGHT: 28px">15.2 为该表增加1列NAME（系名），可变长，最大20个字符</span></p> <p style='BORDER-RIGHT: #0099cc 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: #0099cc 1px solid; PADDING-LEFT: 5px; FONT-SIZE: 12px; PADDING-BOTTOM: 5px; MARGIN: 5px; BORDER-LEFT: #0099cc 1px solid; WIDTH: auto; COLOR: #000000; LINE-HEIGHT: 18px; PADDING-TOP: 5px; BORDER-BOTTOM: #0099cc 1px solid; FONT-FAMILY: " BACKGROUND-COLOR: #ddedfb; Verdana: ; quot: ; Courier: ; mono: '>--实现代码：<br>ALTERTABLECARDADD系名varchar(20)</p> <br><br><p id="TBPingURL">Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=385114</p> <br></getdate>