數(shù)據(jù)庫上機(jī)實(shí)驗(yàn)

1、附錄C上機(jī)實(shí)驗(yàn)C.1第4章上機(jī)實(shí)驗(yàn)下列實(shí)驗(yàn)均使用 SQL Server的SSMS工具實(shí)現(xiàn)。1 .用圖形化方法創(chuàng)建符合如下條件的數(shù)據(jù)庫（創(chuàng)建數(shù)據(jù)庫的方法可參見本書附錄A）:數(shù)據(jù)庫名為：學(xué)生數(shù)據(jù)庫主要數(shù)據(jù)文件的邏輯文件名為：Students_data，存放在D:Data文件夾下（若 D:盤中無此文件夾，請(qǐng)先建立此文件夾，然后再創(chuàng)建數(shù)據(jù)庫。），初始大小為：5MB， 增長方式為自動(dòng)增長，每次增加1MB。日志文件的邏輯文件名字為：Students_log，也存放在D:Data文件夾下，初始大小為：2MB，增長方式為自動(dòng)增長，每次增加10%。2 選用已建立的“學(xué)生數(shù)據(jù)庫”，寫出創(chuàng)建滿足表 C-1到4-4條

2、件的表的SQL語句，并執(zhí)行所寫代碼。（注：“說明”部分不作為表定義內(nèi)容）表C-1 Student 表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學(xué)號(hào)普通編碼定長字符串，長度為7主鍵Sname姓名普通編碼定長字符串，長度為10非空Ssex性別普通編碼定長字符串，長度為2取值范圍：男，女Sage年齡微整型（tinyint）取值范圍：15-45Sdept所在系普通編碼不定長字符串，長度為20默認(rèn)值為“計(jì)算機(jī)系”Sid身份證號(hào)普通編碼定長字符串，長度為10取值不重Sdate入學(xué)日期日期默認(rèn)為系統(tǒng)當(dāng)前日期表C-2 Course表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Cno課程號(hào)普通編碼定長字符串，長度為10主鍵Cname課程名普通

3、編碼不定長字符串，長度為 20非空Credit學(xué)時(shí)數(shù)整型取值大于0Semester學(xué)分小整型表C-3 SC表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學(xué)號(hào)普通編碼定長字符串，長度為7主鍵，引用Student的外鍵Cno課程號(hào)普通編碼定長字符串，長度為10主鍵，引用 Course的外鍵Grade成績小整型取值范圍為0-100表C-4 Teacher表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Tno教師號(hào)普通編碼定長字符串，長度為8非空Tname教師名普通編碼定長字符串，長度為10非空Salary工資定點(diǎn)小數(shù)，小數(shù)點(diǎn)前 4位，小數(shù)點(diǎn)后2位3. 寫出實(shí)現(xiàn)如下功能的SQL語句，并執(zhí)行所寫代碼，查看執(zhí)行結(jié)果。（1） 在Teacher

4、表中添加一個(gè)職稱列，列名為：Title，類型為nchar（4）。（2） 為Teacher表中的Title列增加取值范圍約束，取值范圍為：教授，副教授，講師。（3） 將Course表中Credit列的類型改為：tinyint。（4）刪除Student表中的Sid和Sdate列。（5） 為Teacher表添加主鍵約束，其主鍵為：Tno。C.2第5章上機(jī)實(shí)驗(yàn)本實(shí)驗(yàn)均在SQL Server的SSMS工具中實(shí)現(xiàn)。首先在已創(chuàng)建的“學(xué)生數(shù)據(jù)庫”中創(chuàng)建本 章表5-15-3所示的Student、Course和SC表，并插入表5-45-6所示數(shù)據(jù)，然后編寫實(shí) 現(xiàn)如下操作的SQL語句，執(zhí)行所寫的語句，并查看執(zhí)行結(jié)果

