数据库系统原理与设计第2版 万常选版 第3章 SQL课后答案.docx

数据库系统原理与设计第2版 万常选版 第3章 SQL课后答案.docx

《数据库系统原理与设计第2版 万常选版 第3章 SQL课后答案.docx》由会员分享，可在线阅读，更多相关《数据库系统原理与设计第2版 万常选版 第3章 SQL课后答案.docx（8页珍藏版）》请在冰豆网上搜索。

数据库系统原理与设计第2版万常选版第3章SQL课后答案

数据库系统原理与设计（第2版）万常选版第3章SQL课后答案

　　一个图书管理数据库BookDB的模式如图3-31—图3-34所示。

请基于该数据库模式用SQL语句完成如下操作。

属性含义分类号分类名称属性名classNoclassName类型字符型字符型宽度320小数位图3-31图书分类表BookClass的模式属性含义图书编号分类号图书名称作者姓名出版社名称出版号单价出版时间入库时间入库数量属性名bookNoclassNoBookNameauthorNamepublishingNamepublishingNopricepublishingDateshopDateshopNum类型字符型字符型字符型字符型字符型字符型数值型日期型日期型数值型宽度10340820177883小数位　　2　　图3-32图书表Book的模式

　　属性含义读者编号姓名性别身份证号工作单位属性名readerNoreaderNamesexidentitycardworkUnit类型字符型字符型字符型字符型字符型宽度8821850小数位　　图3-33读者表Reader的模式

　　属性含义读者编号图书编号借阅日期应归还日期归还日期属性名readerNobookNoborrowDateshouldDatereturnDate类型字符型字符型日期型日期型日期型宽度810888小数位　　图3-34借阅表Borrow的模式

　　答案：

　　SETNOCOUNTONSETDATEFORMATYMDUSEmasterGO

　　创建数据库BookDB

　　IFEXISTS（SELECT*FROMsysdatabasesWHEREname='BookDB'）DROPDATABASEBookDB

　　GO

　　CREATEDATABASEBookDBGO

　　USEBookDBGO

　　创建图书分类表BookClassCREATETABLEBookClass（

　　classNochar（3）PRIMARYKEY,/*分类号*/classNamechar（20）NOTNULL/*分类名称*/）GO

　　创建图书表BookCREATETABLEBook（

　　bookNochar（10）PRIMARYKEY,/*图书编号*/classNochar（3）NOTNULL/*分类号*/

　　FOREIGNKEYREFERENCESBookClass,bookNamevarchar（40）NOTNULL,/*图书名称*/authorNamechar（8）NOTNULL,/*作者姓名*/publishingNamevarchar（20）NOTNULL,/*出版社名称*/publishingNochar（17）NOTNULL,/*出版号*/pricenumeric（7,2）NOTNULL,/*单价*/publishingDatedatetimeNOTNULL,/*出版时间*/shopDatedatetimeNOTNULL,/*入库时间*/shopNumintNOTNULL/*入库数量*/）GO

　　创建读者表ReaderCREATETABLEReader（

　　readerNochar（8）PRIMARYKEY,/*读者编号*/readerNamechar（8）NOTNULL,/*读者姓名*/sexchar

（2）NOTNULL,/*性别*/identitycardchar（18）NOTNULL,/*身份证号*/workUnitvarchar（50）NULL/*工作单位*/）GO

　　创建借阅表BorrowCREATETABLEBorrow（

　　readerNochar（8）NOTNULL,/*读者编号*/bookNochar（10）NOTNULL,/*图书编号*/borrowDatedatetimeNOTNULL,/*借阅日期*/

　　shouldDatedatetimeNOTNULL,/*应归还日期*/returnDatedatetimeNULL,/*归还日期*/FOREIGNKEY（readerNo）REFERENCESReader,FOREIGNKEY（bookNo）REFERENCESBook,PRIMARYKEY（readerNo,bookNo,borrowDate））GO

　　查询1991年出生的读者姓名、工作单位和身份证号。

SELECTreaderName,workUnit,identitycardFROMReader

　　WHERECONVERT（int,SUBSTRING（identitycard,7,4））=1991

　　查询在信息管理学院工作的读者编号、姓名和性别。

