數(shù)據(jù)庫(kù)原理與技術(shù)簡(jiǎn)明教程(第二版)課后習(xí)題參考答案

1、第1章單選題：B B B B C/D C A B A簡(jiǎn)述題：1略2星期節(jié)數(shù)課程星期一1語(yǔ)文星期二1數(shù)學(xué)星期一2數(shù)學(xué)星期一3英語(yǔ)。姓名養(yǎng)老保險(xiǎn)失業(yè)保險(xiǎn)醫(yī)療保險(xiǎn)住房公積金張三3005075400李四2705080380王五28050905003略4略5略6略7略8略9略第2章實(shí)踐環(huán)節(jié)設(shè)計(jì)：1CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = '男' OR Ssex = '女'), Sage tinyint

2、CHECK (Sage >= 15 AND Sage <=45), Sdept char (20 ) DEFAULT '計(jì)算機(jī)系' )CREATE TABLE Course ( Cno char(10) NOT NULL, Cname char(20) NOT NULL, Ccredit tinyint CHECK (Ccredit > 0), Semester tinyint CHECK (Semester > 0), Period int CHECK (Period > 0), PRIMARY KEY(Cno) ) CREATE TABLE S

3、C ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint, CHECK (Grade >= 0 AND Grade <= 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) )2Insert into student values(9512101,李勇,男,19,計(jì)算機(jī)系)Insert into course valu

4、es(co1,數(shù)據(jù)庫(kù)原理,3,4,64)Insert into sc values(9512101,co1,90)3Drop TABLE student4 ALTER TABLE SC ADD XKLB char(4) 5 ALTER TABLE SC ALTER COLUMN XKLB char(6)6 Alter table sc drop column xklb7 SELECT Sno，Sname FROM Student 8 SELECT Sname FROM Student WHERE Sdept = '計(jì)算機(jī)系'9 SELECT Sname, Sage FROM S

5、tudent WHERE Sage < 20或：SELECT Sname, Sage FROM Student WHERE NOT Sage >= 2010 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23此句等價(jià)于：SELECT Sname, Sdept, Sage FROM Student WHERE Sage >=20 AND Sage<=2311 SELECT Sname, Ssex FROM Student WHERE Sdept IN ('信息系', 

6、9;數(shù)學(xué)系', '計(jì)算機(jī)系')此句等價(jià)于：SELECT Sname, Ssex FROM Student WHERE Sdept = '信息系' OR Sdept = '數(shù)學(xué)系' OR Sdept = '計(jì)算機(jī)系12 SELECT * FROM Student WHERE Sname LIKE 張%13 SELECT * FROM Student WHERE Sname LIKE 張李劉%14 SELECT Sname, Sno FROM Student WHERE Sname LIKE '_小大%'15 SELE

7、CT Sno, Cno FROM SC WHERE Grade IS NOT NULL16 SELECT Sname FROM Student WHERE Sdept=計(jì)算機(jī)系' AND Sage<2017 SELECT Sno, Grade FROM SC WHERE Cno='c02' ORDER BY Grade DESC18 SELECT * FROM Student ORDER BY Sdept, Sage DESC19 SELECT COUNT （DISTINCT Sno） FROM SC20 SELECT SUM(Grade) FROM SC WHE

8、RE Sno = '9512101'21 SELECT AVG(Grade) FROM SC WHERE Cno='C01'22 SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno='C01'23 SELECT Cno AS 課程號(hào), COUNT(Sno) AS 選課人數(shù) FROM SC GROUP BY Cno24 SELECT Sno 學(xué)號(hào), COUNT(*) 選課門數(shù), AVG(Grade) 平均成績(jī) FROM SC GROUP BY Sno25 SELECT Sno FROM SC GROUP

9、 BY Sno HAVING COUNT(*) >226 SELECT Sno, AVG(Grade) 平均成績(jī), COUNT(*) 修課門數(shù) FROM SC GROUP BY Sno HAVING COUNT(*) >= 227 SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = '計(jì)算機(jī)系'28 SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Cou

10、rse c ON c.Cno = SC.Cno WHERE Sdept = '信息系' AND Cname = 'VB'29 SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = '劉晨' AND S2.Sname != '劉晨 或SELECT Sname, Sdept FROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname

11、 = '劉晨')30 SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno 也可以用右外連接實(shí)現(xiàn)： SELECT Student.Sno, Sname, Cno, Grade FROM SC RIGHT OUTER JOIN Student ON Student.Sno = SC.Sno31SELECT Sno, Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SCWHERE Cno IN (SE

12、LECT Cno FROM CourseWHERE Cname = '數(shù)據(jù)庫(kù)原理') )用多表連接實(shí)現(xiàn)： SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON Course.Cno = SC.Cno WHERE Cname = '數(shù)據(jù)庫(kù)原理'32 SELECT Sno , Grade FROM SC WHERE Cno = 'c02' AND Grade > ( SELECT AVG(Grade) FROM SC WHERE

13、 Cno = 'c02')33 INSERT INTO Student VALUES (9521105, 陳冬, '男', 18, '信息系')34 UPDATE Student SET Sage = 21 WHERE Sno = '9512101'35 DELETE FROM SC WHERE Grade < 6036 DELETE FROM SC WHERE Grade < 60 AND Sno IN ( SELECT Sno FROM Student WHERE Sdept = 計(jì)算機(jī)系 )用多表連接實(shí)現(xiàn) DEL

