– 3.查询平均成绩大于等于60分的同学和学生编号和学生姓名和成绩 – select st.s_id,st.s_name,round(avg(sc.s_score),2) “平均成绩” from student st – left join score sc on sc.s_id = st.s_id – group by st.s_id having AVG(sc.s_score) >= 60 – 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩和无成绩的） – select st.s_id,st.s_name,(case when round(avg(sc.s_score),2) is null then 0 else round(avg(sc.s_score),2) end ) “平均成绩” from student st – left join score sc on sc.s_id = st.s_id – group by st.s_id having avg(sc.s_score) < 60 or avg(sc.s_score) is null – 5.查询所有的同学和学生编号，学生姓名，选课总数，所有课程的成绩 – select st.s_id,st.s_name,count(sc.c_id) “选课总数”,sum(case when sc.s_score is null then 0 else sc.s_score end) “总成绩” – from student st – left join score sc on st.s_id = sc.s_id – group by st.s_id – 6.查询“李”姓老师的数量 – select t.t_name,count(t.t_id) from teacher t – group by t.t_id having t.t_name like “李%” – 7.查询学过“张三”老师授课的同学的信息 – select st.* from student st – left join score sc on sc.s_id=st.s_id – left join course c on c.c_id=sc.c_id – left join teacher t on t.t_id=c.t_id – where t.t_name = “张三” – 8.查询没学习“张三”老师授课的同学的信息 – 张三老师教的课 (有错误) – select c.* from course c left join teacher t on t.t_id=c.t_id where t.t_name=“张三” – -- – 有张三老师的课成绩的st.s_id – select sc.s_id from score sc where sc.c_id in (select c.c_id from course c – left join teacher t on t.t_id=c.t_id – where t.t_name=“张三”) – -- 不在上面查找st.s_id 的学生信息,即没学过张三老师授课的同学信息 – select st.* from student st where st.s_id not in ( – select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher on t.t_id=c.t_id where t.t_name = “张三”) – ) – 9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 – select st.* from student st – inner join score sc on sc.s_id = st.s_id – inner join course c on c.c_id=sc.c_id and c.c_id=“01” – where st.s_id in ( – select st2.s_id from student st2 – inner join score sc2 on sc2.s_id = st2.s_id – inner join course c2 ON c2.c_id=sc2.c_id and c2.c_id=“02” – )
