2021011212
1.定义视图
在stu数据库中完成以下视图定义:
(1)定义计算机系学生基本情况视图V_Computer。
CREATE VIEW V_Computer
AS
SELECT sno, sname, sage, ssex, sdept
FROM studentlyx
WHERE sdept = 'CS'
(2)将学生的学号、姓名、课程号、课程名和成绩定义为视图V_S_C_G。
CREATE VIEW V_S_C_G
AS
SELECT studentlyx.sno, sname, courselyu.cno, cname, grade
FROM courselyu INNER JOIN sclyx ON courselyu.cno = sclyx.cno INNER JOIN studentlyx ON sclyx.sno = studentlyx.sno
(3)将各系学生人数、平均年龄定义为视图V_NUM_AVG。
CREATE VIEW V_NUM_AVG(sdept,scount,avgage)
AS
SELECT sdept,count(sno),avg(sage)
FROM studentlyx
GROUP BY sdept
(4)定义一个反映学生出生年份的视图V_YEAR,包括学号、姓名和出生年份。
CREATE VIEW V_YEAR(sno,sname,birthday)
AS
SELECT sno,sname,2023-sage
FROM studentlyx
(5)将每个学生选修课程的门数及平均成绩定义为视图V_AVG_S_G,包括学号、课程数量和平均成绩。
CREATE VIEW V_AVG_S_G
AS
SELECT sno,count(cno) AS countcno, avg(grade) AS avggrage
FROM sclyx
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 sclyx
GROUP BY cno
2.使用视图
(1)查询以上所建的视图结果。
(2)查询平均成绩为80分以上的学生学号、姓名和平均成绩。
SELECT studentlyx.sno,sname,avggrage
FROM V_AVG_S_G,studentlyx
WHERE V_AVG_S_G.sno = studentlyx.sno and avggrage >= 80
(3)查询各课成绩均大于平均成绩的学生学号、姓名、课程号和成绩。
SELECT studentlyx.sno,sname,sclyx.cno,grade
FROM studentlyx, sclyx
WHERE sclyx.sno = studentlyx.sno and sclyx.sno not in
(SELECT sno
FROM sclyx,V_AVG_C_G
WHERE grade<=avggrade and V_AVG_C_G.cno =sclyx.cno)