实验五SQL的常用数据更新操作

insert into student(sno,sname,sage)
values (‘95030‘,‘李莉‘,18)//插入如下学生记录（学号：95030，姓名：李莉，年龄：18）

insert into sc(sno,cno)
values(‘95030‘,1)//插入如下选课记录（95030，1）

update student
set sage=20
where sdept=‘CS‘//计算机系学生年龄改成20

update sc
set grade=0
where ‘MA‘=
(select sdept
from student
where student.sno=sc.sno) //把数学系所有学生成绩改成0
update sc
set grade=grade+5
where grade<
( select avg(grade) from sc where)//失败作品

update sc
set grade=grade+5
where grade<
(select avg(grade)
from sc inner join student
on student.sno=sc.sno
where ssex=‘女‘)//把低于总平均成绩的女同学成绩提高5分

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‘//修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高4％

delete
from student
where sno=‘95030‘//删除95030学生信息

delete
from sc
where grade is null;//删除SC表中无成绩的记录

delete
from sc
where sno=(select sno from student
where sname=‘张娜‘)//删除张娜的选课记录

delete
from sc
where grade<60//删除不及格的学生选课记录

delete
from sc
where sno in (select sno from student where sdept=‘MA‘)//删除数学系所有学生选课记录

delete
from course
where cno not in (select cno from sc)//删除所有未被选修的课程

insert into student(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

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//查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

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;//建立一个sdeptgrade 表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade