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

《數(shù)據(jù)庫上機(jī)實(shí)驗(yàn)》由會(huì)員分享，可在線閱讀，更多相關(guān)《數(shù)據(jù)庫上機(jī)實(shí)驗(yàn)（5頁珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

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）： l 數(shù)據(jù)庫名為：學(xué)生數(shù)據(jù)庫 l 主要數(shù)據(jù)文件的邏輯文件名為：Students_data，存放在D:\Data文件夾下（若D:盤中無此文件夾，請(qǐng)先建立此文件夾，然后再創(chuàng)建數(shù)據(jù)庫。），初始大小為：5MB，增長(zhǎng)方式為自動(dòng)增長(zhǎng)，每次增加1MB。 l 日志文件的邏輯文件名字為：Students_log，也存放在D:\Data文件夾下，初始大小為：2MB，增長(zhǎng)方式為自動(dòng)增長(zhǎng)，每次增加10%。 2．選用已建立的“學(xué)生

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

3、不重 Sdate 入學(xué)日期 日期 默認(rèn)為系統(tǒng)當(dāng)前日期 表C-2 Course表結(jié)構(gòu) 列名 說明 數(shù)據(jù)類型 約束 Cno 課程號(hào) 普通編碼定長(zhǎng)字符串，長(zhǎng)度為10 主鍵 Cname 課程名 普通編碼不定長(zhǎng)字符串，長(zhǎng)度為20 非空 Credit 學(xué)時(shí)數(shù) 整型 取值大于0 Semester 學(xué)分 小整型 表C-3 SC表結(jié)構(gòu) 列名 說明 數(shù)據(jù)類型 約束 Sno 學(xué)號(hào) 普通編碼定長(zhǎng)字符串，長(zhǎng)度為7 主鍵，引用Student的外鍵 Cno 課程號(hào) 普通編碼定長(zhǎng)字符串，長(zhǎng)度為10 主鍵，引用Course的外鍵 Grade 成績(jī)

4、 小整型 取值范圍為0-100 表C-4 Teacher表結(jié)構(gòu) 列名 說明 數(shù)據(jù)類型 約束 Tno 教師號(hào) 普通編碼定長(zhǎng)字符串，長(zhǎng)度為8 非空 Tname 教師名 普通編碼定長(zhǎng)字符串，長(zhǎng)度為10 非空 Salary 工資 定點(diǎn)小數(shù)，小數(shù)點(diǎn)前4位，小數(shù)點(diǎn)后2位 3. 寫出實(shí)現(xiàn)如下功能的SQL語句，并執(zhí)行所寫代碼，查看執(zhí)行結(jié)果。 （1） 在Teacher表中添加一個(gè)職稱列，列名為：Title，類型為nchar(4)。 （2） 為Teacher表中的Title列增加取值范圍約束，取值范圍為：{教授，副教授，講師}。 （3） 將Course表中Cre

5、dit列的類型改為：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-1~5-3所示的Student、Course和SC表，并插入表5-4~5-6所示數(shù)據(jù)，然后編寫實(shí)現(xiàn)如下操作的SQL語句，執(zhí)行所寫的語句，并查看執(zhí)行結(jié)果。 1. 查詢SC表中的全部數(shù)據(jù)。 2. 查詢計(jì)算機(jī)系學(xué)生的姓名和年齡。 3. 查詢成績(jī)?cè)?0～80分的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)。 4. 查詢計(jì)算機(jī)系年齡在1

6、8～20歲的男生姓名和年齡。 5. 查詢C001課程的最高分。 6. 查詢計(jì)算機(jī)系學(xué)生的最大年齡和最小年齡。 7. 統(tǒng)計(jì)每個(gè)系的學(xué)生人數(shù)。 8. 統(tǒng)計(jì)每門課程的選課人數(shù)和最高成績(jī)。 9. 統(tǒng)計(jì)每個(gè)學(xué)生的選課門數(shù)和考試總成績(jī)，并按選課門數(shù)升序顯示結(jié)果。 10. 列出總成績(jī)超過200的學(xué)生的學(xué)號(hào)和總成績(jī)。 11. 查詢選了C002課程的學(xué)生姓名和所在系。 12. 查詢考試成績(jī)80分以上的學(xué)生姓名、課程號(hào)和成績(jī)，并按成績(jī)降序排列結(jié)果。 13. 查詢與VB在同一學(xué)期開設(shè)的課程的課程名和開課學(xué)期。 14. 查詢與李勇年齡相同的學(xué)生的姓名、所在系和年齡。 15. 查詢哪些課程沒有學(xué)生

7、選修，列出課程號(hào)和課程名。 16. 查詢每個(gè)學(xué)生的選課情況，包括未選課的學(xué)生，列出學(xué)生的學(xué)號(hào)、姓名、選的課程號(hào)。 17. 查詢計(jì)算機(jī)系哪些學(xué)生沒有選課，列出學(xué)生姓名。 18. 查詢計(jì)算機(jī)系年齡最大的三個(gè)學(xué)生的姓名和年齡。 19. 列出“VB”課程考試成績(jī)前三名的學(xué)生的學(xué)號(hào)、姓名、所在系和VB成績(jī)。 20. 查詢選課門數(shù)最多的前2位學(xué)生，列出學(xué)號(hào)和選課門數(shù)。 21. 查詢計(jì)算機(jī)系學(xué)生姓名、年齡和年齡情況，其中年齡情況為：如果年齡小于18，則顯示“偏小”；如果年齡在18-22，則顯示“合適”；如果年齡大于22，則顯示“偏大”。 22. 統(tǒng)計(jì)每門課程的選課人數(shù)，包括有人選的課程和沒有人

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