　　SELECTreaderNo,readerName,sex=CASEsexWHEN'M'THEN'男'WHEN'F'THEN'女'ENDFROMReader

　　WHEREworkUnit='信息管理学院'

　　查询图书名中含有“数据库”的图书的详细信息。

SELECT*FROMBook

　　WHEREbookNameLIKE'%数据库%'

　　查询吴文君老师编写的单价不低于40元的每种图书的图书编号、入库数量。

SELECTbookNo,shopNumFROMBook

　　WHEREauthorName='吴文君'ANDprice>=40

　　查询在20XX-20XX年之间入库的图书编号、出版时间、入库时间和图书名称，并按入库时间排序输出。

　　SELECTbookNo,bookName,publishingDate,shopDateFROMBook

　　WHEREYEAR（shopDate）BETWEEN20XXAND20XXORDERBYshopDate

　　查询借阅了图书编号001-000029图书的读者姓名、图书编号、借书日期。

SELECTreaderName,bookNo,borrowDateFROMBorrow

　　WHERECONVERT（int,bookNo）BETWEEN1AND29

　　查询读者马永强借阅的图书编号、图书名称、借书日期和归还日期。

SELECT,bookName,borrowDate,returnDateFROMBook,Borrow

　　WHERE=ANDreaderNoIN（SELECTreaderNoFROMReader

　　WHEREreaderName='马永强'）

　　查询会计学院没有归还所借图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。

　　SELECT,readerName,bookName,borrowDate,shouldDateFROMReader,Borrow,Book

　　WHERE=AND=

　　ANDworkUnit='会计学院'ANDreturnDateISNULL

　　查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期。

　　SELECT,readerName,bookName,borrowDate,returnDateFROMReader,Borrow,Book

　　WHERE=AND=

　　ANDpublishingName='清华大学出版社'

　　查询借书时间在20XX-20XX年之间的读者编号、读者姓名、图书编号、图书名称。

SELECT,readerName,,bookNameFROMReader,Borrow,Book

　　WHERE=AND=

　　ANDYEAR（borrowDate）BETWEEN20XXAND20XX

　　查询每种类别的图书分类号、最高价格和平均价格，并按最高价格的降序输出。

SELECTclassNo,MAX（price）ASmaxPrice,AVG（price）ASavgPriceFROMBook

　　GROUPBYclassNo

　　ORDERBYMAX（price）DESC

　　查询图书分类号小于009号图书的入库数量。

SELECTclassNo,shopNumFROMBook

　　WHEREshopNum

　　（SELECTclassNo,shopNumFROMBook

　　WHEREclassNo=’009’）

　　查询所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。

SELECT,readerName,SUM（price）ASmoneyFROMReader,Borrow,Book

　　WHERE=AND=ANDreturnDateISNULL

　　GROUPBY,readerNameHAVINGSUM（price）>=150

　　查询没有借阅图书的读者姓名和工作单位。

--useIN

　　SELECTreaderName，workUnitFROMReader

　　WHEREreaderNoNOTIN（SELECTreaderNoFROMBorrow

　　WHEREreturnDateISNULL）

　　--useEXISTS

　　SELECTreaderName，workUnitFROMReader

　　WHERENOTEXISTS（SELECT*FROMBorrow

　　WHERE=ANDreturnDateISNULL）

　　查询借阅了图书编号为001-000029图书的读者编号、读者姓名、以及他们所借图书尚未归还的所有图书的图书名称、借书日期。

--useIN

　　SELECTbookName,readerName,readerNo,borrowDateFROMReadera,Borrowb

　　WHERE=

　　ANDIN（SELECTreaderName,bookNo

　　FROMBorrow

　　WHERECONVERT（int,bookNo）BETWEEN1AND29）

　　--useEXISTS

　　SELECTbookName,readerName,readerNo,borrowDateFROMReadera,Borrowb

　　WHEREEXISTS（SELECT*

　　FROMBorrow

　　WHERE=

　　ANDCONVERT（int,bookNo）BETWEEN1AND29）

　　查询没有借阅图书编号以001开头的图书的读者编号、姓名以及他们所借阅图书的图书名称、借书日期。

--useIN

　　SELECTreaderNo,readerNameFROMReader

