数据库基础复习（三）—示例

基本表结构

student(sno,sname,sage,ssex)学生表
        course(cno,cname,tno) 课程表
        sc(sno,cno,score) 成绩表
        teacher(tno,tname) 教师表

把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩

update sc set score = (select avg(sc_2.score) from sc sc_2 wheresc_2.cno=sc.cno)
from course,teacher where course.cno=sc.cno and course.tno=teacher.tno andteacher.tname='王五'

查询和编号为2的同学学习的课程完全相同的其他同学学号和姓名

分两步查询
1、

select sno
from sc
where sno <> 2
group by sno
having sum(cno) = (select sum(cno) from sc where sno = 2)

2、

select b.sno, b.sname
from sc a, student b
where b.sno <> 2 and a.sno = b.sno
group by b.sno, b.sname
having sum(cno) = (select sum(cno) from sc where sno = 2)

删除学习“王五”老师课的sc表记录

delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

向sc表中插入一些记录，这些记录要求符合以下条件：

将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩

insert sc select sno, 3, (select avg(score) from sc where cno