{za}万常选数据库第3章参考507

1、SET NOCOUNT ONSET DATEFORMAT YMDUSE masterGO- 创建数据库 BookDBIF EXISTS(SELECT * FROM sysdatabases WHERE name='BookDB'.) DROP DATABASE BookDBGOCREATE DATABASE BookDBGOUSE BookDBGO- 创建图书分类表 BookClassCREATE TABLE BookClass(classNo char(3)PRIMARY KEY,/* 分类号 */className char(20) NOT NULL /* 分类名称 */)

2、GO - 创建图书表 BookPRIMARY KEY,/* 图书编号 */NOT NULL/* 分类号 */CREATE TABLE Book( bookNochar(10)classNochar(3)FOREIGN KEY REFERENCES BookClass,bookName varchar(40) NOT NULL, /* 图书名称 */ authorName char(8) NOT NULL, /* 作者姓名 */publishingName varchar(20) NOT NULL, publishingNo char(17) NOT NULL, price numeric(7,

3、2) NOT NULL, publishingDate datetime NOT NULL, shopDate datetime NOT NULL, shopNum int/* 出版社名称 */* 出版号 */* 单价 */* 出版进度 */* 入库进度 */ NOT NULL/* 入库数量 */GO- 创建读者表 ReaderPRIMARY KEY,/* 读者编号 */NOT NULL,/* 姓名 */NOT NULL, /* 性别 */* 身份证号 */* 工作单位 */CREATE TABLE Reader( readerNo char(8) readerName char(8) sex

4、 char(2)- 创建借阅表BorrowCREATE TABLE Borrow(readerNochar(8)NOT NULL,/* 读者编号*/bookNochar(10) NOT NULL,/* 图书编号*/borrowDatedatetimeNOT NULL,/* 借阅日期 */shouldDatedatetimeNOT NULL,/* 应归还日期 */returnDatedatetimeNULL,/* 归还日期 */identitycard char(18)NOT NULL,workUnit varchar(50) NULL ) GOCTGS-资料文件FOREIGN KEY(read

5、erNo) REFERENCES Reader,FOREIGN KEY(bookNo) REFERENCES Book,PRIMARY KEY(readerNo,bookNo,borrowDate)GO-3.1 查询 1991 年出生的读者姓名、工作单位和身份证号。SELECT readerName,workUnit,identitycardFROM ReaderWHERE CONVERT(int,SUBSTRING(identitycard,7,4)=1991 .-3.2 查询在信息管理学院工作的读者编号、姓名和性别。SELECT readerNo,readerName,sex=CASE s

6、ex WHEN 'M' THEN '男' WHEN 'F' THEN ' 女' END. FROM ReaderWHERE workUnit=信息管理学院'-3.3 查询图书名中含有“数据库”的图书的详细信息。SELECT *FROM BookWHERE bookName LIKE '%数据库%'-3.4 查询吴文君老师编写的单价不低于 40 元的每种图书的图书编号、入库数量。SELECT bookNo,shopNumFROM BookWHERE authorName吴文君'AND price>

7、;=40-3.5 查询在 2005 2008年之间入库的图书编号、出版进度、入库进度和图书名称，并按入库 进度排序输出。 .SELECT bookNo,bookName,publishingDate,shopDateFROM BookWHERE YEAR(shopDate) BETWEEN 2005 AND 2008ORDER BY shopDate-3.6 查询借阅了 001000029图书编号的读者编号、图书编号、借书日期。SELECT readerNo,bookNo,borrowDateFROM BorrowWHERE CONVERT(int, bookNo) BETWEEN 1 AND

8、 29-3.7 查询没有借阅图书编号以 001 开头的读者编号和姓名。SELECT readerNo,readerNameFROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE bookNo LIKE '001%')-3.8 查询读者马永强借阅的图书编号、图书名称、借书日期和归还日期。SELECT Book.bookNo,bookName,borrowDate,returnDateFROM Book,BorrowWHERE Book.bookNo=Borrow.bookNoAND readerNo IN(

9、SELECT readerNoFROM ReaderCTGS-资料文件WHERE readerName马永强')-3.9 查询会计学院没有归还图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。SELECT Reader.readerNo,readerName,bookName,borrowDate,shouldDate .FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND workUnit=' 会计学院 ' AND returnD

10、ate IS NULL-3.10 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归 还日期。SELECT Reader.readerNo,readerName,bookName,borrowDate,returnDate .FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND publishingName=' 清华大学出版社 '-3.11 查询借书进度在 20072008 年之间的读者编号、姓名、图书编号、图书名称。S

11、ELECT Reader.readerNo,readerName,Book.bookNo,bookName.FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND YEAR(borrowDate) BETWEEN 2007 AND 2008-3.12 查询在 2005-2008 年之间没有归还图书的读者编号、读者姓名、读者工作单位。SELECT readerNo,readerName,workUnitFROM ReaderWHERE readerNO IN(SELE

12、CT readerNoFROM BorrowWHERE YEAR(borrowDate) BETWEEN 2005 AND 2008AND returnDate IS NULL)-3.13 查询没有借书的读者姓名(分别使用 IN 子查询和存在量词子查询表达)。-use INSELECT readerNameFROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE returnDate IS NULL)-use EXISTSSELECT readerNameFROM ReaderWHERE NOT EXISTS(SELECT

13、 *FROM BorrowWHERE Reader.readerNo=Borrow.readerNoAND returnDate IS NULL)-3.14 查询既借阅了“离散数学”图书又借阅了“数据库系统概念”两本书的读者编号、读者姓 名、借书日期和图书名称。 .SELECT Reader.readerNo,readerName,borrowDate,bookName .FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND Reader.readerNo IN(

14、SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE bookName离散数学') AND Reader.readerNo IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE bookName数,据库系统概念')-3.15 查询没有借阅图书的读者编号、读者姓名和工作单位(分别使用 IN 子查询和存在量词子 查询表达)。 .- 与 3.13 重复-3.16 查询没有借阅“经济管理”类图书的读者编号、读者

15、姓名和出生日期(分别使用 IN 子查 询和存在量词子查询表达)。 .-use INSELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday.FROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE classNo IN(SELECT classNoFROM BookClassWHERE className经济管理')-use EXISTSSELECT readerN

16、o,readerName,SUBSTRING(identitycard,7,8) AS birthday.FROM ReaderWHERE NOT EXISTS(SELECT *FROM Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND classNo=(SELECT classNoFROM BookClassWHERE className经济管理')-3.17 查询至少与读者“马永强”所借的图书一样的读者编号、读者姓名和工作单位。SELECT readerNo,reader

17、Name,workUnitFROM Reader rWHERE NOT EXISTS(SELECT *FROM Borrow b1WHERE readerNo IN(SELECT readerNoFROM ReaderWHERE readerName马永强') AND returnDate IS NULLAND NOT EXISTS( SELECT * FROM Borrow b2 WHERE b1.bookNo=b2.bookNoAND readerNo=r.readerNoAND returnDate IS NULL)-3.18 查询每种类别的图书分类号、最高价格和平均价格，并按最

18、高价格的降序输出。SELECT classNo,MAX(price) AS maxPrice,AVG(price) AS avgPrice .FROM BookGROUP BY classNoORDER BY MAX(price) DESC-3.19 查询图书分类号为 001 号图书的入库数量。SELECT SUM(shopNum) AS sumShopNumFROM BookGROUP BY classNoHAVING classNo='001'-3.20 查询借阅了图书类别为 002 号的所有图书的读者编号、读者姓名、图书名称和借阅日期。SELECT Reader.read

19、erNo,readerName,bookName,borrowDate.FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND NOT EXISTS(SELECT *FROM BookWHERE classNo='002'AND NOT EXISTS(SELECT *FROM BorrowWHERE readerNo=Reader.readerNo AND bookNo=Book.bookNo)-3.21 查询至少借阅了 3 本图书的读者编号、读者姓

20、名、图书编号、图书名称，并按读者编号排 序输出。SELECT Reader.readerNo,readerName,Book.bookNo,bookName.FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND Reader.readerNo IN(SELECT readerNoFROM BorrowWHERE returnDate IS NULLGROUP BY readerNoHAVING COUNT(*)>=3)ORDER BY Reader.read

21、erNo-3.22 查询所借图书的总价在 150 元以上的读者编号、读者姓名和所借图书的总价。SELECT Reader.readerNo,readerName,SUM(price) AS money .FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND returnDate IS NULLGROUP BY Reader.readerNo,readerNameHAVING SUM(price)>=150-3.23 查询所借阅的图书总价最高的读者编号、读者姓

22、名和出生日期。SELECT readerNo,readerName,SUBSTRING(identitycard,7,8) AS birthday.FROM ReaderWHERE readerNo IN(SELECT readerNoFROM Borrow,BookWHERE Borrow.bookNo=Book.bookNoAND returnDate IS NULLGROUP BY readerNoHAVING SUM(price) >=ALL(SELECT SUM(price)FROM Borrow,BookWHERE Borrow.bookNo=Book.bookNoAND r

23、eturnDate IS NULLGROUP BY readerNo)-3.24 将“经济管理”类图书的单价提高 10%。UPDATE BookSET price=price*1.1WHERE classNo IN(SELECT classNoFROM BookClassWHERE className经济管理')-3.25 对于年龄在 2535 之间的读者所借阅的应归还未归还的图书，将其归还日期修改为系统当 天日期。.UPDATE BorrowSET returnDate=GETDATE()WHERE readerNo IN(SELECT readerNoFROM ReaderWHER

24、E CONVERT(int,SUBSTRING(identitycard,7,4) BETWEEN 25 AND 35.)GO-3.26 创建一个视图，该视图为所借图书的总价在 150 元以上的读者编号、读者姓名和所借图书 的总价。 .CREATE VIEW BookView1ASSELECT Reader.readerNo,readerName,SUM(price) AS money .FROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoGROUP BY Reader.readerNo,readerNameHAVING SUM(price)>=150GO-3.27 创建一个视图，该视图为年龄在 2535 岁之间的读者，属性列包括读者编号、读者姓名、 年龄、工作单位、所借图书名称和借书日期。 .CREATE VIEW BookView2ASSELECT Reader.readerNo,readerName,workUnit,bookName