MySQL简单查询课堂实践答案_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