数据库原理及应用实验指导——更新操作练习题.doc

练习题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”的学生姓名改为刘华，年龄增加1岁。7、把选修了“数据库系统”课程而成绩不及格的学生的成绩全改为空值（NULL）。8、将Student的前4位学生的年龄均增加1岁。9、学生王林在3号课程考试中作弊，该课成绩改为空值（NULL）。10、把成绩低于总平均成绩的女同学成绩提高5。11、在基本表SC中修改课程号为“2”号课程的成绩，若成绩小于等于80分时降低2，若成绩大于80分时降低1(用两个UPDATE语句实现)。12、利用“SELECT INTO ”命令来备份Student、SC、Course三表，备份表名自定。13、在基本表SC中删除尚无成绩的选课元组。14、把“钱横”同学的选课情况全部删去。15、能删除学号为“98005”的学生记录吗？一定要删除该记录的话，该如何操作？给出操作命令。16、删除姓“张”的学生记录。17、清空STUDENT与Course两表。18、如何又从备份表中恢复所有的三表。参考答案：1、在学生表Student和学生选课表SC中分别添加如下两表中的记录。Insert into Student values(98010,赵青江,18,男,CS)Insert into Student values(98011,张丽萍,19, 女,CH)Insert into Student values(98012,陈景欢,20, 男,IS)Insert into Student values(98013,陈婷婷,16, 女,PH)Insert into Student values(98014,李 军,16, 女,EH)Insert into SC values(98010, 1,87)Insert into SC values(98010, 2,null)或Insert into SC(sno,cno) values(98010, 2)Insert into SC values(98010, 3,80)Insert into SC values(98010, 4,87)Insert into SC values(98010, 6,85)Insert into SC values(98011, 1,52)Insert into SC values(98011, 2,47)Insert into SC values(98011, 3,53)Insert into SC values(98011, 5,45)Insert into SC values(98012, 1,84)Insert into SC values(98012, 3,null)或Insert into SC(sno,cno) values(98012, 3)Insert into SC values(98012, 4,67)Insert into SC values(98012, 5,81)2、备份Student表到TS中，并清空TS表。 Select * into TS from Student Delete from TS 或 truncate table TS3、给IS系的学生开设7号课程，建立所有相应的选课记录，成绩暂定为60分。Insert into sc select sno, 7,60 from student where sdept=IS4、把年龄小于等于16的女生记录保存到表TS中。INSERT INTO TS SELECT * FROM STUDENT WHERE SAGE=165、在表Student中检索每门课均不及格的学生学号、姓名、年龄、性别及所在系等信息，并把检索到的信息存入TS表中。INSERT INTO TS SELECT * FROM STUDENT WHERE SNO IN (SELECT SNO FROM SC GROUP BY SNO HAVING MAX(GRADE) ALL (SELECT GRADE FROM SC WHERE SC.SNO=STUDENT.SNO)INSERT INTO TS SELECT * FROM STUDENT WHERE 60 (SELECT MAX(GRADE) FROM SC WHERE SC.SNO=STUDENT.SNO)INSERT INTO TS SELECT * FROM STUDENT WHERE NOT EXISTS (SELECT GRADE,SNO FROM SC WHERE SC.SNO=STUDENT.SNO AND GRADE=60)INSERT INTO TS SELECT * FROM STUDENT WHERE SNO IN (SELECT SNO FROM SC WHERE SNO NOT IN (SELECT SNO FROM SC WHERE GRADE59)- 如下有错，意为所有课程均学了，并均不及格。INSERT INTO TS SELECT * FROM STUDENT WHERE NOT EXISTS(SELECT * FROM COURSE WHERE NOT EXISTS (SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO AND GRADE=60)6、将学号为“98011”的学生姓名改为刘华，年龄增加1岁。UPDATE STUDENT SET SNAME=刘华,SAGE=SAGE+1 WHERE SNO=980117、把选修了“数据库系统”课程而成绩不及格的学生的成绩全改为空值（NULL）。UPDATE SC SET GRADE=NULL WHERE GRADE60 AND CNO IN (SELECT CNO FROM COURSE WHERE CNAME=数据库系统)UPDATE SC SET GRADE=NULL FROM COURSEWHERE SC.GRADE60 AND SCX.CNO=COURSE.CNO AND CNAME=数据库系统8、将Student的前4位学生的年龄均增加1岁。Update student set sage=sage+1 where sno in (select top 4 sno from student order by sno)9、学生王林在3号课程考试中作弊，该课成绩改为空值（NULL）。UPDATE SC SET GRADE=NULL WHERE cno=3 AND SNO IN (SELECT SNO FROM STUDENT WHERE SNAME=王林)10、把成绩低于总平均成绩的女同学成绩提高5。 SELECT * INTO TSC FROM SCdelete from scinsert into sc select * from tscdrop table tscselect * into tsc from scupdate sc set sc.grade=1.05*sc.gradefrom sc,(select * from student where ssex=男) as stnwhere sc.sno= stn.sno AND grade(select avg(grade) from sc)select * from scupdate sc set sc.grade=1.05*sc.gradefrom sc,studentwhere ssex=男 AND grade(select avg(grade) from sc) and sc.sno=student.snoselect * from scupdate sc set sc.grade=1.05*sc.gradefrom (select * from student where ssex=男) as stnwhere sc.sno= stn.sno AND grade(select avg(grade) from sc)select * from scupdate sc set sc.grade=1.05*sc.gradewhere sc.sno in (select sno from student where ssex=男) AND grade(select avg(grade) from sc)select * from sc11、在基本表SC中修改课程号为“2”号课程的成绩，若成绩小于等于80分时降低2，若成绩大于80分时降低1(用两个UPDATE语句实现)。UPDATE SC SET GRADE=GRADE*0.98 WHERE CNO=2 AND GRADE80- 使用一条命令完成UPDATE SC SET GRADE=CASE WHEN GRADE80 THEN GRADE*0.98ENDWHERE CNO=212、利用“SELECT INTO ”命令来备份Student、SC、Course三表，备份表名自定。SELECT * INTO TSTUDENT FROM STUDENTSELECT * INTO TSC FROM SCSELECT * INTO TCOURSE FROM COURSE13、在基本表SC中删除尚无成绩的选课元组。DELETE FROM SC WHERE GRADE IS NULL14、把“钱横”同学的选课情况全部删去。DELETE FROM SC WHERE SNO IN (SELECT SNO FROM STUDENT WHERE SNAME=钱横)15、能删除学号为“98005”的学生记录吗？一定要删除该记录的话，该如何操作？给出操作命令。SC表有“98005”学生选课记录时，不能删除；一定要删除得先删除SC中的选课记录。Delete from sc where sno=98005Delete from student where sno=9800516、删除姓“张”的学生记录。Delete from sc where sno in (select sno from student where sname like 张%)Delete from student where sname like 张%17、清空STUDENT与Course两表。Delete from scDelete from studentDelete from course18、如何又从备份表中恢复所有的三表。Insert into STUDENT select * from TSTUDENTInsert into COURSE select * from TCOURSEInsert into SC select