（精選）數(shù)據(jù)庫(kù)原理與應(yīng)用Pg77-No.2題目+答案

1、完成第三章課后第2、3、4題，寫(xiě)相關(guān)SQL程序，提交數(shù)據(jù)庫(kù)備份文件及SQL程序代碼，（如不提交附件）可直接粘貼代碼提交 1、 設(shè)有一圖書(shū)館數(shù)據(jù)庫(kù)，包括三個(gè)表：圖書(shū)表、讀者表、借閱表。三個(gè)表的結(jié)構(gòu)如表3-3、表3-4和表3-5所示。完成以下習(xí)題。表3-3圖書(shū)表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類型約束圖書(shū)號(hào)圖書(shū)唯一的圖書(shū)號(hào)定長(zhǎng)字符串，長(zhǎng)度為20 主鍵 書(shū)名圖書(shū)的書(shū)名變長(zhǎng)字符串，長(zhǎng)度為50空值作者圖書(shū)的編著者名變長(zhǎng)字符串，長(zhǎng)度為30空值 出版社 圖書(shū)的出版社變長(zhǎng)字符串，長(zhǎng)度為30空值 單價(jià)出版社確定的圖書(shū)的單價(jià)浮點(diǎn)型，F(xiàn)LOAT空值 表3- 4讀者表結(jié)構(gòu)列名說(shuō)明數(shù)據(jù)類型約束說(shuō)明讀者號(hào)讀者唯一編號(hào)定長(zhǎng)字符串，長(zhǎng)度為1

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

3、；（2）用SQL語(yǔ)句創(chuàng)建上述三個(gè)表。use 圖書(shū)館數(shù)據(jù)庫(kù) Gocreate table book( bnum char (20) primary key, bname varchar (50), bwri varchar (30), bpub varchar (30), bpri float );use 圖書(shū)館數(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ū)館數(shù)據(jù)庫(kù)

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

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

6、約束； ALTER TABLE BOOKDROP ISBNDEF;5）刪除讀者表中“辦公電話”列的CHECK約束；ALTER TABLE READROP CHECKDEF; 6） 刪除圖書(shū)表中新增的列ISBN；ALTER TABLE BOOKDROP COLUMN ISBN;（4）基于圖書(shū)館數(shù)據(jù)庫(kù)的三個(gè)表，用SQL語(yǔ)言完成以下數(shù)據(jù)更新操作：1）向讀者表加入一個(gè)新讀者，該讀者的信息為：(200197, 王小平, 男 , 88320732,存中樓) INSERT INTO REAVALUES ('200197','王小平','男','8832

7、0732','信息系');2） 向借閱表插入一個(gè)借閱記錄，表示讀者王小平借閱了一本書(shū)，圖書(shū)號(hào)為T(mén)P316/ZW6，借出日期為當(dāng)天的日期，歸還日期為空值。INSERT INTO BOOK(BNUM)VALUES ('TP316/ZW6');INSERTINTO BRVALUES ('200197','TP316/ZW6',convert( CHAR(8) ,GETDATE(),112),NULL); 3） 讀者王小平在借出上述圖書(shū)后10 歸還該書(shū)；UPDATE BRSET BRBACK= BRDATE+10 (10什么時(shí)候開(kāi)

8、應(yīng)該加引號(hào)，什么是不加)WHERE BNUM = 'TP316/ZW6'4） 當(dāng)讀者王小平按期歸還圖書(shū)時(shí)，刪除上述借閱記錄； DELETEFROM BRWHERE rnum=(select rnumfrom reawhere rname = '王小平')（5）針對(duì)以上三個(gè)表，用SQL語(yǔ)言完成以下各項(xiàng)查詢：1）查詢?nèi)w圖書(shū)的圖書(shū)號(hào),書(shū)名,作者,出版社,單價(jià)。select bnum,bname,bwri,bpub,bprifrom book;2） 查詢?nèi)w圖書(shū)的信息,其中單價(jià)打8折，并且將該列設(shè)置別名為打折價(jià) 。select bnum,bname,bwri,bpub

