from S
WHERE not exists
(select * from SC
WHERE S.Sno=Sno);
16、查询选修了3门以上课程的学生学号;
select Sno from SC group by Sno
having count(*)>3;
17、查询选修课程成绩至少有一门在80分以上的学生学号;
select distinct S.Sno from S,SC
WHERE S.Sno=SC.Sno and Grade>80;
18、查询选修课程平均成绩在80分以上的学生学号;
select distinct S.Sno from S,SC
where SC.Sno=S.Sno and SC.Sno in
(select Sno from SC GROUP BY Sno
having avg(Grade)>80);
19、(选做)SC 20、(选做)找出平均成绩在85分以上的学生,结果按系分组,并按平均成绩的升序排列。
实验四 视图的定义、使用实验
实验四成绩:
一、实验目的
熟悉和掌握对数据表中视图的定义操作和SQL命令的使用; 熟悉和掌握对数据表中视图的查询操作和SQL命令的使用;
熟悉和掌握对数据表中视图的更新操作和SQL命令的使用,并注意视图更新与基本表更新的区别与联系; 学习灵活熟练的进行视图的操作,认识视图的作用;
二、实验方法及步骤 1.视图的定义 2.视图的查询 3.视图的修改 三、实验内容:
以students,courses,sc表为基础完成以下视图定义(使用SQL和企业管理器两种方法)及使用 1.定义“IS”系学生基本情况视图V_IS 并查询结果;
2.将students,courses,sc表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_S_C_G并查询结果;
3.将各系学生人数,平均年龄定义为视图V_NUM_AVG并查询结果; 4.定义一个反映学生出生年份的视图V_YEAR并查询结果;
5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果; 6.建立视图V_AVG平均成绩为90分以上的学生学号;
7.通过视图V_IS,分别将学号为“95001”和“95004”的学生姓名更改为“S0001”,”S0004”并查询结果;
8.通过视图V_IS,新增加一个学生记录 (’S0012’,’YAN XI’,19,’IS’),并查询结果; 9.通过视图V_IS,新增加一个学生记录 (’S0013’,’YAN XI’,19,’MA’),并查询结果; 10.通过视图V_IS,删除学号为“S0012”和“95003”的学生信息,并查询结果;
四、实验结果
以students,courses,sc表为基础完成以下视图定义(使用SQL和企业管理器两种方法)及使用 1.定义“IS”系学生基本情况视图V_IS 并查询结果;
create view V_IS as select * from S
where Sdept='IS';
2.将students,courses,sc表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_S_C_G并查询结果;
create view V_S_C_G as
select S.Sno,Sname,C.Cno,Cname,Grade from S,SC,C
where S.Sno=SC.Sno and SC.Cno=C.Cno;
3.将各系学生人数,平均年龄定义为视图V_NUM_AVG并查询结果;
create view V_NUM_AVG(Sdept,Sdept_count,Sage_avg) as
select Sdept,count(Sdept),avg(Sage) from S
group by Sdept;
4.定义一个反映学生出生年份的视图V_YEAR并查询结果;
create view V_YEAR(Sname,Sbirthday) as
select Sname,2013-Sage from S;
5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;
create view V_AVG_S_G(Sno,Cno_count,Grade_avg) as
select Sno,count(Cno),avg(Grade) from SC group by Sno;
6.建立视图V_AVG平均成绩为90分以上的学生学号;
create view V_AVG(Grade_over90_Sno) as
select S.Sno from S,SC
where SC.Sno=S.Sno and SC.Sno in
(select Sno from SC group by Sno
having avg(Grade)>90);
7.通过视图V_IS,分别将学号为“95001”和“95004”的学生姓名更改为“S0001”,”S0004”并查询结果;
update V_IS
set Sname='王明' where Sno='95001';
update V_IS
set Sname='王强' where Sno='95002';
8.通过视图V_IS,新增加一个学生记录 (’S0012’,’YAN XI’,19,’IS’),并查询结果;
insert
into V_IS(Sno,Sname,Sage,Sdept) VALUES ('95005','宋明','19','IS');
9.通过视图V_IS,新增加一个学生记录 (’S0013’,’YAN XI’,19,’MA’),并查询结果;
insert
into V_IS(Sno,Sname,Sage,Sdept) VALUES ('95006','王芳','19','MA');
10.通过视图V_IS,删除学号为“S0012”和“95003”的学生信息,并查询结果;
delete from V_IS
where Sno='95005' or Sno='95006';