　　WHEREreaderNoNOTIN（SELECTreaderNoFROMBorrow

　　WHEREbookNoLIKE'001%'）

　　--useEXISTS

　　SELECTreaderNo,readerNameFROMReaderWHEREEXISTS（SELECT*FROMBorrow

　　WHEREbookNoLIKE'001%'）

　　查询在20XX-20XX年之间借阅但没有归还图书的读者编号、读者姓名、读者工作单位以及他们所借阅过的所有图书的图书编号、图书名称和借书日期。

--useIN

　　SELECTreaderNo,readerName,workUnitFROMReader

　　WHEREreaderNOIN（SELECTreaderNoFROMBorrow

　　WHEREYEAR（borrowDate）BETWEEN20XXAND20XXANDreturnDateISNULL）

　　--useEXISTS

　　SELECTreaderNo,readerName,workUnitFROMReaderWHEREEXISTS（SELECT*FROMBorrow

　　WHEREYEAR（borrowDate）BETWEEN20XXAND20XXANDreturnDateISNULL）

　　查询既借阅了“离散数学”图书又借阅了“数据库系统概念”两本图书的读者编号、读者姓名、借书日期和图书名称。

　　SELECT,readerName,borrowDate,bookNameFROMReader,Borrow,Book

　　WHERE=AND=ANDIN（SELECTreaderNoFROMBorrow

　　WHEREbookNoIN（　　SELECTbookNo　　FROMBook

　　WHEREbookName='离散数学'）

　　）ANDIN（SELECTreaderNoFROMBorrow

　　WHEREbookNoIN（　　SELECTbookNo　　FROMBook

　　WHEREbookName='数据库系统概念'））

　　查询没有借阅“经济管理”类图书的读者编号、读者姓名和出生日期。

--useIN

　　SELECTreaderNo,readerName,SUBSTRING（identitycard,7,8）ASbirthdayFROMReader

　　WHEREreaderNoNOTIN（SELECTreaderNoFROMBorrow

　　WHEREbookNoIN（SELECTbookNoFROMBook

　　WHEREclassNoIN（　　SELECTclassNo　　FROMBookClass

　　WHEREclassName='经济管理'）））

　　--useEXISTS

　　SELECTreaderNo,readerName,SUBSTRING（identitycard,7,8）ASbirthdayFROMReader

　　WHERENOTEXISTS（

　　SELECT*

　　FROMBorrow,Book

　　WHERE=AND=ANDclassNo=（

　　SELECTclassNo　　FROMBookClass

　　WHEREclassName='经济管理'））

　　查询至少与读者“马永强”所借的图书一样的读者编号、读者姓名和工作单位。

SELECTreaderNo,readerName,workUnitFROMReaderrWHERENOTEXISTS（SELECT*

　　FROMBorrowb1WHEREreaderNoIN（SELECTreaderNoFROMReader

　　WHEREreaderName='马永强'）ANDreturnDateISNULLANDNOTEXISTS（SELECT*

　　FROMBorrowb2

　　WHERE=　　ANDreaderNo=　　ANDreturnDateISNULL））

　　查询借阅了图书类别为002号的所有图书的读者编号、读者姓名、以及他们所借阅过的所有图书的图书名称和借阅日期。

　　SELECT,readerName,bookName,borrowDateFROMReader,Borrow,Book

　　WHERE=AND=ANDNOTEXISTS（SELECT*FROMBook

　　WHEREclassNo='002'　　ANDNOTEXISTS（　　SELECT*　　FROMBorrow

　　）

　　WHEREreaderNo=ANDbookNo=）

　　查询借阅了图书类别为002号的所有图书的读者编号、读者姓名、以及他们所借阅过的这些图书的图书名称和借阅日期。

　　SELECT,readerName,bookName,borrowDateFROMReader,Borrow,Book

　　WHERE=AND=ANDNOTEXISTS（SELECT*FROMBook

　　WHEREclassNo='002'　　ANDNOTEXISTS（　　SELECT*　　FROMBorrow

　　WHEREreaderNo=　　ANDbookNo=　　））

　　查询至少借阅了3本图书的读者编号、读者姓名、图书编号、图书名称，并按读者编号排序输出。

　　SELECT,readerName,,bookNameFROMReader,Borrow,Book

