mysql作业4

 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select st.*,sc1.s_Score as '语文',sc2.s_Score as '数学' from Student as st
left join Score as sc1 on sc1.s_id = st.s_id and sc1.c_id='01'
left join Score as sc2 on sc2.s_id = st.s_id and sc2.c_id='02'
where sc1.s_Score < sc2.s_Score;

 2、查询没有学全所有课程的同学的信息

select * from Student
where s_id not in 
(select s_id from Score group by s_id having count(c_id)= 3 );

 3、查询不同老师所教不同课程平均分从高到低显示。

select t.t_id, t.t_name, c.c_name, avg(s.s_Score)
from Teacher as t left outer join Course as c on t.t_id = c.t_id
left outer join Score as s on c.c_id = s.c_id
group by t.t_id, t.t_name, c.c_name
order by avg(s.s_Score) desc; 

 4、查询1990年出生的学生名单

 select * from Student where year(s_birth) = '1990';

 5、查询下月过生日的学生

select st.* from Student as st 
where (month(now()) + 1) % 12 = month(date_format(st.s_birth,'%Y%m%d'));