數(shù)據(jù)庫原理(第三版)張紅娟部分課后習(xí)題參考

1、第一章入校年份班級所在專業(yè)班級號課程屬于班級學(xué)生17、 班級人數(shù)1班長的學(xué)號成績NMN學(xué)分課程名課程號年齡性別姓名學(xué)號選修姓名教師號入校年份班級所在專業(yè)班級號課程屬于班級學(xué)生18、 職稱教師班級人數(shù)專業(yè)屬于M1班長的學(xué)號成績NNMN學(xué)分課程名課程號年齡性別姓名學(xué)號選修會費負(fù)責(zé)人姓名成立時間學(xué)會名稱入會年份MN學(xué)會加入20、供應(yīng)商（供應(yīng)商代號，供應(yīng)商名稱，聯(lián)系電話）項目（項目代號，項目名稱，項目負(fù)責(zé)人）零件（零件號，零件名，價格）供應(yīng)商-項目（項目代號，供應(yīng)商代號）項目-零件（項目代號，零件號，零件數(shù)）供應(yīng)商-零件（供應(yīng)商代號，零件號，零件數(shù)）職員姓名職員號名稱價格職務(wù)名稱位置價格生產(chǎn)屬于裝配工

2、作工作生產(chǎn)處部門設(shè)備設(shè)備處零件職員21、屬于NN1111NNNNMM設(shè)備號地址電話年齡性別部門名電話地址部門號規(guī)格零件號名稱電話地址單位號單位號倉庫保管員姓名倉庫號電話產(chǎn)品號價格廠長姓名廠名零件號價格重量車間主任姓名車間號電話地址職工號性別工種年齡姓名MN組裝MM11M11NNNNNNN存放存放包含包含生產(chǎn)生產(chǎn)屬于工廠工人倉庫零件產(chǎn)品車間22、（1）第三章10、設(shè)有一數(shù)據(jù)庫 GradeManager（成績管理），包括四個表：學(xué)生表（Student）、課程表（Course）、班級表（Class）以及成績表（Grade），其結(jié)構(gòu)如表3-4所示，數(shù)據(jù)如表3-5所示。試用SQL語句創(chuàng)建四個表。表3-4

3、 成績管理數(shù)據(jù)庫的表結(jié)構(gòu)表一 Student屬性名數(shù)據(jù)類型可否為空含義SnoChar（7）否學(xué)號（唯一）SnameVarChar（20）否學(xué)生姓名SsexChar（2）否性別SageSmallint可年齡ClnoChar（5）否學(xué)生所在班級表二 Course屬性名數(shù)據(jù)類型可否為空含義CnoChar（1）否課程號（唯一）CnameVarChar（20）否課程名稱CreditSmallint可學(xué)分表三 Class屬性名數(shù)據(jù)類型可否為空含義ClnoChar（5）否班級號（唯一）SpecialityVarChar（20）否班級所在專業(yè)InyearChar（4）否入校年份NumberInteger可班級

4、人數(shù)MonitorChar（7）可班長學(xué)號表四 Grade屬性名數(shù)據(jù)類型可否為空含義SnoChar（7）否學(xué)號CnoChar（7）否課程號GmarkNumeric（4,1）可成績表3-5 成績管理數(shù)據(jù)庫中的數(shù)據(jù)表一 StudentSnoSnameSsexSageClno2000101李勇男20003112000102劉詩晨女19003112000103王一鳴男20003122000104張婷婷女21003122001101李勇敏女19013112001102賈向東男22013112001103陳寶玉男20013112001104張逸凡男2101311表二 CourseCnoCnameCredi

5、t1數(shù)據(jù)庫42離散數(shù)學(xué)33管理信息系統(tǒng)24操作系統(tǒng)45數(shù)據(jù)結(jié)構(gòu)46數(shù)據(jù)處理27C語言4表三 ClassClnoSpecialityInyearNumberMonitor00311計算機軟件2000120200010100312計算機應(yīng)用2000140200010301311計算機軟件20012202001103表四 GradeSnoCnoGmark20001011922000101388200010158620001021782000102655200010336520001036782000103566200010415420001046832001101270200110146520011

