数据库上机试验报告dk

CREATE VIEW W18_BOY(S#, SNAME, BDATE, HEIGHT) AS SELECT S#, SNAME, BDATE, HEIGHT

FROM JSJ86_S140

WHERE (SEX = '男') AND (DORM LIKE '西18舍%')

查看视图W18_BOY中内容与上图一致：

(2) 先查找出学生然后对其建立视图

SELECT MAX(JSJ86_C140.C#) AS Expr2, MAX(JSJ86_C140.CNAME) AS AVG_GRADE, AVG(JSJ86_SC140.GRADE) AS Expr1 FROM JSJ86_C140 INNER JOIN

JSJ86_SC140 ON JSJ86_C140.C# = JSJ86_SC140.C# WHERE (JSJ86_C140.TEACHER = '张明')

GROUP BY JSJ86_C140.CNAME, JSJ86_C140.C#

CREATE VIEW ZM_CLASS(C#, CNAME, AVG_GRADE) AS SELECT MAX(JSJ86_C140.C#) AS Expr2, MAX(JSJ86_C140.CNAME)

AS AVG_GRADE, AVG(JSJ86_SC140.GRADE) AS Expr1

FROM JSJ86_C140 INNER JOIN JSJ86_SC140 ON JSJ86_C140.C# = JSJ86_SC140.C# WHERE (JSJ86_C140.TEACHER = '张明')

GROUP BY JSJ86_C140.CNAME, JSJ86_C140.C#

(3) 先查找出学生然后对其建立视图

SELECT JSJ86_S140.S#, JSJ86_S140.SNAME, JSJ86_SC140.GRADE FROM JSJ86_S140 INNER JOIN

JSJ86_SC140 ON JSJ86_S140.S# = JSJ86_SC140.S# WHERE (JSJ86_SC140.C# IN (SELECT C#

FROM JSJ86_C140

WHERE (CNAME = '人工智能')))