2021011086解祎梦实验九视图

1．定义视图

在stu数据库中完成以下视图定义：

（1）定义计算机系学生基本情况视图V_Computer。

CREATE VIEW V_Computer
AS
SELECT  Sno, Sname, Sage, Ssex, sdept
FROM  stuxym
WHERE  sdept = 'cs'

（2）将学生的学号、姓名、课程号、课程名和成绩定义为视图V_S_C_G。

CREATE VIEW V_S_C_G
AS
SELECT  stuxym.Sno, Sname, coursexym.Cno, Cname, grade
FROM  coursexym INNER JOIN   scxym ON coursexym.Cno = scxym.Cno INNER JOIN  stuxym ON scxym.Sno = stuxym.Sno

 

（3）将各系学生人数、平均年龄定义为视图V_NUM_AVG。

CREATE VIEW  V_NUM_AVG(sdept,scount,avgage)
AS
SELECT sdept,count(Sno),avg(sage)
FROM stuxym
GROUP BY sdept

 

（4）定义一个反映学生出生年份的视图V_YEAR，包括学号、姓名和出生年份。

CREATE VIEW V_YEAR(sno,sname,birthday)
AS
SELECT Sno,Sname,2020-sage
FROM stuxym

（5）将每个学生选修课程的门数及平均成绩定义为视图V_AVG_S_G，包括学号、课程数量和平均成绩。

CREATE VIEW V_AVG_S_G
AS
SELECT  sno,count(cno) AS countcno, avg(grade) AS avggrage
FROM  scxym
GROUP BY sno

 

（6）将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G，包括课程号、选修人数和平均成绩。

CREATE VIEW V_AVG_C_G(cno,countsc,avggrade)

AS

SELECT cno,count(sno),avg(grade)

FROM scxym

GROUP BY cno

2．使用视图

（1）查询以上所建的视图结果。

SELECT * FROM V_Computer

SELECT * FROM V_S_C_G

SELECT * FROM v_num_avg

SELECT * FROM V_YEAR

SELECT * FROM V_AVG_S_G

SELECT * FROM V_AVG_C_G

（2）查询平均成绩为90分以上的学生学号、姓名和平均成绩。 

 SELECT stuxym.sno,sname,avggrage
FROM V_AVG_S_G,stuxym
WHERE V_AVG_S_G.sno = stuxym.Sno and avggrage >= 90

（3）查询各课成绩均大于平均成绩的学生学号、姓名、课程号和成绩。

SELECT stuxym.Sno,Sname,scxym.Cno,grade
FROM stuxym, scxym
WHERE scxym.Sno = stuxym.Sno   and  scxym.Sno not in
(SELECT Sno
FROM scxym,V_AVG_C_G
WHERE grade<=avggrade and V_AVG_C_G.cno =scxym.Cno)