5、。1. 查詢SC表中的全部數(shù)據(jù)。2. 查詢計(jì)算機(jī)系學(xué)生的姓名和年齡。3. 查詢成績?cè)?080分的學(xué)生的學(xué)號(hào)、課程號(hào)和成績。4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.查詢計(jì)算機(jī)系年齡在 1820歲的男生姓名和年齡。查詢 C001 課程的最高分。查詢計(jì)算機(jī)系學(xué)生的最大年齡和最小年齡。統(tǒng)計(jì)每個(gè)系的學(xué)生人數(shù)。統(tǒng)計(jì)每門課程的選課人數(shù)和最高成績。統(tǒng)計(jì)每個(gè)學(xué)生的選課門數(shù)和考試總成績，并按選課門數(shù)升序顯示結(jié)果。列出總成績超過 200 的學(xué)生的學(xué)號(hào)和總成績。查詢選了 C002 課程的學(xué)生姓名和所在系。查詢考試成績 80 分以上的學(xué)生姓名、課程號(hào)和成績，

6、并按成績降序排列結(jié)果。查詢與 VB 在同一學(xué)期開設(shè)的課程的課程名和開課學(xué)期。查詢與李勇年齡相同的學(xué)生的姓名、所在系和年齡。查詢哪些課程沒有學(xué)生選修，列出課程號(hào)和課程名。 查詢每個(gè)學(xué)生的選課情況，包括未選課的學(xué)生，列出學(xué)生的學(xué)號(hào)、姓名、選的課程號(hào)。查詢計(jì)算機(jī)系哪些學(xué)生沒有選課，列出學(xué)生姓名。查詢計(jì)算機(jī)系年齡最大的三個(gè)學(xué)生的姓名和年齡。列出VB ”課程考試成績前三名的學(xué)生的學(xué)號(hào)、姓名、所在系和VB成績。查詢選課門數(shù)最多的前 2位學(xué)生，列出學(xué)號(hào)和選課門數(shù)。查詢計(jì)算機(jī)系學(xué)生姓名、年齡和年齡情況，其中年齡情況為：如果年齡小于18，則顯示“偏小；”如果年齡在 18-22，則顯示“合適”；如果年齡大于 22

7、 ，則顯示“偏大”。統(tǒng)計(jì)每門課程的選課人數(shù)， 包括有人選的課程和沒有人選的課程， 列出課程號(hào)， 選課人 數(shù)及選課情況， 其中選課情況為： 如果此門課程的選課人數(shù)超過 100 人，則顯示“人多”； 如果此門課程的選課人數(shù)在 40100，則顯示“一般”；如果此門課程的選課人數(shù)在 140 ，則顯示“人少”；如果此門課程沒有人選，則顯示“無人選” 。23. 查詢計(jì)算機(jī)系選了 VB 課程的學(xué)生姓名、所在系和考試成績，并將結(jié)果保存到新表 VB_Grade 中。24. 統(tǒng)計(jì)每個(gè)系的女生人數(shù)，并將結(jié)果保存到新表 Girls 中。25. 用子查詢實(shí)現(xiàn)如下查詢：（1 ）查詢選了 “C001 ”課程的學(xué)生姓名和所在

8、系。（ 2 ）查詢通信工程系成績 80 分以上的學(xué)生的學(xué)號(hào)和姓名。（ 3）查詢計(jì)算機(jī)系考試成績最高的學(xué)生的姓名。（ 4）查詢年齡最大的男生的姓名、所在系和年齡。26. 查詢 C001 課程的考試成績高于該課程平均成績的學(xué)生的學(xué)號(hào)和成績。27. 查詢計(jì)算機(jī)系學(xué)生考試成績高于計(jì)算機(jī)系學(xué)生平均成績的學(xué)生的姓名、考試的課程名和考試成績。28. 查詢 VB 課程考試成績高于 VB 平均成績的學(xué)生姓名和 VB 成績。29. 查詢沒選 VB 的學(xué)生姓名和所在系。30. 查詢每個(gè)學(xué)期學(xué)分最高的課程信息，列出開課學(xué)期、課程名和學(xué)分。31. 查詢每門課程考試成績最高的學(xué)生信息， 列出課程號(hào)、 學(xué)生姓名和最高成績，

