數(shù)據(jù)庫系統(tǒng)原理與設(shè)計(jì)課后習(xí)題答案

1、SET NOCOUNT ONSET DATEFORMAT YMDUSE masterGO-創(chuàng)建數(shù)據(jù)庫BookDBIF EXISTS(SELECT * FROM sysdatabases WHERE name='BookDB' DROP DATABASE BookDBGOCREATE DATABASE BookDBGOUSE BookDBGO-創(chuàng)建圖書分類表BookClassCREATE TABLE BookClass(classNo char(3 PRIMARY KEY, /*分類號*/ className char(20 NOT NULL /*分類名稱*/GO-創(chuàng)建圖書表Bo

2、okCREATE TABLE Book(bookNo char(10 PRIMARY KEY, /*圖書編號*/classNo char(3 NOT NULL /*分類號*/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,2 N

3、OT NULL,/*單價*/ publishingDate datetimeNOT NULL, /*出版時間*/ shopDate datetime NOT NULL, /*入庫時間*/shopNum int NOT NULL /*入庫數(shù)量*/GO-創(chuàng)建讀者表ReaderCREATE TABLE Reader(readerNo char(8 PRIMARY KEY, /*讀者編號*/ readerName char(8 NOT NULL, /*姓名*/sex char(2 NOT NULL, /*性別*/ identitycard char(18 NOT NULL, /*身份證號*/ work

4、Unit varchar(50 NULL /*工作單位*/GO-創(chuàng)建借閱表BorrowCREATE TABLE Borrow(readerNo char(8 NOT NULL, /*讀者編號*/bookNo char(10 NOT NULL, /*圖書編號*/borrowDate datetime NOT NULL, /*借閱日期*/shouldDate datetime NOT NULL, /*應(yīng)歸還日期*/returnDate datetime NULL, /*歸還日期*/FOREIGN KEY(readerNo REFERENCES Reader,FOREIGN KEY(bookNo R

5、EFERENCES Book,PRIMARY KEY(readerNo,bookNo,borrowDateGO-3.1 查詢1991年出生的讀者姓名、工作單位和身份證號。SELECT readerName,workUnit,identitycardFROM ReaderWHERE CONVERT(int,SUBSTRING(identitycard,7,4=1991-3.2 查詢在信息管理學(xué)院工作的讀者編號、姓名和性別。SELECT readerNo,readerName,sex=CASE sex WHEN 'M' THEN '男' WHEN 'F

6、9; THEN '女' END FROM ReaderWHERE workUnit='信息管理學(xué)院'-3.3 查詢圖書名中含有“數(shù)據(jù)庫”的圖書的詳細(xì)信息。SELECT *FROM BookWHERE bookName LIKE '%數(shù)據(jù)庫%'-3.4 查詢吳文君老師編寫的單價不低于40元的每種圖書的圖書編號、入庫數(shù)量。SELECT bookNo,shopNumFROM BookWHERE authorName='吳文君'AND price>=40-3.5 查詢在20052008年之間入庫的圖書編號、出版時間、入庫時間和圖書名

7、稱,并按入庫時間排序輸出。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 29-3.7 查詢沒有借閱圖書編號以001開頭的讀者編號和姓名。SELECT readerNo,rea

8、derNameFROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE bookNo LIKE '001%'-3.8 查詢讀者馬永強(qiáng)借閱的圖書編號、圖書名稱、借書日期和歸還日期。SELECT Book.bookNo,bookName,borrowDate,returnDateFROM Book,BorrowWHERE Book.bookNo=Borrow.bookNoAND readerNo IN(SELECT readerNoFROM ReaderWHERE readerName='馬永強(qiáng)'

9、;-3.9 查詢會計(jì)學(xué)院沒有歸還圖書的讀者編號、讀者姓名、圖書名稱、借書日期和應(yīng)歸還日期。SELECT Reader.readerNo,readerName,bookName,borrowDate,shouldDateFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND workUnit='會計(jì)學(xué)院' AND returnDate IS NULL-3.10 查詢借閱了清華大學(xué)出版社出版的圖書的讀者編號、讀者姓名、圖書名稱、借書日期和歸還日期。SEL

10、ECT Reader.readerNo,readerName,bookName,borrowDate,returnDateFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND publishingName='清華大學(xué)出版社'-3.11 查詢借書時間在20072008年之間的讀者編號、姓名、圖書編號、圖書名稱。SELECT Reader.readerNo,readerName,Book.bookNo,bookNameFROM Reader,Borro

11、w,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(SELECT readerNoFROM BorrowWHERE YEAR(borrowDate BETWEEN 2005 AND 2008AND retur

12、nDate IS NULL-3.13 查詢沒有借書的讀者姓名(分別使用IN子查詢和存在量詞子查詢表達(dá)。-use INSELECT readerNameFROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE returnDate IS NULL-use EXISTSSELECT readerNameFROM ReaderWHERE NOT EXISTS(SELECT *FROM BorrowWHERE Reader.readerNo=Borrow.readerNoAND returnDate IS NULL-3.14 查詢

13、既借閱了“離散數(shù)學(xué)”圖書又借閱了“數(shù)據(jù)庫系統(tǒng)概念”兩本書的讀者編號、讀者姓名、借書日期和圖書名稱。SELECT Reader.readerNo,readerName,borrowDate,bookNameFROM Reader,Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND Reader.readerNo IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE bookName='離散

14、數(shù)學(xué)'AND Reader.readerNo IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE bookName='數(shù)據(jù)庫系統(tǒng)概念'-3.15 查詢沒有借閱圖書的讀者編號、讀者姓名和工作單位(分別使用IN子查詢和存在量詞子查詢表達(dá)。-與3.13重復(fù)-3.16 查詢沒有借閱“經(jīng)濟(jì)管理”類圖書的讀者編號、讀者姓名和出生日期(分別使用IN 子查詢和存在量詞子查詢表達(dá)。-use INSELECT readerNo,readerName,SUBSTRING(identitycard,7

15、,8 AS birthdayFROM ReaderWHERE readerNo NOT IN(SELECT readerNoFROM BorrowWHERE bookNo IN(SELECT bookNoFROM BookWHERE classNo IN(SELECT classNoFROM BookClassWHERE className='經(jīng)濟(jì)管理'-use EXISTSSELECT readerNo,readerName,SUBSTRING(identitycard,7,8 AS birthdayFROM ReaderWHERE NOT EXISTS(SELECT *FR

16、OM Borrow,BookWHERE Reader.readerNo=Borrow.readerNoAND Borrow.bookNo=Book.bookNoAND classNo=(SELECT classNoFROM BookClassWHERE className='經(jīng)濟(jì)管理' -3.17 查詢至少與讀者“馬永強(qiáng)”所借的圖書一樣的讀者編號、讀者姓名和工作單位。 SELECT readerNo,readerName,workUnit FROM Reader r WHERE NOT EXISTS( SELECT * FROM Borrow b1 WHERE readerNo

17、 IN( SELECT readerNo FROM Reader WHERE readerName='馬永強(qiáng)' AND returnDate IS NULL AND NOT EXISTS( SELECT * FROM Borrow b2 WHERE b1.bookNo=b2.bookNo AND readerNo=r.readerNo AND returnDate IS NULL -3.18 查詢每種類別的圖書分類號、最高價格和平均價格，并按最高價格的降序輸出。 SELECT classNo,MAX(price AS maxPrice,AVG(price AS avgPrice

18、 FROM Book GROUP BY classNo ORDER BY MAX(price DESC -3.19 查詢圖書分類號為 001 號圖書的入庫數(shù)量。 SELECT SUM(shopNum AS sumShopNum FROM Book GROUP BY classNo HAVING classNo='001' -3.20 查詢借閱了圖書類別為 002 號的所有圖書的讀者編號、讀者姓名、圖書名稱和借閱日 期。 SELECT Reader.readerNo,readerName,bookName,borrowDate FROM Reader,Borrow,Book WH

19、ERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND NOT EXISTS( SELECT * FROM Book WHERE classNo='002' AND NOT EXISTS( SELECT * FROM Borrow WHERE readerNo=Reader.readerNo AND bookNo=Book.bookNo -3.21 查詢至少借閱了 3 本圖書的讀者編號、讀者姓名、圖書編號、圖書名稱，并按讀者編 號排序輸出。 SELECT Reader.readerNo,read

20、erName,Book.bookNo,bookName FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND Reader.readerNo IN( SELECT readerNo FROM Borrow WHERE returnDate IS NULL GROUP BY readerNo HAVING COUNT(*>=3 ORDER BY Reader.readerNo -3.22 查詢所借圖書的總價在 150 元以上的讀者編號、讀者姓名和所借圖書

21、的總價。 SELECT Reader.readerNo,readerName,SUM(price AS money FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo AND returnDate IS NULL GROUP BY Reader.readerNo,readerName HAVING SUM(price>=150 -3.23 查詢所借閱的圖書總價最高的讀者編號、讀者姓名和出生日期。 SELECT readerNo,readerName,SUBS

22、TRING(identitycard,7,8 AS birthday FROM Reader WHERE readerNo IN( SELECT readerNo FROM Borrow,Book WHERE Borrow.bookNo=Book.bookNo AND returnDate IS NULL GROUP BY readerNo HAVING SUM(price >=ALL( SELECT SUM(price FROM Borrow,Book WHERE Borrow.bookNo=Book.bookNo AND returnDate IS NULL GROUP BY rea

23、derNo -3.24 將“經(jīng)濟(jì)管理”類圖書的單價提高 10%。 UPDATE Book SET price=price*1.1 WHERE classNo IN( SELECT classNo FROM BookClass WHERE className='經(jīng)濟(jì)管理' -3.25 對于年齡在 2535 之間的讀者所借閱的應(yīng)歸還未歸還的圖書， 將其歸還日期修改為系 統(tǒng)當(dāng)天日期。 UPDATE Borrow SET returnDate=GETDATE( WHERE readerNo IN( SELECT readerNo FROM Reader WHERE CONVERT(in

24、t,SUBSTRING(identitycard,7,4 BETWEEN 25 AND 35 GO -3.26 創(chuàng)建一個視圖，該視圖為所借圖書的總價在 150 元以上的讀者編號、讀者姓名和所借 圖書的總價。 CREATE VIEW BookView1 AS SELECT Reader.readerNo,readerName,SUM(price AS money FROM Reader,Borrow,Book WHERE Reader.readerNo=Borrow.readerNo AND Borrow.bookNo=Book.bookNo GROUP BY Reader.readerNo,readerName HAVING SUM(price>=150 GO -3.27 創(chuàng)建一個視圖，該視圖為年齡在 2535 歲之間的讀者，屬性列包括讀者編號、讀者姓 名、年齡、工作單位、所借圖書名稱和借書日期。 CREATE VIEW BookView2 AS SELECT Reader.readerNo,readerName,workUnit,bookName,borrowDate FROM Reader,Borro