附錄C 數(shù)據(jù)庫上機實驗及附錄C 施工資料用表

附錄C上機實驗C.1第4章上機實驗陳宇超，僅供參考下列實驗均使用SQLServer的SSMS工具實現(xiàn)。1．用圖形化方法創(chuàng)建符合如下條件的數(shù)據(jù)庫（創(chuàng)建數(shù)據(jù)庫的方法可參見本書附錄A）：數(shù)據(jù)庫名為：學生數(shù)據(jù)庫主要數(shù)據(jù)文件的邏輯文件名為：Students_data，存放在D:\Data文件夾下（若D:盤中無此文件夾，請先建立此文件夾，然后再創(chuàng)建數(shù)據(jù)庫。），初始大小為：5MB，增長方式為自動增長，每次增加1MB。日志文件的邏輯文件名字為：Students_log，也存放在D:\Data文件夾下，初始大小為：2MB，增長方式為自動增長，每次增加10%。2．選用已建立的“學生數(shù)據(jù)庫”，寫出創(chuàng)建滿足表C-1到4-4條件的表的SQL語句，并執(zhí)行所寫代碼。（注：“說明”部分不作為表定義內容）表C-1Student表結構列名說明數(shù)據(jù)類型約束Sno學號普通編碼定長字符串，長度為7主鍵Sname姓名普通編碼定長字符串，長度為10非空Ssex性別普通編碼定長字符串，長度為2取值范圍：{男，女}Sage年齡微整型（tinyint）取值范圍：15-45Sdept所在系普通編碼不定長字符串，長度為20默認值為“計算機系”Sid身份證號普通編碼定長字符串，長度為10取值不重Sdate入學日期日期默認為系統(tǒng)當前日期表C-2Course表結構列名說明數(shù)據(jù)類型約束Cno課程號普通編碼定長字符串，長度為10主鍵Cname課程名普通編碼不定長字符串，長度為20非空Credit學時數(shù)整型取值大于0Semester學分小整型表C-3SC表結構列名說明數(shù)據(jù)類型約束Sno學號普通編碼定長字符串，長度為7主鍵，引用Student的外鍵Cno課程號普通編碼定長字符串，長度為10主鍵，引用Course的外鍵Grade成績小整型取值范圍為0-100表C-4Teacher表結構列名說明數(shù)據(jù)類型約束Tno教師號普通編碼定長字符串，長度為8非空Tname教師名普通編碼定長字符串，長度為10非空Salary工資定點小數(shù)，小數(shù)點前4位，小數(shù)點后2位3.寫出實現(xiàn)如下功能的SQL語句，并執(zhí)行所寫代碼，查看執(zhí)行結果。在Teacher表中添加一個職稱列，列名為：Title，類型為nchar(4)。ALTERTABLETeacherADDTitleVARCHAR(4)為Teacher表中的Title列增加取值范圍約束，取值范圍為：{教授，副教授，講師}。ALTERTABLETeacherADDCONSTRAINTTitleCHECK(TitleIN('教授','副教授','講師'))將Course表中Credit列的類型改為：tinyint。注意：這里首先需要先刪除Credit的約束,修改完數(shù)據(jù)類型后再重新添加約束項ALTERTABLECourseDROPCONSTRAINTCK__Course__Credit__2B3F6F97ALTERTABLECourseALTERCOLUMNCreditTINYINTALTERTABLECourseADDCONSTRAINTCreditCHECK(Credit>0)刪除Student表中的Sid和Sdate列。注意：這里首先需要先刪除約束ALTERTABLEStudentDROPCONSTRAINTUQ__Student__CA1E5D79B3204FCFALTERTABLEStudentDROPCONSTRAINTDF__Student__Sdate__3B75D760ALTERTABLEStudentDROPCOLUMNSdate,Sid為Teacher表添加主鍵約束，其主鍵為：Tno。ALTERTABLETeacherADDPRIMARYKEY(Tno)※建立數(shù)據(jù)庫主要文件和日志文件※我們可以看到D盤增加了主要數(shù)據(jù)文件.mdf和日志文件.ldf※給students_data建立表格createtableStudent(Snochar(7),Snamechar(10)notnull,Ssexchar(2)check(Ssex='男'orSsex='女'),Sagetinyintcheck(Sage>14ANDSage<46),Sdeptvarchar(20)default'計算機系',Sidchar(10)unique,--身份證號Sdatedatedefaultgetdate()primarykey(Sno))createtableCourse(Cnochar(10)primarykey,Cnamevarchar(20)notnull,Creditintcheck(Credit>0),Semestertinyint,)createtableSC(Snochar(7),Cnochar(10),Gradetinyintcheck(Grade>=0ANDGrade<=100)foreignkey(Sno)referencesStudent(Sno),foreignkey(Cno)referencesCourse(Cno))createtableTeacher(Tnochar(8)notnull,Tnamechar(10)notnull,Salarydecimal(6,2))C.2第5章上機實驗本實驗均在SQLServer的SSMS工具中實現(xiàn)。首先在已創(chuàng)建的“學生數(shù)據(jù)庫”中創(chuàng)建本章表5-1~5-3所示的Student、Course和SC表，并插入表5-4~5-6所示數(shù)據(jù)，然后編寫實現(xiàn)如下操作的SQL語句，執(zhí)行所寫的語句，并查看執(zhí)行結果。有關建表及所需要的數(shù)據(jù)，見本章末尾查詢SC表中的全部數(shù)據(jù)。SELECT*FROMSC 查詢計算機系學生的姓名和年齡。SELECTSname,SageFROMStudentWHERESdept='計算機系'查詢成績在70～80分的學生的學號、課程號和成績。SELECTSno,Cno,GradeFROMSCWHEREGradebetween70AND80查詢計算機系年齡在18～20歲的男生姓名和年齡。SELECTSname,SageFROMStudentWHERESdept='計算機系'ANDSageBETWEEN18AND20ANDSsex='男'查詢C001課程的最高分。SELECTMAX(grade)AS最高成績FROMSCWHERECno='C001'查詢計算機系學生的最大年齡和最小年齡。SELECTMAX(Sage)AS年長,MIN(Sage)AS年幼FROMStudentWHERESdept='計算機系'統(tǒng)計每個系的學生人數(shù)。SELECTSdept,COUNT(Sno)AS人數(shù)FROMStudentGROUPBYSdept統(tǒng)計每門課程的選課人數(shù)和最高成績。SELECTCno,COUNT(Sno)AS人數(shù),MAX(Grade)AS最高成績FROMSCGROUPBYCno統(tǒng)計每個學生的選課門數(shù)和考試總成績，并按選課門數(shù)升序顯示結果。SELECTSno,COUNT(Cno)AS選課數(shù)量,SUM(Grade)AS考試總成績FROMSCGROUPBYSno列出總成績超過200的學生的學號和總成績。SELECTSno,SUM(Grade)AS考試總成績FROMSCGROUPBYSnoHAVINGSUM(Grade)>200查詢選了C002課程的學生姓名和所在系。SELECTSname,SdeptFROMStudentst,SCscWHEREst.Sno=sc.SnoANDCno='C002'或者SELECTSname,SdeptFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno='C002')或者SELECTSname,SdeptFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='C002')查詢考試成績80分以上的學生姓名、課程號和成績，并按成績降序排列結果。SELECTSname,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.SnoANDGrade>80ORDERBYGradeDESC查詢與VB在同一學期開設的課程的課程名和開課學期。SELECTc2.Cname,c2.SemesterFROMCoursec1,Coursec2WHEREc1.Cname=c2.CnameANDc1.Cname='VB'ANDc2.Cname!='VB'查詢與李勇年齡相同的學生的姓名、所在系和年齡。SELECTs2.Sname,s2.Sdept,s2.SageFROMStudents1,Students2WHEREs1.Sage=s2.SageANDs1.Sname='李勇'ANDs2.Sname!='李勇'或者SELECTSname,Sdept,SageFROMStudentWHERESage=(SELECTSageFROMStudentWHERESname='李勇')ANDSname!='李勇'查詢哪些課程沒有學生選修，列出課程號和課程名。SELECTCourse.Cno,Course.CnameFROMCourseLEFTJOINSConSC.Cno=Course.CnoWHERESC.CnoISNULL查詢每個學生的選課情況，包括未選課的學生，列出學生的學號、姓名、選的課程號。SELECTStudent.Sno,Student.Sname,CnoFROMStudentLEFTJOINSCONSC.Sno=Student.Sno查詢計算機系哪些學生沒有選課，列出學生姓名。SELECTSnameFROMStudentWHERESdept='計算機系'ANDSnoNOTIN(SELECTSnoFROMSC)或者SELECTSnameFROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept='計算機系'ANDSC.CnoISNULL查詢計算機系年齡最大的三個學生的姓名和年齡。SELECTTop3Sname,SageFROMStudentWHERESdept='計算機系'ORDERBYSageDESC列出“VB”課程考試成績前三名的學生的學號、姓名、所在系和VB成績。SELECTTOP3WITHTIESSname,Sdept,GradeFROMStudentSJOINSConS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname='VB'ORDERBYGradeDESC查詢選課門數(shù)最多的前2位學生，列出學號和選課門數(shù)?！蘏ELECTtop2Sno,COUNT(Cno)AS選課數(shù)FROMSCGROUPBYSnoORDERBYCOUNT(Cno)DESC查詢計算機系學生姓名、年齡和年齡情況，其中年齡情況為：如果年齡小于18，則顯示“偏小”；如果年齡在18-22，則顯示“合適”；如果年齡大于22，則顯示“偏大”?！蘏ELECTSno,Sage,CASEWHENSage<18THEN'偏小'WHENSage>=18ANDSage<=22THEN'適合'WHENSage>22THEN'偏大'ENDAS年齡情況FROMStudent統(tǒng)計每門課程的選課人數(shù)，包括有人選的課程和沒有人選的課程，列出課程號，選課人數(shù)及選課情況，其中選課情況為：如果此門課程的選課人數(shù)超過100人，則顯示“人多”；如果此門課程的選課人數(shù)在40～100，則顯示“一般”；如果此門課程的選課人數(shù)在1～40，則顯示“人少”；如果此門課程沒有人選，則顯示“無人選”?！蘏ELECTCourse.Cno,COUNT(Sno)AS選課人數(shù),CASEWHENCOUNT(Sno)>100THEN'人多'WHENCOUNT(Sno)<40ANDCOUNT(Sno)>0THEN'人少'WHENCOUNT(Sno)<=100ANDCOUNT(Sno)>=40THEN'一般'WHENCOUNT(Sno)=0THEN'無人選'endAS人數(shù)情況FROMSCRIGHTJOINCourseONCourse.Cno=SC.CnoGROUPBYCourse.Cno查詢計算機系選了VB課程的學生姓名、所在系和考試成績，并將結果保存到新表VB_Grade中。√局部臨時表：#新表名。局部于當前連接，生命期同連接期。全局臨時表：##新表名。可在所有連接中使用，生命期同用戶連接期永久表：新表名，存儲在磁盤上格式：SELECT選擇列表 INTO新表名 FROM子句…SELECTSname,Sdept,GradeINTOVB_GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDCname='VB'統(tǒng)計每個系的女生人數(shù)，并將結果保存到新表Girls中?！蘏ELECTSdept,Ssex,COUNT(Ssex)AS人數(shù)IntoGirlsFROMStudentWHERESsex='女'GROUPBYSdept,Ssex用子查詢實現(xiàn)如下查詢：（1）查詢選了“C001”課程的學生姓名和所在系?！蘏ELECTSname,SdeptFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno='C001')（2）查詢通信工程系成績80分以上的學生的學號和姓名。√SELECTSno,SnameFROMStudentWHERESdept='通信工程系'ANDSnoIN(SELECTSnoFROMSCWHEREGrade>80)（3）查詢計算機系考試成績最高的學生的姓名?！蘏ELECTSnameFROMStudent,SCWHERESC.Sno=Student.SnoANDSdept='計算機系'ANDGradein(SELECTMAX(Grade)FROMSC,StudentWHERESC.Sno=Student.SnoANDSdept='計算機系')（4）查詢年齡最大的男生的姓名、所在系和年齡。√SELECTSname,Sdept,SageFROMStudentWHERESsex='男'ANDSagein(SELECTMAX(Sage)FROMStudentWHERESsex='男')查詢C001課程的考試成績高于該課程平均成績的學生的學號和成績。SELECTSno,GradeFROMSCWHERECno='C001'ANDGrade>(SELECTAVG(Grade)FROMSCWHERECno='C001')查詢計算機系學生考試成績高于計算機系學生平均成績的學生的姓名、考試的課程名和考試成績。SELECTSname,Sdept,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDSdept='計算機系'ANDGrade>(SELECTAVG(Grade)FROMSC,Course,StudentWHERESC.Cno=Course.CnoANDStudent.Sno=SC.SnoANDSdept='計算機系')查詢VB課程考試成績高于VB平均成績的學生姓名和VB成績。SELECTSname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDCname='VB'ANDGrade>(SELECTAVG(Grade)FROMSC,CourseWHERECourse.Cno=SC.CnoANDCname='VB')查詢沒選VB的學生姓名和所在系。SELECTSname,SdeptFROMStudentWHERESnoNOTIN(SELECTSnoFROMSC,CourseWHERESC.Cno=Course.CnoANDCname='VB')或者SELECTSname,SdeptFROMStudentWHERENOTEXISTS(SELECT*FROMSC,CourseWHERECourse.Cno=SC.CnoANDSno=Student.SnoANDCname='VB')查詢每個學期學分最高的課程信息，列出開課學期、課程名和學分。SELECTCname,Semester,CreditFROMCoursec1WHERENOTEXISTS(SELECT*FROMCoursec2WHEREc1.Semester=c2.SemesterANDc1.Credit<c2.Credit)查詢每門課程考試成績最高的學生信息，列出課程號、學生姓名和最高成績，結果按課程號升序排序，不包括沒考試的課程。SELECTCname,sname,SC.Cno,GradeFROMstudentjoinsconstudent.sno=sc.snojoincourseono=oWHEREgrade=(SELECTmax(grade)FROMscWHEREcno=o)orderBYSC.CnoASc或者SELECTsc1.sno,Sname,Cno,GradeFROMSCsc1,studentWHEREnotexists(SELECT*FROMSCsc2WHEREsc1.Cno=sc2.CnoANDsc1.Grade<sc2.Grade)ANDGradeISNOTNULLANDStudent.Sno=sc1.SnoorderBYCnoASC查詢選了全部課程的學生姓名SELECTSnameFROMStudentWHERESnoin(SELECTSnoFROMSCGROUPBYSnohavingCOUNT(*)=(SELECTCOUNT(*)FROMCourse))或者SELECTSnameFROMstudentWHERENOTexists(SELECT*FROMcourseWHERENOTexists(SELECT*FROMSCWHERESno=student.snoANDcno=Course.Cno))創(chuàng)建一個新表，表名為test，其結構為（COL1,COL2,COL3），其中，COL1：整型，允許空值。COL2：普通編碼定長字符型，長度為10，不允許空值。COL3：普通編碼定長字符型，長度為10，允許空值。試寫出按行插入如下數(shù)據(jù)的語句（空白處表示是空值）。COL1COL2COL3B11B2C22B3createtabletest(C0L1int,C0L2char(10)notnull,C0L3char(10))insertintotest(C0L2)values('B1')insertintotestvalues(1,'B2','C2')insertintotest(C0L1,C0L2)values(2,'B3')利用23題建立的VB_Grade表，將信息管理系選了VB課程的學生姓名、所在系和考試成績插入到VB_Grade表中。INSERTINTOVB_GradeSELECTSname,Sdept,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDSdept='信息管理系'ANDCname='VB'將所有選修C001課程的學生的成績加10分。UPDATESCSETGrade=Grade+10WHERECno='C001'將計算機系所有學生的“計算機文化學”的考試成績加10分。UPDATESCSETGrade=Grade+10WHERESnoin(SELECTSC.SnoFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDSdept='計算機系'ANDCname='計算機文化學')修改“VB”課程的考試成績，如果是通信工程系的學生，則增加10分；如果是信息管理系的學生則增加5分，其他系的學生不加分。UPDATESCSETGrade=Grade+CASESdeptWHEN'通信工程系'THEN10WHEN'信息管理系'THEN5else0endFROMStudent,SC,CourseWHERESC.Sno=Student.SnoANDCourse.Cno=SC.CnoANDCname='VB'刪除成績小于50分的學生的選課記錄。DELETEFROMSCWHEREGrade<50刪除計算機系VB考試成績不及格學生的VB選課記錄。DELETEFROMSCFROMSC,Student,CourseWHERESC.Sno=Student.SnoANDSC.Cno=Course.CnoANDSdept='計算機系'ANDCname='VB'ANDGrade<60刪除“VB”考試成績最低的學生的VB修課記錄。DELETEFROMSCFROMSC,CourseWHERESC.Cno=Course.CnoANDCname='VB'ANDGrade=(SELECTMIN(Grade)FROMSC,CourseWHERESC.Cno=Course.CnoANDCname='VB')刪除沒人選的課程的基本信息。DELETEFROMCourseFROMCourseleftjoinSConCourse.Cno=SC.CnoWHERESC.CnoISNULL本章有關建表和插入表格中的數(shù)據(jù)CREATETABLEStudent(SnoCHAR(7)PRIMARYKEY,SnameNCHAR(5)NOTNULL,SsexNCHAR(1),SageTINYINT,SdeptNVARCHAR(20))CREATETABLECourse(CnoCHAR(6),CnameNVARCHAR(20)NOTNULL,CreditTINYINT,SemesterTINYINT,PRIMARYKEY(Cno))CREATETABLESC(SnoCHAR(7)NOTNULL,CnoCHAR(6)NOTNULL,GradeTINYINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))insertintostudentvalues('0811101','李勇','男',21,'計算機系')insertintostudentvalues('0811102','劉晨','男',20,'計算機系')insertintostudentvalues('0811103','王敏','女',20,'計算機系')insertintostudentvalues('0811104','張小紅','女',19,'計算機系')insertintostudentvalues('0821101','張立','男',20,'信息管理系')insertintostudentvalues('0821102','吳賓','女',19,'信息管理系')insertintostudentvalues('0821103','張海','男',20,'信息管理系')insertintostudentvalues('0831101','錢小平','女',21,'通信工程系')insertintostudentvalues('0831102','王大力','男',20,'通信工程系')insertintostudentvalues('0831103','張姍姍','女',19,'通信工程系')insertintocoursevalues('C001','高等數(shù)學',4,1)insertintocoursevalues('C002','大學英語',3,1)insertintocoursevalues('C003','大學英語',3,2)insertintocoursevalues('C004','計算機文化學',2,2)insertintocoursevalues('C005','VB',2,3)insertintocoursevalues('C006','數(shù)據(jù)庫基礎',4,5)insertintocoursevalues('C007','數(shù)據(jù)結構',4,4)insertintocoursevalues('C008','計算機網(wǎng)絡',4,4)insertintoscvalues('0811101','C001',96)insertintoscvalues('0811101','C002',80)insertintoscvalues('0811101','C003',84)insertintoscvalues('0811101','C005',62)insertintoscvalues('0811102','C001',92)insertintoscvalues('0811102','C002',90)insertintoscvalues('0811102','C004',84)insertintoscvalues('0821102','C001',76)insertintoscvalues('0821102','C004',85)insertintoscvalues('0821102','C005',73)insertintoscvalues('0821102','C007',NULL)insertintoscvalues('0821103','C001',50)insertintoscvalues('0821103','C004',80)insertintoscvalues('0831101','C001',50)insertintoscvalues('0831101','C004',80)insertintoscvalues('0831102','C007',NULL)insertintoscvalues('0831103','C004',78)insertintoscvalues('0831103','C005',65)insertintoscvalues('0831103','C007',NULL)C.3第6章上機實驗下列實驗均使用SQLServer的SSMS工具實現(xiàn)。利用第4章上機實驗創(chuàng)建的“學生數(shù)據(jù)庫”中Student、Course和SC表，完成下列實驗。寫出實現(xiàn)下列操作的SQL語句，并執(zhí)行所寫代碼。（1）在Student表上為Sname列建立一個聚集索引，索引名為：IdxSno。（提示：若執(zhí)行創(chuàng)建索引的代碼，請先刪除該表的主鍵約束）注意:一個表只能有一個聚集索引，可以有多個非聚集索引(聚集索引對數(shù)據(jù)按索引關鍵字值進行物理排序，數(shù)據(jù)行本身只能按一個順序存儲)Student本身創(chuàng)建主鍵(Sno)的同時會默認創(chuàng)建唯一聚集索引，且Sno為SC表的外鍵，所以，首先要刪除SC表的外鍵約束，再刪除Student表的主鍵約束chenyuchao ALTERTABLESCDROPCONSTRAINTFK__SC__Sno__31EC6D26ALTERTABLESTUDENTDROPCONSTRAINTPK__Student__CA1FE464CD8CBE4FCREATECLUSTEREDINDEXidxSnoONStudent(Sname)（2）在Course表上為Cname列建立一個唯一的非聚集索引，索引名為：IdxCNCREATEUNIQUENONCLUSTEREDINDEXidxCNONCourse(Cname) (如果是在表中數(shù)據(jù)都插入完成后建立索引，此處會報錯，因為Cname中有兩個不同學期的大學英語課程，就不唯一了，請注意)（3）在SC表上為Sno和Cno建立一個組合的聚集索引，索引名為：IdxSnoCno。（提示：若執(zhí)行創(chuàng)建索引的代碼，請先刪除該表的主鍵約束）同樣需要刪除主鍵約束 ALTERTABLESCDROPCONSTRAINTPK__SC__E6000253A86AC951CREATECLUSTEREDINDEXidxSnoCnoONSC(Sno,Cno)（4）刪除Sname列上建立的IdxSno索引。DROPINDEXStudent.idxSno寫出創(chuàng)建滿足下述要求的視圖的SQL語句，并執(zhí)行所寫代碼。（1）查詢學生的學號、姓名、所在系、課程號、課程名、課程學分。 CREATEVIEWv1ASSELECTStudent.Sno,Sname,Sdept,SC.Cno,Cname,CreditFROMstudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoGO（2）查詢學生的學號、姓名、選修的課程名和考試成績。CREATEVIEWv2ASSELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoGO（3）統(tǒng)計每個學生的選課門數(shù)，要求列出學生學號和選課門數(shù)。 CREATEVIEWv3ASSELECTSno,COUNT(Cno)CoursenumFROMSCGROUPBYSnoGO（4）統(tǒng)計每個學生的修課總學分，要求列出學生學號和總學分（說明：考試成績大于等于60才可獲得此門課程的學分）。CREATEVIEWv4ASSELECTSno,SUM(Credit)CreditsumFROMSC,CourseWHERESC.Cno=Course.CnoANDGrade>=60GROUPBYSnoGO利用第2題建立的視圖，完成如下查詢。（1）查詢考試成績大于等于90分的學生的姓名、課程名和成績。 SELECTSname,Cname,GradeFROMv2WHEREGrade>=90（2）查詢選課門數(shù)超過3門的學生的學號和選課門數(shù)。SELECTSname,Cname,GradeFROMv2WHEREGrade>=90（3）查詢計算機系選課門數(shù)超過3門的學生的姓名和選課門數(shù)。SELECTSname,CnumFROMStudent,v3WHEREv3.Sno=Student.SnoANDSdept='計算機系'ANDCoursenum>3（4）查詢修課總學分超過10分的學生的學號、姓名、所在系和修課總學分。 SELECTStudent.Sno,Sname,Sdept,CreditsumFROMStudent,v4WHEREv4.Sno=Student.SnoANDCreditsum>10（5）查詢年齡大于等于20歲的學生中，修課總學分超過10分的學生的姓名、年齡、所在系和修課總學分。SELECTSname,Sage,Sdept,CreditsumFROMStudent,v4WHEREv4.Sno=Student.SnoANDCreditsum>10ANDSage>=20修改第3題（4）定義的視圖，使其查詢每個學生的學號、總學分以及總的選課門數(shù)。ALTERVIEWv4ASSELECTSno,SUM(Credit)Creditsum,COUNT(SC.Cno)CoursenumFROMSC,CourseWHERESC.Cno=Course.CnoGROUPBYSnoC.4第7章上機實驗利用第5章建立的學生數(shù)據(jù)庫以及Student、Coures和SC表，完成下列操作。創(chuàng)建滿足如下要求的后觸發(fā)型觸發(fā)器。說明:對于insertinto表名values()單行插入語句，觸發(fā)器每次都會被觸發(fā)對于insertinto表名selecFROM表名或者是insertinto表名values(),(),()多行插入語句，觸發(fā)器只會觸發(fā)一次，所有的插入數(shù)據(jù)都會一并備份到inserted表中，然后執(zhí)行觸發(fā)篩選chenyuchao（1）限制學生的考試成績必須在0-100之間。CREATETRIGGERtri1ONSCafterinsertASifexists(SELECTGradeFROMinsertedWHEREGradenotbetween0AND100)rollbackgo（2）限制不能刪除成績不及格的考試記錄。CREATETRIGGERtri2ONSCAFTERDELETEASifexists(SELECT*FROMdeletedWHEREGrade<60)rollbackgo（3）限制每個學期開設的課程總學分不能超過20。CREATETRIGGERtri3ONCourseAFTERINSERTASifexists(SELECTSUM(Credit)FROMCourseGROUPBYSemesterhavingSUM(Credit)>20)rollbackgo（4）限制每個學生每學期選的課程不能超過5門。CREATETRIGGERtri4ONSCAFTERINSERTASifexists(SELECTCOUNT(SC.Cno)FROMSC,CourseWHERESC.Cno=Course.CnoGROUPBYSemester,SnohavingCOUNT(SC.Cno)>5)ROLLBACKgo創(chuàng)建滿足如下要求的存儲過程。（1）查詢每個學生的修課總學分，要求列出學生學號及總學分。CREATEPROCp1ASSELECTStudent.Sno,SUM(Credit)CreditSumFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoGROUPBYStudent.Sno執(zhí)行：execp1（2）查詢學生的學號、姓名、修的課程號、課程名、課程學分，將學生所在的系作為輸入?yún)?shù)，執(zhí)行此存儲過程，并分別指定一些不同的輸入?yún)?shù)值。CREATEPROCp2@deptchar(20)='信息管理系'ASSELECTStudent.Sno,Sname,SC.Cno,Course.Cname,CreditFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDSdept=@dept執(zhí)行:execp2執(zhí)行:execp2@dept='計算機系'（3）查詢指定系的男生人數(shù)，其中系為輸入?yún)?shù)，人數(shù)為輸出參數(shù)。 CREATEPROCp3@deptchar(20),@numberintoutputASSELECT@number=COUNT(Sno)FROMStudentWHERESdept=@deptANDSsex='男'執(zhí)行:declare@numintexecp3計算機系,@numoutputprint@num（4）刪除指定學生的修課記錄，其中學號為輸入?yún)?shù)。CREATEPROCp4@snochar(20)ASdeleteFROMSCWHERESno=@sno執(zhí)行:execp4'0811101'（5）修改指定課程的開課學期。輸入?yún)?shù)為：課程號和修改后的開課學期。CreateProcp5@cnochar(20),@semesterIntASupdateCoursesetSemester=@semesterWHERECno=@cno執(zhí)行:execp5'C001',4C.5第11章上機實驗利用第4、5章建立的學生數(shù)據(jù)庫和其中的Student、Course、SC表，并利用SSMS工具完成下列操作。用SSMS工具建立SQLServer身份驗證模式的登錄名：log1、log2和log3。用log1建立一個新的數(shù)據(jù)庫引擎查詢，在“可用數(shù)據(jù)庫”下列列表框中是否能選中學生數(shù)據(jù)庫？為什么？用系統(tǒng)管理員身份建立一個新的數(shù)據(jù)庫引擎查詢，將log1、log2和log3映射為學生數(shù)據(jù)庫中的用戶，用戶名同登錄名。在log1建立的數(shù)據(jù)庫引擎查詢中，現(xiàn)在在“可用數(shù)據(jù)庫”下列列表框中是否能選中學生數(shù)據(jù)庫？為什么？在log1建立的數(shù)據(jù)庫引擎查詢中，選中學生數(shù)據(jù)庫，執(zhí)行下述語句，能否成功？為什么？SELECT*FROMCourse在系統(tǒng)管理員的數(shù)據(jù)庫引擎查詢中，執(zhí)行合適的授權語句，授予log1具有對Course表的查詢權限，授予log2具有對Course表的插入權限。用log2建立一個新的數(shù)據(jù)庫引擎查詢，執(zhí)行下述語句，能否成功？為什么？INSERTINTOCourseVALUES('C1001'，'數(shù)據(jù)庫基礎'，4，5)再執(zhí)行下述語句，能否成功？為什么？SELECT*FROMCourse在log1建立的數(shù)據(jù)庫引擎查詢中，再次執(zhí)行下述語句：SELECT*FROMCourse這次能否成功？但如果執(zhí)行下述語句：INSERTINTOCourseVALUES('C103','軟件工程',4,5)能否成功？為什么？log3建立一個新的數(shù)據(jù)庫引擎查詢，執(zhí)行下述語句，能否成功？為什么？ CREATETABLENewTable(C1int,C2char(4))授予log3在學生數(shù)據(jù)庫中具有創(chuàng)建表的權限。在系統(tǒng)管理員的數(shù)據(jù)庫引擎查詢中，執(zhí)行下述語句： GRANTCREATETABLETOlog3 GO CREATESCHEMAlog3AUTHORIZATIONlog3 GO ALTERUSERlog3WITHDEFAULT_SCHEMA=log3在log3建立一個新的數(shù)據(jù)庫引擎查詢中，再次執(zhí)行第9題的語句，能否成功？為什么？ 如果執(zhí)行下述語句： SELECT*NewTable 能否成功？為什么？附錄C施工資料用表表C001工程概況工程名稱編號一般情況建設單位建設用途設計單位建設地點勘察單位建筑面積監(jiān)理單位工期施工單位計劃開工日期計劃竣工日期結構類型基礎類型層次建筑檐高地上面積地下面積人防等級抗震等級構造特征地基與基礎柱、內外墻梁、板、樓蓋外墻裝飾內墻裝飾樓地面裝飾屋面構造防火設備機電系統(tǒng)名稱其他表C002施工現(xiàn)場質量管理檢查記錄工程名稱施工許可證（開工證）編號建設單位項目負責人設計單位項目負責人勘察單位項目負責人監(jiān)理單位總監(jiān)理工程師施工單位項目經(jīng)理項目技術負責人序號項目內容1現(xiàn)場質量管理制度2質量責任制3主要專業(yè)工種操作上崗證書4專業(yè)承包單位資質管理制度5施工圖審查情況6地質勘察資料7施工組織設計編制及審批8施工技術標準9工程質量檢驗制度10混凝土攪拌站及計量設置11現(xiàn)場材料、設備存放與管理制度12檢查結論：總監(jiān)理工程師（建設單位項目負責人）年月日表C003分包單位資質報審表工程名稱施工編號監(jiān)理編號日期致（監(jiān)理單位）經(jīng)考察，我方認為擬選擇的（專業(yè)承包單位）具有承擔下列工程的施工資質和施工能力，可以保證本工程項目按合同的約定進行施工。分包后，我方仍然承擔總包單位的全部責任。請予以審查和批準。附：1、□分包單位資質材料2、□分包單位業(yè)績材料3、□中標通知書分包工程名稱（部位）工程量分包工程合同額備注合計施工總承包單位（章）項目經(jīng)理專業(yè)監(jiān)理工程師審查意見：專業(yè)監(jiān)理工程師日期總監(jiān)理工程師審核意見：監(jiān)理單位總監(jiān)理工程師日期表C004建設工程質量事故調查、勘察記錄工程名稱編號日期調（勘）查時間年月日時分至時分調（勘）查地點參加人員單位姓名職務電話被調查人陪同調（勘）查人員調（勘）查筆錄現(xiàn)場證物照片□有□無共張共頁事故證據(jù)資料□有□無共條共頁被調查人簽字調（勘）查人簽字表C005建筑材料檢測計劃工程名稱編號材料名稱檢測內容取樣批次計劃時間12345678910111213141516171819202122232425表C006工程實體檢測計劃工程名稱編號檢測項目檢測內容檢測點數(shù)計劃時間12345678910111213141516171819202122232425表C007施工日記工程名稱編號日期年月日星期氣溫最高：℃最低：℃天氣狀況生產(chǎn)情況記錄（施工部位、施工內容、機械作業(yè)、班組工作、生產(chǎn)存在問題等）日記的內容應包括：1.日期（年月日及星期）；2.天氣情況（最高、最低氣溫、雨、雪、風等發(fā)生的時段及其它需要說明的氣象情況）；3.生產(chǎn)活動（施工部位、使用材料種類、數(shù)量、作業(yè)班組、施工機械、作業(yè)內容、砂漿、混凝土配比及調整等等）；4.施工管理工作（包括技術交底、檢查、評定、驗收、控制手段以及往來技術文件和信息交換、問題的處理等等）。技術質量安全工作記錄(技術質量安全活動、檢查評定驗收、技術質量安全問題等)表C008監(jiān)理工程師通知回復單工程名稱施工編號監(jiān)理編號日期致：（監(jiān)理單位）我方接到編號為的監(jiān)理工程師通知后，已按要求完成了工作，現(xiàn)報上，請予以復查。詳細內容：專業(yè)承包單位項目經(jīng)理/責任人施工總承包單位項目經(jīng)理/責任人復查意見：監(jiān)理單位總/專業(yè)監(jiān)理工程師日期表C009工程材料/構配件/設備報審表工程名稱編號致：（項目監(jiān)理機構）我單位于年月日進場的工程材料/構配件/設備數(shù)量如下（見附件）。現(xiàn)將質量證明文件及自檢結果報上，擬用于下述部位：。請予以審核。附件：1、材料、構配件進場檢驗記錄2、質量證明文件3、自檢結果在承包單位（章）：項目經(jīng)理：日期：審查意見：經(jīng)檢查，上述工程材料/構配件/設備，符合/不符合設計文件和規(guī)范的要求，準予/不準予進場，同意/不同意使用于擬定部位。項目監(jiān)理機構：總/專業(yè)監(jiān)理工程師：日期：表C010___________________報驗申請表工程名稱：致：（監(jiān)理單位）我單位已完成了_____________________________________工作，現(xiàn)報上該工程報驗申請表，請予以審查和驗收。附件：承包單位（章）項目經(jīng)理_____________日期_____________審查意見：項目監(jiān)理機構________________總/專業(yè)監(jiān)理工程師________________日期________________表C011工程技術文件報審表工程名稱施工編號監(jiān)理編號日期致（監(jiān)理單位）我方已編制完成了技術文件，并經(jīng)相關技術負責人審查批準，請予以審定。附：技術文件頁冊施工總承包單位項目經(jīng)理/責任人專業(yè)承包單位項目經(jīng)理/責任人專業(yè)監(jiān)理工程師審查意見：專業(yè)監(jiān)理工程師日期總監(jiān)理工程師審批意見：審定結論：□同意□修改后再報□重新編制監(jiān)理單位總監(jiān)理工程師日期表C011危險性較大分部分項工程施工方案專家論證表工程名稱編號施工總承包單位項目負責人專業(yè)承包單位項目負責人分項工程名稱專家一覽表姓名性別年齡工作單位職務職稱專業(yè)任職年限專家論證意見：年月日簽名欄組長：專家：表C013技術交底記錄工程名稱編號交底日期施工單位分項工程名稱交底摘要頁數(shù)共頁，第頁交底內容：簽字欄交底人審核人接受交底人表C014圖紙會審記錄工程名稱編號日期設計單位專業(yè)名稱地點頁數(shù)共頁，第頁序號圖號圖紙問題答復意見簽字欄建設單位監(jiān)理單位設計單位施工單位表C015設計變更通知單工程名稱編號日期設計單位專業(yè)名稱變更摘要頁數(shù)共頁，第頁序號圖號變更內容簽字欄建設單位監(jiān)理單位設計單位施工單位表C015工程洽商記錄(技術核定單)工程名稱編號日期提出單位專業(yè)名稱洽商摘要頁數(shù)共頁，第頁序號圖號洽商內容簽字欄建設單位監(jiān)理單位設計單位施工單位表C017工程開工報審表工程名稱施工編號監(jiān)理編號日期致（監(jiān)理單位）我方承擔的工程。已完成了以下各項工作，具備了開工條件。特此申請施工，請核查并簽發(fā)開工指令。附件：施工總承包單位（章）項目經(jīng)理審查意見：監(jiān)理單位總監(jiān)理工程師日期表C017工程復工報審表工程名稱施工編號監(jiān)理編號日期致（監(jiān)理單位）根據(jù)號《工程暫停令》，我方已按照要求完成了以下各項工作，具備了復工條件，特此申請，請核查并簽發(fā)復工指令。附：具備復工條件的說明或證明專業(yè)承包單位項目經(jīng)理/責任人施工總承包單位項目經(jīng)理/責任人審查意見：監(jiān)理單位專業(yè)監(jiān)理工程師總監(jiān)理工程師日期表C019施工進度計劃報審表工程名稱施工編號監(jiān)理編號日期致（監(jiān)理單位）我方已根據(jù)施工合同的有關約定完成了工程總/年第季度月份工程施工進度計劃的編制，請予以審查。附：施工進度計劃及說明施工總承包單位（章）項目經(jīng)理專業(yè)監(jiān)理工程師審查意見：專業(yè)監(jiān)理工程師日期總監(jiān)理工程師審核意見：監(jiān)理單位總監(jiān)理工程師日期表C019年月人、機、料動態(tài)表工程名稱編號日期致（監(jiān)理單位）根據(jù)年月施工進度情況，我方現(xiàn)報上年月人、機、料統(tǒng)計表。勞動力工種合計人數(shù)持證人數(shù)主要機械機械名稱生產(chǎn)廠家規(guī)格、型號數(shù)量主要材料名稱單位上月庫存量本月進場量本月消耗量本月庫存量附件：施工單位項目經(jīng)理表C020工程延期申請表工程名稱編號日期致（監(jiān)理單位）根據(jù)施工合同條款的約定，由于的原因，我方申請工程延期，請予以批準。附件：1.工程延期的依據(jù)及工期計算合同竣工日期：申請延長竣工日期：2.證明材料專業(yè)承包單位項目經(jīng)理/責任人施工總承包單位項目經(jīng)理/責任人表C021工程款支付申請表工程名稱編號日期致（監(jiān)理單位）我方已完成了工作，按照施工合同條款的約定，建議單位應在年月日前支付該項工程款共（大寫）（小寫）：，現(xiàn)報上工程付款申請表，請予以審查并開具工程款支付證書。附件：工程量清單；計算方法。施工總承包單位（章）項目經(jīng)理表C022工程變更費用報審表工程名稱施工編號監(jiān)理編號日期致（監(jiān)理單位）茲申請第號工程變更單，申請費用見附表，請予以審核。附件：工程變更費用計算書專業(yè)承包單位項目經(jīng)理/責任人施工總承包單位項目經(jīng)理/責任人監(jiān)理工程師審核意見：監(jiān)理工程師日期總監(jiān)理工程師審查意見：監(jiān)理單位總監(jiān)理工程師日期表C023費用索賠申請表工程名稱編號日期致（監(jiān)理單位）根據(jù)施工合同條款的約定，由于的原因，我方要求索賠金額（大寫）元，請予以批準。附件：1.索賠的詳細理由及經(jīng)過2.索賠金額的計算3.證明材料專業(yè)承包單位項目經(jīng)理/責任人施工總承包單位項目經(jīng)理/責任人表C025材料、構配件進場檢驗記錄工程名稱編號檢驗日期序號名稱規(guī)格型號進場數(shù)量生產(chǎn)廠家質量證明書編號外觀檢驗項目檢驗結果試件編號復驗結果備注12345678910檢查意見（施工單位）：附件：共頁驗收意見（監(jiān)理/建設單位）□同意□重新檢驗□退場驗收日期：簽字欄施工單位專業(yè)質檢員專業(yè)工長質檢員監(jiān)理或建設單位專業(yè)工程師表C025設備開箱檢驗記錄工程名稱編號檢驗日期設備名稱規(guī)格型號生產(chǎn)廠家產(chǎn)品合格證編號總數(shù)量檢驗數(shù)量進場檢驗記錄包裝情況隨機文件備件及附件外觀情況測試情況缺、損附備件明細序號附備件名稱規(guī)格單位數(shù)量備注檢查意見（施工單位）：附件：共頁驗收意見（監(jiān)理/建設單位）：□同意□重新檢驗□退場驗收日期：簽字欄供應單位責任人施工單位專業(yè)工長監(jiān)理或建設單位專業(yè)工程師表C026工程材料、構配件、設備退場記錄工程名稱編號序號名稱規(guī)格型號進場數(shù)量生產(chǎn)廠家質量證明書編號外觀檢驗項目檢驗結果試件編號復驗結果實際退場數(shù)量12345退場原因：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員專業(yè)工長監(jiān)理或建設單位專業(yè)工程師表C027設備及管道附件試驗記錄工程名稱編號使用部位實驗日期實驗要求設備/管道附件名稱材質、型號規(guī)格試驗數(shù)量試驗介質公稱或工作壓力（MPa）強度試驗試驗壓力（MPa）試驗持續(xù)時間（s）試驗壓力降（MPa）滲漏情況實驗結論嚴密性試驗試驗壓力（MPa）試驗持續(xù)時間（s）試驗壓力降（MPa）滲漏情況實驗結論簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員專業(yè)工長監(jiān)理或建設單位專業(yè)工程師

