數(shù)據(jù)庫(kù)原理及應(yīng)用實(shí)驗(yàn)指導(dǎo)★---更新操作練習(xí)題

1、練習(xí)題1、在學(xué)生表Student和學(xué)生選課表SC中分別添加如下兩表中的記錄。學(xué)生表Student 學(xué)生選課表SC學(xué)號(hào)(Sno)姓名(Sname)年齡(Sage)性別(Ssex)所在系(Sdept)98010趙青江18男CS98011張麗萍19女CH98012陳景歡20男IS98013陳婷婷16女PH98014李 軍16女EH學(xué)號(hào)(Sno)課程號(hào)(Cno)成績(jī)(Grade)9801018798010298010380980104879801068598011152980112479801135398011545980121849801239801246798012581課程表Course課程號(hào)(

2、Cno)課程名(Cname)先修課號(hào)(Cpno)學(xué)分(Ccredit)2、備份Student表到TS中，并清空TS表。3、給IS系的學(xué)生開(kāi)設(shè)7號(hào)課程，建立所有相應(yīng)的選課記錄，成績(jī)暫定為60分。4、把年齡小于等于16的女生記錄保存到表TS中。5、在表Student中檢索每門(mén)課均不及格的學(xué)生學(xué)號(hào)、姓名、年齡、性別及所在系等信息，并把檢索到的信息存入TS表中。6、將學(xué)號(hào)為“98011”的學(xué)生姓名改為劉華，年齡增加1歲。7、把選修了“數(shù)據(jù)庫(kù)系統(tǒng)”課程而成績(jī)不及格的學(xué)生的成績(jī)?nèi)臑榭罩担∟ULL）。8、將Student的前4位學(xué)生的年齡均增加1歲。9、學(xué)生王林在3號(hào)課程考試中作弊，該課成績(jī)改為空值（NU

3、LL）。10、把成績(jī)低于總平均成績(jī)的女同學(xué)成績(jī)提高5。11、在基本表SC中修改課程號(hào)為“2”號(hào)課程的成績(jī)，若成績(jī)小于等于80分時(shí)降低2，若成績(jī)大于80分時(shí)降低1(用兩個(gè)UPDATE語(yǔ)句實(shí)現(xiàn))。12、利用“SELECT INTO ”命令來(lái)備份Student、SC、Course三表，備份表名自定。13、在基本表SC中刪除尚無(wú)成績(jī)的選課元組。14、把“錢(qián)橫”同學(xué)的選課情況全部刪去。15、能刪除學(xué)號(hào)為“98005”的學(xué)生記錄嗎？一定要?jiǎng)h除該記錄的話，該如何操作？給出操作命令。16、刪除姓“張”的學(xué)生記錄。17、清空STUDENT與Course兩表。18、如何又從備份表中恢復(fù)所有的三表。參考答案：1、在

4、學(xué)生表Student和學(xué)生選課表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)Inser

5、t 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(98

6、011, 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系的學(xué)生開(kāi)設(shè)7號(hào)課程，建立所有相應(yīng)

7、的選課記錄，成績(jī)暫定為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中檢索每門(mén)課均不及格的學(xué)生學(xué)號(hào)、姓名、年齡、性別及所在系等信息，并把檢索到的信息存入TS表中。INSERT INTO TS SELECT * FROM STUDENT WHERE SNO IN (SELECT SNO FROM SC GROUP BY SNO HAVING MAX(

8、GRADE)60)6、將學(xué)號(hào)為“98011”的學(xué)生姓名改為劉華，年齡增加1歲。UPDATE STUDENT SET SNAME=劉華,SAGE=SAGE+1 WHERE SNO=980117、把選修了“數(shù)據(jù)庫(kù)系統(tǒng)”課程而成績(jī)不及格的學(xué)生的成績(jī)?nèi)臑榭罩担∟ULL）。UPDATE SC SET GRADE=NULL WHERE GRADE60 AND CNO IN (SELECT CNO FROM COURSE WHERE CNAME=數(shù)據(jù)庫(kù)系統(tǒng))UPDATE SC SET GRADE=NULL FROM COURSEWHERE SC.GRADE60 AND SCX.CNO=COURSE.CNO

9、 AND CNAME=數(shù)據(jù)庫(kù)系統(tǒng)8、將Student的前4位學(xué)生的年齡均增加1歲。Update student set sage=sage+1 where sno in (select top 4 sno from student order by sno)9、學(xué)生王林在3號(hào)課程考試中作弊，該課成績(jī)改為空值（NULL）。UPDATE SC SET GRADE=NULL WHERE cno=3 AND SNO IN (SELECT SNO FROM STUDENT WHERE SNAME=王林)10、把成績(jī)低于總平均成績(jī)的女同學(xué)成績(jī)提高5。 SELECT * INTO TSC FROM SCde

10、lete 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

11、 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

12、 sno from student where ssex=男) AND grade(select avg(grade) from sc)select * from sc11、在基本表SC中修改課程號(hào)為“2”號(hào)課程的成績(jī)，若成績(jī)小于等于80分時(shí)降低2，若成績(jī)大于80分時(shí)降低1(用兩個(gè)UPDATE語(yǔ)句實(shí)現(xiàn))。UPDATE SC SET GRADE=GRADE*0.98 WHERE CNO=2 AND GRADE8012、利用“SELECT INTO ”命令來(lái)備份Student、SC、Course三表，備份表名自定。SELECT * INTO TSTUDENT FROM STUDENTSELECT

13、* INTO TSC FROM SCSELECT * INTO TCOURSE FROM COURSE13、在基本表SC中刪除尚無(wú)成績(jī)的選課元組。DELETE FROM SC WHERE GRADE IS NULL14、把“錢(qián)橫”同學(xué)的選課情況全部刪去。DELETE FROM SC WHERE SNO IN (SELECT SNO FROM STUDENT WHERE SNAME=錢(qián)橫)15、能刪除學(xué)號(hào)為“98005”的學(xué)生記錄嗎？一定要?jiǎng)h除該記錄的話，該如何操作？給出操作命令。SC表有“98005”學(xué)生選課記錄時(shí)，不能刪除；一定要?jiǎng)h除得先刪除SC中的選課記錄。Delete from sc w

14、here sno=98005Delete from student where sno=9800516、刪除姓“張”的學(xué)生記錄。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、如何又從備份表中恢復(fù)所有的三表。Insert into STUDENT select * from TSTUDENTInsert into COURSE select * from TCOURSEInsert into SC select * from TSC學(xué)生表Student 學(xué)生選課表SC學(xué)號(hào)(S