附录C 数据库上机实验及附录D 全国主要城市年平均雷暴日数统计表

附录C上机实验C.1第4章上机实验-------------------仅供参考------------------------------下列实验均使用SQLServer的SSMS工具实现。1．用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：数据库名为：学生数据库主要数据文件的逻辑文件名为：Students_data，存放在D:\Data文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB，增长方式为自动增长，每次增加1MB。日志文件的逻辑文件名字为：Students_log，也存放在D:\Data文件夹下，初始大小为：2MB，增长方式为自动增长，每次增加10%。2．选用已建立的“学生数据库”，写出创建满足表C-1到4-4条件的表的SQL语句，并执行所写代码。（注：“说明”部分不作为表定义内容）表C-1Student表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname姓名普通编码定长字符串，长度为10非空Ssex性别普通编码定长字符串，长度为2取值范围：{男，女}Sage年龄微整型（tinyint）取值范围：15-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”Sid身份证号普通编码定长字符串，长度为10取值不重Sdate入学日期日期默认为系统当前日期表C-2Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型表C-3SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型取值范围为0-100表C-4Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串，长度为10非空Salary工资定点小数，小数点前4位，小数点后2位3.写出实现如下功能的SQL语句，并执行所写代码，查看执行结果。在Teacher表中添加一个职称列，列名为：Title，类型为nchar(4)。ALTERTABLETeacherADDTitleVARCHAR(4)为Teacher表中的Title列增加取值范围约束，取值范围为：{教授，副教授，讲师}。ALTERTABLETeacherADDCONSTRAINTTitleCHECK(TitleIN('教授','副教授','讲师'))将Course表中Credit列的类型改为：tinyint。注意：这里首先需要先删除Credit的约束,修改完数据类型后再重新添加约束项ALTERTABLECourseDROPCONSTRAINTCK__Course__Credit__2B3F6F97ALTERTABLECourseALTERCOLUMNCreditTINYINTALTERTABLECourseADDCONSTRAINTCreditCHECK(Credit>0)删除Student表中的Sid和Sdate列。注意：这里首先需要先删除约束ALTERTABLEStudentDROPCONSTRAINTUQ__Student__CA1E5D79B3204FCFALTERTABLEStudentDROPCONSTRAINTDF__Student__Sdate__3B75D760ALTERTABLEStudentDROPCOLUMNSdate,Sid为Teacher表添加主键约束，其主键为：Tno。ALTERTABLETeacherADDPRIMARYKEY(Tno)※建立数据库主要文件和日志文件※我们可以看到D盘增加了主要数据文件.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工具中实现。首先在已创建的“学生数据库”中创建本章表5-1~5-3所示的Student、Course和SC表，并插入表5-4~5-6所示数据，然后编写实现如下操作的SQL语句，执行所写的语句，并查看执行结果。有关建表及所需要的数据，见本章末尾查询SC表中的全部数据。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='计算机系'统计每个系的学生人数。SELECTSdept,COUNT(Sno)AS人数FROMStudentGROUPBYSdept统计每门课程的选课人数和最高成绩。SELECTCno,COUNT(Sno)AS人数,MAX(Grade)AS最高成绩FROMSCGROUPBYCno统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。SELECTSno,COUNT(Cno)AS选课数量,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查询选课门数最多的前2位学生，列出学号和选课门数。√SELECTtop2Sno,COUNT(Cno)AS选课数FROMSCGROUPBYSnoORDERBYCOUNT(Cno)DESC查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。√SELECTSno,Sage,CASEWHENSage<18THEN'偏小'WHENSage>=18ANDSage<=22THEN'适合'WHENSage>22THEN'偏大'ENDAS年龄情况FROMStudent统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40～100，则显示“一般”；如果此门课程的选课人数在1～40，则显示“人少”；如果此门课程没有人选，则显示“无人选”。√SELECTCourse.Cno,COUNT(Sno)AS选课人数,CASEWHENCOUNT(Sno)>100THEN'人多'WHENCOUNT(Sno)<40ANDCOUNT(Sno)>0THEN'人少'WHENCOUNT(Sno)<=100ANDCOUNT(Sno)>=40THEN'一般'WHENCOUNT(Sno)=0THEN'无人选'endAS人数情况FROMSCRIGHTJOINCourseONCourse.Cno=SC.CnoGROUPBYCourse.Cno查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade中。√局部临时表：#新表名。局部于当前连接，生命期同连接期。全局临时表：##新表名。可在所有连接中使用，生命期同用户连接期永久表：新表名，存储在磁盘上格式：SELECT选择列表 INTO新表名 FROM子句…SELECTSname,Sdept,GradeINTOVB_GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDCourse.Cno=SC.CnoANDCname='VB'统计每个系的女生人数，并将结果保存到新表Girls中。√SELECTSdept,Ssex,COUNT(Ssex)AS人数IntoGirlsFROMStudentWHERESsex='女'GROUPBYSdept,Ssex用子查询实现如下查询：（1）查询选了“C001”课程的学生姓名和所在系。√SELECTSname,SdeptFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno='C001')（2）查询通信工程系成绩80分以上的学生的学号和姓名。√SELECTSno,SnameFROMStudentWHERESdept='通信工程系'ANDSnoIN(SELECTSnoFROMSCWHEREGrade>80)（3）查询计算机系考试成绩最高的学生的姓名。√SELECTSnameFROMStudent,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=o=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))创建一个新表，表名为test，其结构为（COL1,COL2,COL3），其中，COL1：整型，允许空值。COL2：普通编码定长字符型，长度为10，不允许空值。COL3：普通编码定长字符型，长度为10，允许空值。试写出按行插入如下数据的语句（空白处表示是空值）。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本章有关建表和插入表格中的数据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','高等数学',4,1)insertintocoursevalues('C002','大学英语',3,1)insertintocoursevalues('C003','大学英语',3,2)insertintocoursevalues('C004','计算机文化学',2,2)insertintocoursevalues('C005','VB',2,3)insertintocoursevalues('C006','数据库基础',4,5)insertintocoursevalues('C007','数据结构',4,4)insertintocoursevalues('C008','计算机网络',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工具实现。利用第4章上机实验创建的“学生数据库”中Student、Course和SC表，完成下列实验。写出实现下列操作的SQL语句，并执行所写代码。（1）在Student表上为Sname列建立一个聚集索引，索引名为：IdxSno。（提示：若执行创建索引的代码，请先删除该表的主键约束）注意:一个表只能有一个聚集索引，可以有多个非聚集索引(聚集索引对数据按索引关键字值进行物理排序，数据行本身只能按一个顺序存储)Student本身创建主键(Sno)的同时会默认创建唯一聚集索引，且Sno为SC表的外键，所以，首先要删除SC表的外键约束，再删除Student表的主键约束----chenyuchao ALTERTABLESCDROPCONSTRAINTFK__SC__Sno__31EC6D26ALTERTABLESTUDENTDROPCONSTRAINTPK__Student__CA1FE464CD8CBE4FCREATECLUSTEREDINDEXidxSnoONStudent(Sname)（2）在Course表上为Cname列建立一个唯一的非聚集索引，索引名为：IdxCNCREATEUNIQUENONCLUSTEREDINDEXidxCNONCourse(Cname) (如果是在表中数据都插入完成后建立索引，此处会报错，因为Cname中有两个不同学期的大学英语课程，就不唯一了，请注意)（3）在SC表上为Sno和Cno建立一个组合的聚集索引，索引名为：IdxSnoCno。（提示：若执行创建索引的代码，请先删除该表的主键约束）同样需要删除主键约束 ALTERTABLESCDROPCONSTRAINTPK__SC__E6000253A86AC951CREATECLUSTEREDINDEXidxSnoCnoONSC(Sno,Cno)（4）删除Sname列上建立的IdxSno索引。DROPINDEXStudent.idxSno写出创建满足下述要求的视图的SQL语句，并执行所写代码。（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）统计每个学生的选课门数，要求列出学生学号和选课门数。 CREATEVIEWv3ASSELECTSno,COUNT(Cno)CoursenumFROMSCGROUPBYSnoGO（4）统计每个学生的修课总学分，要求列出学生学号和总学分（说明：考试成绩大于等于60才可获得此门课程的学分）。CREATEVIEWv4ASSELECTSno,SUM(Credit)CreditsumFROMSC,CourseWHERESC.Cno=Course.CnoANDGrade>=60GROUPBYSnoGO利用第2题建立的视图，完成如下查询。（1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩。 SELECTSname,Cname,GradeFROMv2WHEREGrade>=90（2）查询选课门数超过3门的学生的学号和选课门数。SELECTSname,Cname,GradeFROMv2WHEREGrade>=90（3）查询计算机系选课门数超过3门的学生的姓名和选课门数。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）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。ALTERVIEWv4ASSELECTSno,SUM(Credit)Creditsum,COUNT(SC.Cno)CoursenumFROMSC,CourseWHERESC.Cno=Course.CnoGROUPBYSnoC.4第7章上机实验利用第5章建立的学生数据库以及Student、Coures和SC表，完成下列操作。创建满足如下要求的后触发型触发器。说明:对于insertinto表名values()单行插入语句，触发器每次都会被触发对于insertinto表名selecFROM表名或者是insertinto表名values(),(),()....多行插入语句，触发器只会触发一次，所有的插入数据都会一并备份到inserted表中，然后执行触发筛选----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创建满足如下要求的存储过程。（1）查询每个学生的修课总学分，要求列出学生学号及总学分。CREATEPROCp1ASSELECTStudent.Sno,SUM(Credit)CreditSumFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoGROUPBYStudent.Sno执行：execp1（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在的系作为输入参数，执行此存储过程，并分别指定一些不同的输入参数值。CREATEPROCp2@deptchar(20)='信息管理系'ASSELECTStudent.Sno,Sname,SC.Cno,Course.Cname,CreditFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDSdept=@dept执行:execp2执行:execp2@dept='计算机系'（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。 CREATEPROCp3@deptchar(20),@numberintoutputASSELECT@number=COUNT(Sno)FROMStudentWHERESdept=@deptANDSsex='男'执行:declare@numintexecp3计算机系,@numoutputprint@num（4）删除指定学生的修课记录，其中学号为输入参数。CREATEPROCp4@snochar(20)ASdeleteFROMSCWHERESno=@sno执行:execp4'0811101'（5）修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期。CreateProcp5@cnochar(20),@semesterIntASupdateCoursesetSemester=@semesterWHERECno=@cno执行:execp5'C001',4C.5第11章上机实验利用第4、5章建立的学生数据库和其中的Student、Course、SC表，并利用SSMS工具完成下列操作。用SSMS工具建立SQLServer身份验证模式的登录名：log1、log2和log3。用log1建立一个新的数据库引擎查询，在“可用数据库”下列列表框中是否能选中学生数据库？为什么？用系统管理员身份建立一个新的数据库引擎查询，将log1、log2和log3映射为学生数据库中的用户，用户名同登录名。在log1建立的数据库引擎查询中，现在在“可用数据库”下列列表框中是否能选中学生数据库？为什么？在log1建立的数据库引擎查询中，选中学生数据库，执行下述语句，能否成功？为什么？SELECT*FROMCourse在系统管理员的数据库引擎查询中，执行合适的授权语句，授予log1具有对Course表的查询权限，授予log2具有对Course表的插入权限。用log2建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？INSERTINTOCourseVALUES('C1001'，'数据库基础'，4，5)再执行下述语句，能否成功？为什么？SELECT*FROMCourse在log1建立的数据库引擎查询中，再次执行下述语句：SELECT*FROMCourse这次能否成功？但如果执行下述语句：INSERTINTOCourseVALUES('C103','软件工程',4,5)能否成功？为什么？log3建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？ CREATETABLENewTable(C1int,C2char(4))授予log3在学生数据库中具有创建表的权限。在系统管理员的数据库引擎查询中，执行下述语句： GRANTCREATETABLETOlog3 GO CREATESCHEMAlog3AUTHORIZATIONlog3 GO ALTERUSERlog3WITHDEFAULT_SCHEMA=log3在log3建立一个新的数据库引擎查询中，再次执行第9题的语句，能否成功？为什么？ 如果执行下述语句： SELECT*NewTable 能否成功？为什么？附录D全国主要城市年平均雷暴日数统计表地名雷暴日数（d/a）地名雷暴日数（d/a）地名雷暴日数（d/a）1、北京市36.3吉林市40.514