Mysql面试题

学生表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 st where name like ‘李%’;
3列出每个学生的平均成绩和姓名
select avg(sc.score),st.name from st inner join sc on st.id=sc.sid group by sid;
4查询平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score) from sc group by sid having avg (score)>60;
5查询所有同学的学号，姓名，选课数，总成绩
select st.id,st.name,avg(sc.score),count(sc.kid),sum(sc.score) from st inner join sc on st.id=sc.sid
6查询每个学生的学习成绩总和，只查询总成绩大于300的学生
select st.id,st.name,avg(sc.score),count(sc.kid),sum(sc.score) from st inner join sc on st.id=sc.sid
group by st.id having sum(sc.score)>300
7查询没学过”叶平“老师的课的同学的学号、姓名
【叶萍老师教了哪些课】select k.* from k inner join t on k.id=t.id where t.name=‘叶萍’
【哪些同学学了 叶萍老师的课程】select k.id from k inner join t on k.id=t.id INNER JOIN sc on k.id=sc.kid where t.name=‘叶萍’
【没选叶萍老师课程】用到了子查询 select * from st where id not in(select k.id from k inner join t on k.id=t.id INNER JOIN sc on k.id=sc.kid where t.name=‘叶萍’)
8列出有2门以上（含2门）不及格课程的学生姓名和平均成绩
select avg(score) ,st.name from sc inner join st on sc.sid=st.id where sid in(select sid from sc where score <60 group by sid having count(kid)>=2) group by sid
9每门课程不及格人数大于2的课程信息
select count(sid) ,kid,k.name from sc INNER JOIN k on k.id =sc.sid where score<60 group by kid having count(sid)>2
10查询”001“课程比“002”课程成绩高的所有的学生的学号（sno）
select s1.sid from sc s1 INNER JOIN sc s2 on s1.sid =s2.sid where s1.kid=1 and s2.kid=2 and s1.score>s2.score
11每科成绩最好的学生及成绩信息
select sc.score,st.name from sc INNER JOIN st on st.id =sc.sid inner join (select max(sc.score) score,kid from sc GROUP BY kid) t on sc.kid=t.kid and sc.score=t.score
12查询选修“叶平”老所授课程的学生中，成绩最高的学生姓名和成绩
SELECT st.name,sc.score from st
inner join sc on sc.sid=st.id
INNER JOIN k on k.id=sc.kid
INNER JOIN t on t.id=t.id
where t.name =‘叶萍’
order by score DESC limit 1
13查出没门课程成绩都大于80的学生姓名
select min(score) ,sid ,st.name
from sc inner join st on sc.sid =st.id GROUP BY sid having min(score)>80