MYSQL練習題及答案

1、題目一問題描述：為管理崗位業(yè)務培訓信息，建立3個表:S (S#,SN,SD,SA)   S#,SN,SD,SA 分別代表學號、學員姓名、所屬單位、學員年齡C (C#,CN )        C#,CN       分別代表課程編號、課程名稱SC ( S#,C#,G )    S#,C#,G 

2、0;   分別代表學號、所選修的課程編號、學習成績 1. 使用標準SQL嵌套語句查詢選修課程名稱為稅收基礎的學員學號和姓名 -實現(xiàn)代碼：SELECT SN,S# FROM SWHERE S# IN(    SELECT S# FROM C,SC    WHERE C.C#=SC.C#        AND C

3、N=N'稅收基礎')2. 使用標準SQL嵌套語句查詢選修課程編號為C2的學員姓名和所屬單位-實現(xiàn)代碼：SELECT S.SN,S.SD FROM S,SCWHERE S.S#=SC.S#    AND SC.C#='C2'3. 使用標準SQL嵌套語句查詢不選修課程編號為C5的學員姓名和所屬單位-實現(xiàn)代碼：SELECT SN,SD FROM SWHERE S# NOT IN(   

4、60;SELECT S# FROM SC     WHERE C#='C5')4. 使用標準SQL嵌套語句查詢選修全部課程的學員姓名和所屬單位-實現(xiàn)代碼：SELECT SN,SD FROM SWHERE S# IN(    SELECT S# FROM SC         RIGH

5、T JOIN C ON SC.C#=C.C#     GROUP BY S#    HAVING COUNT(*)=COUNT(S#)5. 查詢選修了課程的學員人數(shù)-實現(xiàn)代碼：SELECT 學員人數(shù)=COUNT(DISTINCT S#) FROM SC6. 查詢選修課程超過5門的學員學號和所屬單位-實現(xiàn)代碼：SELECT SN,SD FROM SWHERE S#

