1、SET NOCOUNT ONSET DATEFORMAT YMDUSE masterGO- 創(chuàng)建數據庫 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 /* 分類名稱 */)

2、GO - 創(chuàng)建圖書表 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- 創(chuàng)建讀者表 ReaderPRIMARY KEY,/* 讀者編號 */NOT NULL,/* 姓名 */NOT NULL, /* 性別 */* 身份證號 */* 工作單位 */CREATE TABLE Reader( readerNo char(8) readerName char(8) sex

4、 char(2)- 創(chuàng)建借閱表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 查詢既借閱了“離散數學”圖書又借閱了“數據庫系統(tǒng)概念”兩本書的讀者編號、讀者姓 名、借書日期和圖書名稱。 .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數,據庫系統(tǒng)概念')-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 之間的讀者所借閱的應歸還未歸還的圖書，將其歸還日期修改為系統(tǒng)當 天日期。.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 創(chuàng)建一個視圖，該視圖為所借圖書的總價在 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 創(chuàng)建一個視圖，該視圖為年齡在 2535 歲之間的讀者，屬性列包括讀者編號、讀者姓名、 年齡、工作單位、所借圖書名稱和借書日期。 .CREATE VIEW BookView2ASSELECT Reader.readerNo,readerName,workUnit,bookName