数据库系统原理与设计实验教程答案 实验6数据更新操作.doc

实验六 数据更新操作根据BookDB中4张关系表，完成以下更新操作：(1) 分别给这4张表添加信息，要求图书分类表、图书表、读者表各插入5个元组，借阅表插入20个元组。答：详细见BookDB.sql (2) 将联合股份有限公司的读者工作单位修改为联合立华股份有限公司。update Reader48set workunit48=联合立华股份有限公司where workunit48=联合股份有限公司(3) 将入库数量最多的图书单价下调5%。update Book48set price48=price48*0.95where shopNum48=(select max(shopNum48)from Book48) (4) 将“经济类”的图书单价提高10%。update Book48 set price48=price48*1.1where classNo48=(select distinct a.classNo48from Book48 a, BookClass48 bwhere a.classNo48=b.classNo48 and b.className48=经济类)(5) 将借阅次数高于2次的图书数量增加50%。update Book48set shopNum48=shopNum48*1.5where bookNo48 in(select bookNo48from borrow48group by bookNo48having count(bookNo48)=2)(6) 将富士康科技集团读者的借书期限延长至3个月。update borrow48set shouldDate48=dateadd(m,3,borrowDate48)where readerNo48 in (select a.readerNo48from borrow48 a, reader48 bwhere a.readerNo48=b.readerNo48 and workUnit48=富士康科技集团)(7) 根据借阅表修改每个读者的借书数量。update reader48set borrowCount48=numberfrom reader48 a,(select readerNo48,count(readerNo48)numberfrom borrow48group by readerNo48) bwhere a.readerNo48=b.readerNo48 (8) 删除价格超过50元的图书借阅信息。delete from borrow48where bookNo48 in (select bookNo48from book48where price4850)(9) 删除借阅了大学英语的借阅记录。delete from borrow48where bookNo48 =(select bookNo48from book48where bookName48=大学英语)(10) 删除从未借过书的读者。d