6、60;IN(    SELECT S# FROM SC     GROUP BY S#    HAVING COUNT(DISTINCT C#)>5)題目二問題描述：本題用到下面三個關系表：CARD     借書卡。   CNO 卡號，NAME  姓名，CLASS 班級BO

7、OKS    圖書。     BNO 書號，BNAME 書名,AUTHOR 作者，PRICE 單價，QUANTITY 庫存冊數(shù) BORROW   借書記錄。 CNO 借書卡號，BNO 書號，RDATE 還書日期備注：限定每人每種書只能借一本；庫存冊數(shù)隨借書、還書而改變。要求實現(xiàn)如下15個處理：  1 寫出建立BORROW表的SQL語句，要求定義主碼

8、完整性約束和引用完整性約束。  2 找出借書超過5本的讀者,輸出借書卡號及所借圖書冊數(shù)。  3 查詢借閱了"水滸"一書的讀者，輸出姓名及班級。  4 查詢過期未還圖書，輸出借閱者（卡號）、書號及還書日期。  5 查詢書名包括"網(wǎng)絡"關鍵詞的圖書，輸出書號、書名、作者。  6 查詢現(xiàn)有圖書中價格最高的圖書，輸出書名及作者。  7 查詢當前借了"計算方法"但沒有

9、借"計算方法習題集"的讀者，輸出其借書卡號，并按卡號降序排序輸出。  8 將"C01"班同學所借圖書的還期都延長一周。  9 從BOOKS表中刪除當前無人借閱的圖書記錄。  10如果經(jīng)常按書名查詢圖書信息，請建立合適的索引。  11在BORROW表上建立一個觸發(fā)器，完成如下功能：如果讀者借閱的書名是"數(shù)據(jù)庫技術及應用"，就將該讀者的借閱記錄保存在BORROW_SAVE表中（注ORROW_SAVE表結構同BORROW表）。 &#

10、160;12建立一個視圖，顯示"力01"班學生的借書信息（只要求顯示姓名和書名）。  13查詢當前同時借有"計算方法"和"組合數(shù)學"兩本書的讀者，輸出其借書卡號，并按卡號升序排序輸出。  14假定在建BOOKS表時沒有定義主碼，寫出為BOOKS表追加定義主碼的語句。  15對CARD表做如下修改：    a. 將NAME最大列寬增加到10個字符（假定原為6個字符）。    b. 

11、;為該表增加1列NAME（系名），可變長，最大20個字符。1. 寫出建立BORROW表的SQL語句，要求定義主碼完整性約束和引用完整性約束-實現(xiàn)代碼：CREATE TABLE BORROW(    CNO int FOREIGN KEY REFERENCES CARD(CNO),    BNO int FOREIGN KEY REFERENCES BOOKS(BNO),  &#

12、160; RDATE datetime,    PRIMARY KEY(CNO,BNO) 2. 找出借書超過5本的讀者,輸出借書卡號及所借圖書冊數(shù)-實現(xiàn)代碼：SELECT CNO,借圖書冊數(shù)=COUNT(*)FROM BORROWGROUP BY CNOHAVING COUNT(*)>53. 查詢借閱了"水滸"一書的讀者，輸出姓名及班級-實現(xiàn)代碼：SELECT * FROM CARD cWHERE E

13、XISTS(    SELECT * FROM BORROW a,BOOKS b     WHERE a.BNO=b.BNO        AND b.BNAME=N'水滸'        AND a.CNO=c.CNO) 4. 查詢過期未還圖書，輸出借

14、閱者（卡號）、書號及還書日期-實現(xiàn)代碼：SELECT * FROM BORROW WHERE RDATE<GETDATE() 5. 查詢書名包括"網(wǎng)絡"關鍵詞的圖書，輸出書號、書名、作者-實現(xiàn)代碼：SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE BNAME LIKE N'%網(wǎng)絡%' 6. 查詢現(xiàn)有圖書中價格最高的圖書，輸出書名及作者-實現(xiàn)代碼：SELECT BNO,BNAME,AUTHOR F

15、ROM BOOKSWHERE PRICE=(    SELECT MAX(PRICE) FROM BOOKS) 7. 查詢當前借了"計算方法"但沒有借"計算方法習題集"的讀者，輸出其借書卡號，并按卡號降序排序輸出-實現(xiàn)代碼：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO AND b.BNAME=N'計算方法' 

16、0;  AND NOT EXISTS(        SELECT * FROM BORROW aa,BOOKS bb        WHERE aa.BNO=bb.BNO            AND bb.

17、BNAME=N'計算方法習題集'            AND aa.CNO=a.CNO)ORDER BY a.CNO DESC 8. 將"C01"班同學所借圖書的還期都延長一周-實現(xiàn)代碼：UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)FROM CARD a,BORROW bWHERE a.CNO

18、=b.CNO    AND a.CLASS=N'C01' 9. 從BOOKS表中刪除當前無人借閱的圖書記錄-實現(xiàn)代碼：DELETE A FROM BOOKS aWHERE NOT EXISTS(    SELECT * FROM BORROW    WHERE BNO=a.BNO) 10. 如果經(jīng)常按書名查詢圖書信息，請建立合適的索引-實現(xiàn)代碼：CRE

19、ATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)11. 在BORROW表上建立一個觸發(fā)器，完成如下功能：如果讀者借閱的書名是"數(shù)據(jù)庫技術及應用"，就將該讀者的借閱記錄保存在BORROW_SAVE表中（注ORROW_SAVE表結構同BORROW表）-實現(xiàn)代碼：CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATEASIF ROWCOUNT>0INSERT 

20、BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE i.BNO=b.BNO    AND b.BNAME=N'數(shù)據(jù)庫技術及應用' 12. 建立一個視圖，顯示"力01"班學生的借書信息（只要求顯示姓名和書名）-實現(xiàn)代碼：CREATE VIEW V_VIEWASSELECT a.NAME,b.BNAMEFROM BORROW ab,CARD a,

21、BOOKS bWHERE ab.CNO=a.CNO    AND ab.BNO=b.BNO    AND a.CLASS=N'力01'13. 查詢當前同時借有"計算方法"和"組合數(shù)學"兩本書的讀者，輸出其借書卡號，并按卡號升序排序輸出-實現(xiàn)代碼：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO  &#

22、160; AND b.BNAME IN(N'計算方法',N'組合數(shù)學')GROUP BY a.CNOHAVING COUNT(*)=2ORDER BY a.CNO DESC 14. 假定在建BOOKS表時沒有定義主碼，寫出為BOOKS表追加定義主碼的語句-實現(xiàn)代碼：ALTER TABLE BOOKS ADD PRIMARY KEY(BNO) 15.1 將NAME最大列寬增加到10個字符（假定原為6個字符）-實現(xiàn)代碼：ALTE