6、022802001102490200010327620001034562000103788答：CREATE TABLE Student(Sno Char(7)NOT NULL UNIQUE,Sname VarChar(20) NOT NULL,Ssex Char(2) NOT NULL,Sage Smallint,Clno Char(5) NOT NULL);CREATE TABLE Course(CnoChar(1) NOT NULL UNIQUE,CnameVarChar(20)NOT NULL,CreditSmallint);CREATE TABLEClass(ClnoChar(5)NO

7、T NULLUNIQUE,SpecialityVarChar(20)NOT NULL,InyearChar(4)NOT NULL,NumberInteger,MonitorChar(7);CREATE TABLE Grade(SnoChar(7)NOT NULL,CnoChar(1)NOT NULL,GmarkNumeric(4,1);INSERTINTOStudentVALUE(2000101,李勇,男,20,00311);INSERTINTOStudentVALUE(2000102,劉詩晨,女,19,00311);INSERTINTOStudentVALUE(2000103,王一鳴,男,2

8、0,00312);INSERTINTOStudentVALUE(2000104,張婷婷,女,21,00312);INSERTINTOStudentVALUE(2001101,李勇敏,女,19,01311);INSERTINTOStudentVALUE(2001102,賈向東,男,22,01311);INSERTINTOStudentVALUE(2001103,陳寶玉,男,20,01311);INSERTINTOStudentVALUE(2001104,張逸凡,男,21,01311);INSERTINTOCourseVALUE(1,數(shù)據(jù)庫,4);INSERTINTOCourseVALUE(2,離

9、散數(shù)學(xué),3);INSERTINTOCourseVALUE(3,管理信息系統(tǒng),2);INSERTINTOCourseVALUE(4,操作系統(tǒng),4);INSERTINTOCourseVALUE(5,數(shù)據(jù)結(jié)構(gòu),4);INSERTINTOCourseVALUE(6,數(shù)據(jù)處理,2);INSERTINTOCourseVALUE(7,c語言,4);INSERTINTOClassVALUE(00311,計算機軟件,2000,120,2000101);INSERTINTOClassVALUE(00312,計算機應(yīng)用,2000,140,2000103);INSERTINTOClassVALUE(01311,計算機

10、軟件,2001,220,2001103);INSERTINTOGradeVALUE(2000101,1,92);INSERTINTOGradeVALUE(2000101,3,88);INSERTINTOGradeVALUE(2000101,5,86);INSERTINTOGradeVALUE(2000102,1,78);INSERTINTOGradeVALUE(2000102,6,55);INSERTINTOGradeVALUE(2000103,3,65);INSERTINTOGradeVALUE(2000103,6,78);INSERTINTOGradeVALUE(2000103,5,66)

11、;INSERTINTOGradeVALUE(2000104,1,54);INSERTINTOGradeVALUE(2000104,6,83);INSERTINTOGradeVALUE(2001101,2,70);INSERTINTOGradeVALUE(2001101,4,83);INSERTINTOGradeVALUE(2001102,2,80);INSERTINTOGradeVALUE(2001102,4,90);INSERTINTOGradeVALUE(2000103,1,83);INSERTINTOGradeVALUE(2000103,2,76);INSERTINTOGradeVALU

12、E(2000103,4,56);INSERTINTOGradeVALUE(2000103,7,88);11、針對T10的四個表，用SQL語言完成以下各項操作：（1）給學(xué)生表增加一屬性Nation（民族），數(shù)據(jù)類型為VarChar（20）；ALTERTABLEStudentALTERCOLUMNNationVarChar(20);（2）刪除學(xué)生表中新增的屬性Nation；ALTERTABLEStudentDROPCOLUMNNation;（3）向成績表中插入記錄（“2001110”，“3”，80）；INSERTINTOGradeVALUE(2001110,3,80);（4）將學(xué)號為“200110

13、”的學(xué)生的成績修改為70分；UPDATEGradeSETGmark = 70WHERE Sno = 2001110;（5）刪除學(xué)號為“2001110”的學(xué)生的成績記錄；DELETEFROMGradeWHERESno = 2001110;（6）在學(xué)生表的Clno屬性上創(chuàng)建一個名為IX_Class的索引，以班級號的升序排序；CREATEINDEXIX_ClassONStudent(Clno);（7）刪除IX_Class索引DROPINDEXStudent.IX_Class;12、針對T10的四個表，用SQL語言完成以下各項操作：（1）找出所有被學(xué)生選修了的課程號；SELECTDISTINCT Cn

14、o 課程號 FROM Grade（2）找出01312班女生的個人信息；SELECT * FROM StudentWHERE Clno='01311' and Ssex='女'（3）找出01311班和01312班的學(xué)生姓名 性別 出生年份；SELECT sname 學(xué)生姓名,ssex 性別,2011-sage 出生年份FROM StudentWHERE Clno='01311' or Clno='01312'（4）找出所有姓李的學(xué)生的個人信息；SELECT * FROM StudentWHERE Sname like '李%

15、'（5）找出李勇所在班級的學(xué)生人數(shù)；SELECT COUNT(*) FROM StudentWHERE Clno in(SELECT Clno FROM StudentWHERE Sname='李勇')（6）找出課程名為操作系統(tǒng)的平均成績 最高分 最低分；SELECT AVG(Gmark) 平均成績,MAX(Gmark) 最高分,MIN(Gmark) 最低分 FROM GradeWHERE Cno in(SELECT Cno FROM CourseWHERE Cname='操作系統(tǒng)')（7）選修了課程的學(xué)生人數(shù)；SELECT COUNT(DISTINCT

16、 sno) 學(xué)生人數(shù)FROM Grade（8）選修了操作系統(tǒng)的學(xué)生人數(shù)；SELECT COUNT(DISTINCT sno) 學(xué)生人數(shù)FROM GradeWHERE Cno in(SELECT Cno FROM CourseWHERE Cname='操作系統(tǒng)')（9）找出2000級計算機軟件班的成績?yōu)榭盏膶W(xué)生姓名。SELECT Sname 學(xué)生姓名 FROM(SELECT * FROM StudentWHERE Clno in(SELECT Clno FROM ClassWHERE Speciality='計算機軟件' AND Inyear='2000&

17、#39;) ) ruanjianWHERE ruanjian.Sno not in(SELECT DISTINCT Sno FROMGrade)with ruanjian as(SELECT Sno,SnameFROM Student join Classon Student.Clno=Class.ClnoWHERE Class.Speciality='計算機軟件' and Class.Inyear='2000')SELECT Sname 學(xué)生姓名 FROM ruanjianWHERE not exists(SELECT * FROM GradeWHERE ru

18、anjian.Sno=Grade.Sno)13、針對T10的四個表，用SELECT的嵌套查詢完成以下各項查詢：（1）找出和李勇在同一個班級的學(xué)生信息；SELECT * FROM StudentWHERE Sname<>'李勇' and Clno in(SELECT Clno FROM StudentWHERE Sname='李勇')（2）找出所有與學(xué)生李勇有相同選修課程的學(xué)生信息；SELECT * FROMS tudentWHERE Sname<>'李勇' and exists(SELECT * FROM GradeWHE

19、RE Student.Sno=Grade.Sno and Grade.Cno in(SELECT Cno FROMG radeWHERE Sno in(SELECT Sno FROM Student WHERE Sname='李勇')（3）找出年齡介于學(xué)生李勇和25歲之間的學(xué)生信息；SELECT * FROMStudentWHERE Sage < 25 and Sage > (SELECT Sage FROMStudent WHERE Sname='李勇')（4）找出選修了課程是操作系統(tǒng)的學(xué)生學(xué)號和姓名；SELECT Sno 學(xué)號,Sname 姓名

20、FROM StudentWHERE exists(SELECT *FROM Grade WHERE Cno in(SELECT Cno FROM Course WHERE Cname='操作系統(tǒng)')and Student.Sno=Grade.Sno)（5）找出沒有選修1號課程的所有學(xué)生姓名；SELECT Sname 姓名 FROM StudentWHERE not exists(SELECT * FROM Grade WHERE Student.Sno=Grade.Sno and Cno=1)（6）找出選修了全部課程的學(xué)生姓名。SELECT Sname 姓名 FROM Stud

21、entWHERE not exists(SELECT Cno FROM CourseexceptSELECT Cno FROMGradeWHERE Student.Sno=Grade.Sno)14、針對T10的四個表，用SQL語言完成以下各項查詢：（1）查詢選修了3號課程的學(xué)生學(xué)號及成績,并按成績的降序排列；SELECT Sno 學(xué)號,Gmark 成績 FROM GradeWHERE Cno=3ORDER BY Gmark desc（2）查詢?nèi)w學(xué)生信息,要求查詢結(jié)果按班級號升序排列,同一班級學(xué)生按年齡降序排列；SELECT * FROM StudentORDER BY Clno asc,Sa

22、ge desc（3）求每個課程號及相應(yīng)的選課人數(shù)；SELECT Grade.Cno 課程號,COUNT(Grade.Cno) 選課人數(shù) FROM Grade join Course on Grade.Cno=Course.CnoGROUP BY Grade.Cno（4）查詢選修了3門以上課程的學(xué)生學(xué)號SELECT Sno 學(xué)號 FROMGradeGROUP BY SnoHAVING COUNT(Sno)>315、針對T10的四個表，用SQL語言完成以下各項操作：（1）將01311班的全體學(xué)生的成績置零UPDATE Grade SET Gmark=0WHERE Sno in(SELECT

23、Sno FROM StudentWHERE Clno='01311')（2）刪除2001級計算機軟件的全體學(xué)生的選課記錄DELETE FROM GradeWHERE Sno in(SELECT Sno FROM StudentWHERE Clno in(SELECT Clno FROM ClassWHERE Speciality='計算機軟件' and Inyear='2001')（3）學(xué)生李勇已退學(xué),從數(shù)據(jù)庫中刪除有關(guān)他的記錄；DELETE FROM GradeWHERE Sno in(SELECT Sno FROM StudentWHERE

24、Sname='李勇')UPDATE Class SET Number=Number-1WHERE Clno in(SELECT Clno FROM StudentWHERE Sname='李勇')UPDATE Class SET Monitor=case WHEN Monitor=(SELECT Sno FROM Student WHERE Sname='李勇')THEN ''endFROM ClassWHERE Clno in(SELECT Clno FROM Student WHERE Sname='李勇')D

25、ELETE FROMStudentWHERE Sname='李勇'（4）對每個班,求學(xué)生的平均年齡,并把結(jié)果存入數(shù)據(jù)庫。ALTER TABLE Class ADD HCage Smallint NULLUPDATE Class SET Cage=caseWHEN Clno='00311' THEN (SELECT AVG(Sage) FROM Student WHERE Clno='00311')WHEN Clno='00312' THEN (SELECT AVG(Sage) FROM Student WHERE Clno=

26、9;00312')WHEN Clno='01311' THEN (SELECT AVG(Sage) FROM Student WHERE Clno='01311')endFROM Class16、 視圖操作：（1） 建立01311班選修了1號課程的學(xué)生視圖Stu_01311_2；create view Stu_01311_1as SELECT Student.Sno,Sname,Gmark FROM Student,Grade WHERE Cno = '1' and Clno = '01311' and Student.S

27、no = Grade.Snowith check option（2）建立01311班選修了1號課程并且成績不合格的學(xué)生視圖Stu_01311_2；create view Stu_01311_2as SELECT * FROM Stu_01311_1 WHERE Gmark < 60（3）建立視圖Stu_year,由學(xué)生學(xué)號、姓名、出生年份組成；create view Stu_year(Sno,Sname,yearsas SELECT Sno,Sname,year(getdate() - Sage FROM Student（4）查詢1990以后出生的學(xué)生姓名；SELECT Sname FROM Stu_yearWHERE years > 1990;（5）查詢01311班選修了1號課程并且成績不合格的學(xué)生的學(xué)號、姓名、出生年份。SELECT * FROM Stu_yearWHERE Sno in (SELECT Sno FROM Stu_01311_2);第七章15、設(shè)有關(guān)系模式R(A,B,C,D)，函數(shù)依賴集F=AB,BC，試求此關(guān)系的碼，并指出在函數(shù)依賴的范圍內(nèi)，它達(dá)到了第幾范式。此關(guān)系的碼是A；此函數(shù)依賴集沒有部分函數(shù)依賴，有傳遞函數(shù)依賴，所以是2NF。16、在函數(shù)依賴范圍內(nèi)，試問下列關(guān)系模