14、ETE FROM SC FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = 計(jì)算機(jī)系A(chǔ)ND Grade < 6037 delete from sc where sno in (select sno from student where sname=李勇 ) and cno in(select cno from course where cname=數(shù)據(jù)庫(kù)原理 ) 多表連接方式： delete from sc from sc join student on sc.sno=student.sno join course on

15、o=owhere sname=李勇 and cname=數(shù)據(jù)庫(kù)原理38 select student.*,course.* from student join sc on student.sno=sc.sno join course on o=owhere sno in (select sno from sc group by sno having count(cno) >2)派生關(guān)系(沒調(diào)試，可以不講)： select student.*,course.* from student join sc on student.sno=sc.sno join course on o=ojoin

16、 (select sno from sc group by sno having count(cno) >3) as tt(sno) on tt.sno=student.sno39(方法可以有很多種)select * from student where sno in (select sno from sc where grade=(select max(grade) from sc where cno =(select cno from course where cname=數(shù)據(jù)庫(kù)原理) and cno =(select cno from course where cname=數(shù)據(jù)庫(kù)原

17、理) ) )40select * from student where sno in ( select sno from sc join ( select max(grade),cno from sc group by cno) as max_grade(m_grade,cno) on o=max_o and sc.grade=max_grade.m_grade )41select * from student where sno in ( select sno from sc group by sno having count(*) >=all ( select count(*) fr

18、om sc group by sno )42 略43 略44 select top 3 with ties sname,sdept,grade from student join sc on Student.sno=sc.sno join course on o=o where cname=VB order by grade desc45 select sname,sdept from student where sno not in (select sno from sc where cno=c01)Select sname,sdept from student where not exis

19、ts(select * from sc where sno=student.sno and cno=c01)46 select Top 3 sname,sage,sdept from student order by sage desc47 select cno,count(*) as total,avg(grade) as avggrade,max(grade) as maxgrade,min(grade) as mingrade from student join sc on student.sno=sc.sno where sdept=計(jì)算機(jī)系 group by cno48 select

20、 ame,c1.credit from course c1 join course c2 on c1.credit=c2.credit where ame=數(shù)據(jù)結(jié)構(gòu)49 select o,cname from course c left join sc on o=o where o is null50 select sname,sex from student where sno not in (Select sno from sc join course on o=o where cname=VB) And sdept=計(jì)算機(jī)系習(xí)題：?jiǎn)芜x題：B B A D A / C B A A D / B

21、 D D A B / C C C B B簡(jiǎn)述題：已知有顧客購(gòu)買商品信息的三張表：顧客表Customer、定購(gòu)表Order、商品表Commodity。按要求創(chuàng)建三張表：1 表名：Customer屬性：ID 字符型 最大10個(gè)字符 顧客編號(hào) NAME 字符型 最大16個(gè)字符 顧客姓名 SEX 字符型 最大2個(gè)字符 性別 MOBILE 字符型 最大11個(gè)字符 移動(dòng)電話 ADDRESS 字符型 最大50個(gè)字符 家庭住址約束： ID主碼； NAME非空屬性； SEX取值“男”或“女”；MOBILE唯一性； ADDRESS默認(rèn)為UNKOWN；表名：OrderBook 屬性：CSID 字符型 最大10個(gè)字符

