1查询所有学生的学号、姓名、选课数、总成绩，3、查询出每门课程都大于80分的学生姓名4、删除除了id号不同,其他信息都相同的学生冗余信息

 

 

 

select s.xuehao,s.xingming,sum(sc.kechenghao),sum(sc.chengji)
from Student s left join Sc sc on s.xuehao=sc.xuehao
left join Course c on c.kechenghao=sc.kechenghao

group by s.xuehao

 

select s.xuehao ,s.xingming,sc.chengji from Student s
left join Sc sc on s.xuehao=sc.xuehao
left join Course c on c.kechenghao=sc.kechenghao
WHERE c.kechengming='SQLserver'
order by sc.chengji desc

 

select s.xingming from Student s
left join Sc sc on s.xuehao=sc.xuehao
left join Course c on c.kechenghao=sc.kechenghao
group by s.xingming having min(sc.chengji)>=80

 

 

 

delete from Student where xuehao not in (select bid from (select min(xuehao) as bid from Student group by xingming,xingbie,nianling)as b)