数据库原理张红娟答案

11.（1）给学生表增加一个属性Nation,数据类型为Varchar（20）：ALTERTABLEStudentADDNationVARCHAR(20)NULL;（2）删除Nation：ALTERTABLEStudentDropColumnNation;（3）向成绩表中插入记录(2001110,3,80)：insertintoGradevalues('2001110','3',80);（4）将学号为2001110的学生的成绩改为70分：updateGradesetGmark='70'whereSno='2001110';（5）删除学号为'2001110'的学生的成绩记录：deletefromGradewhereSno='2001110';（6）在学生表的clno属性上创建一个名为'IX_Class'的索引，以班级号的升序排序：createindexIX_ClassonStudent(clnoAsc);（7）删除'IX_Class'索引：dropindexStudent.IX_Class;12.（1）找出所有被学生选修了的课程号：selectdistinctCnofromGrade;（2）找出01311班女学生的个人信息：select*fromStudentwhereClno='01311'andSsex='女';（3）找出01311班和01312班的学生姓名、姓名、出生年份selectSname,Ssex,2014-Sageas[yearofbirth]fromStudentwhereClno='01311'orClno='01312';（4）找出所有姓李的学生的个人信息select*fromStudentwhereSnamelike'李%';（5）找出学生李勇所在班级的学生人数selectnumberfromstudentinnerjoinclassonstudent.clno=class.clnowheresname='李勇'（6）找出课程名为操作系统的平均成绩、最高分、最低分selectAVG(Gmark)平均成绩,MAX(Gmark)最高分,MIN(Gmark)最低分fromGradewhereCnoin(selectCnofromCoursewhereCname='操作系统')（7）选修了课程的学生人数;selectCOUNT(distinctsno)学生人数fromGrade（8）选修了操作系统的学生人数;selectCOUNT(sno)学生人数fromcourseinnerjoingradeoncourse.cno=grade.cnowhereCname='操作系统'（9）找出2000级计算机软件班的成绩为空的学生姓名selectSname学生姓名from(Studentinnerjoinclassonstudent.clno=class.clno)innerjoingradeonstudent.sno=grade.snowhereSpeciality='计算机软件'andinyear='2000'andgmarkisnull13.1)找出和李勇在同一个班级的学生信息select*fromStudentwhereclnoin(selectClnofromStudentwhereSname='李勇')2)找出所有与学生李勇有相同选修课程的学生信息select*fromStudentwheresnoin(selectsnofromgradewherecnoin(selectcnofromgradewheresnoin(selectsnofromstudentwhereSname='李勇')));3)找出年龄介于学生李勇和25岁之间的学生信息select*fromStudentwhereSage25andSage(selectSagefromStudentwhereSname='李勇')4)找出选修了课程是操作系统的学生学号和姓名selectSno学号,Sname姓名fromStudentwheresnoin(selectsnofromGradewhereCnoin(selectCnofromCoursewherecnoin(selectcnofromcoursewhereCname='操作系统')));5)找出没有选修1号课程的所有学生姓名selectSname姓名fromStudentwherenotexists(select*fromGradewhereStudent.Sno=Grade.SnoandCno='1')6)找出选修了全部课程的学生姓名selectSname姓名fromStudentwherenotexists(select*fromCoursewherenotexists(select*fromGradewhereStudent.Sno=Grade.SnoandGrade.cno=course.cno))14.1)查询选修了3号课程的学生学号及成绩,并按成绩的降序排列selectSno学号,Gmark成绩fromGradewhereCno='3'orderbyGmarkdesc2)查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列select*fromStudentorderbyClnoasc,Sagedesc3)求每个课程号及相应的选课人数selectGrade.Cno课程号,COUNT(Grade.Cno)选课人数fromGradejoinCourseonGrade.Cno=Course.CnogroupbyGrade.Cno4)查询选修了3门以上课程的学生学号selectSno学号fromGradegroupbySnohavingCOUNT(Sno)315.1)将01311班的全体学生的成绩置零updateGradesetGmark=0whereSnoin(selectSnofromStudentwhereClno='01311')2)删除2001级计算机软件的全体学生的选课记录deletefromGradewhereSnoin(selectSnofromStudentwhereClnoin(selectClnofromClasswhereSpeciality='计算机软件'andInyear='2001'))3)学生李勇已退学,从数据库中删除有关他的记录deletefromGradewhereSnoin(selectSnofromStudentwhereSname='李勇')updateClasssetNumber=Number-1whereClnoin(selectClnofromStudentwhereSname='李勇')updateClasssetMonitor=casewhenMonitor=(selectSnofromStudentwhereSname='李勇')then''endfromClasswhereClnoin(selectClnofromStudentwhereSname='李勇')deletefromStudentwhereSname='李勇'4)对每个班,求学生的平均年龄,并把结果存入数据库altertableClassaddCagesmallintnullupdateClasssetCage=casewhenClno='00311'then(selectAVG(Sage)fromStudentwhereClno='00311')whenClno='00312'then(selectAVG(Sage)fromStudentwhereClno='00312')whenClno='01311'then(selectAVG(Sage)fromStudentwhereClno='01311')endfromClass16.1、createviewstu_01311_1asselectstudentsno,sname,gmarkfromstudent,gradewherecno=1andclno=01311andstudent.sno=grade.snowithcheckoption2、createviewstu_01311_2anselect*fromstu_01311_1wheregmark603.createviewstu_year(sno,sname,years)asselectsno,sname,year(getdate())-sagefromstudent4.selectsnamefromstu_yearwhereyears19905.select*fromstu_yearwheresnoin(selectsnofromstu_01311_2)第四章10.创建course表createtablecourse(cnochar(1)primarykey,cnamevarchar(20)notnull,creditsmallintcheck(creditin('1','2','3','4','5','6','7')))创建class表createtableclass(clnochar(5)primarykey,specialityvarchar(20)notnull,inyearchar(4)notnull,numberintegercheck(number1andnumber300),monitorchar(7))创建student表createtablestudent(snochar(7)primarykey,snamevarchar(20)notnull,ssexchar(2)notnulldefault'男'check(ssexin('男','女')),sagesmallintcheck(sage14andsage65),clnochar(5)notnullforeignkey(clno)referencesclass(clno)onupdatecascade)为class添加参照完整性altertableclassaddconstraintmonitorforeignkey(monitor)referencesstudent(sno)创建grade表createtablegrade(snochar(7)notnullforeignkey(sno)referencesstudent(sno)onupdatecascadeondeletecascade,cnochar(1)notnullforeignkey(cno)referencescourse(cno)onupdatecascadeondeletecascade,gmarkdecimal(4,1)check(gmark0andgmark100),primarykey(sno,cno))11.插入createtriggerstu_insertonstudentafterinsertasupdateclasssetnumber=number+1fromclass,insertedwhereclass.clno=inserted.clno;删除createtriggerstu_deleteonstudentafterdeleteasupdateclasssetnumber=number-1fromclass,deletedwhereclass.clno=deleted.clno;12.createtriggerstu_updateonclassafterupdateasifupdate(monitor)if(selectmonitorfrominserted)notin(selectsnofromstudentwhereclno=(selectclnofromdeleted))beginprint'thereisnotthenewmonitorintheclass'rollbacktransactionend13.新建product表createt