MySQL数据库：11、多表查询练习题

MySQL数据库

一、多表练习题

"""
编写复杂的SQL不要想着一口气写完
	一定要先明确思路 然后一步步写一步步查一步步补
"""
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级

第一题

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

# 解题思路
    # 涉及表格：课程表、讲师表
    # 1、查看课程及讲师表数据
    -- SELECT * FROM course;
    -- select * from teacher;
    # 2、以课程表为主表，拼接课程与讲师表
    SELECT
        course.cname,
        teacher.tname 
    FROM
        course
        LEFT JOIN teacher ON course.teacher_id = tid;

第二题

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

# 解题思路
# 涉及表格：成绩表（score）、学生表（student）
# 1、以学生标号为准，进行分组，同时获取学生平均成绩进行赛选
-- select student_id,avg(num) as num from score GROUP BY student_id HAVING avg(num)>=80;
# 2、分析结果需要用到两张表，采用拼表
SELECT
	student.sname,
	t1.num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS num FROM score GROUP BY student_id HAVING avg( num )>= 80 ) AS t1 ON student.class_id = t1.student_id;

第三题：

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

# 解题思路：
# 涉及表格：讲师表、学生表、课程表、分数表
# 1、先获取到李平老师的编号
-- select tid from teacher where teacher.tname='李平老师';
# 2、获取到李平老师所教授的课程编号
-- select course.cid from course where course.teacher_id = (select tid from teacher where teacher.tname='李平老师');
# 3、通过课程编号获取分数表里没有报李平老师课程的学生编号 2、4
-- SELECT DISTINCT student_id FROM score where course_id IN ( select cid from course where teacher_id = (select tid from teacher where teacher.tname='李平老师'));

# 通过学生编号，获取学生姓名并去重
SELECT DISTINCT
	student.sname 
FROM
	student 
WHERE
	student.sid not IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
		score.course_id IN (
		SELECT
			course.cid 
		FROM
			course 
	WHERE
	course.teacher_id = ( SELECT tid FROM teacher WHERE teacher.tname = '李平老师' )));

第四题

-- 8、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表  学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
-- select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by student_id
-- having count(course_id) = 1;
# 7.子查询获取学生姓名即可
-- 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(course_id) = 1
-- 	) 

第五题

-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表	分数表 学生表 班级表
# 2.预览表数据
-- select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
-- select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
-- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
	student.sname,
	class.caption
FROM
	student
INNER JOIN class ON student.class_id = class.cid
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			num < 60
		GROUP BY
			student_id
		HAVING
			count(course_id) >= 2
	);