mysql语句练习

1、

select m.name,count(c.name) 
from major m 
join major_course mc on m.major_id=mc.major_id 
join course c on mc.course_id=c.course_id 
group by m.name having count(c.name)=6 
order by m.name desc limit 3;

2、

select ct.teacher_id,avg(c.credit) as avg_credit  
from course_teacher ct 
left join course c on ct.course_id=c.course_id 
group by ct.teacher_id 
having avg_credit>2.5 
order by avg_credit desc  
limit 5;  

3、

select s.name,count(sc.course_id),avg(sc.score) 
from student s left 
join student_course sc on s.student_id=sc.student_id 
group by s.name 
having avg(sc.score)>70 and count(sc.course_id)>=4   
order by avg(sc.score) desc 
limit 10;

4、

select m.name,sum(c.credit) 
from major m left 
join major_course mc on m.major_id=mc.major_id 
left join course c on mc.course_id=c.course_id 
group by m.name having sum(c.credit)>15 
order by sum(c.credit) 
limit 3;

5、

select c.name,c.credit,count(sc.student_id) 
from course c 
join student_course sc on c.course_id=sc.course_id 
group by c.name,c.credit 
having c.credit>2.0 and count(sc.student_id)>=5 
order by count(sc.student_id) desc 
limit 5;