附錄C 數(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）：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é)生數(shù)據(jù)庫”

2、，寫出創(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-45Sdept所在系普通編碼不定長字符串，長度為20默認(rèn)值為“計算機(jī)系”Sid身份證號普通編碼定長字符串，長度為10取值不重Sdate入學(xué)日期日期默認(rèn)為系統(tǒng)當(dāng)前日期表C-2 Course表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Cno課程號普通編碼定長字符串，長度

3、為10主鍵Cname課程名普通編碼不定長字符串，長度為20非空Credit學(xué)時數(shù)整型取值大于0Semester學(xué)分小整型表C-3 SC表結(jié)構(gòu)列名說明數(shù)據(jù)類型約束Sno學(xué)號普通編碼定長字符串，長度為7主鍵，引用Student的外鍵Cno課程號普通編碼定長字符串，長度為10主鍵，引用Course的外鍵Grade成績小整型取值范圍為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. 寫出實(shí)現(xiàn)如下功能的SQL語句，并執(zhí)行所寫代碼，查看執(zhí)行結(jié)果。

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ù)類型后再重新添加約束項(xiàng)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課程的學(xué)生姓名和所在系。 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分以上的學(xué)生姓名、課程號和成績，并按成績降序排列結(jié)果。SELECT Sname,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno AND Grade80 ORDER BY Grade DESC13. 查詢與VB在同一學(xué)期開設(shè)的課程的課程名和開課學(xué)期。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. 查詢與李勇年齡相同的學(xué)生的姓名、所在系和年齡。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. 查詢哪些課程沒有學(xué)生選修，列出課程號和課程名。SELECT Course.Cno,Course.Cname FROM Course LEFT JOIN SC on SC.Cno=Course.Cno WHERE SC.Cno IS NULL16. 查詢每個學(xué)生的選課情況，包括未選課的學(xué)生，列出學(xué)生的學(xué)號、姓名、選的課程號。SELECT Student.Sno,Student.Sname,Cno FROM Student LEFT JOIN SC ON SC.Sno=Student.Sno17. 查詢計算機(jī)系哪些學(xué)生沒有選課，列出學(xué)生姓名。SELECT Sname FROM

11、 Student WHERE Sdept=計算機(jī)系 AND Sno NOT IN(SELECT Sno FROM SC)或者SELECT Sname FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = 計算機(jī)系 AND SC.Cno IS NULL18. 查詢計算機(jī)系年齡最大的三個學(xué)生的姓名和年齡。SELECT Top 3 Sname,Sage FROM Student WHERE Sdept=計算機(jī)系 ORDER BY Sage DESC19. 列出“VB”課程考試成績前三名的學(xué)生的學(xué)號、姓名、所在系和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位學(xué)生，列出學(xué)號和選課門數(shù)。SELECT top 2 Sno,COUNT(Cno) AS 選課數(shù) FROM SC GROUP BY Sno ORDER BY COUNT(Cno) DESC21. 查詢計算機(jī)系學(xué)生姓名、年齡和年齡情況，其中年齡情況為：如果年齡小于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. 查詢計算機(jī)系選了VB課程的學(xué)生姓名、所在系和考試成績，并將結(jié)果保存到新表VB_Grade中。v 局部臨時表：#新表名。局部于當(dāng)前連接，生命期同連接期。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ù)，并將結(jié)果保存到新表Girls中。SELECT Sdept,Ssex,COUNT(Ssex) AS 人數(shù) Into Girls FROM Student WHERE Ssex=女GROUP BY Sdept,Ssex 25. 用子查

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

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

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

19、=Course.Cno AND Student.Sno=SC.Sno AND Sdept=計算機(jī)系)28. 查詢VB課程考試成績高于VB平均成績的學(xué)生姓名和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的學(xué)生姓名和所在系。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. 查詢每個學(xué)期學(xué)分最高的課程信息，列出開課學(xué)期、課程名和學(xué)分。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. 查詢每門課程考試成績最高的學(xué)生信息，列出課程號、學(xué)生姓名和最高成績，結(jié)果按課程號升序排序，不包括沒考試的課程。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. 查詢選了全部課程的學(xué)生姓名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，其結(jié)構(gòu)為（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課程的學(xué)生姓名、所在系和考試成

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課程的學(xué)生的成績加10分。UPDATE SC SET Grade=Grade+10 WHERE Cno=C00136. 將計算機(jī)系所有學(xué)生的“計算機(jī)文化學(xué)”的考試成績加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=計算機(jī)系 AND Cname=計算機(jī)文化學(xué))37. 修改“VB”課程的考試成績，如果是通信工程系的學(xué)生，則增加10分；如果是信息管理系的學(xué)生則增加5分，其他系的學(xué)生不加分。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分的學(xué)生的選課記錄。DELETE FROM SC WHERE Grade 5039. 刪除計算機(jī)系VB考試成績不及格學(xué)生的VB選課記錄。DELETE FROM SC FROM SC,Student,Course WHERE SC.Sno=Student.SnoAND SC.Cno=Course.Cno AND Sdept=計算機(jī)系 ANDCname=VB AND Grade=60 GROUP BY SnoGO3. 利用第2題建立的視圖，完成如下查詢。（1）查詢考試成績大于等于90分

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

29、nt.Sno,Sname,Sdept,Creditsum FROM Student,v4 WHERE v4.Sno=Student.Sno AND Creditsum10（5）查詢年齡大于等于20歲的學(xué)生中，修課總學(xué)分超過10分的學(xué)生的姓名、年齡、所在系和修課總學(xué)分。 SELECT Sname,Sage,Sdept,Creditsum FROM Student,v4 WHERE v4.Sno=Student.Sno AND Creditsum10 AND Sage=204. 修改第3題（4）定義的視圖，使其查詢每個學(xué)生的學(xué)號、總學(xué)分以及總的選課門數(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章上機(jī)實(shí)驗(yàn)利用第5章建立的學(xué)生數(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）限制學(xué)生的考試成績必須在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）限制每個學(xué)生每學(xué)期選的課程不能超過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）查詢每個學(xué)生的修課總學(xué)分，要求列出學(xué)生學(xué)號及總學(xué)分。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）查詢學(xué)生的學(xué)號、姓名、修的課程號、課程名、課程學(xué)分，將學(xué)生所在的系作為輸入?yún)?shù)，執(zhí)行此存儲過程，并分別指定一些不同的輸入?yún)?shù)值。CREATE PROC p2 dept char(20)=信息管理系A(chǔ)SSELECT 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=計算機(jī)系（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 計算機(jī)系,num outputprint num（4）刪除指定學(xué)生的修課記錄，其中學(xué)號為輸入?yún)?shù)。CREATE PROC p4 sno char(20)ASdelete FROM SC WHERE S