數(shù)據(jù)庫(kù)原理與應(yīng)用(龐國(guó)莉)題目+答案

1、1、 設(shè)有一圖書館數(shù)據(jù)庫(kù)，包括三個(gè)表：圖書表、讀者表、借閱表。三個(gè)表的結(jié)構(gòu)如表3-3、表3-4和表3-5所示。完成以下習(xí)題。表3-3圖書表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類型約束圖書號(hào)圖書唯一的圖書號(hào)定長(zhǎng)字符串，長(zhǎng)度為20 主鍵 書名圖書的書名變長(zhǎng)字符串，長(zhǎng)度為50空值作者圖書的編著者名變長(zhǎng)字符串，長(zhǎng)度為30空值 出版社 圖書的出版社變長(zhǎng)字符串，長(zhǎng)度為30空值 單價(jià)出版社確定的圖書的單價(jià)浮點(diǎn)型，F(xiàn)LOAT空值 表3- 4讀者表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類型約束說(shuō)明讀者號(hào)讀者唯一編號(hào)定長(zhǎng)字符串，長(zhǎng)度為10 主鍵 姓名讀者姓名定長(zhǎng)字符串，長(zhǎng)度為8非空值性別讀者性別定長(zhǎng)字符串，長(zhǎng)度為2非空值辦公電話讀者辦公電話定長(zhǎng)字符串，長(zhǎng)

2、度為8空值 部門 讀者所在部門變長(zhǎng)字符串，長(zhǎng)度為30空值 表3- 5借閱表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類型約束說(shuō)明讀者號(hào)讀者的唯一編號(hào)定長(zhǎng)字符串，長(zhǎng)度為10 外碼，引用讀者表的主鍵 圖書號(hào)圖書的唯一編號(hào)定長(zhǎng)字符串，長(zhǎng)度為20 外碼，引用圖書表的主鍵 借出日期 圖書借出的日期定長(zhǎng)字符串，長(zhǎng)度為8，為yymmdd 非空值 歸還日期圖書歸還的日期定長(zhǎng)字符串，長(zhǎng)度為8，為yymmdd空值主鍵為：(讀者號(hào), 圖書號(hào))（1） 用SQL語(yǔ)句創(chuàng)建圖書館數(shù)據(jù)庫(kù)。Create database 圖書館數(shù)據(jù)庫(kù)；（2）用SQL語(yǔ)句創(chuàng)建上述三個(gè)表。use 圖書館數(shù)據(jù)庫(kù) Gocreate table book( bnum char

3、(20) primary key, bname varchar (50), bwri varchar (30), bpub varchar (30), bpri float );use 圖書館數(shù)據(jù)庫(kù) gocreate table rea( rnum char (10) primary key, rname char (8) not null, rsex char (2) not null, rphone char (8), rpar char(30) );use 圖書館數(shù)據(jù)庫(kù) gocreate table br( rnum char (10) not null, bnum char (20)

4、not null, brdate char (8) not null, brback char (8), foreign key (rnum) references rea(rnum), foreign key (bnum) references book(bnum) );（3）基于圖書館數(shù)據(jù)庫(kù)的三個(gè)表，用SQL語(yǔ)言完成以下各項(xiàng)操作：1）給圖書表增加一列“ISBN”，數(shù)據(jù)類型為CHAR(10)alter table book add ISBN CHAR(10);2） 為剛添加的ISBN列增加缺省值約束，約束名為ISBNDEF，缺省值為7111085949；ALTER TABLE BOOKADD

5、 CONSTRAINT ISBNDEF DEFAULT '7111085949' FOR ISBN;3） 為讀者表的辦公電話列，添加一個(gè)CHECK約束，要求前五位88320，約束名為CHECKDEF。ALTER TABLE REAADD CONSTRAINT CHECKDEF CHECK (RPHONE >='88320000' AND RPHONE <='88320999');4） 刪除圖書表中ISBN列增加缺省值約束； ALTER TABLE BOOKDROP ISBNDEF;5）刪除讀者表中“辦公電話”列的CHECK約束；ALT

6、ER TABLE READROP CHECKDEF; 6） 刪除圖書表中新增的列ISBN；ALTER TABLE BOOKDROP COLUMN ISBN;（4）基于圖書館數(shù)據(jù)庫(kù)的三個(gè)表，用SQL語(yǔ)言完成以下數(shù)據(jù)更新操作：1）向讀者表加入一個(gè)新讀者，該讀者的信息為：(200197, 王小平, 男 , 88320732,存中樓) INSERT INTO REAVALUES ('200197','王小平','男','88320732','信息系');2） 向借閱表插入一個(gè)借閱記錄，表示讀者王小平借閱了一本書，圖書號(hào)為T

