數(shù)據(jù)庫原理及應用實驗指導——更新操作練習題.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系的學生開設(shè)7號課程，建立所有相應的選課記錄，成績暫定為60分。4、把年齡小于等于16的女生記錄保存到表TS中。5、在表Student中檢索每門課均不及格的學生學號、姓名、年齡、性別及所在系等信息，并把檢索到的信息存入TS表中。6、將學號為“98011”的學生姓名改為劉華，年齡增加1歲。7、把選修了“數(shù)據(jù)庫系統(tǒng)”課程而成績不及格的學生的成績?nèi)臑榭罩担∟ULL）。8、將Student的前4位學生的年齡均增加1歲。9、學生王林在3號課程考試中作弊，該課成績改為空值（NULL）。10、把成績低于總平均成績的女同學成績提高5。11、在基本表SC中修改課程號為“2”號課程的成績，若成績小于等于80分時降低2，若成績大于80分時降低1(用兩個UPDATE語句實現(xiàn))。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系的學生開設(shè)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、把選修了“數(shù)據(jù)庫系統(tǒng)”課程而成績不及格的學生的成績?nèi)臑榭罩担∟ULL）。UPDATE SC SET GRADE=NULL WHERE GRADE60 AND CNO IN (SELECT CNO FROM COURSE WHERE CNAME=數(shù)據(jù)庫系統(tǒng))UPDATE SC SET GRADE=NULL FROM COURSEWHERE SC.GRADE60 AND SCX.CNO=COURSE.CNO AND CNAME=數(shù)據(jù)庫系統(tǒng)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語句實現(xiàn))。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