SQL数据更新、视图

(1)    插入如下学生记录（学号：95030，姓名：李莉，年龄：18）

INSERT INTO student(sno,sname,sage)
VALUES('95030',"李莉" ,18);

(2)    插入如下选课记录（95030，1）

INSERT INTO sc(sno,cno)
VALUES('95030',1);

(3)    CS系学生年龄改成20

UPDATE student
SET sage=20
WHERE sdept='CS';

(4)    把MA系所有学生成绩改成0

UPDATE sc
SET grade=0
WHERE sno in(SELECT sno FROM student WHERE sdept='MA');

(5)    把低于总平均成绩的女同学成绩提高5分

UPDATE sc
SET grade=grade+5 
WHERE grade<(SELECT * from(SELECT AVG(grade) from sc)as a) 
AND sno in (SELECT sno FROM student WHERE ssex='女');

(6)    修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高4％（可用两个语句实现，注意顺序）

UPDATE sc
SET grade=grade*1.04
WHERE grade>75 AND cno='2';

UPDATE sc
SET grade=grade*1.05
WHERE grade<75 AND cno='2';

(7)    删除95030学生信息

DELETE 
FROM student 
WHERE sno='95030';S

(8)    删除SC表中无成绩的记录

DELETE 
FROM sc
WHERE grade is null;

(9)    删除张娜的选课记录

DELETE 
FROM sc
WHERE sno=(SELECT sno FROM student WHERE sname='张娜');

(10)    删除不及格的学生选课记录

DELETE 
FROM sc
WHERE grade<60;

(11)    删除MA系所有学生选课记录

DELETE 
FROM sc
WHERE sno in(SELECT sno FROM student WHERE sdept='MA');

(12)    删除所有未被选修的课程

DELETE
FROM course
WHERE cno not in(SELECT cno FROM sc);

（13）创建视图STU(SNO,SNAME,SSEX)，
查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，
并限制对视图的数据更新必须符合视图的定义；

CREATE VIEW stu
AS SELECT sno,sname,ssex FROM student;

Select * from stu where sno in(select sno from sc 
and sno not in(select sno from sc where grade<80);

（14）建立一个sdeptgrade视图，包含(sdept,avggrade)字段，求每个系的学生的平均成绩。

CREATE VIEW sdeptgrade(sdept,avggrade)
AS SELECT sdept,AVG(grade) FROM student ,sc 
WHERE student.sno=sc.sno
GROUP BY sdept