數(shù)據(jù)庫實(shí)驗(yàn)內(nèi)容

1、精品好資料學(xué)習(xí)推薦數(shù)據(jù)庫實(shí)驗(yàn)內(nèi)容環(huán)境選擇：WINDOWS XP SP2+SQL SERVER 2005 實(shí)驗(yàn)一：安裝SQL SERVER 2005 EXPRESS + SQL Server Management Studio Express實(shí)驗(yàn)二：實(shí)驗(yàn)三：使用SQL語句實(shí)現(xiàn)視圖的更新，例如：UPDATE C_P_N SET pna=人民日?qǐng)?bào)-1 where pna=人民日?qǐng)?bào)將“人民日?qǐng)?bào)”修改成“人民日?qǐng)?bào)-1”實(shí)驗(yàn)四SELECT查詢操作1、基于“教學(xué)管理”數(shù)據(jù)庫jxgl，試用SQL的查詢語句表達(dá)下列查詢：(1)檢索年齡大于23歲的男學(xué)生的學(xué)號(hào)和姓名。SELECT Sno, Sname FROM

2、Student WHERE(Sage 23)AND(Ssex =男)(2)檢索至少選修一門課程的女學(xué)生姓名。select a.Sname from student as a ,sc as b where a.Ssex=女a(chǎn)nd a.Sno=b.Sno groupby a.Sname havingcount(b.Cno)=1(3)檢索王同學(xué)不學(xué)的課程的課程號(hào)。select Cno from SC where Sno in(select Sno from student where Sname like王%)(4)檢索至少選修兩門課程的學(xué)生學(xué)號(hào)。select Sno from sc groupby

3、 Sno havingcount(*)=2(5)檢索全部學(xué)生都選修的課程的課程號(hào)與課程名。select Cno,Cname from Course where Cno in(select Cno from sc groupby Cno)(6)檢索選修了所有3學(xué)分課程的學(xué)生學(xué)號(hào)。select Sno from sc where Cno in(select Cno from Course where Ccredit=3)2、基于“教學(xué)管理”數(shù)據(jù)庫jxgl，試用SQL的查詢語句表達(dá)下列查詢：(1)統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。SELECTCOUNT(DISTINCT Cno)FROM SC(2)求選修4

4、號(hào)課程的學(xué)生的平均年齡。SELECTAVG(a.Sage)from Student as a,Sc as b where a.Sno=b.Sno and b.Cno=4(3)求學(xué)分為3的每門課程的學(xué)生平均成績(jī)。SELECTAVG(a.Grade)from Sc as a,Course as b where a.Cno=b.Cno and b.Ccredit=3(4)統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)，超過3人的課程才統(tǒng)計(jì)。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列。SELECT Cno,Count(*)as 選修人數(shù)from sc groupby cnohaving

5、count(*)3 orderby 選修人數(shù)DESC,cno ASC(5)檢索學(xué)號(hào)比王非同學(xué)大，而年齡比他小的學(xué)生姓名。SELECT Sname from student whereSno(select sno from student where Sname=王非)andSage(Selectavg(Sage)from Student where Ssex=女) and Ssex=男(9)求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。SELECT Sname,Sage from Student whereSage(SelectMax(Sage)from Student where Ssex=女

6、) and Ssex=男(10) 檢索所有比“王華”年齡大的學(xué)生姓名、年齡和性別。SELECT Sname,Sage,Ssex from Student whereSage(Select Sage from Student where Sname=王華)(11) 檢索選修“2”課程的學(xué)生中成績(jī)最高的學(xué)生的學(xué)號(hào)。SELECT Sno from sc where Cno=2 and Grade=(selectMax(Grade)from sc where Cno=2)(12) 檢索學(xué)生姓名及其所選修課程的課程號(hào)和成績(jī)。SELECT a.Sname,b.Cno,b.Grade from studen

7、t as a,sc as b where a.Sno=b.Sno(13) 檢索選修4門以上課程的學(xué)生總成績(jī)(不統(tǒng)計(jì)不及格的課程)，并要求按總成績(jī)的降序排列出來。SELECTSUM(Grade)from sc where Grade=60 andcno in(select cno from sc groupby cno havingcount(*)4)orderbySUM(Grade)DESC實(shí)驗(yàn)五：更新操作命令實(shí)驗(yàn)內(nèi)容1、學(xué)生表Student、課程表Course、選課表SC的表結(jié)構(gòu)等信息同上實(shí)驗(yàn)4，請(qǐng)實(shí)踐以下命令式更新操作：（1）在學(xué)生表Student和學(xué)生選課表SC中分別添加如下兩表中的記錄