9、, bpri*0.8 打折價(jià)from book;3） 顯示所有借閱者的讀者號(hào)，并去掉重復(fù)行。select distinct rnumfrom br4） 查詢所有單價(jià)在20到30之間的圖書(shū)信息。select *from bookwhere bpri between 20 and 305） 查詢所有單價(jià)不在20到30之間的圖書(shū)信息。select *from bookwhere bpri not between 20 and 306） 查詢機(jī)械工業(yè)出版社、科學(xué)出版社、人民郵電出版社的圖書(shū)信息select *from bookwhere bpub in ('機(jī)械工業(yè)出版社','

10、科學(xué)出版社','人民郵電出版社')7） 查詢既不是機(jī)械工業(yè)出版社、也不是科學(xué)出版社出版的圖書(shū)信息select *from bookwhere bpub not in ('機(jī)械工業(yè)出版社','科學(xué)出版社')8） 查找姓名的第二個(gè)字符是建并且只有兩三個(gè)字符的讀者的讀者號(hào)、姓名。select rnum ,rnamefrom reawhere rname in ('_建，_建_')9） 查找姓名以王開(kāi)頭的所有讀者的讀者號(hào)、姓名。select rnum ,rnamefrom reawhere rname like '王%&#

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

12、有歸還日期的借閱信息。select *from brwhere brback != 'null'14） 查詢單價(jià)在20元以上，30元以下的機(jī)械工業(yè)出版社出版的圖書(shū)名，單價(jià)。select bname,bprifrom bookwhere (bpri between 20 and 30) and bpub = '機(jī)械工業(yè)出版社'15） 查詢機(jī)械工業(yè)出版社或科學(xué)出版社出版的圖書(shū)名，出版社,單價(jià)。select bname,bpub,bprifrom bookwhere bpub = '機(jī)械工業(yè)出版社' or bpub = '科學(xué)出版社'1

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

14、y rnumhaving COUNT(bnum) >2order by (count (bnum) desc（6）針對(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)、姓名、借閱的圖書(shū)名、借出日期、歸還日期。select br.bnum ,rea.rname,book.bname,br.brback ,br.brdate from rea,br,b

15、ook where rea.rnum = br.rnum and book.bnum=br.bnum 3） 查詢借閱了機(jī)械工業(yè)出版社出版,并且書(shū)名中包含數(shù)據(jù)庫(kù)三個(gè)字的圖書(shū)的讀者，顯示讀者號(hào)、姓名、書(shū)名、出版社，借出日期、歸還日期。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.bnu

16、m 4） 查詢至少借閱過(guò)1本機(jī)械工業(yè)出版社出版的書(shū)的讀者的讀者號(hào)、姓名、書(shū)名，借閱本數(shù)，并按借閱本數(shù)多少降序排列。select 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） 查詢與王平的辦公電話

17、相同的讀者的姓名。select r1.rnamefrom rea r1,rea r2where r1.rname = '王平' and r1.rphone = r2.rphone6） 查詢辦公電話為88320701的所有讀者的借閱情況，要求包括借閱了書(shū)籍的讀者和沒(méi)有借閱的讀者，顯示他們的讀者號(hào)、姓名、書(shū)名、借閱日期。select rea.rnum,rname,bname,brdatefrom book,rea,brwhere rphone = '88320701' and rea.rnum = br.rnum and book.bnum = br.bnum 7）

18、 查詢所有單價(jià)小于平均單價(jià)的圖書(shū)號(hào)、書(shū)名、出版社select bnum,bname,bpubfrom book where bpri < (select AVG(bpri)from book)8） 查詢科學(xué)出版社的圖書(shū)中單價(jià)比機(jī)械工業(yè)出版社最高單價(jià)還高的的圖書(shū)書(shū)名、單價(jià)。select bname ,bprifrom bookwhere bpub = '科學(xué)出版社' and bpri > (select MAX (bpri) rom book where bpub = '機(jī)械工業(yè)出版社')9） 查詢'科學(xué)出版社'的圖書(shū)中單價(jià)比'

19、機(jī)械工業(yè)出版社'最低單價(jià)高的的圖書(shū)書(shū)名、單價(jià)。select bname ,bprifrom bookwhere bpub = '科學(xué)出版社' and bpri > (select Min (bpri) from book where bpub = '機(jī)械工業(yè)出版社')10） 查詢已被借閱過(guò)并已歸還的圖書(shū)信息。select *from bookwhere bnum in ( select bnumfrom brwhere brback is not null)11） 查詢從未被借閱過(guò)的圖書(shū)信息。select *from bookwhere bnum

20、not in ( select bnumfrom br)12） 查詢正在借閱的圖書(shū)信息。select *from bookwhere bnum in ( select bnumfrom brwhere brdate not like 'null' and brback like null)13） 查詢借閱了機(jī)械工業(yè)出版社出版的書(shū)名中含有數(shù)'據(jù)庫(kù)書(shū)'三個(gè)字的圖書(shū)、或者借閱了科學(xué)出版社出版的書(shū)名中含有數(shù)'據(jù)庫(kù)書(shū)'三個(gè)字的圖書(shū)的讀者姓名、書(shū)名。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ū)名中含有數(shù)'據(jù)庫(kù)書(shū)'三個(gè)字的圖