一天几道SQL题目（三）

Student(S#,Sname,Sage,Ssex)学生表    
Course(C#,Cname,T#) 课程表    
SC(S#,C#,score) 成绩表    
Teacher(T#,Tname) 教师表 

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；    

select sc1.s#,sname
from sc sc1,student
where exists
(
select *
from sc sc2
where sc1.c#=sc2.c# and sc2.s#='00001'
)
and sc1.s#=student.s#

12、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；   

update sc sc1
set sc1.score=
(
select avg(score)
from sc,teacher,coures
where sc.c#=coures.c# and coures.t#=teacher.t# and teacher.tname='叶平'
group by sc.c#
having sc1.c#=sc.c#
)

 

13、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；

select sc1.s#
from sc sc1
where sc1.c#  in
(
select c#
from sc
where sc.s#='00002'
)
group by s#
having count(*)=
(
select count(sc3.c#)
from sc sc3
where sc3.s#='00002'
)