【数据库原理及应用 - 作业】视图的简单操作

一、利用Student、Course、SC表，写出创建满足下述要求的视图的SQL语句

1.1、查询学生的学号、姓名 、所在系、课程号、课程名、课程学分

create view sql_1
as
select Sno, Sname, Sdept, Cno, Cname, Ccredit
from Student JOIN SC ON Student.Sno=SC.Sno AND Course JOIN SC ON SC.Cno=Course.Cno

1.2、查询学生的学号、姓名、选修的课程名和考试成绩

create view sql_2
as
select Sno, Sname, Cname, Grade
from Student JOIN SC ON Student.Sno=SC.Sno AND Course JOIN SC ON Course.Cno=SC.Cno

1.3、统计每个学生的选课门数，要求列出学号和选课门数

create view sql_3
as
select Sno, count(Cno) as CnoNum
from Student JOIN SC ON Student.Sno=SC.Sno 
group by Sno

1.4、统计每个学生的选修课总学分，要求列出学生学号和总学分

create view sql_4
as
select Sno, sum(Ccredit) as SumCredit
from Student JOIN SC ON Student.Sno=SC.Sno AND Course JOIN SC ON Course.Cno=SC.Cno

1.5、查询计算机系VB考试成绩最高的学生学号、姓名和VB考试成绩

create view sql_5
as
select Sno, Sname, Grade as VBGrade
from Student JOIN SC ON Student.Sno=SC.Sno AND Course JOIN SC ON Course.Cno=SC.Cno
where Student.Sdept='计算机系' AND Course.Cname='VB' AND SC.Grade IN(
    select max(Grade) from SC
)

---

二、利用第一题建立的视图，完成以下查询

2.1、查询考试成绩大于等于90分的学生的姓名、课程名和成绩

select Sname, Cname, Grade from sql_2 where Grade>=90

2.2、查询选课门数超过三门的学生的学号和选课门数

select Sno, CnoNum from sql_3 where CnoNum>3

3.3、查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学分

select Sno, Sname, Sdept, SumCredit from sql_4 where SumCredit>10

---

三、修改第一题（4）小题定义的视图，使其查询每个学生的学号、总学分以及总的选课门数

alter view sql_4
as
select Sno, sum(Ccredit), sum(Cno)
from Student JOIN SC ON Student.Sno=SC.Sno AND Course JOIN SC ON Course.Cno=SC.Cno

---

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

alter view sql_5
as
select SnO, Sname, Sdept, Grade as MaxVBGrade 
from Student JOIN SC ON Student.Sno=SC.Sno AND Course JOIN SC ON Course.Cno=SC.Cno
where Cname='VB' AND Grade IN(
    select max(Grade) from SC
)