数据库实验9-视图

1．定义视图

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

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

CREATE VIEW V_Computer
AS
SELECT sno,sname,sage,ssex,sdept
FROM studentfcy
WHERE sdept='cs'

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

CREATE VIEW V_S_C_G
AS
SELECT studentfcy.sno,sname,coursefcy.cno,cname,grade
FROM coursefcy INNER JOIN scfcy ON coursefcy.cno = scfcy.cno INNER JOIN studentfcy ON scfcy.sno = studentfcy.sno

CREATE VIEW V_S_C_G
AS
SELECT studentfcy.sno,sname,coursefcy.cno,cname,grade
FROM studentfcy,coursefcy,scfcy
WHERE studentfcy.sno=scfcy.sno AND coursefcy.cno=scfcy.sno

为什么这样不行呢 

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

CREATE VIEW V_NUM_AVG
AS
SELECT sdept,COUNT(sno),AVG(sage)
FROM studentfcy
GROUP BY sdept

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

CREATE VIEW V_YEAR(sno,sname,birthday)
AS
SELECT sno,sname,2009-sage
FROM studentfcy

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

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

CREATE VIEW V_AVG_C_G(cno,countsc,avggrade)
AS
SELECT cno,COUNT(sno),AVG(grade)
FROM scfcy
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）查询平均成绩为60分以上的学生学号、姓名和平均成绩。

SELECT studentfcy.sno,sname,avggrade
FROM v_avg_s_g,studentfcy
WHERE v_avg_s_g.sno=studentfcy.sno AND avggrade>=60

 

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

SELECT studentfcy.sno,sname,scfcy.cno,grade
FROM studentfcy,scfcy
WHERE scfcy.sno=studentfcy.sno AND scfcy.sno NOT IN(
    SELECT sno
    FROM scfcy,v_avg_c_g
    WHERE grade<=avggrade AND v_avg_c_g.cno=scfcy.cno)

SELECT studentfcy.sno,sname,scfcy.cno,grade
FROM studentfcy INNER JOIN scfcy ON scfcy.sno=studentfcy.sno 
WHERE scfcy.sno NOT IN(
    SELECT sno
    FROM scfcy,v_avg_c_g
    WHERE grade<=avggrade AND v_avg_c_g.cno=scfcy.cno)

 

UPDATE scfcy
SET grade='90'
WHERE sno=2008000000