实验五 连接查询参考答案

--创建学生视图

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_stu]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[v_stu]

go

create view v_stu

as

select sid, sname, ssexy, sbdate, Datediff(year, sbdate, getdate()) as sage, stele,

ugrade.gid, gname, gyear,

udept.did, dname, daddr, dtele, demail,

uteacher.tid, tname, tsexy, tbdate, tfield, tprof, tele, qq, email, msn

from ustudent, ugrade, udept, uteacher

where ustudent.gid=ugrade.gid and ugrade.did=udept.did and ugrade.tid=uteacher.tid

--创建选修课程视图

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_sc]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[v_sc]

go

create view v_sc

as

select ustudent.sname, usc.*, cname, credit, pcid, chour, cattr, cnum

from ustudent, usc, ucourse

where ustudent.sid=usc.sid and usc.cid=ucourse.cid

--1、显示所有学生的详细信息，包括学号、姓名、性别、年龄、班级名称，入学年份select sid, sname, ssexy, sage, gname, gyear

from v_stu

--2、显示信息科学与技术系同学的名单，包括学号、姓名、性别、年龄、班级名称、入学年份

select sid, sname, ssexy, sage, gname, gyear

from v_stu

where dname='信息科学与技术系'

--3、显示选修了“数据库”的所有同学的学号、姓名

select sid, sname

from v_sc

where cname='数据库'

--4、显示白云同学的班主任老师的姓名、联系电话

select tname, tele

from v_stu

where sname='白云'

--5、显示白云同学所在院系的名称、办公地点与联系电话

select dname, daddr, dtele

from v_stu

where sname='白云'

--6、统计计算机科学与技术系每个同学已经修完的学分，显示学号、姓名、学分总数select v_stu.sid, v_stu.sname, sum(v_sc.credit)

from v_stu, v_sc

where v_stu.sid=v_sc.sid and v_stu.dname='计算机科学与技术系'

group by v_stu.sid, v_stu.sname

--7、显示李山同学已修课程及期末成绩

select sid, sname, cname, score2

from v_sc

where sname='李山'

--8、显示计算机科学与技术系、信息科学与技术系的班级名称、入学年份、班导名称与联系电话

select distinct gname, gyear, tname, tele

from v_stu

where dname in ('计算机科学与技术系', '信息科学与技术系')

--9、显示2006年入学的同学的学号、姓名、班级名称

select sid, sname, gname

from v_stu

where gyear='2006'

--10、显示已修数据库的同学的学号、姓名及期末成绩

select sid, sname, score2

from v_sc

where cname='数据库'

--11、显示平均成绩75分以上的课程名称与平均成绩

select cname, cast(avg(score2) as dec(5,2)) as '平均成绩'

from v_sc

group by cid, cname

having avg(score2)>75

--12、显示平均成绩80分以上同学的学号、姓名与平均成绩

select sid, sname, cast(avg(score2) as dec(5,2)) as '平均成绩'

from v_sc

group by sid, sname

having avg(score2)>80

--13、显示一周课时数为6节及以上的教师的姓名与研究领域select tname, tfield

from uteacher

where tid in (select tid

from ujobtable

group by tid

having sum(len(timeseg))>=6)

--14、按照班级统计期末平均成绩，显示班级名称与平均成绩select gname, cast(avg(score2) as dec(5,2)) as '平均成绩'

from v_stu, usc

where v_stu.sid=usc.sid

group by gid, gname

--15、按照学期统计计算机科学与技术系的期末平均成绩select term, cast(avg(score2) as dec(5,2)) as '平均成绩'

from v_stu, usc

where v_stu.sid=usc.sid and v_stu.dname='计算机科学与技术系' group by term

--16、统计每个院系一周的课时数，显示院系名称与课时数select dname, sum(len(timeseg))

from ujobtable, uteacher, udept

where ujobtable.tid=uteacher.tid and uteacher.did=udept.did

group by udept.dname

--17、显示没有选修任何课程的学生学号、姓名、班级名称select sid, sname, gname

from v_stu

where sid not in (select sid from usc)

--18、显示上过李飞老师的课的学生的学号、姓名与联系电话select sid, sname, stele

from ustudent

where gid in (select gid

from ujobtable, uteacher

where ujobtable.tid=uteacher.tid and uteacher.tname='李飞')

--19、显示一周6节课及以上的课程名称、学分

select cname, credit

from ucourse