MySQL经典多表查询试题_mysql多表查询练习题（答案）

1、查询所有的课程的名称以及对应的任课老师姓名

SELECT

course.cname,

teacher.tname

FROM

course

INNER JOIN teacher ON course.teacher_id=teacher.tid;2、查询学生表中男女生各有多少人

SELECT

gender,

count(sid)

FROM

student

GROUP BY

gender;3、查询物理成绩等于100的学生的姓名

SELECT

sname

FROM

student

WHERE

sid IN (

SELECT

score.student_id

FROM

score

LEFT JOIN course ON score.course_id=course.cid

WHERE

score.num= 100AND course.cname= "物理");4、查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT

t1.sname,

t2.avg_num

FROM

student t1

INNER JOIN (

SELECT

student_id,

avg(num) avg_num

FROM

score

GROUP BY

student_id

HAVING

avg(num)> 80) t2 ON t1.sid=t2.student_id;5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)

SELECT

sid,

sname,

t1.count_course,

t1.sum_num

FROM

student

LEFT JOIN (

SELECT

student_id,

count(course_id) count_course,

sum(num) sum_num

FROM

score

GROUP BY

student_id

) t1 ON student.sid=t1.student_id;6、 查询姓李老师的个数

SELECT

count(tid)

FROM

teacher

WHERE

tname LIKE'李%';7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)

SELECT

sname

FROM

student

WHERE

sid NOT IN (

SELECT DISTINCT

student_id

FROM

score

WHERE

course_id IN (

SELECT

cid

FROM

course

WHERE

teacher_id=(

SELECT

tid

FROM

teacher

WHERE

tname= '李平')

)

);8、 查询物理课程的分数比生物课程