SQL-SERVER练习题及答案

题目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嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名--实现代码：SELECTSN,SDFROMSWHERE[S#]IN(SELECT[S#]FROMC,SCWHEREC.[C#]=SC.[C#]ANDCN=N\'税收基础\')2.使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位--实现代码：SELECTS.SN,S.SDFROMS,SCWHERES.[S#]=SC.[S#]ANDSC.[C#]=\'C2\'3.使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位--实现代码：SELECTSN,SDFROMSWHERE[S#]NOTIN(SELECT[S#]FROMSCWHERE[C#]=\'C5\')4.使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位--实现代码：SELECTSN,SDFROMSWHERE[S#]IN(SELECT[S#]FROMSCRIGHTJOINCONSC.[C#]=C.[C#]GROUPBY[S#]HAVINGCOUNT(*)=COUNT([S#]))5.查询选修了课程的学员人数--实现代码：SELECT学员人数=COUNT(DISTINCT[S#])FROMSC6.查询选修课程超过5门的学员学号和所属单位--实现代码：SELECTSN,SDFROMSWHERE[S#]IN(SELECT[S#]FROMSCGROUPBY[S#]HAVINGCOUNT(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语句，要求定义主码完整性约束和引用完整性约束--实现代码：CREATETABLEBORROW(CNOintFOREIGNKEYREFERENCESCARD(CNO),BNOintFOREIGNKEYREFERENCESBOOKS(BNO),RDATEdatetime,PRIMARYKEY(CNO,BNO))2.找出借书超过5本的读者,输出借书卡号及所借图书册数--实现代码：SELECTCNO,借图书册数=COUNT(*)FROMBORROWGROUPBYCNOHAVINGCOUNT(*)53.查询借阅了\水浒\一书的读者，输出姓名及班级--实现代码：SELECT*FROMCARDcWHEREEXISTS(SELECT*FROMBORROWa,BOOKSbWHEREa.BNO=b.BNOANDb.BNAME=N\'水浒\'ANDa.CNO=c.CNO)4.查询过期未还图书，输出借阅者（卡号）、书号及还书日期--实现代码：SELECT*FROMBORROWWHERERDATEGETDATE()5.查询书名包括\网络\关键词的图书，输出书号、书名、作者--实现代码：SELECTBNO,BNAME,AUTHORFROMBOOKSWHEREBNAMELIKEN\'%网络%\'6.查询现有图书中价格最高的图书，输出书名及作者--实现代码：SELECTBNO,BNAME,AUTHORFROMBOOKSWHEREPRICE=(SELECTMAX(PRICE)FROMBOOKS)7.查询当前借了\计算方法\但没有借\计算方法习题集\的读者，输出其借书卡号，并按卡号降序排序输出--实现代码：SELECTa.CNOFROMBORROWa,BOOKSbWHEREa.BNO=b.BNOANDb.BNAME=N\'计算方法\'ANDNOTEXISTS(SELECT*FROMBORROWaa,BOOKSbbWHEREaa.BNO=bb.BNOANDbb.BNAME=N\'计算方法习题集\'ANDaa.CNO=a.CNO)ORDERBYa.CNODESC8.将\C01\班同学所借图书的还期都延长一周--实现代码：UPDATEbSETRDATE=DATEADD(Day,7,b.RDATE)FROMCARDa,BORROWbWHEREa.CNO=b.CNOANDa.CLASS=N\'C01\'9.从BOOKS表中删除当前无人借阅的图书记录--实现代码：DELETEAFROMBOOKSaWHERENOTEXISTS(SELECT*FROMBORROWWHEREBNO=a.BNO)10.如果经常按书名查询图书信息，请建立合适的索引--实现代码：CREATECLUSTEREDINDEXIDX_BOOKS_BNAMEONBOOKS(BNAME)11.在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是\数据库技术及应用\，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）--实现代码：CREATETRIGGERTR_SAVEONBORROWFORINSERT,UPDATEASIF@@ROWCOUNT0INSERTBORROW_SAVESELECTi.*FROMINSERTEDi,BOOKSbWHEREi.BNO=b.BNOANDb.BNAME=N\'数据库技术及应用\'12.建立一个视图，显示\力01\班学生的借书信息（只要求显示姓名和书名）--实现代码：CREATEVIEWV_VIEWASSELECTa.NAME,b.BNAMEFROMBORROWab,CARDa,BOOKSbWHEREab.CNO=a.CNOANDab.BNO=b.BNOANDa.CLASS=N\'力01\'13.查询当前同时借有\计算方法\和\组合数学\两本书的读者，输出其借书卡号，并按卡号升序排序输出--实现代码：SELECTa.CNOFROMBORROWa,BOOKSbWHEREa.BNO=b.BNOANDb.BNAMEIN(N\'计算方法\',N\'组合数学\')GROUPBYa.CNOHAVINGCOUNT(*)=2ORDERBYa.CNODESC14.假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句--实现代码：ALTERTABLEBOOKSADDPRIMARYKEY(BNO)15.1将NAME最大列宽增加到10个字符（假定原为6个字符）--实现代码：ALTERTABLECARDALTERCOLUMNNAMEvarchar(10)15.2为该表增加1列NAME（系名），可变长，最大20个字符--实现代码：ALTERTABLECARDADD系名varchar(20)