一、定义视图
1.定义计算机系学生基本情况视图V_Computer。
CREATE VIEW V_Computer
AS
SELECT Sno,Sname,Sage,Ssex,Sdept
FROM studentlj
WHERE Sdept='cs'
2.将学生的学号、姓名、 课程号、课程名和成绩定义为视图V_S_C_G。
CREATE VIEW V_S_C_G
AS
SELECT studentlj.Sno,sname,courselj.Cno,cname,grade
FROM courselj INNER JOIN sclj ON courselj.Cno=sclj.Cno INNER JOIN
studentlj ON sclj.Sno=studentlj.Sno
CREATE VIEW V_YEAR(sno,sname,birthday)
AS
SELECT sno,sname,2020-sage
FROM studentlj
3.将各系学生人数、平均年龄定义为视图V_NUM_AVG。
CREATE VIEW V_NUM_AVG(sdept,scount,avgage)
AS
SELECT sdept,COUNT(sno),AVG(sage)
FROM studentlj
GROUP BY sdept
4.定义一个反映学生出生年份的视图V_YEAR,包括学号、课程数量和平均成绩。
CREATE VIEW v_year(sno,sname,birthday)
AS
SELECT sno,sname,2020-sage
FROM studentlj
5.将每个学生选修课程的门数及平均成绩定义为V_AVG_S_G,包括学号、课程数量和平均成绩。
CREATE VIEW V_AVG_S_G
AS
SELECT sno,COUNT(cno) AS countcno, AVG(grade) AS avggrage
FROM sclj
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 sclj
GROUP BY Cno
二、使用视图
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.查询平均成绩为80分以上的学生学号、姓名和平均成绩
SELECT studentlj.Sno,Sname,avggrage
FROM v_avg_s_g,studentlj
WHERE v_avg_s_g.Sno=studentlj.Sno AND avggrage>=80
3.查询各课成绩均大于平均成绩的学生学号、姓名、课程号和成绩
SELECT studentlj.Sno,Sname,sclj.Cno,Grade
FROM studentlj,sclj
WHERE sclj.Sno=studentlj.Sno AND sclj.Sno NOT IN
(SELECT Sno
FROM sclj,v_avg_c_g
WHERE Grade<=avggrade AND v_avg_c_g.Cno=sclj.Cno)