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

1、數(shù)據(jù)庫(kù)實(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語(yǔ)句實(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ù)庫(kù)jxgl，試用SQL的查詢語(yǔ)句表達(dá)下列查詢： (1)檢索年齡大于23歲的男學(xué)生的學(xué)號(hào)和姓名。SELECT Sno,

2、 Sname FROM Student WHERE (Sage > 23) AND (Ssex = '男' )(2)檢索至少選修一門課程的女學(xué)生姓名。select a.Sname from student as a ,sc as b where a.Ssex= '女' and a.Sno=b.Sno group by a.Sname having count(b.Cno)>=1(3)檢索王同學(xué)不學(xué)的課程的課程號(hào)。select Cno from SC where Sno in (select Sno from student where Sname l

3、ike '王%')(4)檢索至少選修兩門課程的學(xué)生學(xué)號(hào)。select Sno from sc group by Sno having count(*)>=2(5)檢索全部學(xué)生都選修的課程的課程號(hào)與課程名。select Cno,Cname from Course where Cno in (select Cno from sc group by 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ù)庫(kù)jxgl，試

4、用SQL的查詢語(yǔ)句表達(dá)下列查詢：(1)統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。SELECT COUNT(DISTINCT Cno) FROM SC(2)求選修4號(hào)課程的學(xué)生的平均年齡。SELECT AVG(a.Sage) from Student as a,Sc as b where a.Sno=b.Sno and b.Cno=4(3)求學(xué)分為3的每門課程的學(xué)生平均成績(jī)。SELECT AVG(a.Grade) from Sc as a,Course as b where a.Cno=b.Cno and b.Ccredit=3(4)統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)，超過(guò)3人的課程才統(tǒng)計(jì)。要求輸出課程號(hào)和選修人數(shù)，查

5、詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列。SELECT Cno,Count(*)as 選修人數(shù)from sc group by cnohaving count(*)>3 order by 選修人數(shù)DESC,cno ASC(5)檢索學(xué)號(hào)比王非同學(xué)大，而年齡比他小的學(xué)生姓名。SELECT Sname from student where Sno>(select sno from student where Sname='王非') and Sage<(select Sage from student where Sname='王非')(6)

6、檢索姓名以王打頭的所有學(xué)生的姓名和年齡。SELECT Sname,Sage from student where sname like '王%'(7)在SC中檢索成績(jī)?yōu)榭罩档膶W(xué)生學(xué)號(hào)和課程號(hào)。SELECT Sno,Cno from Sc where Grade is NULL(8)求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。SELECT Sname,Sage from Student where Sage>(Select avg(Sage) from Student where Ssex='女') and Ssex='男'(9)求年齡大于所

7、有女同學(xué)年齡的男學(xué)生姓名和年齡。SELECT Sname,Sage from Student where Sage>(Select Max(Sage) from Student where Ssex='女') and Ssex='男'(10) 檢索所有比“王華”年齡大的學(xué)生姓名、年齡和性別。SELECT Sname,Sage,Ssex from Student where Sage>(Select Sage from Student where Sname='王華')(11) 檢索選修“2”課程的學(xué)生中成績(jī)最高的學(xué)生的學(xué)號(hào)。SELEC

8、T Sno from sc where Cno=2 and Grade=(select Max(Grade) from sc where Cno=2)(12) 檢索學(xué)生姓名及其所選修課程的課程號(hào)和成績(jī)。SELECT a.Sname,b.Cno,b.Grade from student as a,sc as b where a.Sno=b.Sno(13) 檢索選修4門以上課程的學(xué)生總成績(jī)(不統(tǒng)計(jì)不及格的課程)，并要求按總成績(jī)的降序排列出來(lái)。SELECT SUM(Grade) from sc where Grade>=60 and cno in (select cno from sc gro

9、up by cno having count(*)>4) order by SUM(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中分別添加如下兩表中的記錄。INSERT INTO Student VALUES('98010','趙青江',18,'男','CS');INSERT INTO Student VALUES('98011','張麗萍

10、9;,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

11、 SC VALUES('98010','1',87);INSERT INTO SC VALUES('98010','2',NULL);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',&

12、#39;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 S

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

14、女生記錄保存到表TS中。INSERT INTO TS SELECT Sno, Sname, Sage, Ssex, Sdept FROM Student where Ssex='女' and Sage<=16（5）在表Student中檢索每門課均不及格的學(xué)生學(xué)號(hào)、姓名、年齡、性別及所在系等信息，并把檢索到的信息存入TS表中。Insert into TS SELECT Sno,Sname,Sage,Ssex,Sdept TS from student Where NOT EXISTS ( Select * From SC Where Student.Sno=SC.Sno a

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

16、4位學(xué)生的年齡均增加1歲。UPDATE Student SET Sage=Sage+1 FROM (SELECT TOP 4 * FROM Student ORDER BY SNO) AS A WHERE A.SNO=Student.SNO（9）學(xué)生王林在3號(hào)課程考試中作弊，該課成績(jī)改為空值（NULL）。UPDATE SC SET GRADE=NULL WHERE CNO = '3' AND '王林' = (SELECT SNAME FROM STUDENT WHERE STUDENT.SNO =SC.SNO)（10）把成績(jī)低于總平均成績(jī)的女同學(xué)成績(jī)提高5。UP

17、DATE SC SET GRADE = GRADE*1.05 WHERE Grade<(SELECT AVG(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語(yǔ)句實(shí)現(xiàn))。UPDATE SC SET GRADE=GRADE*0.98 WHERE Cno='2' AND GRADE<=80;UPDATE SC SET GRADE=GRADE*0

18、.99 WHERE Cno='2' AND GRADE>80;（12）利用“SELECT INTO ”命令來(lái)備份Student、SC、Course三表，備份表名自定。SELECT * INTO Student1 FROM Student;SELECT * INTO SC1 FROM SC;SELECT * INTO Course1 FROM Course;（13）在基本表SC中刪除尚無(wú)成績(jī)的選課元組。DELETE FROM SC WHERE GRADE='NULL'（14）把“錢橫”同學(xué)的選課情況全部刪去。DELETE FROM SC WHERE Sno=(SELECT Sno FROM Student WHERE Sname='錢橫')（15）能刪除學(xué)號(hào)為“98005”的學(xué)生記錄嗎？一定要?jiǎng)h除該記錄的話，該如何操作？給出操作命令。DELETE FROM Student WHERE SNO='98005'（16）刪除姓“張”的學(xué)生記錄。DELE