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 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