MySQL——homework04

#[1] 定义视图
#(1) 定义信息系学生基本情况视图 V_IS
CREATE VIEW V_IS
AS
SELECT *
FROM student
WHERE department='信息系'
#(2) 将 Student，Course 和 SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图 V_S_C_G
CREATE VIEW V_S_C_G
AS
SELECT student.sno,student.sname,course.cno,course.cname,score.grade
FROM student,course,score
WHERE student.sno=score.sno AND score.cno=course.cno
#(3) 将各系学生人数，平均身高定义为视图 V_NUM_AVG
CREATE VIEW V_NUM_AVG
AS
SELECT department,COUNT(*),AVG(height)
FROM student
GROUP BY department
#(4) 定义一个反映学生出生年份的视图 V_YEAR[即出生不同年份的学生人数总数]
CREATE VIEW V_YEAR
AS
SELECT COUNT(*)
FROM student
GROUP BY YEAR(birthday)
#(5) 将各位学生选修课程的门数及平均成绩定义为视图 V_AVG_S_G
CREATE VIEW V_AVG_S_G
AS
SELECT student.sno,COUNT(*),AVG(grade) AS avg    #考虑null值是否参与计算平均值
FROM student JOIN score
ON student.sno=score.sno
GROUP BY student.sno
#(6) 将各门课程的选修人数及平均成绩定义为视图 V_AVG_C_G
CREATE VIEW V_AVG_C_G
AS
SELECT course.cname,COUNT(*),AVG(grade) AS avg
FROM course JOIN score
ON course.cn