Question

第一步，使用SQL语句建立一个表avg_grade，两个字段sno、avggrade，分别为字符串类型和实数类型。

第二步，查询每个学生的平均分，将结果填充到avg_grade表。

第三步，在sc上建立触发器，当新插入数据、修改数据和删除数据时，自动更新avg_grade表的相应数据。

Answer 1

CREATE TABLE AVG_GRADE(SNO VARCHAR(50),AVGGRADE DECIMAL(10,4))
GO
INSERT INTO AVG_GRADE SELECT SNO,AVG(AVGGRADE) FROM SC GROUP BY SNO
GO
CREATE TRIGGER 触发器名 ON SC
FOR INSERT,UPDATE,DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED)
BEGIN
DELETE FROM AVG_GRADE WHERE SNO = (SELECT SNO FROM INSERTED)
INSERT INTO AVG_GRADE SELECT SNO,AVG(AVGGRADE) FROM SC WHERE SNO = (SELECT SNO FROM INSERTED) GROUP BY SNO
END
IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
BEGIN
DELETE FROM AVG_GRADE WHERE SNO = (SELECT SNO FROM DELETED)
INSERT INTO AVG_GRADE SELECT SNO,AVG(AVGGRADE) FROM SC WHERE SNO = (SELECT SNO FROM DELETED) GROUP BY SNO
END
END
GO