- 学生表st（id “学号”，name “姓名”）
- 分数表sc（sid “学号”， kid “科目id”， score “分数”）
- 科目表k（id “科目id”， name “科目名称”， tid “老师id”）
- 教师表t（id “教师id”， name “教师姓名”）
1.查询姓张的学生名单
select name
from st
where name like '张%';
2.查询姓李的老师的个数
select count(id)
from t
where name like '李%';
3.列出每个学生的平均成绩和姓名
select avg(sc.score),st.`name`
from sc
inner join st on st.id = sc.sid
group by st.id;
4.查询平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score)
from sc
group by sid
having avg(score) > 60;
5.查询出所有同学的学号、姓名、选课数、总成绩
select st.id,st.`name`,count(sc.kid),sum(sc.score)
from st
left join sc on st.id = sc.sid
group by st.id
6.查询每个同学的学习成绩总和，只查询总成绩大于300的学生
select st.*,sum(sc.score)
from st
left join sc on st.id = sc.sid
group by st.id
having sum(sc.score) > 300
7.查询没学过叶品老师的同学的学号、姓名
select *
from st
where id not in (
select sc.sid
from sc
inner join k on sc.kid = k.id
inner join t on t.id = k.tid
where t.`name` = '叶平'
);
8.列出有两门以上（含两门）不及格课程的学生姓名及平均成绩
select st.`name`,avg(sc.score)
from st
inner join sc on st.id = sc.sid
where st.id in(
select sid
from sc
where score < 60
group by sc.sid
having count(sc.kid)>=2
)
group by st.id
9.每门课程不及格人数大于2的课程信息
select k.*
from sc
inner join k on sc.kid = k.id
where sc.score < 60
group by sc.kid
having count(sc.sid) > 2
10.查询1课程比2课程成绩高的所有学生的学号
select s1.sid
from sc as s1
inner join sc as s2 on s1.sid = s2.sid
where s1.kid = 1 and s2.kid = 2 and s1.score > s2.score
11.每科成绩最好的学生及成绩信息
select st.name,sc.kid,sc.score
from sc
inner join st on st.id = sc.sid
inner join (
select sc.kid,max(sc.score) as score
from sc
group by sc.kid
) as res on sc.kid = res.kid and res.score = sc.score
12.查询选修叶平老师所授课程的学生中，成绩最高的学生姓名及成绩
select st.name,sc.score
from st
inner join sc on st.id = sc.sid
inner join k on sc.kid = k.id
inner join t on t.id = k.tid
where t.`name` = '叶平'
order by score desc
limit 1
13.查出每门课成绩都大于80的学生姓名
#第一种解法：前提是所有人每门课都有分数
select st.`name`
from st
where st.id not in (
select sid
from sc
where sc.score < 80
group by sc.sid
)
#第二种解法：最小分数大于80，即所有成绩都大于80
select st.name
from sc
inner join st on st.id = sc.sid
group by sc.sid
having min(sc.score) > 80
