视图课堂作业

	-- 1.
CREATE VIEW courseavg(cno,avggrade,maxgrade,mingrade)
AS
SELECT Cno,avg(Grade),max(Grade),min(Grade) from sc GROUP BY Cno;

-- 查询结果
SELECT * from courseavg


-- 2.
-- 创建表
create  table course_a(
cno char(4),
avggrade int,
maxgrade int,
mingrade int
);

INSERT 
INTO course_a(cno,avggrade,maxgrade,mingrade)
SELECT * from courseavg;
-- 查看结果
SELECT * FROM course_a 

-- 3。
-- 因为之前的数据表做作业，所以可能跟老师筛选的结果不太一样 
SELECT sc.Sno,sc.Cno,sc.Grade 
from courseavg,sc 
WHERE sc.Cno=courseavg.cno and (sc.Grade=courseavg.maxgrade or sc.Grade=courseavg.mingrade)

-- 4.
-- 按题目创建视图
create view stugrade
as
select student.sno,sname,sc.cno,cname,grade
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno

-- 1)
UPDATE stugrade
set sname='张成浩'
WHERE sno='201415011'

-- 2)
UPDATE  stugrade
set Grade=Grade+20
WHERE sno='201415011'

UPDATE stugrade
SET Grade=100
WHERE Grade>100 AND sno='201415011'