数据库管理与应用-实验报告4-SQL语言(3)

浙江科技学院
实验报告课程名称：数据库原理与应用B
学院：经济管理学院
专业班：物流工程102
姓名：
学号：
指导教师：
2012年11月9 日
一、实验项目名称
SQL语言（3）
二、实验目的和要求
（一）实验目的
了解SQL语言（视图的建立与查询）。

（二）实验要求
根据学生表Student (Sno, Sname, Ssex, Sage, Sdept)、课程表Course (Cno, Cname, Cpno, Ccredit)、选课表SC (Sno, Cno, Grade)、教师表Teacher（Tno, Tname, Tsex, Tbirthday, Tdept, Tzc）。

实现视图的建立与查询。

三、实验内容和原理
（一）实验内容
使用SQL语言表来完成视图的建立与查询等。

任务如下：
（1）建立教师讲课的视图V1（把没上课的教师也罗列出来，用左连接实现，要求包括工号、教师姓名、课程号、课程名、学分字段）。

（2）查询视图V1的全部信息。

（3）建立每个学生选课的总分与平均分视图V2（要求包括学号、学生姓名、总分、平均分字段）。

（4）查询视图V2的全部信息。

（5）建立选修了“数学”这门课的视图V3（要求包括学号、学生姓名、课程名字段）。

（6）查询视图V3的全部信息。

（7）建立最高分的视图V4（要求包括学号、学生姓名、课程名、最高分字段）。

（8）查询视图V4的全部信息。

（9）建立所有学生的课程编号为“1”和“2”的成绩视图V5，且需要显示学号、学生姓名、课程名、成绩字段。

（10）查询视图V5的全部信息。

（11）建立课程编号为“1”和“2”的平均成绩视图V6，且需要显示课程号、课程名、平均成绩字段。

（12）查询视图V6的全部信息。

（13）建立每一门课程的及格人数视图V7，且需要显示课程号、及格人数字段。

（14）查询视图V7的全部信息。

（15）建立出生年月为1~6月份的教师视图V8，且需要显示工号、教师姓名、出生年月
（16）查询视图V8的全部信息。

（17）建立成绩平均分在86分及以上的视图V9，且需要显示学号、姓名、平均分
（18）查询视图V9的全部信息。

（二）实验原理
SQL语言。

四、主要仪器设备
计算机、SQL Server数据库
五、操作方法与实验步骤
第一步：分析问题
第二步：打开Enterprise Manager(企业管理器)中名为“yexiaoqiang”的数据库
第三步：打开工具中的SQL查询分析器
第四步：输入命令语句，建立视图
第五步：查询并检验
第6步：截图保存。

六、试验结果与分析
（1）建立教师讲课的视图V1（把没上课的教师也罗列出来，用左连接实现，要求包括工号、教师姓名、课程号、课程名、学分字段）。

create view V1 (tno,tname,cno,cname,ccredit)
as
select teacher.tno,tname,o,cname,ccredit
from teacher left outer join
course on(teacher.tno=course.tno)
（2）查询视图V1的全部信息。

select *
from V1
（3）建立每个学生选课的总分与平均分视图V2（要求包括学号、学生姓名、总分、平均分字段）。

create view V2
as
select student.sno,student.sname,avg(grade) as avg ,sum(grade) as sum from student,sc
where student.sno=sc.sno group by student.sno,student.sname
（4）查询视图V2的全部信息。

select *
from V2
（5）建立选修了“数学”这门课的视图V3（要求包括学号、学生姓名、课程名字段）。

create view V3
as
select student.sno,student.sname,ame
from student,sc,course
where o=2 and sc.sno=student.sno and o=o
（6）查询视图V3的全部信息。

select *
from V3
（7）建立最高分的视图V4（要求包括学号、学生姓名、课程名、最高分字段）。

create view V4 as
SELECT student.sno, student.sname,sc.grade,ame FROM sc,student,course
WHERE sc.grade=(select MAX(grade) from sc)
and student.sno=sc.sno
and o=o
（8）查询视图V4的全部信息。

select *
from V4
（9）建立所有学生的课程编号为“1”和“2”的成绩视图V5，且需要显示学号、学生姓名、课程名、成绩字段。

create view v5
as
select student.sno,student.sname,ame,sc.grade
from student,sc,course
where student.sno=sc.sno and o=o and (o=1 or o=2)
（10）查询视图V5的全部信息。

select *
from V5
（11）建立课程编号为“1”和“2”的平均成绩视图V6，且需要显示课程号、课程名、平均成绩字段。

create view V6(cno,cname,平均成绩)
as select o,ame,avg(grade) as 'avg'
from student,sc,course
where student.sno=sc.sno and o=o and o in (1,2) group by o,ame
（12）查询视图V6的全部信息。

select *
from V6
（13）建立每一门课程的及格人数视图V7，且需要显示课程号、及格人数字段。

create view V7
as SELECT cno,count(grade)
as count FROM SC
where grade>=60 GROUP BY cno
（14）查询视图V7的全部信息。

select *
from V7
（15）建立出生年月为1~6月份的教师视图V8，且需要显示工号、教师姓名、出生年月
create view V8
AS
select Tno,tname,tbirthday
from teacher
where tbirthday like '%-01-%' OR tbirthday like '%-02-%'
OR tbirthday like '%-03-%' OR tbirthday like '%-04-%' OR tbirthday like '%-05-%' OR tbirthday like '%-06-%'
（16）查询视图V8的全部信息。

select *
from V8
（17）建立成绩平均分在86分及以上的视图V9，且需要显示学号、姓名、平均分create view V9
as select distinct sc.sno,sname,avggrade=avg(grade)
from student,sc
where student.sno=sc.sno
group by sc.sno ,sname having avg(grade)>=86
（18）查询视图V9的全部信息。

select *
from V9。