22、 顧客編號(hào) CMID 字符型 最大12個(gè)字符 商品編號(hào) COUNT 整型 定購(gòu)數(shù)量 BOOKDATE 日期型 訂貨日期 TAKEDATE 日期型 交貨日期 約束：CSID，CMID主碼； 定購(gòu)數(shù)量要大于0； 訂貨日期要小于交貨日期； CSID外碼，引用Customer表的ID； CMID外碼，引用Commodity表的ID；表名：Commodity 屬性：ID 字符型 最大12個(gè)字符 商品編號(hào) NAME 字符型 最大20個(gè)字符 商品名稱 MANUFACTURE 字符型 最大20個(gè)字符 生產(chǎn)廠商 PRICE 小數(shù)型 最大不超過4位數(shù)，保留2位小數(shù) 商品單價(jià) 約束：ID主碼； NAME非空； 針對(duì)

23、上面的三個(gè)基本表做如下練習(xí)：2往基本表Customer中插入顧客元組（”0421F901”，”WU”，”女”3往基本表Commodity中插入一條商品記錄（“03110408591”，“牙膏”，“保潔公司”，5.00）4修改“WANGYAN”顧客定購(gòu)商品的記錄交貨日期為2005-12-25。Update order set taketime=2005-12-25 where csid in (select id from customer where name=wangyan)5. 查詢“ANAN”顧客的手機(jī)號(hào)和住址。 Select mobile, address f

24、rom customer where name=anan6. 查詢商品的平均價(jià)格高于75元錢的廠商名稱。 Select manufacture from commodity group by manufacture having avg(price) >757. 查詢顧客的定購(gòu)信息，并按訂貨日期升序排列，若訂貨日期相同，則按定購(gòu)數(shù)量降序排列。 Select * from orderbook order by bookdate,count desc8. 查詢定購(gòu)數(shù)量超過100的顧客姓名、電話和住址。 Select name, mobile, address from customer wh

25、ere id in(select csid from orderbook where count>100)9. 查詢沒有訂單的商品編號(hào)、名稱和廠商。Select id,name, manufacture from comodity where id not in(select csid from orderbook)10. 查詢定購(gòu)了商品號(hào)為“M900532481”的顧客總?cè)藬?shù)和最高數(shù)量。 Select count(*) ,max(count) from order where cmid=m90053248111. 查詢定購(gòu)了“可比克”商品的顧客編號(hào)、姓名。Select id,name

