每次一使用视图的查询语句就出错,请教这样的问题该怎么解决?谢谢~
CREATE VIEW V_SC_G
AS
SELECT STUDENT.SNO,SNAME,SC.CNO,CNAME,GRADE
FROM STUDENT,COURSE,SC
WHERE SC.SNO=STUDENT.SNO AND SC.CNO=COURSE.CNO
GO
--1) 将Student, Course 和SC表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_SC_G;
CREATE VIEW V_YEAR
AS
SELECT SNO,SNAME,SAGE AGE
FROM STUDENT
GO
--2) 定义一个反映学生出生年份的视图V_YEAR;
CREATE VIEW V_AVG_S_G
AS
SELECT STUDENT.SNO,COUNT(CNO) CNONUM,AVG(GRADE) AVGGRA
FROM SC,STUDENT
WHERE SC.SNO=STUDENT.SNO GROUP BY STUDENT.SNO
GO
--3) 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G;
CREATE VIEW V_AVG_C_G
AS
SELECT CNO,COUNT(*)
AS CNONUM, AVG(GRADE) AVGGRA
FROM SC
GROUP BY CNO
GO
--4) 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G。
SELECT * FROM V_SC_G
DROP VIEW V_SC_G
SELECT * FROM V_YEAR
DROP VIEW V_YEAR
SELECT * FROM V_AVG_S_G
DROP VIEW V_AVG_S_G
SELECT * FROM V_AVG_C_G
DROP VIEW V_AVG_C_G
-- 1) 查询以上所建的视图结果。
SELECT STUDENT.SNO,SNAME,AVGGRA FROM V_AVG_S_G A,STUDENT WHERE STUDENT.SNO=A.SNO AND A.AVGGRA>='90'
-- 2) 查询平均成绩为90分及以上的学生学号、姓名和成绩;
SELECT STUDENT.SNO,SNAME,CNO,GRADE,AVGGRA FROM SC B,STUDENT,V_AVG_S_G WHERE B.SNO=STUDENT.SNO AND STUDENT.SNO=V_AVG_S_G.SNO AND
NOT EXISTS(SELECT * FROM SC C,V_AVG_C_G WHERE B.GRADE<V_AVG_C_G.AVGGRA AND B.CNO=C.CNO)
-- 3) 查询课程成绩大于平均成绩的学生学号、课程号、成绩和平均成绩;
SELECT SNAME,SNO FROM V_YEAR WHERE AGE='1994'
-- 4) 查询1994年出生的学生学号和姓名。