附錄C 數(shù)據(jù)庫上機實驗

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

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

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

4、（1） 在Teacher表中添加一個職稱列，列名為：Title，類型為nchar(4)。ALTER TABLE Teacher ADD Title VARCHAR(4)（2） 為Teacher表中的Title列增加取值范圍約束，取值范圍為：教授，副教授，講師。ALTER TABLE Teacher ADD CONSTRAINT Title CHECK(Title IN(教授,副教授,講師)（3） 將Course表中Credit列的類型改為：tinyint。注意：這里首先需要先刪除Credit的約束,修改完數(shù)據(jù)類型后再重新添加約束項ALTER TABLE Course DROP CONSTRAI

5、NT CK_Course_Credit_2B3F6F97ALTER TABLE Course ALTER COLUMN Credit TINYINTALTER TABLE Course ADD CONSTRAINT Credit CHECK (Credit0)（4） 刪除Student表中的Sid和Sdate列。注意：這里首先需要先刪除約束 ALTER TABLE Student DROP CONSTRAINT UQ_Student_CA1E5D79B3204FCFALTER TABLE Student DROP CONSTRAINT DF_Student_Sdate_3B75D760ALTE

6、R TABLE Student DROP COLUMN Sdate,Sid（5） 為Teacher表添加主鍵約束，其主鍵為：Tno。ALTER TABLE Teacher ADD PRIMARY KEY(Tno)建立數(shù)據(jù)庫主要文件和日志文件我們可以看到D盤增加了主要數(shù)據(jù)文件.mdf和日志文件.ldf給students_data建立表格create table Student(Sno char(7),Sname char(10) not null,Ssex char(2) check( Ssex=男 or Ssex=女 ) ,Sage tinyint check(Sage 14 AND Sage

7、0), Semester tinyint, )create table SC(Sno char(7),Cno char(10),Grade tinyint check( Grade=0 AND Grade20011. 查詢選了C002課程的學生姓名和所在系。 SELECT Sname,Sdept FROM Student st,SC sc WHERE st.Sno=sc.Sno AND Cno=C002 或者 SELECT Sname,Sdept FROM Student WHERE Sno IN(SELECT Sno FROM SC WHERE Cno=C002)或者SELECT Sname

8、,Sdept FROM Student WHERE EXISTS (SELECT * FROM SCWHERE Sno = Student.Sno AND Cno = C002) 12. 查詢考試成績80分以上的學生姓名、課程號和成績，并按成績降序排列結果。SELECT Sname,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno AND Grade80 ORDER BY Grade DESC13. 查詢與VB在同一學期開設的課程的課程名和開課學期。SELECT c2.Cname,c2.Semester FROM Course c1,Cour

9、se c2 WHERE c1.Cname=c2.Cname AND c1.Cname=VB AND c2.Cname!=VB14. 查詢與李勇年齡相同的學生的姓名、所在系和年齡。SELECT s2.Sname,s2.Sdept,s2.Sage FROM Student s1,Student s2WHERE s1.Sage=s2.Sage AND s1.Sname=李勇 AND s2.Sname!=李勇或者SELECT Sname,Sdept,Sage FROM Student WHERE Sage=(SELECT Sage FROM Student WHERE Sname=李勇) AND Sn

10、ame!=李勇15. 查詢哪些課程沒有學生選修，列出課程號和課程名。SELECT Course.Cno,Course.Cname FROM Course LEFT JOIN SC on SC.Cno=Course.Cno WHERE SC.Cno IS NULL16. 查詢每個學生的選課情況，包括未選課的學生，列出學生的學號、姓名、選的課程號。SELECT Student.Sno,Student.Sname,Cno FROM Student LEFT JOIN SC ON SC.Sno=Student.Sno17. 查詢計算機系哪些學生沒有選課，列出學生姓名。SELECT Sname FROM

11、 Student WHERE Sdept=計算機系 AND Sno NOT IN(SELECT Sno FROM SC)或者SELECT Sname FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = 計算機系 AND SC.Cno IS NULL18. 查詢計算機系年齡最大的三個學生的姓名和年齡。SELECT Top 3 Sname,Sage FROM Student WHERE Sdept=計算機系 ORDER BY Sage DESC19. 列出“VB”課程考試成績前三名的學生的學號、姓名、所在系和VB成績。SELECT

12、 TOP 3 WITH TIES Sname, Sdept, Grade FROM Student S JOIN SC on S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = VBORDER BY Grade DESC20. 查詢選課門數(shù)最多的前2位學生，列出學號和選課門數(shù)。SELECT top 2 Sno,COUNT(Cno) AS 選課數(shù) FROM SC GROUP BY Sno ORDER BY COUNT(Cno) DESC21. 查詢計算機系學生姓名、年齡和年齡情況，其中年齡情況為：如果年齡小于18，則顯示“偏小

13、”；如果年齡在18-22，則顯示“合適”；如果年齡大于22，則顯示“偏大”。 SELECT Sno,Sage,CASEWHEN Sage=18 AND Sage22 THEN 偏大ENDAS 年齡情況 FROM Student22. 統(tǒng)計每門課程的選課人數(shù)，包括有人選的課程和沒有人選的課程，列出課程號，選課人數(shù)及選課情況，其中選課情況為：如果此門課程的選課人數(shù)超過100人，則顯示“人多”；如果此門課程的選課人數(shù)在40100，則顯示“一般”；如果此門課程的選課人數(shù)在140，則顯示“人少”；如果此門課程沒有人選，則顯示“無人選”。 SELECT Course.Cno,COUNT(Sno)AS 選課

14、人數(shù),CASEWHEN COUNT(Sno)100 THEN 人多WHEN COUNT(Sno)0 THEN 人少WHEN COUNT(Sno)=40 THEN 一般WHEN COUNT(Sno)=0 THEN 無人選 endAS 人數(shù)情況 FROM SC RIGHT JOIN Course ON Course.Cno=SC.Cno GROUP BY Course.Cno23. 查詢計算機系選了VB課程的學生姓名、所在系和考試成績，并將結果保存到新表VB_Grade中。v 局部臨時表：#新表名。局部于當前連接，生命期同連接期。v 全局臨時表： #新表名?？稍谒羞B接中使用，生命期同用戶連接期v

15、 永久表： 新表名，存儲在磁盤上v 格式：SELECT 選擇列表INTO 新表名 FROM 子句 SELECT Sname,Sdept,Grade INTO VB_Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname=VB24. 統(tǒng)計每個系的女生人數(shù)，并將結果保存到新表Girls中。SELECT Sdept,Ssex,COUNT(Ssex) AS 人數(shù) Into Girls FROM Student WHERE Ssex=女GROUP BY Sdept,Ssex 25. 用子查

16、詢實現(xiàn)如下查詢：（1）查詢選了“C001”課程的學生姓名和所在系。SELECT Sname,Sdept FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno=C001 )（2）查詢通信工程系成績80分以上的學生的學號和姓名。SELECT Sno,Sname FROM Student WHERE Sdept=通信工程系 AND Sno IN (SELECT Sno FROM SC WHERE Grade80)（3）查詢計算機系考試成績最高的學生的姓名。SELECT Sname FROM Student,SC WHERE SC.Sno=St

17、udent.Sno AND Sdept=計算機系 AND Grade in(SELECT MAX(Grade) FROM SC,Student WHERE SC.Sno=Student.Sno AND Sdept=計算機系)（4）查詢年齡最大的男生的姓名、所在系和年齡。SELECT Sname,Sdept,Sage FROM Student WHERE Ssex=男 AND Sage in(SELECT MAX(Sage) FROM Student WHERE Ssex=男)26. 查詢C001課程的考試成績高于該課程平均成績的學生的學號和成績。SELECT Sno, Grade FROM S

18、C WHERE Cno = C001 AND Grade ( SELECT AVG(Grade) FROM SC WHERE Cno = C001)27. 查詢計算機系學生考試成績高于計算機系學生平均成績的學生的姓名、考試的課程名和考試成績。SELECT Sname,Sdept,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sdept=計算機系 AND Grade(SELECT AVG(Grade) FROM SC,Course,Student WHERE SC.Cno

19、=Course.Cno AND Student.Sno=SC.Sno AND Sdept=計算機系)28. 查詢VB課程考試成績高于VB平均成績的學生姓名和VB成績。SELECT Sname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname=VB AND Grade(SELECT AVG(Grade) FROM SC,Course WHERE Course.Cno=SC.Cno AND Cname=VB)29. 查詢沒選VB的學生姓名和所在系。SELECT Sname,Sd

20、ept FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC,Course WHERE SC.Cno=Course.Cno AND Cname=VB)或者SELECT Sname,Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC,Course WHERE Course.Cno=SC.Cno AND Sno=Student.Sno AND Cname=VB)30. 查詢每個學期學分最高的課程信息，列出開課學期、課程名和學分。SELECT Cname,Semester,Credit FROM C

