Mysql-通用类题目

数据库结构下载：course

1、检索分配有班级的学生信息

内连接：去掉所有不符合条件的记录

SELECT student_no, student_name, student_contact, student.class_no, class_name
	, department_name
FROM student
	JOIN classes ON student.class_no = classes.class_no;

2、检索所有学生对应的班级信息

外连接之左连接：保留表一的所有记录

SELECT student_no, student_name, student_contact, student.class_no, class_name
	, department_name
FROM student
	LEFT JOIN classes ON student.class_no = classes.class_no;

3、检索所有班级的学生信息

外连接之右连接：保留表二的所有记录

SELECT student_no, student_name, student_contact, student.class_no, class_name
	, department_name
FROM student
	RIGHT JOIN classes ON student.class_no = classes.class_no;

4、查询所有学生的成绩

SELECT student.student_no, student_name, course.course_no, course_name, score
FROM student
	INNER JOIN choose ON student.student_no = choose.student_no
	INNER JOIN course ON choose.course_no = course.course_no;

5、检索“2018 自动化1班”所有学生、所有课程的成绩

SELECT class_name, student.student_no, student_name, choose.course_no, course_name
	, score
FROM classes
	LEFT JOIN student ON classes.class_no = student.class_no
	JOIN choose ON student.student_no = choose.student_no
	JOIN course ON course.course_no = choose.course_no
WHERE class_name = '2018 自动化1班';

6、若choose表成绩为NULL时，代表该生缺考。统计Java语言程序设计课程中，参加考试的学生人数、缺考学生人数、缺考百分比。

SELECT course_name AS 课程, COUNT(choose_no) AS 参加考试人数
	, COUNT(choose_no) - COUNT(score) AS 缺考人数
	, (COUNT(choose_no) - COUNT(score)) / COUNT(choose_no) * 100 AS 缺考率
FROM choose
	JOIN course ON choose.course_no = course.course_no
WHERE choose.course_no = 1;

7、统计每个学生已经选修的课程数，该生最高分，最低分，总分及平均成绩

SELECT student.student_no, student_name, COUNT(course_no)
	, MAX(score), MIN(score)
	, SUM(score), AVG(score)
FROM student
	LEFT JOIN choose ON student.student_no = choose.student_no
GROUP BY student.student_no;

8、统计平均分高于70分的学生的已经选修的课程数，该生最高分，最低分，总分及平均成绩

SELECT student.student_no, student_name, COUNT(course_no)
	, MAX(score), MIN(score)
	, SUM(score), AVG(score)
FROM student
	LEFT JOIN choose ON student.student_no = choose.student_no
GROUP BY student.student_no
HAVING AVG(score) > 70;

9、统计所有班级的学生名单

SELECT class_name AS 班级名, GROUP_CONCAT(student_name) AS 学生名单, concat(student_name) AS 部分名单
FROM classes
	LEFT JOIN student ON student.class_no = classes.class_no
GROUP BY classes.class_no;

10、检索所有的学生及教师信息

SELECT student_no AS 编号, student_name AS 姓名, student_contact AS 联系方式
FROM student
UNION ALL
SELECT teacher_no, teacher_name, teacher_contact
FROM teacher

11、给定一门课程（如course_no=1），统计哪些学生选修了这门课程，查询结果先按照院系排序，院系相同的按照班级排序，班级相同的按照学号排序。

SELECT department_name, class_name, student.student_no, student_name, student_contact
FROM student
	JOIN classes ON student.class_no = classes.class_no
	JOIN choose ON student.student_no = choose.student_no
WHERE course_no = 1
ORDER BY department_name, class_name, student_no;

12、统计哪些课程已经报满，其它学生不能再选修。

SELECT course.course_no, course_name, teacher_name, up_limit, description
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 up_limit = COUNT(*);