数据库原理及安全技术教学实验报告SQL实践（二）
目录
1．使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。
2．使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。
3．根据学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。
一、实验目的
1．掌握各种查询的使用方法。
2．掌握数据操纵的使用方法。
3. 掌握视图的使用方法。
二、实验软硬件要求
1、SQL Server 2008
三、实验预习
表连接、表更新、视图操作等SQL语句。
四、实验内容（实验步骤、测试数据等）
根据SQL实践（一）实验中创建的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。
1．使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。
（1）查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
（2）查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。
（3）查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。
（4）查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。
2．使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。
（1）在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。
（2）将所有课程的学分数变为原来的两倍。
（3）删除张乐的信息。
3．根据学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。
（1）创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。
（2）创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。
（3）创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。
（4）修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。
（5）向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。
（6）将电子05的学生视图中赵亦的性别改为“女”（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。
（7）删除电子05的学生视图中赵亦的记录。
（8）删除电子05的学生视图（给出SQL语句即可）。
运行结果：
1．使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。
（1）查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
连接查询
select *
from S Student1,S Student2
where Student1.Sclass=Student2.Sclass
and Student2.Sname='张志国';
子查询
select *
from S
where Sclass in(
select Sclass
from S
where Sname='张志国'
);
（2）查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。
连接查询
select C2.Ccredit,C2.Chour,C2.Cname,C2.Cno,C2.Cteacher
from C C1,C C2
where C1.Cname='计算机应用基础'
and C1.Chour<C2.Chour;
子查询
select C2.Ccredit,C2.Chour,C2.Cname,C2.Cno,C2.Cteacher
from C C2
where C2.Chour>(
select C1.Chour
from C C1
where C1.Cname='计算机应用基础');
（3）查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。
连接查询
select S.Sno,S.Sname
from S,W
where S.Sno=W.Sno
and W.Cno='K002';
普通子查询
select S.Sno,S.Sname
from S
where S.Sno in(
select Sno
from W
where W.Cno='K002');
相关子查询
select x.Sno,x.Sname
from S x
where x.Sno in(
select Sno
from W y
where y.Cno='K002' and
x.Sno=y.Sno);
使用exists关键字的相关子查询
select x.Sno,x.Sname
from S x
where exists(
select Sno
from W y
where y.Cno='K002' and
x.Sno=y.Sno);
（4）查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。
select x.Sno,x.Cno,x.Wn1,x.Wn2,x.Wn3
from W x
where not exists(
select Sno
from W y
where y.Cno='K001'
and x.Sno=y.Sno
or y.Cno='M001'
and x.Sno=y.Sno);
select x.Sno,x.Cno,x.Wn1,x.Wn2,x.Wn3
from W x
where x.Sno not in(
select y.Sno
from W y
where y.Cno in('K001','M001'));
2．使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。
（1）在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。
insert
into S(Sno,Sname,Ssex,Sclass)
values( '0593','张乐','男','电子05');
（2）将所有课程的学分数变为原来的两倍。
update C
set Ccredit=Ccredit*2;
（3）删除张乐的信息。
delete
from S
where Sname='张乐';
3．根据学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。
（1）创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。
create view 电子05
as
select S.Sno,S.Sname,S.Ssex,S.Sclass,S.Sbirth
from S
where S.Sclass='电子05';
（2）创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。
create view 生物05
as
select S.Sno,S.Sname,C.Cname,W.Wn1,W.Wn2,W.Wn3
from S,W,C
where S.Sclass='生物05'
and S.Sno=W.Sno
and W.Cno=C.Cno;
（3）创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。
create view 学生作业平均成绩
as
select W.Sno,avg(W.Wn1) as Wn1平均成绩,avg(W.Wn2) as Wn2平均成绩,avg(W.Wn3) as Wn3平均成绩
from W
group by W.Sno;
（4）修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。
alter view 生物05
as
select S.Sno,S.Sname,C.Cname,W.Wn1
from S,W,C
where S.Sclass='生物05'
and S.Sno=W.Sno
and W.Cno=C.Cno;
（5）向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。
insert
into 电子05(Sno,Sname,Ssex,Sclass,Sbirth)
values('0596','赵亦','男','电子05','1986-6-8')
（6）将电子05的学生视图中赵亦的性别改为“女”（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。
update 电子05
set Ssex='女'
where Sname='赵亦';
（7）删除电子05的学生视图中赵亦的记录。
delete
from 电子05
where Sname='赵亦';
（8）删除电子05的学生视图（给出SQL语句即可）。
drop view 电子05;
五、实验体会
操作不够熟练，需要加强练习。
