MYSQL练习题及答案

题目一问题描述：为管理岗位业务培训信息，建立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,S#FROMSWHERE[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)题目二问题描述：本题用到下面三个关系表：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)题目三假设有下面两个关系模式：职工（职工号、姓名、年龄、职务、工资、部门号），其中职工号为主码；部门（部门号、名称、部门经理、电话），其中部门号为主码；用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：定义每个模式的主码和非空约束；定义参照完整性；定义职工年龄不得超过60岁。答：Createtablebm（bmnochar（10）primarykey，Bmnamechar（20）notnull，Bmjlchar（10）notnull，Bmtelchar（10）unique）；Createtablezhigong（zgnochar（10），Zgnamechar（20），Zgageint，Zgsalnumeric(10),Bmnochar(10),Primarykey(zgno),Foreignkey(bmno)referencebm(bmno)OndeletenoactionOnupdatecascade,Constraintzg1check(zgage=60));题目四1、有以下三个关系模式：学生关系S，包括学号SNo、姓名SN、年龄SA、系别SD；课程关系C，包括课程号CNo、课程名CN、学分CC；学生选课关系SC，包括SNo、CNo和成绩G。（1）用SQL语言定义SC表，注意：说明主键码和外键码(如果有的话)。（2）对于学生选课关系SC，授予李小兰删除权限以及对SNo和CNo的修改权限。（3）删除学号为“9900205”的学生。（4）从学生选课关系SC中，删除王云(学生关系中可能有重名)的所有选课。答：（1）CREATETABLESC{SNoINT,CNoINT,GINT,PRIMARYKEY(SNo,CNo),FOREIGNKEY(SNo)REFERENCESS(SNo),FOREIGNKEY(CNo)REFERENCESC(CNo)};（3分）（2）GRANTDELETE,UPDATE(SNo,CNo)ONSCTO李小兰;（3分）（3）DELETEFROMSWHERESno=9900205;（3分）（4）DELETEFROMSCWHERESNoIN(SELECTSNoFROMSWHERESN=‘王云’);题目五设某商业集团关于商店销售商品的数据库中有三个基本表：商店SHOP（S#，SNAME，AREA，MGR_NAME）其属性是商店编号，商店名称，区域名，经理姓名。销售SALE（S#，G#，QUANTITY）其属性是商店编号，商品编号，销售数量。商品GOODS（G#，GNAME，PRICE）其属性是商品编号，商品名称，单价。（1）写出下列查询的关系代数表达式：检索销售“冰箱”的商店的编号和商店名称。（2）从SALE表中，把“开开商店”中销售单价高于1000元的商品的销售元组全部删除。（3）写出下列操作的SQL语句：统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。（1）关系代数表达式：πS#，SNAME（σGNAME='冰箱'（SHOP⋈SALE⋈GOODS））（3分）（2）DELETEFROMSALEWHERES#IN（SELECTS#FROMSHOPWHERESNAME='开开商店'）ANDG#IN（SELECTG#FROMGOODSWHEREPRICE1000）；（3）SELECTC.G#，GNAME，SUM（QUANTITY）ASSUM_QUANTITY，P