测试必会的sql查询

1.Course表
2.Score表
3.Student表
4.Teacher表
sql：
1、查询课程编号为“02”的总成绩
select * from Score where c_id=02；

2.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
select st.*,a.s_core,b.s_core
from Student st
inner join (select s_id,s_core from Score where c_id=‘01’) a
on st.s_id=a.s_id
inner join (select s_id,s_core from Score where c_id=‘02’) b
on a.s_id=b.s_id
where a.s_core>b.s_core;

3.查询没学过“张三”老师课的学生的学号、姓名
select st.s_id,st.s_name from Student st
where st.s_id not in
(select sc.s_id from Score sc inner join Course co
on sc.c_id=co.c_id
inner join Teacher te
on te.t_id=co.t_id
where te.t_name=‘张三’);

4.查询没有学全所有课的学生的学号、姓名
select st.s_id,st.s_name from Student st
inner join Score sc on st.s_id=sc.s_id
group by st.s_id having count(c_id)<(select count(distinct c_id)from Course);

5，