数据库实验内容

1、数据库实验内容环境选择：WINDOWS XP SP2+SQL SERVER 2005 实验一：安装SQL SERVER 2005 EXPRESS + SQL Server Management Studio Express实验二：实验三：使用SQL语句实现视图的更新，例如：UPDATE C_P_N SET pna='人民日报-1' where pna='人民日报'将“人民日报”修改成“人民日报-1”实验四SELECT查询操作1、基于“教学管理”数据库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、基于“教学管理”数据库jxgl，试

4、用SQL的查询语句表达下列查询：(1)统计有学生选修的课程门数。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)统计每门课程的学生选修人数，超过3人的课程才统计。要求输出课程号和选修人数，查

5、询结果按人数降序排列，若人数相同，按课程号升序排列。SELECT Cno,Count(*)as 选修人数from sc group by cnohaving count(*)>3 order by 选修人数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中检索成绩为空值的学生学号和课程号。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门以上课程的学生总成绩(不统计不及格的课程)，并要求按总成绩的降序排列出来。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实验五：更新操作命令实验内容1、学生表Student、课程表Course、选课表SC的表结构等信息同上实验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系的学生开设7号课程，建立所有相应的选课记录，成绩暂定为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）把选修了“数据库系统”课程而成绩不及格的学生的成绩全改为空值（NULL）。UPDATE SC SET GRADE = NULL FROM Course,SC WHERE Cname = '数据库系统' 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语句实现)。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