数据库 何玉洁 第五章习题答案

第五章习题答案

3．利用第三章建立的Student、Course和SC表，写出创建满足下述要求的视图的SQL语句。 ⑴查询学生学号、姓名、所在系、课程号、课程名、课程学分。 Create view t1(sno,sname,sdept,cno,cname,credit) As

Select s.sno ,s.sname,s.dept,c.cno,c.cname,c.credit From student s join course c on s.sno=c.sno

⑵查询学生的学号、姓名、选修的课程名和考试成绩。 Create view t2(sno,sname,cname,grade) As

Select s.sno,s.sname,c.cname,c.grade

From student s join course c on s.sno=c.cno

⑶统计每个学生的选课门数，要求列出学生学号和选课门数。 Create view t3(sno,选课门数) As

Select s.sno ,count(*) 选课门数

From student s LEFT OUTER join sc on s.sno=sc.sno group by s.sno

⑷统计每个学生的选课总学分，要求列出学生学号和总学分（说明：考试成绩大于等于陆拾才可获得此门课程的学分）。 Create view t4 (sno,总学分) As

Select sno , sum(credit) From sc

Where grade>=60 Group by sno

⑸查询计算机系VB考试成绩最高的学生的学号、姓名和VB考试成绩。 Create view t5(sno,sname,VB考试成绩) As

Select top 1 with ties s.sno,s.sname ,sc.grade

From student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno Where s.dept=’计算机系’and cname=’vb’ Order by sc.grade DESC

4．利用第三题建立的视图，完成下列查询：

⑴查询考试成绩大于等于90分的学生的姓名、课程名和成绩。 Select sname,cname,grade from t2

where grade>=90

⑵查询选课门数超过三的学生的学号和选课门数 Select sno,选课门数 from t3 where 选课门数 >3

⑶查询计算机系选课门数超过3的学生的学号和选课门数 Select sno,选课门数

from t3 join student on t3.sno=student.so where sdept=’计算机系’and 选课门数 >3

⑷查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分 Select sno,sname,sdept,总学分

From t4 join student on student.sno=t4.sno Where 总学分 >10

⑸查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名、年龄、所在系和修课总学分

Select sname,sage,sdept,总学分

From t4 join student on student.sno=t4.sno Where 总学分 >10 and age >=20

5．修改第三题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。 Alter view t4 (sno,总学分，总的选课门数) As

Select sno , sum(credit)， count(*) 选课门数 From sc join course on sc.cno=course.cno Where grade>=60 Group by sno

6．修改第三题（5）定义的视图，使其统计全体学生中VB考试成绩最高的学生的学号、姓名、所在系和VB考试成绩。

Alter view t5(sno,sname,sdept,VB考试成绩) As

Select top 1 with ties s.sno,s.sname ,s.sdept ,sc.grade

From student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno Where cname=’vb’ Order by sc.grade DESC