SQL SERVER练习题及答案.doc
题目1:问题描述:为管理岗位业务培训信息,建立3个表:S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (C#,CN ) C#,CN 分别代表课程编号、课程名称SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 1. 使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名 -实现代码:SELECT SN,SD FROM SWHERE S# IN( SELECT S# FROM C,SC WHERE C.C#=SC.C# AND CN=N'税收基础')2. 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位-实现代码:SELECT S.SN,S.SD FROM S,SCWHERE S.S#=SC.S# AND SC.C#='C2'3. 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位-实现代码:SELECT SN,SD FROM SWHERE S# NOT IN( SELECT S# FROM SC WHERE C#='C5')4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位-实现代码:SELECT SN,SD FROM SWHERE S# IN( SELECT S# FROM SC RIGHT JOIN C ON SC.C#=C.C# GROUP BY S# HAVING COUNT(*)=COUNT(S#)5. 查询选修了课程的学员人数-实现代码:SELECT 学员人数=COUNT(DISTINCT S#) FROM SC6. 查询选修课程超过5门的学员学号和所属单位-实现代码:SELECT SN,SD FROM SWHERE S# IN( SELECT S# FROM SC GROUP BY S# HAVING COUNT(DISTINCT C#)>5)题目2问题描述:本题用到下面三个关系表: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语句,要求定义主码完整性约束和引用完整性约束-实现代码:CREATE TABLE BORROW( CNO int FOREIGN KEY REFERENCES CARD(CNO), BNO int FOREIGN KEY REFERENCES BOOKS(BNO), RDATE datetime, PRIMARY KEY(CNO,BNO) 2. 找出借书超过5本的读者,输出借书卡号及所借图书册数-实现代码:SELECT CNO,借图书册数=COUNT(*)FROM BORROWGROUP BY CNOHAVING COUNT(*)>53. 查询借阅了"水浒"一书的读者,输出姓名及班级-实现代码:SELECT * FROM CARD cWHERE EXISTS( SELECT * FROM BORROW a,BOOKS b WHERE a.BNO=b.BNO AND b.BNAME=N'水浒' AND a.CNO=c.CNO) 4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期-实现代码:SELECT * FROM BORROW WHERE RDATE<GETDATE() 5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者-实现代码:SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE BNAME LIKE N'%网络%' 6. 查询现有图书中价格最高的图书,输出书名及作者-实现代码:SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE PRICE=( SELECT MAX(PRICE) FROM BOOKS) 7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出-实现代码:SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO AND b.BNAME=N'计算方法' AND NOT EXISTS( SELECT * FROM BORROW aa,BOOKS bb WHERE aa.BNO=bb.BNO AND bb.BNAME=N'计算方法习题集' AND aa.CNO=a.CNO)ORDER BY a.CNO DESC 8. 将"C01"班同学所借图书的还期都延长一周-实现代码:UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)FROM CARD a,BORROW bWHERE a.CNO=b.CNO AND a.CLASS=N'C01' 9. 从BOOKS表中删除当前无人借阅的图书记录-实现代码:DELETE A FROM BOOKS aWHERE NOT EXISTS( SELECT * FROM BORROW WHERE BNO=a.BNO) 10. 如果经常按书名查询图书信息,请建立合适的索引-实现代码:CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)11. 在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)-实现代码:CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATEASIF ROWCOUNT>0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE i.BNO=b.BNO AND b.BNAME=N'数据库技术及应用' 12. 建立一个视图,显示"力01"班学生的借书信息(只要求显示姓名和书名)-实现代码:CREATE VIEW V_VIEWASSELECT a.NAME,b.BNAMEFROM BORROW ab,CARD a,BOOKS bWHERE ab.CNO=a.CNO AND ab.BNO=b.BNO AND a.CLASS=N'力01'13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出-实现代码:SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO AND b.BNAME IN(N'计算方法',N'组合数学')GROUP BY a.CNOHAVING COUNT(*)=2ORDER BY a.CNO DESC 14. 假定在建BOOKS表时没有定义主码,写出为BOOKS表追加定义主码的语句-实现代码:ALTER TABLE BOOKS ADD PRIMARY KEY(BNO) 15.1 将NAME最大列宽增加到10个字符(假定原为6个字符)-实现代码:ALTER TABLE CARD ALTER COLUMN NAME varchar(10) 15.2 为该表增加1列NAME(系名),可变长,最大20个字符-实现代码:ALTER TABLE CARD ADD 系名 varchar(20)10 0(题 理 位 :代培实字0,变名立 该:为.) # , :现-#字个原 字个 增列 ( 实-的义追 为码义表 建假. * . 学数'算' = , :代-出排卡并号其,书本学"和方"借时当.'0' = . :-)书姓求(借生"力示显个建 '应技据 . _ 0 :-) 同 _ 中 保阅者将"应术据是书阅读功成,触一表 _ 代-引的建,图询书果. :现-记的借当中 从 '' = , 现一都书借班0. '习计 . .= 法= 实-出序卡,借其者习算"没方前询 ) ) 码实-作出书的格图现查 % , 现实作书书,的关络"名查.)( * :现日还)(借输还过询. . . '水 = 码-级名输读的浒借查.> * =图 代实数书及号出, 过借 ,( :-束整引约完义求语 立写.。0大长)( 表 。符 定符0增列 将 改修做 句语定追 ,码有表 建假 。序升号,书输者的合""算计借同询 书名显(书生班0,个建 表 结 表 录阅者就及库"名书读如下完发一建 在 。引适,信查按经0 录图阅无除中 从 。一延的借同0将 。出排按号卡其者的题算没算"前当 作书出的最书现查者、书书词"括名询 。日及、号借,还期 。班姓输读""查 册借所书,的过书找。束用束整主求句语的 建 理 如实。而还书数本借种人:备期 ,书 录 数 单 ,者 名 号 图 级 姓 卡 卡 系三到题述问目 # # :-属学学 程询. )# 数学 实员的修查 =* # # 码-单所姓的部选语 用.) # , 实属名学 编选询句 准 # =. . :码-和姓的号修查套 标 基 # # ( 码现-姓学员础称修询套 准使 绩、号的、表分 #( 称、程课 员、所姓员学代