发布时间 : 星期五 文章《SQL - Server - 2000 - 实验指导》课后作业更新完毕开始阅读
update score_view set score=100 where sno='991102' and cname= go 5、创建一个视图，计算机系学生的成绩单score_view_cdept,版含学号sno，姓名sn，课程名cn，成绩score。 use jiaoxuedb go create view score_view_cdept go
6、给视图s_view增加一个年龄age字段。 use jiaoxuedb go alter view s_view go
7、创建一个教师工资表视图sal_view，版含字段教师名tname，性别sex，职称prof，工资总和salsum，系别dept。 use jiaoxuedb go create view sal_view go
8、通过视图查询教师“张明”的工资收入。 use jiaoxuedb select tname,salsum from sal_view where tname='张朋' go 实验7：习题
基于jiaoxuedb实验
- 8 -
as select tname,sex,prof,sal+comm as salsum from teacher as select sno,sname,sex,age from student where dept='计算机' as select student.sno,sname,cname,score from student,course,sc and student.sno=sc.sno and sc.cno=course.cno (select cname from course where cno='01001') where student.dept='计算机'
1、 查询成绩在80~90之间的记录。
select student.sno,sname,dept,course.cno,score from student,sc,course where student.sno=sc.sno 2、 查询至少有4个同学选修的课程名。 use jiaoxuedb select cno as 课程号,cname as 课程名 from course where cno=any go
3、 查询其他系中比“信息系”所有学生年龄都打的学生名单及年龄，并按年龄降序输出。 use jiaoxuedb select sno as 学号,sname as 姓名,age as 年龄,dept as 专业 from student where age>all go
4、 查询与学生张建国同岁的所有学生的学号、姓名和系别。 /*方法一*/ use jiaoxuedb select sno as 学号,sname as 姓名,age as 年龄,dept as 专业 from student where age=any go /*方法二*/ (select age from student where sname='张建国') and sname <> '张建国' (select age from student where dept='信息') and dept <> '信息' (select cno as 课程号 from sc group by cno having count(*)>=4) and sc.score between 80 and 90 and sc.cno=course.cno; order by age desc order by age desc - 9 -
use jiaoxuedb select s2.sno,s2.sname,s2.dept from student s1, student s2 where s1.age=s2.age and s1.sname='张建国' and s2.sname <> '张建国' 5、 查询选修了2门以上课程的学生名单。 use jiaoxuedb select sno as 学号,sname as 姓名,age as 年龄,dept as 专业 from student where sno=any go
6、 查询至少有一门与“张建国”选课相同的学生的姓名、课程名和系别。 use jiaoxuedb select sno as 学号,sname as 姓名,age as 年龄,dept as 专业 from student where sno=any go
7、 查询成绩比该课程平均成绩高的学生的成绩表。 /*方法一*/ use jiaoxuedb select sno as 学号,sname as 姓名,age as 年龄,dept as 专业 from student where sno=any go /*方法二*/ (select sno from sc x where score >= (select avg(score) from sc y where y.sno=x.sno)); (select sno from sc where cno=any (select cno from sc where sno=’991103’)) (select sno from sc group by sno having count(*) >=2) and sname <> '张建国' - 10 -
select * from sc sc1 where score > go
8、 查询选修课号为01001课程且成绩高于课程01002学生的姓名、此两门课程的k而成名和成绩。 /*方法一*/ use jiaoxuedb
select sname,cname,score from student ,sc,course where student.sno= go
/*方法二*/ use jiaoxuedb
select sname,cname,score from student ,sc,course where student.sno= go
9、查询所有未修01001号课程的学生名单。 use jiaoxuedb select student.sno,sname from student where student.sno in (select s1.sno (select sno from sc x where score >=
(select avg(score) from sc y
where y.sno=x.sno and x.cno='01001' and y.cno='01002') (select s1.sno from sc s1,sc s2
where s1.cno='01001' and s2.cno='01002' and s1.score>s2.score and and sc.sno=student.sno and course.cno=sc.cno (select avg(score) from sc sc2) and sc1.cno=sc2.cno; s1.sno=s2.sno)
and sc.sno=student.sno and course.cno=sc.cno)
- 11 -