7、P316/ZW6，借出日期為當(dāng)天的日期，歸還日期為空值。INSERT INTO BOOK(BNUM)VALUES ('TP316/ZW6');INSERTINTO BRVALUES ('200197','TP316/ZW6',convert( CHAR(8) ,GETDATE(),112),NULL); 3） 讀者王小平在借出上述圖書后10 歸還該書；UPDATE BRSET BRBACK= BRDATE+10 (10什么時(shí)候開應(yīng)該加引號(hào)，什么是不加)WHERE BNUM = 'TP316/ZW6'4） 當(dāng)讀者王小平按期歸還圖書時(shí)

8、，刪除上述借閱記錄； DELETEFROM BRWHERE rnum=(select rnumfrom reawhere rname = '王小平')（5）針對(duì)以上三個(gè)表，用SQL語(yǔ)言完成以下各項(xiàng)查詢：1）查詢?nèi)w圖書的圖書號(hào),書名,作者,出版社,單價(jià)。select bnum,bname,bwri,bpub,bprifrom book;2） 查詢?nèi)w圖書的信息,其中單價(jià)打8折，并且將該列設(shè)置別名為打折價(jià) 。select bnum,bname,bwri,bpub, bpri*0.8 打折價(jià)from book;3） 顯示所有借閱者的讀者號(hào)，并去掉重復(fù)行。select distinc

9、t rnumfrom br4） 查詢所有單價(jià)在20到30之間的圖書信息。select *from bookwhere bpri between 20 and 305） 查詢所有單價(jià)不在20到30之間的圖書信息。select *from bookwhere bpri not between 20 and 306） 查詢機(jī)械工業(yè)出版社、科學(xué)出版社、人民郵電出版社的圖書信息select *from bookwhere bpub in ('機(jī)械工業(yè)出版社','科學(xué)出版社','人民郵電出版社')7） 查詢既不是機(jī)械工業(yè)出版社、也不是科學(xué)出版社出版的圖書信息

10、select *from bookwhere bpub not in ('機(jī)械工業(yè)出版社','科學(xué)出版社')8） 查找姓名的第二個(gè)字符是建并且只有兩三個(gè)字符的讀者的讀者號(hào)、姓名。select rnum ,rnamefrom reawhere rname in ('_建，_建_')9） 查找姓名以王開頭的所有讀者的讀者號(hào)、姓名。select rnum ,rnamefrom reawhere rname like '王%'10） 查找姓名以王、張或李開頭的所有讀者的讀者號(hào)、姓名。select rnum ,rnamefrom reawh

11、ere rname like ('王%','張%','李%')11） 查找姓名不是以王、張或李開頭的所有讀者的讀者號(hào)、姓名。select rnum ,rnamefrom reawhere rname not like ('王%','張%','李%')12） 查詢無(wú)歸還日期的借閱信息。select *from brwhere brback = 'null'13） 查詢有歸還日期的借閱信息。select *from brwhere brback != 'null'14）

12、查詢單價(jià)在20元以上，30元以下的機(jī)械工業(yè)出版社出版的圖書名，單價(jià)。select bname,bprifrom bookwhere (bpri between 20 and 30) and bpub = '機(jī)械工業(yè)出版社'15） 查詢機(jī)械工業(yè)出版社或科學(xué)出版社出版的圖書名，出版社,單價(jià)。select bname,bpub,bprifrom bookwhere bpub = '機(jī)械工業(yè)出版社' or bpub = '科學(xué)出版社'16） 求讀者的總?cè)藬?shù)select count (*) 總?cè)藬?shù)from rea17） 求借閱了圖書的讀者的總?cè)藬?shù)selec

