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

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

2、 Sname FROM Student WHERE (Sage > 23) AND (Ssex = '男' )(2)檢索至少選修一門課程的女學生姓名。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)檢索王同學不學的課程的課程號。select Cno from SC where Sno in (select Sno from student where Sname l

3、ike '王%')(4)檢索至少選修兩門課程的學生學號。select Sno from sc group by Sno having count(*)>=2(5)檢索全部學生都選修的課程的課程號與課程名。select Cno,Cname from Course where Cno in (select Cno from sc group by Cno)(6)檢索選修了所有3學分課程的學生學號。select Sno from sc where Cno in (select Cno from Course where Ccredit=3)2、基于“教學管理”數(shù)據(jù)庫jxgl，試

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

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

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

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

8、T Sno from sc where Cno=2 and Grade=(select Max(Grade) from sc where Cno=2)(12) 檢索學生姓名及其所選修課程的課程號和成績。SELECT a.Sname,b.Cno,b.Grade from student as a,sc as b where a.Sno=b.Sno(13) 檢索選修4門以上課程的學生總成績(不統(tǒng)計不及格的課程)，并要求按總成績的降序排列出來。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實驗五：更新操作命令實驗內(nèi)容1、學生表Student、課程表Course、選課表SC的表結(jié)構(gòu)等信息同上實驗4，請實踐以下命令式更新操作：（1）在學生表Student和學生選課表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系的學生開設(shè)7號課程，建立所有相應(yīng)的選課記錄，成績暫定為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中檢索每門課均不及格的學生學號、姓名、年齡、性別及所在系等信息，并把檢索到的信息存入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）將學號為“98011”的學生姓名改為'劉華'，年齡增加1歲。UPDATE Student SET Sname='劉華',Sage=Sage+1 Where Sno='98011'（7）把選修了“數(shù)據(jù)庫系統(tǒng)”課程而成績不及格的學生的成績?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的前

16、4位學生的年齡均增加1歲。UPDATE Student SET Sage=Sage+1 FROM (SELECT TOP 4 * FROM Student ORDER BY SNO) AS A WHERE A.SNO=Student.SNO（9）學生王林在3號課程考試中作弊，該課成績改為空值（NULL）。UPDATE SC SET GRADE=NULL WHERE CNO = '3' AND '王林' = (SELECT SNAME FROM STUDENT WHERE STUDENT.SNO =SC.SNO)（10）把成績低于總平均成績的女同學成績提高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中修改課程號為“2”號課程的成績，若成績小于等于80分時降低2，若成績大于80分時降低1(用兩個UPDATE語句實現(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 ”命令來備份Student、SC、Course三表，備份表名自定。SELECT * INTO Student1 FROM Student;SELECT * INTO SC1 FROM SC;SELECT * INTO Course1 FROM Course;（13）在基本表SC中刪除尚無成績的選課元組。DELETE FROM SC WHERE GRADE='NULL'（14）把“錢橫”同學的選課情況全部刪去。DELETE FROM SC WHERE Sno=(SELECT Sno FROM Student WHERE Sname='錢橫')（15）能刪除學號為“98005”的學生記錄嗎？一定要刪除該記錄的話，該如何操作？給出操作命令。DELETE FROM Student WHERE SNO='98005'（16）刪除姓“張”的學生記錄。DELE