SQL语言增、删、改、查功能练习

一、数据库SQL语言增、删、改、查功能

1．查询选了1号课且选了2号课的学生的班号、学号
2. 查询选了1号课但不选2号课的学生的班号、学号
3．查询1班平均分在85分以上的同学班号、学号、姓名、性别、系、各科课程号及成绩
4．查询至少选了1班2号同学所选课的所有班号、学号及同学姓名
5． 查询不选1号课的学生班号及学号
6． 查询选2号课的学生名字及相应2号课成绩，按成绩从高到低排序
7． 统计学生选修课程的班号、学号及总学分
8． 统计1班选修3号课的学号及平均分
9． 把个人信息及选课信息插入到Student和SC 表及新增加一门“无机化学”课程信息
10. 删除选修3号课的所有选课信息并显示删除后的结果
11. 把选修1号课的所有男同学年龄增加1岁并显示最终学生Student信息
12. 把每个选课人的学号、班号及平均成绩插入到一个新表中。

二、视图SQL语言功能

1 使用企业管理器创建视图：在ST库中以“student”表为基础，建立信息系学生的视图V_ISStudent
2 使用SQL语句创建视图：
① 建立一个每个学生的学号、班号、姓名、选修的课名及成绩的视图S_C_GRADE；
② 建立信息系建立信息系选修了1号课程且成绩在90分以上的学生的视图V_IS_Score
③ 将各系学生人数，平均年龄定义为视图V_NUM_AVG。
3 查询以上所建的视图结果
4 查询选修了1号课程的信息系学生
5 在信息系学生的视图中找出年龄小于20岁的学生
6 将信息系学生视图V_ISStudent中学号一班2号的学生姓名改为“刘辰”
7 用SQL语句删除视图S_C_GRADE

三、源程序代码和实验结果

E-R图：

1.1查询选了1号课且选了2号课的学生的班号、学号

select distinct SCX.sno,SCX.sclass
from SC SCX
where exists ( select *
				from SC SCY
				where SCX.sno=SCY.sno and SCX.sclass=SCY.sclass and SCY.cno=1)
 and exists( select * 
			from SC SCZ 
			where SCX.sno=SCZ.sno and SCX.sclass=SCZ.sclass	and SCZ.cno=2);

1.2查询选了1号课但不选2号课的学生的班号、学号

select SCX.sno,SCX.sclass
from SC SCX
where exists ( select *
				from SC SCY
				where SCX.sno=SCY.sno and SCX.sclass=SCY.sclass and SCY.cno=1)
 and not exists( select * 
			from SC SCZ 
			where SCX.sno=SCZ.sno and SCX.sclass=SCZ.sclass	and SCZ.cno=2);

1.3查询1班平均分在85分以上的同学班号、学号、姓名、性别、系、各科课程号及成绩

select SC.sclass,SC.sno,sname,ssex,Sdept,cno,grade
from SC,Student
where SC.sclass=Student.sclass and SC.sno=Student.sno and SC.sclass=1 and SC.sno in
(select sno from SC where sclass=1 group by sno having avg(grade)>85);

1.4查询至少选了1班2号同学所选课的所有班号、学号及同学姓名

select distinct SCX.sclass,SCX.sno,sname
from Student,SC SCX
where Student.sno=SCX.sno and Student.sclass=SCX.sclass and not exists(
	select *
    from SC SCY
	where SCY.sclass='1' and SCY.sno='2' and not exists (
	select *
	from SC SCZ
	where SCZ.cno=SCY.cno and 
	SCZ.sno=SCX.sno and SCZ.sclass=SCX.sclass)

1.5查询不选1号课的学生班号及学号

select Student.sclass,Student.sno
from Student
where not exists (
		select *
		from SC
		where SC.sclass=Student.sclass and SC.sno=Student.sno and SC.cno=1)

1.6查询选2号课的学生名字及相应2号课成绩，按成绩从高到低排序

select Student.sname,grade
from SC,Student
where SC.sclass=Student.sclass and SC.sno=Student.sno and cno=2 
order by grade desc

1.7统计学生选修课程的班号、学号及总学分

select sclass,sno,sum(ccredit) sum_credit
from Course,SC
where Course.cno=SC.cno
group by sno,sclass

1.8统计1班选修3号课的学号及平均分

select avg(grade) avg_grade
from SC
where sclass=1 and cno=3
select sno
from SC
where sclass=1 and cno=3

1.9把个人信息及选课信息插入到Student和SC 表及新增加一门“无机化学”课程信息

insert into Course(cno,cname)values(8, '无机化学');

1.10删除选修3号课的所有选课信息并显示删除后的结果

delete from SC where cno=3;
select *
from SC;

1.11把选修1号课的所有男同学年龄增加1岁并显示最终学生Student信息

update  Student 
set sage=sage+1 
where ssex= '男'and exists
		(select * 
		from SC 
		where cno=1 and SC.sclass=Student.sclass and SC.sno=Student.sno);
select * 
from Student;

1.12把每个选课人的学号、班号及平均成绩插入到一个新表中

create table avg_grade(
	sno int not null,
	sclass int not null,
	avggrade float,
	primary key(sclass,sno) 
);
insert into avg_grade
select sno,sclass,avg(grade)
from SC
group by sclass,sno;
select *
from avg_grade

2.1使用企业管理器创建视图：在ST库中以“student”表为基础，建立信息系学生的视图V_ISStudent

create view V_ISStudent AS 
select * 
from Student 
where Sdept = 'IS' with check option;

select *
from V_ISStudent

2.2
① 建立一个每个学生的学号、班号、姓名、选修的课名及成绩的视图S_C_GRADE；

create view S_C_GRADE AS 
select Student.sno,Student.sclass,sname,cname,grade 
from Student join SC on (Student.sclass=SC.sclass and Student.sno=SC.sno)join Course on SC.cno=Course.cno;

select *
from S_C_GRADE

② 建立信息系建立信息系选修了1号课程且成绩在90分以上的学生的视图V_IS_Score

create view V_IS_Score AS 
select * 
from V_ISStudent 
where exists
		(select * 
		from SC 
		where cno=1 and grade>90 
		and V_ISStudent.sno=SC.sno 
		and V_ISStudent.sclass=SC.sclass);

select * 
from V_IS_Score 

③ 将各系学生人数，平均年龄定义为视图V_NUM_AVG。

create view V_NUM_AVG AS
select Sdept,count(*) num,avg(sage) avg_age 
from Student 
group by Sdept;
select  * 
from V_NUM_AVG

2.3查询以上所建的视图结果
见上题
2.4查询选修了1号课程的信息系学生

select * from V_ISStudent 
where exists
(select *
from SC 
where cno=1 and V_ISStudent.sno=SC.sno 
and V_ISStudent.sclass=SC.sclass);

2.5在信息系学生的视图中找出年龄小于20岁的学生

select * 
from V_ISStudent 
where sage<20;

2.6将信息系学生视图V_ISStudent中学号一班2号的学生姓名改为“刘辰”

update V_ISStudent 
set sname= '刘辰' 
where sclass=1 and sno=2;

select sname
from V_ISStudent
where sclass=1 and sno=2

2.7用SQL语句删除视图S_C_GRADE

drop view S_C_GRADE;