數(shù)據(jù)庫上機(jī)實驗

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

1、附錄C 上機(jī)實驗 C.1 第4章上機(jī)實驗 下列實驗均使用SQL Server 的SSMS工具實現(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:盤中無此文件夾，請先建立此文件夾，然后再創(chuàng)建數(shù)據(jù)庫。），初始大小為：5MB，增長方式為自動增長，每次增加1MB。 l 日志文件的邏輯文件名字為：Students_log，也存放在D:\Data文件夾下，初始大小為：2MB，增長方式為自動增長，每次增加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é)號 普通編碼定長字符串，長度為7 主鍵 Sname 姓名 普通編碼定長字符串，長度為10 非空 Ssex 性別 普通編碼定長字符串，長度為2 取值范圍：{男，女} Sage 年齡 微整型（tinyint） 取值范圍：15-45 Sdept 所在系 普通編碼不定長字符串，長度為20 默認(rèn)值為“計算機(jī)系” Sid 身份證號 普通編碼定長字符串，長度為10 取值

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

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

5、dit列的類型改為：tinyint。 （4） 刪除Student表中的Sid和Sdate列。 （5） 為Teacher表添加主鍵約束，其主鍵為：Tno。 C.2 第5章上機(jī)實驗 本實驗均在SQL Server 的SSMS工具中實現(xiàn)。首先在已創(chuàng)建的“學(xué)生數(shù)據(jù)庫”中創(chuàng)建本章表5-1~5-3所示的Student、Course和SC表，并插入表5-4~5-6所示數(shù)據(jù)，然后編寫實現(xiàn)如下操作的SQL語句，執(zhí)行所寫的語句，并查看執(zhí)行結(jié)果。 1. 查詢SC表中的全部數(shù)據(jù)。 2. 查詢計算機(jī)系學(xué)生的姓名和年齡。 3. 查詢成績在70～80分的學(xué)生的學(xué)號、課程號和成績。 4. 查詢計算機(jī)系年齡在1

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

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

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

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

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

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

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

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

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

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

16、個新的數(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具有對Course表的查詢權(quán)限，授予log2具有對Course表的插入權(quán)限。 7. 用log2建立一個新的數(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建立一個新的數(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建立一個新的數(shù)據(jù)庫引擎查詢中，再次執(zhí)行第9題的語句，能否成功？為什么？ 如果執(zhí)行下述語句： SELECT * NewTable 能否成功？為什么？