9、 結(jié)果按課 程號(hào)升序排序，不包括沒考試的課程。32. 創(chuàng)建一個(gè)新表，表名為 test，其結(jié)構(gòu)為（C0L1, COL 2, COL 3），其中，COL1 ：整型，允許空值。C0L2 ：普通編碼定長字符型，長度為 10，不允許空值。C0L3 ：普通編碼定長字符型，長度為 10，允許空值。 試寫出按行插入如下數(shù)據(jù)的語句（空白處表示是空值） 。COL1COL2COL3B11B2C22B333.利用23題建立的VB_Grade表，將信息管理系選了VB課程的學(xué)生姓名、所在系和考試成績插入到VB_Grade表中。34. 將所有選修C001課程的學(xué)生的成績加 10分。35. 將計(jì)算機(jī)系所有學(xué)生的“計(jì)算機(jī)文化學(xué)

10、”的考試成績加10分。36. 修改VB ”課程的考試成績，如果是通信工程系的學(xué)生，則增加10分；如果是信息管理系的學(xué)生則增加5分，其他系的學(xué)生不加分。37. 刪除成績小于50分的學(xué)生的選課記錄。38. 刪除計(jì)算機(jī)系 VB考試成績不及格學(xué)生的 VB選課記錄。39. 刪除VB ”考試成績最低的學(xué)生的VB修課記錄。40. 刪除沒人選的課程的基本信息。C.3第6章上機(jī)實(shí)驗(yàn)下列實(shí)驗(yàn)均使用 SQL Server的SSMS工具實(shí)現(xiàn)。利用第 4章上機(jī)實(shí)驗(yàn)創(chuàng)建的“學(xué)生數(shù)據(jù)庫”中Student、Course和SC表，完成下列實(shí)驗(yàn)。1. 寫出實(shí)現(xiàn)下列操作的 SQL語句，并執(zhí)行所寫代碼。（1 ）在Student表上為

11、Sname列建立一個(gè)聚集索引，索引名為：IdxSno。（提示：若執(zhí)行創(chuàng)建索引的代碼，請(qǐng)先刪除該表的主鍵約束）（2 ）在Course表上為Cname列建立一個(gè)唯一的非聚集索引，索引名為：IdxCN（3 ）在SC表上為Sno和Cno建立一個(gè)組合的聚集索引，索引名為：IdxSnoCno。（提 示：若執(zhí)行創(chuàng)建索引的代碼，請(qǐng)先刪除該表的主鍵約束）（ 4）刪除 Sname 列上建立的 IdxSno 索引。2. 寫出創(chuàng)建滿足下述要求的視圖的 SQL 語句，并執(zhí)行所寫代碼。（1）查詢學(xué)生的學(xué)號(hào)、姓名、所在系、課程號(hào)、課程名、課程學(xué)分。（2）查詢學(xué)生的學(xué)號(hào)、姓名、選修的課程名和考試成績。（3）統(tǒng)計(jì)每個(gè)學(xué)生的選課

12、門數(shù)，要求列出學(xué)生學(xué)號(hào)和選課門數(shù)。（4）統(tǒng)計(jì)每個(gè)學(xué)生的修課總學(xué)分，要求列出學(xué)生學(xué)號(hào)和總學(xué)分（說明：考試成績大于等 于 60 才可獲得此門課程的學(xué)分） 。3. 利用第 2 題建立的視圖，完成如下查詢。（ 1）查詢考試成績大于等于 90 分的學(xué)生的姓名、課程名和成績。（ 2）查詢選課門數(shù)超過 3 門的學(xué)生的學(xué)號(hào)和選課門數(shù)。（ 3）查詢計(jì)算機(jī)系選課門數(shù)超過 3 門的學(xué)生的姓名和選課門數(shù)。（ 4）查詢修課總學(xué)分超過 10 分的學(xué)生的學(xué)號(hào)、姓名、所在系和修課總學(xué)分。（ 5）查詢年齡大于等于 20 歲的學(xué)生中，修課總學(xué)分超過 10 分的學(xué)生的姓名、年齡、所 在系和修課總學(xué)分。4. 修改第 3 題（ 4）定

