sql面试

Stuent(s# sname sage ssex)
Course(c#,cname,t#)
SC(s#,c#,score)
Teacher(t#,tname)

1.查询和1002号的同学学习的课程完全相同的其他同学的学号和姓名
select * 
from Student a
inner join SC b
on a.s#=b.s#
where not exists(select 1 from SC where s#=1002 and b.c#<>c#)

2.删除学习“叶平”老师的sc表记录
delete from SC where c# in(
   select c# 
   from Course a
   inner join Teacher b
   on a.t#=b.t#
   where b.tname='叶平'
)
3.统计各科成绩，各分数段的人数
课程id，课程名称，
分段【100-85】【85-70】【70-60】【<60】

select a.c#,a.cname
       ,sum(case when b.score<=100 and b.score>=85 then 1 else 0 end)
       ,sum(case when b.score<85 and b.score>=70 then 1 else 0 end)
       ,sum(case when b.score<70 and b.score>=60 then 1 else 0 end)
       ,sum(case when b.score<60 then 1 else 0 end)
from Course a
inner join SC b
on a.c#=b.c#
group by a.c#,a.cname