表C028智能設備材料進場檢驗表工程名稱編號系統(tǒng)名稱檢驗日期序號產(chǎn)品名稱規(guī)格、型號、產(chǎn)地主要性能/功能數(shù)量包裝及外觀檢驗結果備注合格不合格檢查意見（施工單位）附件：共頁驗收意見（監(jiān)理/建設單位）□同意□重新檢驗□退場驗收日期：簽字欄施工單位專業(yè)質檢員專業(yè)工長檢驗員監(jiān)理或建設單位專業(yè)工程師表C030隱蔽工程驗收記錄工程名稱編號隱檢項目隱檢日期隱檢部位層軸線標高隱檢依據(jù)：施工圖號，設計變更/洽商/技術核定單（編號）及有關國家現(xiàn)行標準等。主要材料名稱及規(guī)格/型號：隱檢內容：檢查結論：□同意隱蔽□不同意隱蔽，修改后復檢復查結論：復查人：復查日期：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員專業(yè)工長監(jiān)理或建設單位專業(yè)工程師表C030鋼筋工程隱蔽驗收記錄工程名稱編號施工單位施工日期隱蔽部位施工圖號作業(yè)班長等級/直徑化驗單編號等級/直徑化驗單編號試件規(guī)格代表部位代表數(shù)量連接方式報告編號試驗結果需要說明的事項（可加附頁或附圖）：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員專業(yè)工長監(jiān)理或建設單位專業(yè)工程師表C031智能建筑隱蔽工程（隨工檢查）驗收表工程名稱編號隱檢日期檢查內容與結果檢查內容檢查結果安裝質量樓層（部位）圖號驗收意見：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員專業(yè)工長監(jiān)理或建設單位專業(yè)工程師表C032施工檢查記錄工程名稱編號檢查日期檢查部位檢查項目檢查依據(jù)：檢查內容：檢查結論：復查結論：復查人：復查日期：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員專業(yè)工長表C033交接檢查記錄工程名稱編號檢查日期移交單位見證單位交接部位接收單位交接內容：檢查結論：復查結論（由接收單位填寫）：復查人：復查日期：見證單位意見：簽字欄移交單位接收單位見證單位表C034工程定位測量記錄工程名稱編號圖紙編號委托單位施測日期復測日期平面坐標依據(jù)高程依據(jù)使用儀器允許誤差儀器校驗日期定位抄測示意圖：復測結果：簽字欄施工單位測量人員崗位證書號專業(yè)技術負責人施工測量負責人復測人施測人監(jiān)理或建設單位專業(yè)工程師表C035基槽驗線記錄工程名稱編號驗線日期驗線依據(jù)及內容：基槽平面、剖面簡圖：檢查結論：□檢查合格□檢查不合格，修改后復檢復查結論：復查人：復查日期：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員施測人監(jiān)理或建設單位專業(yè)工程師表C036樓層平面放線記錄工程名稱編號放線日期放線內容放線部位放線依據(jù)：放線簡圖：檢查結論：□檢查合格□檢查不合格，修改后復檢復查結論：復查人：復查日期：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員施測人監(jiān)理或建設單位專業(yè)工程師表C037樓層標高抄測記錄工程名稱編號檢查時間抄測內容抄測部位抄測依據(jù)：抄測說明及簡圖：檢查結論：□檢查合格□檢查不合格，修改后復檢復查結論：復查人：復查日期：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員施測人監(jiān)理或建設單位專業(yè)工程師表C038建筑物垂直度、標高觀測記錄工程名稱編號施工階段觀測日期觀測說明（附觀測示意圖）：垂直度測量（全高）標高測量（全高）觀測部位實測偏差（mm）觀測部位實測偏差（mm）結論：簽字欄施工單位專業(yè)技術負責人專業(yè)質檢員施測人監(jiān)理或建設單位專業(yè)工程師表C039建筑區(qū)域原始地面高程測量記錄工程名稱編號測量日期網(wǎng)格間距水準點高程放線示意圖（可加A3附圖）：高程測量記錄：序號高程序號高程序號高程序號高程序號高程序號高程147101316258111417369121518平均高程：簽字欄施工