13、義的視圖，使其查詢每個(gè)學(xué)生的學(xué)號(hào)、總學(xué)分以及總的選課門數(shù)。C.4 第 7 章上機(jī)實(shí)驗(yàn)利用第 5 章建立的學(xué)生數(shù)據(jù)庫以及 Student 、 Coures 和 SC 表，完成下列操作。1. 創(chuàng)建滿足如下要求的后觸發(fā)型觸發(fā)器。（ 1）限制學(xué)生的考試成績必須在 0-100 之間。（2）限制不能刪除成績不及格的考試記錄。3）限制每個(gè)學(xué)期開設(shè)的課程總學(xué)分不能超過20。（ 4）限制每個(gè)學(xué)生每學(xué)期選的課程不能超過 5 門。2. 創(chuàng)建滿足如下要求的存儲(chǔ)過程。 （1）查詢每個(gè)學(xué)生的修課總學(xué)分，要求列出學(xué)生學(xué)號(hào)及總學(xué)分。（2）查詢學(xué)生的學(xué)號(hào)、姓名、修的課程號(hào)、課程名、課程學(xué)分，將學(xué)生所在的系作為輸 入?yún)?shù)，執(zhí)行此

14、存儲(chǔ)過程，并分別指定一些不同的輸入?yún)?shù)值。（3）查詢指定系的男生人數(shù)，其中系為輸入?yún)?shù)，人數(shù)為輸出參數(shù)。（4）刪除指定學(xué)生的修課記錄，其中學(xué)號(hào)為輸入?yún)?shù)。（5）修改指定課程的開課學(xué)期。輸入?yún)?shù)為：課程號(hào)和修改后的開課學(xué)期。C.5 第 11 章上機(jī)實(shí)驗(yàn)利用第 4、5 章建立的學(xué)生數(shù)據(jù)庫和其中的 Student 、Course 、 SC 表，并利用 SSMS 工具 完成下列操作。1. 用 SSMS 工具建立 SQL Server 身份驗(yàn)證模式的登錄名： log1 、log2 和 log3 。2. 用 log1 建立一個(gè)新的數(shù)據(jù)庫引擎查詢， 在“可用數(shù)據(jù)庫”下列列表框中是否能選中學(xué)生數(shù)據(jù)庫？ 為什么

15、？3. 用系統(tǒng)管理員身份建立一個(gè)新的數(shù)據(jù)庫引擎查詢，將log1 、 log2 和 log3 映射為學(xué)生數(shù)據(jù)庫中的用戶，用戶名同登錄名。4. 在 log1 建立的數(shù)據(jù)庫引擎查詢中， 現(xiàn)在在“可用數(shù)據(jù)庫”下列列表框中是否能選中學(xué)生數(shù)據(jù)庫？ 為什么？5. 在 log1 建立的數(shù)據(jù)庫引擎查詢中，選中學(xué)生數(shù)據(jù)庫，執(zhí)行下述語句，能否成功？為什么？SELECT * FROM Course6. 在系統(tǒng)管理員的數(shù)據(jù)庫引擎查詢中， 執(zhí)行合適的授權(quán)語句， 授予 log1 具有對(duì) Course 表的查 詢權(quán)限，授予 log2 具有對(duì) Course 表的插入權(quán)限。7. 用 log2 建立一個(gè)新的數(shù)據(jù)庫引擎查詢，執(zhí)行下述

16、語句，能否成功？為什么？INSERT INTO Course VALUES( 'C1001 '，'數(shù)據(jù)庫基礎(chǔ) '，4， 5) 再執(zhí)行下述語句，能否成功？為什么？SELECT * FROM Course8. 在 log1 建立的數(shù)據(jù)庫引擎查詢中，再次執(zhí)行下述語句：SELECT * FROM Course這次能否成功？但如果執(zhí)行下述語句：INSERT INTO Course VALUES( 'C103 ', '軟件工程 ', 4, 5) 能否成功？為什么？9. log3 建立一個(gè)新的數(shù)據(jù)庫引擎查詢，執(zhí)行下述語句，能否成功？為什么？CREATE TABLE NewTable(C1 int,C2 char(4)10. 授予 log3 在學(xué)生數(shù)據(jù)庫中具有創(chuàng)建表