Java常用面试题之数据库篇（十二）

基本表结构：

    student(sno,sname,sage,ssex)学生表
    course(cno,cname,tno) 课程表
    sc(sno,cno,score) 成绩表
    teacher(tno,tname) 教师表
        
111、把“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='王五'

112、查询和编号为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)

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

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

114、向sc表中插入一些记录，这些记录要求符合以下条件：
将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩

	insert sc select sno, 3, (select avg(score)</