MySQL数据库查询 多表查询练习题 及答案

一、题目

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

2、查询学生表中男女生各有多少人

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

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号,姓名,选课数,总成绩

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程的分数比生物课程的分数高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级

11、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录

13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询只选修了一门课程的学生学号和姓名

16、查询所有学生考出的成绩并按从高到低排序(成绩去重)

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的课程id、学生姓名和分数

21、查询不同课程但成绩相同的课程号、学生号、成绩

22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称

23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名

24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数

二、答案

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 = 100
AND 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、 查询物理课程的分数比生物课程的分数高的学生的学号
SELECT
t1.student_id
FROM
(
SELECT
student_id,
num
FROM
score
LEFT JOIN course ON score.course_id = course.cid
WHERE
cname = ‘物理’
) t1
LEFT JOIN (
SELECT
student_id,
num
FROM
score
LEFT JOIN course ON score.course_id = course.cid
WHERE
cname = ‘生物’
) t2 ON t1.student_id = t2.student_id
WHERE
t1.num > t2.num;

9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的,思路是得到物理+体育课程的学生信息表,然后基于学生分组,统计count(课程)=1)
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN (‘物理’, ‘体育’)
)
GROUP BY
student_id
HAVING
count(student_id) = 1
);

10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表,然后对学生进行分组,统计课程数目>=2)

方式一:

SELECT
t1.sname,
t2.caption
FROM
(
SELECT
class_id,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) > 1
)
) t1
INNER JOIN (SELECT cid, caption FROM class) t2 ON t1.class_id = t2.cid;

方式二:

SELECT
student.sname,
class.caption
FROM
student
INNER JOIN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;

11、查询选修了所有课程的学生姓名(先从course表统计课程的总数,然后基于score表按照student_id分组,统计课程数据等于课程总数即可)
SELECT
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
count(course_id) = (
SELECT
count(cid) AS course_total
FROM
course
)
);

12、查询李平老师教的课程的所有成绩记录

方式一:

SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id = (
SELECT
tid
FROM
teacher
WHERE
tname = ‘李平’
)
);

方式二:

SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = ‘李平’
);

13、查询全部学生都选修了的课程号和课程名(先统计出有课程的学生总数,再以课程分组统计出有多少学生学,如果有课程跟学生总数相等,就说明这就是都选修的课程)
SELECT
cid,
cname
FROM
course
WHERE
cid IN (
SELECT
course_id
FROM
score
GROUP BY
course_id
HAVING
count(student_id) = (
SELECT
count(DISTINCT student_id)
FROM
score
)
)

14、查询每门课程被选修的次数
SELECT
course_id,
count(student_id)
FROM
score
GROUP BY
course_id;

15、查询只选修了一门课程的学生学号和姓名
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
count(course_id) = 1
);

16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
SELECT DISTINCT
num
FROM
score
ORDER BY
num DESC;

17、查询平均成绩大于85的学生姓名和平均成绩
SELECT
sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num) > 85
) t1 ON student.sid = t1.student_id;

18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
sname,
t1.num
FROM
student
INNER JOIN (
SELECT
student_id,
num
FROM
score
WHERE
course_id = (
SELECT
cid
FROM
course
WHERE
cname = ‘生物’
)
HAVING
num < 60
) t1 ON student.sid = t1.student_id;

19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名
SELECT
sname
FROM
student
WHERE
sid = (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
teacher
INNER JOIN course ON teacher.tid = course.teacher_id
WHERE
teacher.tname = ‘李平’
)
GROUP BY
student_id
ORDER BY
avg(num) DESC
LIMIT 1
);

20、查询每门课程成绩最好的课程id、学生姓名和分数
SELECT
course_id,
sname,
num
FROM
student
INNER JOIN (
SELECT
t1.course_id,
student_id,
num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) AS max_num
FROM
score
GROUP BY
course_id
) t1 ON score.course_id = t1.course_id
WHERE
score.num = t1.max_num
) t2 ON student.sid = t2.student_id;

21、查询不同课程但成绩相同的课程号、学生号、成绩
SELECT DISTINCT
s1.course_id,
s2.course_id,
s1.student_id,
s2.student_id,
s1.num,
s2.num
FROM
score AS s1,
score AS s2
WHERE
s1.num = s2.num
AND s1.course_id != s2.course_id;

22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称
SELECT
student.sname,
course.cname
FROM
student
INNER JOIN (
SELECT
student_id,
course_id
FROM
score
WHERE
student_id IN (
SELECT
sid
FROM
student
WHERE
sid NOT IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
tname = ‘李平’
)
)
)
) t1 ON student.sid = t1.student_id
INNER JOIN course ON course.cid = t1.course_id;

23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course_id
FROM
score
WHERE
student_id = 2
)
)
AND sid != 2;

24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数
SELECT
t1.course_id,
student.sname,
num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) AS max_num
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id = (
SELECT
teacher_id
FROM
course
GROUP BY
teacher_id
ORDER BY
count(teacher_id) DESC
LIMIT 1
)
)
GROUP BY
course_id
) t1 ON score.course_id = t1.course_id
INNER JOIN student ON score.student_id = student.sid
WHERE
score.course_id = t1.course_id
AND num = t1.max_num;

  • 10
    点赞
  • 61
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值