实验四 SQL 数据更新、视图

目的：

掌握SQL的常用数据更新操作，熟练应用INSERT,UPDATE,DELETE语句。

内容：

1、在XSGL数据库中应用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='CS');

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

update sc set grade=grade+5 where sno in (select sno from student where ssex="女")

and grade<(select * from (select avg(grade) as grade from sc) as a);

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

update sc

set grade=(

case when grade<75

then grade*(1+0.05)

when grade>75

then grade*(1+0.04)

else grade end) where cno='2';

(7)  删除95030学生信息

delete from student where sno='95030';

(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 table STU

(sno char(8) primary key,

sname char(8) not null unique,

ssex char(2) default '男' check(ssex='男' or ssex='女')

);

insert into STU(sno,sname,ssex) select distinct student.sno,sname,ssex from student,sc where student.sno not in (select sno from sc where grade<80) and student.sno=sc.sno;

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

Create table sdeptgrade

(sdept char(8) primary key,

avggrade int);

insert into sdeptgrade select student.sdept, avg(sc.grade) from student inner join SC on (student.sno = SC.sno) group by student.sdept;

      

2、 在MySPJ数据库中应用SQL语句进行如下数据更新操作：

（1）把全部红色零件的颜色改成蓝色；

update p set color='蓝' where color='红';

（2）将P表中的所有红色零件的重量增加5;

update p set weight = weight + 5 where color = '红';

（3）将SPJ表中所有天津供应商的QTY属性值减少10;

update spj set qty=qty-10 where sno in(select sno from s where city='天津');

（4）由S5供给J4的零件P6改为由S3供应，请作必要修改；

update spj set sno='S3' where sno='S5' and jno='J4' and pno='P6';

（5）从供应商关系中删除S2的记录，并从供应情况关系中删除相应记录；

delete from s where sno='S2';

delete from spj where sno='S2';

（6）请将（S2,J6,P4,200)插入供应情况关系。

insert into spj values('S2','J6','P4',200);