数据库课后作业

数据库大作业11.针对习题10的四个表，用SQL语言完成以下各项操作：（1）给学生表增加一属性Nation（民族），数据类型为Varchar（20）；ALTERTABLEdbo.StudentADDNationVarChar(20);SElECT*FROMdbo.Student;（2）删除学生表中新增的属性Nation；ALTERTABLEdbo.StudentDROPCOLUMNNation;SElECT*FROMdbo.Student;（3）向成绩表中插入记录（“2001110”，“3”，80）；INSERTINTOdbo.GradeVALUES('2001110','3',80);SElECT*FROMdbo.Grade;（4）将学号为“2001110”的学生的成绩修改为70分；UPDATEdbo.GradeSETGmark=70WHERESno='2001110';SElECT*FROMdbo.Grade;（5）删除学号为“2001110”的学生的成绩记录；DELETEFROMdbo.GradeWHERESno='2001110';SElECT*FROMdbo.Grade;（6）在学生表的Clno属性上创建一个名为IX_Class的索引，以班级号的升序排序；CREATEINDEXIX_CLASSONdbo.Student(Clno);SELECT*FROMdbo.Student;（7）删除IX_Class索引。DROPINDEXStudent.IX_CLASS;SELECT*FROMdbo.Student;12.针对习题10的四个表，用SQL语言完成以下各项查询：（1）找出所有被学生选修了的课程号；SELECTDISTINCTCnoFROMGrade,StudentWHEREGrade.Sno=Student.Sno;（2）找出01311班女学生的个人信息；SELECT*FROMStudentWHERESsex='女'ANDClno='01311';（3）找出0311班和01312班的学生姓名、性别、出生年份；SELECTSname,Ssex,(2014-Sage)AS'出生年份'FROMStudentWHEREClnoIN('01311','01312');（4）找出所有姓李的学生的个人信息；SELECT*FROMStudentWHERESnameLIKE'李%';（5）找出学生李勇所在班级的学生人数；SELECTCOUNT(*)AS'李勇班级人数'FROMStudentWHEREClnoIN(SELECTClnoFROMStudentWHERESname='李勇');（6）找出课程名为操作系统的平均成绩、最高分、最低分；SELECTMAX(Gmark)AS'最高分',MIN(Gmark)AS'最低分',AVG(Gmark)AS'平均成绩'FROMGradeg,CoursecWHEREg.CnoIN(SELECTc.CnoFROMCourseWHEREc.Cname='操作系统');（7）找出选修了课程的学生人数；SELECTCOUNT(DISTINCTSno)AS'选修了课程的学生人数'FROMGrade;（8）找出选修了课程操作系统的学生学号和姓名；SELECTS.Sno,S.SnameFROMStudentS,GradeGWHERES.SnoIN(SELECTG.SnoFROMGradeWHEREG.Cno=(SELECTCnoFROMCourseWHERECname='操作系统'));（9）找出2000级计算机软件班的成绩为空的学生姓名。SELECTS.SnameFROMStudentSWHERES.SnoNOTIN(SELECTSnoFROMGrade)ANDS.Clno=(SELECTClnoFROMClassWHEREInyear='2000'ANDSpeciality='计算机软件');13.针对习题10的四个表，用SELECT的嵌套查询完成以下各项查询：（1）找出与李勇在同一个班级的学生信息；SELECT*FROMStudentWHEREClno=(SELECTClnoFROMStudentWHERESname='李勇')ANDSnameNOTIN('李勇');（2）找出所有与李勇有相同选修课程的学生信息；SELECT*FROMStudentWHERESnoIN(SELECTSnoFROMGradeWHEREGrade.CnoIN(SELECTCnoFROMGradeWHERESno=(SELECTSnoFROMStudentWHERESname='李勇')))ANDSnameNOTIN('李勇');（3）找出年龄介于学生李勇与25岁之间的学生信息（已知李勇的年龄小于25岁）；SELECT*FROMStudentWHERESage25ANDSage(SELECTSageFROMStudentWHERESname='李勇');（4）找出选修了课程操作系统的学生学号与姓名；SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMGradeWHERECno=(SELECTCnoFROMCourseWHERECname='操作系统'));（5）找出没有选修1号课程的所有学生姓名；SELECTSnameFROMStudentWHERESnoNOTIN(SELECTSnoFROMGradeWHERECno='1');（6）找出选修了全部课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMGradeWHERESno=Student.SnoANDCno=Course.Cno));14.针对习题10的四个表，用SQL语言完成以下各项查询：（1）查询选修了3门以上的课程的学生学号及其成绩，并按成绩的降序排列；SELECTSno,GmarkFROMGradeWHERECno=3ORDERBYGmarkDESC;（2）查询全体学生信息，要求查询结果按班级号升序排列同一班级按年龄降序排列；SELECT*FROMStudentORDERBYClno,SageDESC;（3）求每个课程号及相应的选课人数；SELECTCno,COUNT(Cno)AS'选修人数'FROMGradeGROUPBYCno;（4）查询选修了3门以上课程的学生学号。SELECTSno,COUNT(Sno)AS'选修课程数'FROMGradeGROUPBYSnoHAVING(COUNT(Sno)3);15.针对习题10的四个表，用SQL语言完成以下各项操作：（1）将01311班的全体学生的成绩置零；UPDATEGradeSETGmark=0WHERESnoIN(SELECTSnoFROMStudentWHEREClno='01311');（2）删除2001级计算机软件的全体学生的选课记录；DELETEFROMGradeWHERESnoIN(SELECTSnoFROMStudentWHEREClno=(SELECTClnoFROMClassWHEREInyear='2001'ANDSpeciality='计算机软件'));（3）学生李勇已退学，从数据库中删除有关他的记录；UPDATEClassSETMonitor=NULLWHEREClno=(SELECTClnoFROMStudentWHERESname='李勇')DELETEFROMGradeWHERESno=(SELECTSnoFROMStudentWHERESname='李勇')DELETEFROMStudentWHERESname='李勇';（4）对每个班，求学生的平均年龄，并把结果存入数据库。CREATEVIEWV_SagASSELECTClno,AVG(Sage)AS'Sag'FROMStudentGROUPBYClno;16.视图操作：（1）建立01311班选修了1号课程的学生视图Stu_01311_1；CREATEVIEWStu_01311_1ASSELECT*FROMStudentWHERESnoIN(SELECTSnoFROMGradeWHERECno=1)ANDClno='01311';（2）建立01311班选修了1号课程并且不及格的学生视图Stu_01311_2；CREATEVIEWStu_01311_2ASSELECT*FROMStudentWHERESnoIN(SELECTSnoFROMGradeWHERECno=1ANDGmark60)ANDClno='01311';（3）建立视图Stu_year，由学生学号、姓名、出生年份组成；CREATEVIEWStu_yearASSELECTSnoAS'学号',SnameAS'姓名',(2014-Sage)AS'出生年份'FROMStudent;（4）查询1990年以后出生的学生姓名；SELECTSnameFROMdbo.Stu_yearWHERE'出生年份''1990';（5）查询01311班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年份。SELECT*FROMStu_yearWHERESnoIN(SELECTSnoFROMStu_01311_2);第四章触发器11.对于成绩管理数据库,为成绩管理数据库中的Student表创建一触发器:当向表中插入或删除记录时,修改Class表中相应的班级人数CREATETRIGGERfri_stuONStudentAFTERINSERT,DELETEASIFUPDATE(Sno)UPDATEClassSETNumber=Number+1WHEREClno=(SELECTClnoFROMINSERTED)ELSEUPDATEClassSETNumber=Number-1WHEREClno=(SELECTClnoFROMDELETED);第五章存储过程10.创建一存储过程:根据学生学号查询该学生所有选修课成绩,学号作为输入参数CREATEPROCEDUREproc_Stu@stu_Snochar(7)ASSELECTGmark,CnoFROMGradeWHERESno=@Stu_Sno;