21、ourse c1 WHERE NOT EXISTS(SELECT * FROM Course c2 WHERE c1.Semester=c2.Semester AND c1.Creditc2.Credit )31. 查詢每門課程考試成績最高的學生信息，列出課程號、學生姓名和最高成績，結果按課程號升序排序，不包括沒考試的課程。SELECT Cname,sname,SC.Cno,Grade FROM student join sc on student.sno=sc.sno join course on o=o WHERE grade=(SELECT max(grade

22、)FROM scWHERE cno=o ) order BY SC.Cno ASc或者SELECT sc1.sno,Sname,Cno ,Grade FROM SC sc1,student WHERE not exists(SELECT * FROM SC sc2 WHERE sc1.Cno=sc2.Cno AND sc1.Gradesc2.Grade) AND Grade IS NOT NULL AND Student.Sno=sc1.Sno order BY Cno ASC32. 查詢選了全部課程的學生姓名SELECT Sname FROM Student WHERE S

23、no in (SELECT Sno FROM SC GROUP BY Sno having COUNT(*)=(SELECT COUNT(*) FROM Course)或者SELECT Sname FROM student WHERE NOT exists(SELECT * FROM courseWHERE NOT exists(SELECT * FROM SC WHERE Sno=student.sno AND cno=Course.Cno)33. 創(chuàng)建一個新表，表名為test，其結構為（COL1, COL 2, COL 3），其中，COL1：整型，允許空值。COL2：普通編碼定長字符型，長

24、度為10，不允許空值。COL3：普通編碼定長字符型，長度為10，允許空值。試寫出按行插入如下數(shù)據(jù)的語句（空白處表示是空值）。COL1COL2COL3B11B2C22B3create table test(C0L1 int ,C0L2 char(10) not null,C0L3 char(10)insert into test(C0L2) values(B1)insert into test values(1,B2,C2)insert into test(C0L1,C0L2) values(2,B3)34. 利用23題建立的VB_Grade表，將信息管理系選了VB課程的學生姓名、所在系和考試成

25、績插入到VB_Grade表中。INSERT INTO VB_Grade SELECT Sname,Sdept,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Sdept=信息管理系 AND Cname=VB35. 將所有選修C001課程的學生的成績加10分。UPDATE SC SET Grade=Grade+10 WHERE Cno=C00136. 將計算機系所有學生的“計算機文化學”的考試成績加10分。UPDATE SC SET Grade=Grade+10 WHERE Sno i

26、n (SELECT SC.Sno FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Sdept=計算機系 AND Cname=計算機文化學)37. 修改“VB”課程的考試成績，如果是通信工程系的學生，則增加10分；如果是信息管理系的學生則增加5分，其他系的學生不加分。UPDATE SC SET Grade=Grade+CASE SdeptWHEN 通信工程系 THEN 10WHEN 信息管理系 THEN 5else 0endFROM Student,SC,Course WHERE SC.Sno=S

27、tudent.Sno AND Course.Cno=SC.Cno AND Cname=VB38. 刪除成績小于50分的學生的選課記錄。DELETE FROM SC WHERE Grade 5039. 刪除計算機系VB考試成績不及格學生的VB選課記錄。DELETE FROM SC FROM SC,Student,Course WHERE SC.Sno=Student.SnoAND SC.Cno=Course.Cno AND Sdept=計算機系 ANDCname=VB AND Grade=60 GROUP BY SnoGO3. 利用第2題建立的視圖，完成如下查詢。（1）查詢考試成績大于等于90分

28、的學生的姓名、課程名和成績。 SELECT Sname,Cname,Grade FROM v2 WHERE Grade=90（2）查詢選課門數(shù)超過3門的學生的學號和選課門數(shù)。SELECT Sname,Cname,Grade FROM v2 WHERE Grade=90（3）查詢計算機系選課門數(shù)超過3門的學生的姓名和選課門數(shù)。 SELECT Sname,Cnum FROM Student,v3 WHERE v3.Sno=Student.Sno AND Sdept=計算機系 AND Coursenum3（4）查詢修課總學分超過10分的學生的學號、姓名、所在系和修課總學分。 SELECT Stude

29、nt.Sno,Sname,Sdept,Creditsum FROM Student,v4 WHERE v4.Sno=Student.Sno AND Creditsum10（5）查詢年齡大于等于20歲的學生中，修課總學分超過10分的學生的姓名、年齡、所在系和修課總學分。 SELECT Sname,Sage,Sdept,Creditsum FROM Student,v4 WHERE v4.Sno=Student.Sno AND Creditsum10 AND Sage=204. 修改第3題（4）定義的視圖，使其查詢每個學生的學號、總學分以及總的選課門數(shù)。ALTER VIEW v4 ASSELECT

30、 Sno,SUM(Credit) Creditsum,COUNT(SC.Cno) CoursenumFROM SC,Course WHERE SC.Cno=Course.Cno GROUP BY SnoC.4 第7章上機實驗利用第5章建立的學生數(shù)據(jù)庫以及Student、Coures和SC表，完成下列操作。1. 創(chuàng)建滿足如下要求的后觸發(fā)型觸發(fā)器。說明:對于insert into 表名 values() 單行插入語句，觸發(fā)器每次都會被觸發(fā) 對于 insert into 表名 selec FROM表名 或者是 insert into 表名 values (),(),().多行插入語句，觸發(fā)器只會觸發(fā)

31、一次，所有的插入數(shù)據(jù)都會一并備份到inserted表中，然后執(zhí)行觸發(fā)篩選-chenyuchao（1）限制學生的考試成績必須在0-100之間。 CREATE TRIGGER tri1 ON SC after insertASif exists(SELECT Grade FROM inserted WHERE Grade not between 0 AND 100)rollbackgo（2）限制不能刪除成績不及格的考試記錄。 CREATE TRIGGER tri2 ON SC AFTER DELETEASif exists(SELECT * FROM deleted WHERE Grade20)r

32、ollbackgo（4）限制每個學生每學期選的課程不能超過5門。 CREATE TRIGGER tri4 ON SC AFTER INSERTASif exists(SELECT COUNT(SC.Cno) FROM SC,Course WHERE SC.Cno=Course.Cno GROUP BY Semester,Sno having COUNT(SC.Cno)5 )ROLLBACKgo 2. 創(chuàng)建滿足如下要求的存儲過程。（1）查詢每個學生的修課總學分，要求列出學生學號及總學分。CREATE PROC p1ASSELECT Student.Sno,SUM(Credit) CreditSu

33、m FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno GROUP BY Student.Sno執(zhí)行：exec p1（2）查詢學生的學號、姓名、修的課程號、課程名、課程學分，將學生所在的系作為輸入?yún)?shù)，執(zhí)行此存儲過程，并分別指定一些不同的輸入?yún)?shù)值。CREATE PROC p2 dept char(20)=信息管理系ASSELECT Student.Sno,Sname,SC.Cno,Course.Cname,Credit FROM Student,SC,CourseWHERE Student.Sno=SC.S

34、no AND SC.Cno=Course.Cno AND Sdept=dept執(zhí)行: exec p2執(zhí)行: exec p2 dept=計算機系（3）查詢指定系的男生人數(shù)，其中系為輸入?yún)?shù)，人數(shù)為輸出參數(shù)。 CREATE PROC p3 dept char(20),number int outputASSELECT number=COUNT(Sno) FROM Student WHERE Sdept=dept AND Ssex=男執(zhí)行: declare num intexec p3 計算機系,num outputprint num（4）刪除指定學生的修課記錄，其中學號為輸入?yún)?shù)。CREATE PROC p4 sno char(20)ASdelete FROM SC WHERE S