mysql練習題進階版

標籤：_id   等於   成績   select   編號   資訊   student   mysql   col   

student表

1 查詢"01"課程比"02"課程成績高的學生的資訊及課程分數  

select student.*,s_score from student,score where c_id = 01 and score.s_id = student.s_id and score.s_id in(select s1.s_id from score s1,score s2 where s1.c_id = 01 and s2.c_id = 02 and s1.s_score >s2.s_score and s1.s_id = s2.s_id)

2 查詢平均成績大於等於60分的同學的學生編號和學生姓名和平均成績

select sc.s_id,st.s_name,avg(s_score) from score sc left join student st on sc.s_id = st.s_id group by sc.s_id having avg(s_score)>60 

3 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

select st.s_id,s_name,count(c_id) sum_course,sum(s_score) sum_score from student st left join score sc on sc.s_id = st.s_id group by st.s_id 

 

mysql練習題進階版