8、。INSERTINTO Student VALUES(98010,趙青江,18,男,CS);INSERTINTO Student VALUES(98011,張麗萍,19,女,CH);INSERTINTO Student VALUES(98012,陳景歡,20,男,IS);INSERTINTO Student VALUES(98013,陳婷婷,16,女,PH);INSERTINTO Student VALUES(98014,李軍,16,女,EH);INSERTINTO SC VALUES(98010,1,87);INSERTINTO SC VALUES(98010,2,NULL);INSERTI

9、NTO SC VALUES(98010,3,80);INSERTINTO SC VALUES(98010,4,87);INSERTINTO SC VALUES(98010,6,85);INSERTINTO SC VALUES(98011,1,52);INSERTINTO SC VALUES(98011,2,47);INSERTINTO SC VALUES(98011,3,53);INSERTINTO SC VALUES(98011,5,45);INSERTINTO SC VALUES(98012,1,84);INSERTINTO SC VALUES(98012,3,NULL);INSERTIN

10、TO SC VALUES(98012,4,67);INSERTINTO SC VALUES(98012,5,81);（2）備份Student表到TS中，并清空TS表。SELECT*INTO TS FROM studentTRUNCATETABLE TS（3）給IS系的學(xué)生開設(shè)7號(hào)課程，建立所有相應(yīng)的選課記錄，成績(jī)暫定為60分。INSERTINTO SC(Sno,Cno,Grade)SELECT Sno,7,60 from student where sdept=IS（4）把年齡小于等于16的女生記錄保存到表TS中。INSERTINTO TS SELECT Sno, Sname, Sage, S

11、sex, Sdept FROM Student where Ssex=女a(chǎn)nd Sage=60);（6）將學(xué)號(hào)為“98011”的學(xué)生姓名改為劉華，年齡增加1歲。UPDATE Student SET Sname=劉華,Sage=Sage+1 Where Sno=98011;（7）把選修了“數(shù)據(jù)庫系統(tǒng)”課程而成績(jī)不及格的學(xué)生的成績(jī)?nèi)臑榭罩担∟ULL）。UPDATE SC SET GRADE =NULL FROM Course,SC WHERE Cname =數(shù)據(jù)庫系統(tǒng)and Grade 60 and SC.Cno = Course.Cno;（8）將Student的前4位學(xué)生的年齡均增加1歲。UP

12、DATE Student SET Sage=Sage+1 FROM(SELECTTOP 4 *FROM Student ORDERBY SNO)AS A WHERE A.SNO=Student.SNO（9）學(xué)生王林在3號(hào)課程考試中作弊，該課成績(jī)改為空值（NULL）。UPDATE SC SET GRADE=NULLWHERE CNO =3AND王林=(SELECT SNAME FROM STUDENT WHERE STUDENT.SNO =SC.SNO)（10）把成績(jī)低于總平均成績(jī)的女同學(xué)成績(jī)提高5。UPDATE SC SET GRADE = GRADE*1.05 WHERE Grade(SEL

13、ECTAVG(GRADE)FROM SC)and Sno IN(SELECT SNO FROM Student WHERE Ssex=女)（11）在基本表SC中修改課程號(hào)為“2”號(hào)課程的成績(jī)，若成績(jī)小于等于80分時(shí)降低2，若成績(jī)大于80分時(shí)降低1(用兩個(gè)UPDATE語句實(shí)現(xiàn))。UPDATE SC SET GRADE=GRADE*0.98 WHERE Cno=2AND GRADE80;（12）利用“SELECT INTO ”命令來備份Student、SC、Course三表，備份表名自定。SELECT*INTO Student1 FROM Student;SELECT*INTO SC1 FROM

14、SC;SELECT*INTO Course1 FROM Course;（13）在基本表SC中刪除尚無成績(jī)的選課元組。DELETEFROM SC WHERE GRADE=NULL（14）把“錢橫”同學(xué)的選課情況全部刪去。DELETEFROM SC WHERE Sno=(SELECT Sno FROM Student WHERE Sname=錢橫)（15）能刪除學(xué)號(hào)為“98005”的學(xué)生記錄嗎？一定要?jiǎng)h除該記錄的話，該如何操作？給出操作命令。DELETEFROM Student WHERE SNO=98005（16）刪除姓“張”的學(xué)生記錄。DELETEFROM Student WHERE SNAME LIKE張%;（17）清空STUDENT與Co