数据库原理及应用实验指导★-更新操作练习题

./练习题1、在学生表Student和学生选课表SC中分别添加如下两表中的记录。学生表Student学生选课表SC学号<Sno><Sname>年龄<Sage>性别<Ssex>所在系<Sdept>98010青江18男CS98011丽萍19女CH98012景欢20男IS98013婷婷16女PH98014军16女EH学号<Sno>课程号<Cno>成绩<Grade>9801018798010298010380980104879801068598011152980112479801135398011545980121849801239801246798012581课程表Course课程号<Cno>课程名<Cname>先修课号<Cpno>学分<Ccredit>2、备份Student表到TS中,并清空TS表。3、给IS系的学生开设7号课程,建立所有相应的选课记录,成绩暂定为60分。4、把年龄小于等于16的女生记录保存到表TS中。5、在表Student中检索每门课均不及格的学生学号、、年龄、性别及所在系等信息,并把检索到的信息存入TS表中。6、将学号为"98011”7、把选修了"数据库系统"课程而成绩不及格的学生的成绩全改为空值〔NULL。8、将Student的前4位学生的年龄均增加1岁。9、学生王林在3号课程考试中作弊,该课成绩改为空值〔NULL。10、把成绩低于总平均成绩的女同学成绩提高5％。11、在基本表SC中修改课程号为"2”12、利用"SELECTINTO……"命令来备份Student、SC、Course三表,备份表名自定。13、在基本表SC中删除尚无成绩的选课元组。14、把"钱横"同学的选课情况全部删去。15、能删除学号为"98005”16、删除姓""的学生记录。17、清空STUDENT与Course两表。18、如何又从备份表中恢复所有的三表。参考答案：1、在学生表Student和学生选课表SC中分别添加如下两表中的记录。InsertintoStudentvalues<'98010','青江',18,'男','CS'>InsertintoStudentvalues<'98011','丽萍',19,'女','CH'>InsertintoStudentvalues<'98012','景欢',20,'男','IS'>InsertintoStudentvalues<'98013','婷婷',16,'女','PH'>InsertintoStudentvalues<'98014','军',16,'女','EH'>InsertintoSCvalues<'98010','1',87>InsertintoSCvalues<'98010','2',null>或InsertintoSC<sno,cno>values<'98010','2'>InsertintoSCvalues<'98010','3',80>InsertintoSCvalues<'98010','4',87>InsertintoSCvalues<'98010','6',85>InsertintoSCvalues<'98011','1',52>InsertintoSCvalues<'98011','2',47>InsertintoSCvalues<'98011','3',53>InsertintoSCvalues<'98011','5',45>InsertintoSCvalues<'98012','1',84>InsertintoSCvalues<'98012','3',null>或InsertintoSC<sno,cno>values<'98012','3'>InsertintoSCvalues<'98012','4',67>InsertintoSCvalues<'98012','5',81>2、备份Student表到TS中,并清空TS表。Select*intoTSfromStudentDeletefromTS或truncatetableTS3、给IS系的学生开设7号课程,建立所有相应的选课记录,成绩暂定为60分。Insertintoscselectsno,'7',60fromstudentwheresdept='IS'4、把年龄小于等于16的女生记录保存到表TS中。INSERTINTOTSSELECT*FROMSTUDENTWHERESAGE<=165、在表Student中检索每门课均不及格的学生学号、、年龄、性别及所在系等信息,并把检索到的信息存入TS表中。INSERTINTOTSSELECT*FROMSTUDENTWHERESNOIN<SELECTSNOFROMSCGROUPBYSNOHAVINGMAX<GRADE><60>INSERTINTOTSSELECT*FROMSTUDENTWHERE60>ALL<SELECTGRADEFROMSCWHERESC.SNO=STUDENT.SNO>INSERTINTOTSSELECT*FROMSTUDENTWHERE60><SELECTMAX<GRADE>FROMSCWHERESC.SNO=STUDENT.SNO>INSERTINTOTSSELECT*FROMSTUDENTWHERENOTEXISTS<SELECTGRADE,SNOFROMSCWHERESC.SNO=STUDENT.SNOANDGRADE>=60>INSERTINTOTSSELECT*FROMSTUDENTWHERESNOIN<SELECTSNOFROMSCWHERESNONOTIN<SELECTSNOFROMSCWHEREGRADE>59>>--如下有错,意为所有课程均学了,并均不及格。INSERTINTOTSSELECT*FROMSTUDENTWHERENOTEXISTS<SELECT*FROMCOURSEWHERENOTEXISTS<SELECT*FROMSCWHERESNO=STUDENT.SNOANDCNO=COURSE.CNOANDGRADE<=60>>6、将学号为"98011”UPDATESTUDENTSETSNAME='华',SAGE=SAGE+1WHERESNO='98011'7、把选修了"数据库系统"课程而成绩不及格的学生的成绩全改为空值〔NULL。UPDATESCSETGRADE=NULLWHEREGRADE<60ANDCNOIN<SELECTCNOFROMCOURSEWHERECNAME='数据库系统'>UPDATESCSETGRADE=NULLFROMCOURSEWHERESC.GRADE<60ANDSCX.CNO=COURSE.CNOANDCNAME='数据库系统'8、将Student的前4位学生的年龄均增加1岁。Updatestudentsetsage=sage+1wheresnoin<selecttop4snofromstudent[orderbysno]>9、学生王林在3号课程考试中作弊,该课成绩改为空值〔NULL。UPDATESCSETGRADE=NULLWHEREo='3'ANDSNOIN<SELECTSNOFROMSTUDENTWHERESNAME='王林'>10、把成绩低于总平均成绩的女同学成绩提高5％。SELECT*INTOTSCFROMSCdeletefromscinsertintoscselect*fromtscdroptabletscselect*intotscfromscupdatescsetsc.grade=1.05*sc.gradefromsc,<select*fromstudentwheressex='男'>asstnwheresc.sno=stn.snoANDgrade<<selectavg<grade>fromsc>select*fromscupdatescsetsc.grade=1.05*sc.gradefromsc,studentwheressex='男'ANDgrade<<selectavg<grade>fromsc>andsc.sno=student.snoselect*fromscupdatescsetsc.grade=1.05*sc.gradefrom<select*fromstudentwheressex='男'>asstnwheresc.sno=stn.snoANDgrade<<selectavg<grade>fromsc>select*fromscupdatescsetsc.grade=1.05*sc.gradewheresc.snoin<selectsnofromstudentwheressex='男'>ANDgrade<<selectavg<grade>fromsc>select*fromsc11、在基本表SC中修改课程号为"2”UPDATESCSETGRADE=GRADE*0.98WHERECNO='2'ANDGRADE<=80UPDATESCSETGRADE=GRADE*0.99WHERECNO='2'ANDGRADE>80--使用一条命令完成UPDATESCSETGRADE=CASEWHENGRADE<=80THENGRADE*0.98WHENGRADE>80THENGRADE*0.98ENDWHERECNO='2'12、利用"SELECTINTO……"命令来备份Student、SC、Course三表,备份表名自定。SELECT*INTOTSTUDENTFROMSTUDENTSELECT*INTOTSCFROMSCSELECT*INTOTCOURSEFROMCOURSE13、在基本表SC中删除尚无成绩的选课元组。DELETEFROMSCWHEREGRADEISNULL14、把"钱横"同学的选课情况全部删去。DELETEFROMSCWHERESNOIN<SELECTSNOFROMSTUDENTWHERESNAME='钱横'>15、能删除学号为"98005”SC表有"98005”Deletefromscwheresno='98005'Deletefromstudentwheresno='98005'16、删除姓""的学生记录。Deletefromscwheresnoin<selectsnofromstudentwheresnamelike'%'>Deletefromstudentwheresnamelike'%'17、清空STUDENT与Course两表。DeletefromscDeletefromstudentDeletefromcourse18、如何又从备份表中恢复所有的三表。InsertintoSTUDENTselect*fromTSTUDENTInsertintoCOURSEselect*fromTCOURSEInsertintoS