13、t count (distinct rnum ) 借閱總?cè)藬?shù)from br18） 求機(jī)械工業(yè)出版社圖書的平均價(jià)格、最高價(jià)、最低價(jià)。select AVG (bpri) avg , MAX (bpri) max , MIN (bpri) minfrom bookwhere bpub = '機(jī)械工業(yè)出版社'19） 查詢借閱圖書本數(shù)超過(guò)2本的讀者號(hào)、總本數(shù)。并按借閱本數(shù)值從大到小排序。select rnum,COUNT (bnum) 總本數(shù)from brGroup by rnumhaving COUNT(bnum) >2order by (count (bnum) desc（6）

14、針對(duì)以上三個(gè)表，用SQL語(yǔ)言完成以下各項(xiàng)多表連接查詢、子查詢、組合查詢1）查詢讀者的基本信息以及他/她借閱的情況。select rea.* ,br.bnum ,br.brback ,br.brdate from rea,brwhere rea.rnum = br.rnum 2） 查詢讀者的讀者號(hào)、姓名、借閱的圖書名、借出日期、歸還日期。select br.bnum ,rea.rname,book.bname,br.brback ,br.brdate from rea,br,book where rea.rnum = br.rnum and book.bnum=br.bnum 3） 查詢借閱了機(jī)

15、械工業(yè)出版社出版,并且書名中包含數(shù)據(jù)庫(kù)三個(gè)字的圖書的讀者，顯示讀者號(hào)、姓名、書名、出版社，借出日期、歸還日期。select rea.rnum,rea.rname,book.bname,br.brback ,br.brdate from rea,br,book where book.bname like '%數(shù)據(jù)庫(kù)%' and bpub = '機(jī)械工業(yè)出版社' and rea.rnum = br.rnum and book.bnum=br.bnum 4） 查詢至少借閱過(guò)1本機(jī)械工業(yè)出版社出版的書的讀者的讀者號(hào)、姓名、書名，借閱本數(shù)，并按借閱本數(shù)多少降序排列。sel

16、ect br.rnum,rea.rname,bname,COUNT(br.bnum )本數(shù)from book,rea,brwhere rea.rnum = br.rnum and book.bnum = br.bnum andbook.bnum = ' 機(jī)械工業(yè)出版社'Group by br.rnum ,rname,bnamehaving COUNT(bname) >=1order by COUNT(br.bnum) desc 5） 查詢與王平的辦公電話相同的讀者的姓名。select r1.rnamefrom rea r1,rea r2where r1.rname = &

17、#39;王平' and r1.rphone = r2.rphone6） 查詢辦公電話為88320701的所有讀者的借閱情況，要求包括借閱了書籍的讀者和沒(méi)有借閱的讀者，顯示他們的讀者號(hào)、姓名、書名、借閱日期。select rea.rnum,rname,bname,brdatefrom book,rea,brwhere rphone = '88320701' and rea.rnum = br.rnum and book.bnum = br.bnum 7） 查詢所有單價(jià)小于平均單價(jià)的圖書號(hào)、書名、出版社select bnum,bname,bpubfrom book wher

18、e bpri < (select AVG(bpri)from book)8） 查詢科學(xué)出版社的圖書中單價(jià)比機(jī)械工業(yè)出版社最高單價(jià)還高的的圖書書名、單價(jià)。select bname ,bprifrom bookwhere bpub = '科學(xué)出版社' and bpri > (select MAX (bpri) rom book where bpub = '機(jī)械工業(yè)出版社')9） 查詢'科學(xué)出版社'的圖書中單價(jià)比'機(jī)械工業(yè)出版社'最低單價(jià)高的的圖書書名、單價(jià)。select bname ,bprifrom bookwhere

19、bpub = '科學(xué)出版社' and bpri > (select Min (bpri) from book where bpub = '機(jī)械工業(yè)出版社')10） 查詢已被借閱過(guò)并已歸還的圖書信息。select *from bookwhere bnum in ( select bnumfrom brwhere brback is not null)11） 查詢從未被借閱過(guò)的圖書信息。select *from bookwhere bnum not in ( select bnumfrom br)12） 查詢正在借閱的圖書信息。select *from boo

20、kwhere bnum in ( select bnumfrom brwhere brdate not like 'null' and brback like null)13） 查詢借閱了機(jī)械工業(yè)出版社出版的書名中含有數(shù)'據(jù)庫(kù)書'三個(gè)字的圖書、或者借閱了科學(xué)出版社出版的書名中含有數(shù)'據(jù)庫(kù)書'三個(gè)字的圖書的讀者姓名、書名。select rname,bnamefrom book,rea,brwhere bname like '%數(shù)據(jù)庫(kù)%' and rea.rnum in ( select br.rnum from br,bookwhere bpub = '機(jī)械工業(yè)出版社' or bpub = '科學(xué)出版社')14） 查詢借閱了機(jī)械工業(yè)出版社出版的書名中含有數(shù)&