　　WHERE=AND=ANDIN（SELECTreaderNoFROMBorrow

　　WHEREreturnDateISNULLGROUPBYreaderNoHAVINGCOUNT（*）>=3）

　　ORDERBY

　　查询所借阅的图书总价最高的读者编号、读者姓名和出生日期。

　　SELECTreaderNo,readerName,SUBSTRING（identitycard,7,8）ASbirthdayFROMReader

　　WHEREreaderNoIN（SELECTreaderNo

　　FROMBorrow,Book

　　WHERE=　　ANDreturnDateISNULLGROUPBYreaderNo

　　HAVINGSUM（price）>=ALL（SELECTSUM（price）FROMBorrow,Book

　　WHERE=　　ANDreturnDateISNULLGROUPBYreaderNo））

　　将“经济管理”类图书的单价提高10%。

UPDATEBook

　　SETprice=price*WHEREclassNoIN（SELECTclassNoFROMBookClass

　　WHEREclassName='经济管理'）

　　对于年龄在25-35之间的读者所借阅的应归还未归还的图书，将其归还日期修改为系统当天日期。

UPDATEBorrow

　　SETreturnDate=GETDATEWHEREreaderNoIN（SELECTreaderNoFROMReader

　　WHERECONVERT（int,SUBSTRING（identitycard,7,4））BETWEEN25AND35）GO

　　创建一个视图，该视图为所借图书的总价在150元以上的读者编号、读者姓名和所借图书的总价。

　　CREATEVIEWBookView1AS

　　SELECT,readerName,SUM（price）ASmoneyFROMReader,Borrow,Book

　　WHERE=AND=GROUPBY,readerNameHAVINGSUM（price）>=150GO

　　创建一个视图，该视图为年龄在25-35岁之间的读者，属性列包括读者编号、读者姓名、年龄、工作单位、所借图书名称和借书日期。

CREATEVIEWBookView2AS

　　SELECT,readerName,workUnit,bookName,borrowDateFROMReader,Borrow,Book

　　WHERE=AND=

　　ANDCONVERT（int,SUBSTRING（identitycard,7,4））BETWEEN25AND35GO

　　创建一个视图，该视图仅包含“清华大学出版社”在20XX-20XX年出版的“计算机类”的图书基本信息。

CREATEVIEWBookView3AS

　　SELECT*FROMBook

　　WHEREpublishingName='清华大学出版社'

　　ANDYEAR（publishingDate）IN（20XX,20XX）ANDclassNo=（

　　SELECTclassNo　　FROMBookClass

　　WHEREclassName='计算机类'）GO

　　对题所建立的视图进行插入、删除和更新操作。

　　INSERTBookView3VALUES（'0000001','001','Linux网络技术','王波','机械工业出版社','9787111216063',28,'20XX0701','20XX0901',80）DELETEBookView3WHEREbookNameLIKE'Linux%'UPDATEBookView3SETshopNum=shopNum-10

　　将入库数量最多的图书单价下调5%。

UPDATEBook

　　SETprice=price*WHEREshopNum=（

　　SELECTMAX（shopNum）FROMBook）

　　一个图书管理数据库BookDB的模式如图3-31—图3-34所示。

请基于该数据库模式用SQL语句完成如下操作。

属性含义分类号分类名称属性名classNoclassName类型字符型字符型宽度320小数位图3-31图书分类表BookClass的模式属性含义图书编号分类号图书名称作者姓名出版社名称出版号单价出版时间入库时间入库数量属性名bookNoclassNoBookNameauthorNamepublishingNamepublishingNopricepublishingDateshopDateshopNum类型字符型字符型字符型字符型字符型字符型数值型日期型日期型数值型宽度10340820177883小数位　　2　　图3-32图书表Book的模式

　　属性含义读者编号姓名性别身份证号工作单位属性名readerNoreaderNamesexidentitycardworkUnit类型字符型字符型字符型字符型字符型宽度8821850小数位　　图3-33读者表Reader的模式

　　属性含义读者编号图书编号借阅日期应归还日期归还日期属性名readerNobookNoborrowDateshouldDatereturnDate类型字符型字符型日期型日期型日期型宽度810888小数位　　图3-34借阅