23、R TABLE CARD ALTER COLUMN NAME varchar(10) 15.2 為該表增加1列NAME（系名），可變長，最大20個字符-實現(xiàn)代碼：ALTER TABLE CARD ADD 系名 varchar(20)題目三假設有下面兩個關系模式：職工（職工號、姓名、年齡、職務、工資、部門號），其中職工號為主碼；部門（部門號、名稱、部門經(jīng)理、電話），其中部門號為主碼；用SQL語言定義這兩個關系模式，要求在模式中完成以下完整性約束條件的定義：定義每個模式的主碼和非空約束；定

24、義參照完整性；定義職工年齡不得超過60歲。答：Create table bm（bmno char（10） primary key，Bmname char（20） not null，Bmjl char（10）not null，Bmtel char（10）unique）；Create table zhigong（zgno char（10），Zgname char（20），Zgage int，Zgsal numeric(10),Bmno char(10),Primary key(zgno),Foreign key(bmno) reference bm(bmno) On delete no actio

25、n On update cascade,Constraint zg1 check(zgage<=60);題目四1、有以下三個關系模式：學生關系S，包括學號SNo、姓名SN、年齡SA、系別SD；課程關系C，包括課程號CNo、課程名CN、學分CC；學生選課關系SC，包括SNo、CNo和成績G。（1）用SQL語言定義SC表，注意：說明主鍵碼和外鍵碼(如果有的話)。（2）對于學生選課關系SC，授予李小蘭刪除權限以及對SNo和CNo的修改權限。（3）刪除學號為“9900205”的學生。（4）從學生選課關系SC中，刪除王云(學生關系中可能有重名)的所有選課。答：（1）CREATE TABLE SC

26、SNo INT, CNo INT, G INT, PRIMARY KEY( SNo, CNo), FOREIGN KEY(SNo) REFERENCES S( SNo), FOREIGN KEY(CNo) REFERENCES C(CNo); （3分）（2）GRANT DELETE, UPDATE( SNo, CNo) ON SC TO 李小蘭; （3分）（3）DELETE FROM S WHERE Sno = 9900205; （3分）（4） DELETE FROM SC WHERE SNo IN ( SELECT SNo FROM S WHERE SN = 王云 );題目五設某商業(yè)集團關于

27、商店銷售商品的數(shù)據(jù)庫中有三個基本表：商店SHOP（S#，SNAME，AREA，MGR_NAME）其屬性是商店編號，商店名稱，區(qū)域名，經(jīng)理姓名。銷售SALE（S#，G#， QUANTITY）其屬性是商店編號，商品編號，銷售數(shù)量。商品GOODS（G#，GNAME，PRICE）其屬性是商品編號，商品名稱，單價。（1）寫出下列查詢的關系代數(shù)表達式：檢索銷售“冰箱”的商店的編號和商店名稱。（2）從SALE表中，把“開開商店”中銷售單價高于1000元的商品的銷售元組全部刪除。（3）寫出下列操作的SQL語句：統(tǒng)計區(qū)域名為“EAST”的所有商店銷售的每一種商品的總數(shù)量和總價值。（1）關系代數(shù)表達式：S#，SN

28、AME（GNAME='冰箱'（SHOPSALEGOODS） （3分）（2）DELETE FROM SALEWHERE S# IN（SELECT S#FROM SHOPWHERE SNAME='開開商店'）AND G# IN（SELECT G#FROM GOODSWHERE PRICE>1000）； （3）SELECT C.G#，GNAME，SUM（QUANTITY）AS SUM_QUANTITY，PRICE*SUM（QUANTITY）AS SUM_VALUEFROM SHOP A，SALE B，GOODS CWHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'GROUP BY C.G#，GNAME；題目六：某學校有若干系，每個系有若干學生，若干課程，每個學生選修若干課程，每門課有若干學生選修，某一門課可以為不同的系開設，今要建立該校學生選修課程的數(shù)據(jù)庫，數(shù)據(jù)庫中要存放以下信息：系：系名，系代號，系主任名，電話 學生：學號，姓名，年齡，性別，所在系代號 課程：課程號碼，課程名稱 每個學生選修某門課的日期，成績 每個系開設的課程。請你設計：關