SQL Server 练习题3

题目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()

5. 查询书名包括"网络"关键词的图书，输出书号、书名、作者

--实现代码：
SELECTBNO,BNAME,AUTHORFROMBOOKS
WHEREBNAMELIKEN'%网络%'

6. 查询现有图书中价格最高的图书，输出书名及作者

--实现代码：
SELECTBNO,BNAME,AUTHORFROMBOOKS
WHEREPRICE=(
SELECTMAX(PRICE)FROMBOOKS)

7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出

--实现代码：
SELECTa.CNO
FROMBORROWa,BOOKSb
WHEREa.BNO=b.BNOANDb.BNAME=N'计算方法'
ANDNOTEXISTS(
SELECT*FROMBORROWaa,BOOKSbb
WHEREaa.BNO=bb.BNO
ANDbb.BNAME=N'计算方法习题集'
ANDaa.CNO=a.CNO)
ORDERBYa.CNODESC

8. 将"C01"班同学所借图书的还期都延长一周

--实现代码：
UPDATEbSETRDATE=DATEADD(Day,7,b.RDATE)
FROMCARDa,BORROWb
WHEREa.CNO=b.CNO
ANDa.CLASS=N'C01'

9. 从BOOKS表中删除当前无人借阅的图书记录

--实现代码：
DELETEAFROMBOOKSa
WHERENOTEXISTS(
SELECT*FROMBORROW
WHEREBNO=a.BNO)

10. 如果经常按书名查询图书信息，请建立合适的索引

--实现代码：
CREATECLUSTEREDINDEXIDX_BOOKS_BNAMEONBOOKS(BNAME)

11. 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）

--实现代码：
CREATETRIGGERTR_SAVEONBORROW
FORINSERT,UPDATE
AS
IF@@ROWCOUNT>0
INSERTBORROW_SAVESELECTi.*
FROMINSERTEDi,BOOKSb
WHEREi.BNO=b.BNO
ANDb.BNAME=N'数据库技术及应用'

12. 建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）

--实现代码：
CREATEVIEWV_VIEW
AS
SELECTa.NAME,b.BNAME
FROMBORROWab,CARDa,BOOKSb
WHEREab.CNO=a.CNO
ANDab.BNO=b.BNO
ANDa.CLASS=N'力01'

13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出

--实现代码：
SELECTa.CNO
FROMBORROWa,BOOKSb
WHEREa.BNO=b.BNO
ANDb.BNAMEIN(N'计算方法',N'组合数学')
GROUPBYa.CNO
HAVINGCOUNT(*)=2
ORDERBYa.CNODESC

14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句

--实现代码：
ALTERTABLEBOOKSADDPRIMARYKEY(BNO)

15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）

--实现代码：
ALTERTABLECARDALTERCOLUMNNAMEvarchar(10)

15.2 为该表增加1列NAME（系名），可变长，最大20个字符

--实现代码：
ALTERTABLECARDADD系名varchar(20)