26、from customer where id in(select csid from order where cmid in(select id from comodity where name= 可比克)12. 查詢商品名中包含“糖”的所有商品的編號(hào)、廠商、單價(jià)。 Select id,manufacture ,price from comodity where name like %糖%13. 查詢至少有兩位顧客定購(gòu)的商品的名稱。 Select name from comodity where id in(select cmid from order group by cmid having

27、 count(*)>=2)14. 查詢截至2005年底所有商品訂單最晚的交貨日期。 SELECT taketime from order where taketime = (select max(taketime) from order where taketime<2005-12-31)15. 查詢住址中含有“軟件園”三個(gè)字的顧客人數(shù)。Select count(*) from customer where address like%軟件園%16查詢訂購(gòu)了“保潔公司”生產(chǎn)的產(chǎn)品中價(jià)格最高的商品的客戶信息。Select customer.* from customer, OrderB

28、ook, Commodity where customer.id= orderbook. CSID and Commodity.id=orderbook.cmid and Commodity.id in(Select id from Commodity where MANUFACTURE =“保潔公司” and price=(Select max(price) from Commodity where MANUFACTURE =“保潔公司”)17查詢2007年3月份住址在“大連”的顧客對(duì)“上好佳”商品的定購(gòu)信息，列出顧客姓名，商品數(shù)量和定購(gòu)日期。Select ,cou

29、nt, BOOKDATE from customer, OrderBook, Commodity where customer.id= orderbook. CSID and Commodity.id=orderbook.cmid and ADDRESS like%大連% and C=上好佳 and BOOKDATE between 2007-03-01 and 2007-03-3118查詢訂單最多的顧客姓名。Select name from customer where id in(Select CSID from OrderBook group by csid h

30、aving count(*)>=all(select Count(*) from orderbook group by csid)19查詢定購(gòu)了“保潔公司”的商品最多的客戶信息。Select * from customer where id in(Select csid from orderbook where cmid in(Select id from Commodity where MANUFACTURE =“保潔公司”) group by csid having count(*)>=all(select count(*) from orderbook where cmid

31、in(Select id from Commodity where MANUFACTURE =“保潔公司”) group by csid )20把“雀巢奶粉”的定購(gòu)商品記錄全部刪去。 Delete from order where cmid in (select id from commodity where name=雀巢奶粉)第3章1實(shí)體完整性和參照完整性。2主碼唯一 、 外碼參考。3主碼。4實(shí)體完整性和參照完整性5屬性列6投影 選擇7選擇 投影 連接 除8Where select9a 1+b110a 2*b211（student）12（student）13（student）14（cour

32、se）15. (（student）)16. ( (student sc)17. (student sc course)18. ( (student sc)÷( (student sc course)- ( (student)第4章1. 基本表或視圖、定義、數(shù)據(jù)2With check option3數(shù)據(jù)庫(kù)結(jié)構(gòu)、列、邏輯指針清單4聚集索引、非聚集索引51）create view s_c_view(sno,sname,sdept,cno,cname,ccredit,grade) as Select student.sno,sname,sdept,o,cname,ccredit,grade

33、from student,sc,course where student.sno=sc.sno and o=o2) select sno,sname,cno,cname,grade from s_c_view where grade>=603)create view compstu_view (學(xué)號(hào),姓名,總成績(jī)) as Select sno,sname,sum(grade) from s_c_view where sdept=計(jì)算機(jī)系 Group by sno,sname having count(*)>=24)create view cs_view as select sno,

34、sname,ssex,sage from student Where sdept=計(jì)算機(jī)系 and sage>23 with check option5)drop view s_c_view6)create index index1 on sc(cno,grade desc)6視圖是一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表。他與基本表不同，是一個(gè)虛表，可以和基本表一樣能被查詢，被刪除，但對(duì)視圖的增、刪、改操作則有一定限制。、表是內(nèi)模式，視圖是外模式視圖的建立和刪除只影響視圖本身，不影響對(duì)應(yīng)的基本表。7略8略9索引的優(yōu)點(diǎn)。1 加大數(shù)據(jù)的檢索速度，也是最主要的原因2 通過創(chuàng)建唯一確保每行數(shù)據(jù)的唯一

35、性。3 加速表與表之間的連接。4 提高查詢中分組和排序的效率。索引的缺點(diǎn)。創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，而且這種時(shí)間會(huì)隨著數(shù)據(jù)量的增加而增加。索引要占據(jù)數(shù)據(jù)庫(kù)的物理空間，索引越多，占據(jù)的空間越多。維護(hù)索引要花費(fèi)很多的時(shí)間，尤其是在增加、刪除和修改表中數(shù)據(jù)的時(shí)候。第5章1一、一、多2物理3瀏覽器、客戶端4概念模式也稱模式，是數(shù)據(jù)庫(kù)中全部數(shù)據(jù)的邏輯結(jié)構(gòu)和特征的描述。外模式也稱用戶模式或子模式。是用戶與數(shù)據(jù)庫(kù)系統(tǒng)的接口，是用戶用到的那部分?jǐn)?shù)據(jù)的描述。內(nèi)模式也稱存儲(chǔ)模式。是數(shù)據(jù)物理結(jié)構(gòu)和存儲(chǔ)方式的描述，是數(shù)據(jù)在數(shù)據(jù)庫(kù)內(nèi)部的表示方式。定義所有的內(nèi)部記錄類型、索引和文件的組織方式，以及數(shù)據(jù)控制方面的細(xì)節(jié)。

36、5略第6章單選題：A B B A B / D B A D B簡(jiǎn)述題：1 .候選碼（學(xué)號(hào)） 第二范式非主屬性（姓名、所在系、班號(hào)、班主任、系主任）分解成如下三個(gè)第三范式的表:學(xué)生表（學(xué)號(hào)，姓名，班號(hào)，系別）班級(jí)表（班號(hào)，班主任）系別表（系別，系主任）2 .候選碼為：（課程號(hào)，授課教師號(hào)） 第一范式非主屬性有：課程名、學(xué)分、教師名、授課時(shí)數(shù)分解成如下三個(gè)第三范式的表:課程表（課程號(hào)，課程名，學(xué)分）教師表（授課教師號(hào)，教師名）授課表（課程號(hào)，授課教師號(hào)，授課時(shí)數(shù)）3.滿足BCNF4 .候選碼為：職工號(hào) 第二范式非主屬性有：職工名，年齡，性別，單位號(hào)，單位名分解成如下兩個(gè)第三范式的表:職工表（職工號(hào)，

37、職工名，年齡，性別，單位號(hào)）單位表（單位號(hào)，單位名）5.略6.略7.略8 .候選碼為：學(xué)號(hào) 第二范式非主屬性有：姓名，出生日期，所在系，宿舍樓分解成如下兩個(gè)第三范式的表:學(xué)生表（學(xué)號(hào)，姓名，出生日期，所在系）宿舍（所在系，宿舍樓）9.一定、一定、一定、不一定10一定、一定、一定、一定、一定第7章單選題：B C C C D/C D D B D/B D簡(jiǎn)述題：1-8簡(jiǎn)答略(教材中有答案)。9(1)分支機(jī)構(gòu)員工貸款客戶工作nmnmnnn1儲(chǔ)蓄發(fā)放借貸1儲(chǔ)蓄賬戶名字資產(chǎn)Costumer-id姓名街道城市賬戶號(hào)開始工作日期親屬電話號(hào)碼姓名經(jīng)理idEmployee-id利率余額最近訪問日期金額貸款號(hào)n1n

38、nmnnn1(2)轉(zhuǎn)換成關(guān)系模式并指出每個(gè)模式的主碼，外碼表名屬性主碼外碼分支機(jī)構(gòu)名字，城市，資產(chǎn)名字貸款貸款號(hào)，金額，分支機(jī)構(gòu)名稱貸款號(hào)分支機(jī)構(gòu)名稱儲(chǔ)蓄賬戶賬戶號(hào)，金額，利率，最近訪問日期，透支額賬戶號(hào)客戶costumer-id，姓名，街道，城市，employee-idcostumer-idemployee-id員工employee-id，姓名，經(jīng)理id，電話號(hào)碼，開始工作日期，親屬employee-id借貸貸款號(hào)，costumer-id貸款號(hào)，costumer-id貸款號(hào)，costumer-id儲(chǔ)蓄賬戶號(hào)，costumer-id賬戶號(hào)，costumer-id賬戶號(hào)，costumer-id1

39、0. (1) 根據(jù)上述規(guī)則設(shè)計(jì) E-R 模型。(2) 將E-R模型轉(zhuǎn)換成關(guān)系數(shù)據(jù)模型，并指出每個(gè)關(guān)系的主鍵和外鍵。部門（部門號(hào)，部門名）職工（職工號(hào)，職工名，性別，部門號(hào)）項(xiàng)目（工程號(hào)，項(xiàng)目名，部門號(hào)）參與（職工號(hào)，工程號(hào)，酬金）單獨(dú)的職工號(hào)和工程號(hào)是 外碼(3) 每一個(gè)關(guān)系模式是第三范式。第8章單選題：B B D C D / A D B簡(jiǎn)答與操作題略(教材中可找出答案)。第9章單選題：C A D A D /A C B D D / B C D A D簡(jiǎn)答題略(教材中可找出答案)。第10章單選題：C C A D B / C B D簡(jiǎn)單題：1 完全備份2-13操作略。第11章1 簡(jiǎn)答略(教材中可找出答案)。2（1）略（參見課件類似題目）。（2）略（參見課件類似題目）。（3）alter table readeradd telephone char(13)create trigger checktelon readerfor insert,updateasdeclare telephone char(13)select telephone=telephone from insertedif LEN(telephone)!=LEN('#39;) and telephone not like '