Mysql-子查询类题目

数据库结构下载：course

1、检索成绩比学生张三平均分高的所有学生及课程信息。

SELECT class_name, student.student_no, student_name, course_name, score
FROM classes
	JOIN student ON student.class_no = classes.class_no
	JOIN choose ON choose.student_no = student.student_no
	JOIN course ON choose.course_no = course.course_no
WHERE score > (
	SELECT AVG(score)
	FROM student, choose
	WHERE student.student_no = choose.student_no
		AND student_name = '张三'
)

2、检索平均成绩比学生张三平均分高的所有学生及课程信息

SELECT class_name, student.student_no, student_name, course_name
	, AVG(score)
FROM classes
	JOIN student ON student.class_no = classes.class_no
	JOIN choose ON choose.student_no = student.student_no
	JOIN course ON choose.course_no = course.course_no
GROUP BY student.student_no
HAVING AVG(score) > (
	SELECT AVG(score)
	FROM choose
		JOIN student ON student.student_no = choose.student_no
	WHERE student_name = '张三'
)

3、检索没有申请选修课的教师信息

SELECT *
FROM teacher
WHERE teacher_no NOT IN (
	SELECT teacher.teacher_no
	FROM course
	WHERE course.teacher_no = teacher.teacher_no
);

4、检索尚未被任何学生选修的课程信息

SELECT *
FROM course
WHERE NOT EXISTS (
	SELECT *
	FROM choose
	WHERE course.course_no = choose.course_no
)

5、检索“2018 自动化2班”比“2018 自动化1班”最低分高的学生信息

SELECT student.student_no, student_name, class_name
FROM student
	JOIN classes ON student.class_no = classes.class_no
	JOIN choose ON choose.student_no = student.student_no
WHERE class_name = '2018 自动化2班'
	AND score > ANY (
		SELECT score
		FROM choose
			JOIN student ON student.student_no = choose.student_no
			JOIN classes ON classes.class_no = student.class_no
		WHERE class_name = '2018 自动化1班'
	)

6、检索“2018 自动化2班”比“2018 自动化1班”最高分高的学生信息

SELECT student.student_no, student_name, class_name
FROM student
	JOIN classes ON student.class_no = classes.class_no
	JOIN choose ON choose.student_no = student.student_no
WHERE class_name = '2018 自动化2班'
	AND score > ALL (
		SELECT score
		FROM choose
			JOIN student ON student.student_no = choose.student_no
			JOIN classes ON classes.class_no = student.class_no
		WHERE class_name = '2018 自动化1班'
	)

7、统计选修人数少于30人的所有课程信息

SELECT course.course_no, course_name, teacher_name, teacher_contact
	, COUNT(*) AS student_num
FROM choose
	JOIN course ON choose.course_no = course.course_no
	JOIN teacher ON teacher.teacher_no = course.teacher_no
GROUP BY course_no
HAVING COUNT(*) < 30
UNION ALL
SELECT course.course_no, course_name, teacher_name, teacher_contact, 0
FROM course
	JOIN teacher ON teacher.teacher_no = course.teacher_no
WHERE NOT EXISTS (
	SELECT *
	FROM choose
	WHERE course.course_no = choose.course_no
);

8、统计每一门课程已经有多少学生选修，还能供多少学生选修

SELECT course.course_no, course_name, teacher_name, up_limit
	, COUNT(*) AS student_num
	, up_limit - COUNT(*) AS available
FROM choose
	JOIN course ON choose.course_no = course.course_no
	JOIN teacher ON teacher.teacher_no = course.teacher_no
GROUP BY course_no
UNION ALL
SELECT course.course_no, course_name, teacher_name, up_limit, 0
	, up_limit
FROM course
	JOIN teacher ON teacher.teacher_no = course.teacher_no
WHERE NOT EXISTS (
	SELECT *
	FROM choose
	WHERE course.course_no = choose.course_no
)