ER图
student表
score 表
1-- 查询学过"张三"老师授课的同学的信息
select s.* ,t_name from student s left join score sc on s.s_id = sc.s_id
left join course c on sc.c_id = c.c_id
left join teacher t on c.t_id = t.t_id
where t_name = '张三';
2-- 查询同名同性学生名单,并统计同名人数
select s_name,s_sex,count(s_name)
from student
group by s_name,s_sex
having count(s_name)>1;
3-- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select sc1.*
from score sc1 ,score sc2
where sc1.s_id=sc2.s_id and sc1.score=sc2.score and sc1.c_id<>sc2.c_id
group by sc1.s_id, sc1.c_id;
4 -- 查询每门课程成绩最好的前两名
select * from score sc1 where (select count(*) from score sc2 where sc1.c_id = sc2.c_id and
sc1.score <= sc2.score) <3
order by sc1.c_id , sc1.score desc;