9、最高的學(xué)生的姓名。 （4）查詢年齡最大的男生的姓名、所在系和年齡。 26. 查詢C001課程的考試成績(jī)高于該課程平均成績(jī)的學(xué)生的學(xué)號(hào)和成績(jī)。 27. 查詢計(jì)算機(jī)系學(xué)生考試成績(jī)高于計(jì)算機(jī)系學(xué)生平均成績(jī)的學(xué)生的姓名、考試的課程名和考試成績(jī)。 28. 查詢VB課程考試成績(jī)高于VB平均成績(jī)的學(xué)生姓名和VB成績(jī)。 29. 查詢沒選VB的學(xué)生姓名和所在系。 30. 查詢每個(gè)學(xué)期學(xué)分最高的課程信息，列出開課學(xué)期、課程名和學(xué)分。 31. 查詢每門課程考試成績(jī)最高的學(xué)生信息，列出課程號(hào)、學(xué)生姓名和最高成績(jī)，結(jié)果按課程號(hào)升序排序，不包括沒考試的課程。 32. 創(chuàng)建一個(gè)新表，表名為test，其結(jié)構(gòu)為

10、（COL1, COL 2, COL 3），其中， COL1：整型，允許空值。 COL2：普通編碼定長(zhǎng)字符型，長(zhǎng)度為10，不允許空值。 COL3：普通編碼定長(zhǎng)字符型，長(zhǎng)度為10，允許空值。 試寫出按行插入如下數(shù)據(jù)的語句（空白處表示是空值）。 COL1 COL2 COL3 B1 1 B2 C2 2 B3 33. 利用23題建立的VB_Grade表，將信息管理系選了VB課程的學(xué)生姓名、所在系和考試成績(jī)插入到VB_Grade表中。 34. 將所有選修C001課程的學(xué)生的成績(jī)加10分。 35. 將計(jì)算機(jī)系所有學(xué)生的“計(jì)算機(jī)文化學(xué)”的考試成績(jī)加10分。 36.

11、 修改“VB”課程的考試成績(jī)，如果是通信工程系的學(xué)生，則增加10分；如果是信息管理系的學(xué)生則增加5分，其他系的學(xué)生不加分。 37. 刪除成績(jī)小于50分的學(xué)生的選課記錄。 38. 刪除計(jì)算機(jī)系VB考試成績(jī)不及格學(xué)生的VB選課記錄。 39. 刪除“VB”考試成績(jī)最低的學(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表上為Sn

12、ame列建立一個(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)、姓名、選修的課程名和考試成績(jī)。 （3）統(tǒng)計(jì)每個(gè)學(xué)生的

13、選課門數(shù)，要求列出學(xué)生學(xué)號(hào)和選課門數(shù)。 （4）統(tǒng)計(jì)每個(gè)學(xué)生的修課總學(xué)分，要求列出學(xué)生學(xué)號(hào)和總學(xué)分（說明：考試成績(jī)大于等于60才可獲得此門課程的學(xué)分）。 3. 利用第2題建立的視圖，完成如下查詢。 （1）查詢考試成績(jī)大于等于90分的學(xué)生的姓名、課程名和成績(jī)。 （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）定義的視圖，使其查詢

14、每個(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é)生的考試成績(jī)必須在0-100之間。 （2）限制不能刪除成績(jī)不及格的考試記錄。 （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í)行此存儲(chǔ)過程，并

15、分別指定一些不同的輸入?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ù)庫？為什么？ 3. 用系統(tǒng)管理員身份建立一

16、個(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 Course 6. 在系統(tǒng)管理員的數(shù)據(jù)庫引擎查詢中，執(zhí)行合適的授權(quán)語句，授予log1具有對(duì)Course表的查詢權(quán)限，授予log2具有對(duì)Course表的插入權(quán)限。 7. 用log2建立一個(gè)新的數(shù)據(jù)庫引擎查詢，執(zhí)行下述語句，能否成功？為什么？ INSERT INTO Cou

17、rse VALUES(C1001，數(shù)據(jù)庫基礎(chǔ)，4，5) 再執(zhí)行下述語句，能否成功？為什么？ SELECT * FROM Course 8. 在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)建表的權(quán)限。 11. 在系統(tǒng)管理員的數(shù)據(jù)庫引擎查詢中，執(zhí)行下述語句： GRANT CREATE TABLE TO log3 GO CREATE SCHEMA log3 AUTHORIZATION log3 GO ALTER USER log3 WITH DEFAULT_SCHEMA = log3 12. 在log3建立一個(gè)新的數(shù)據(jù)庫引擎查詢中，再次執(zhí)行第9題的語句，能否成功？為什么？ 如果執(zhí)行下述語句： SELECT * NewTable 能否成功？為什么？