mysql数据库期末考试题

好的，以下是一道可能的MySQL数据库期末考试题：

考试题目：

一个学生选课的数据库包含两张表：学生表（student）和选课表（course_student）。学生表包含学生的id、姓名、性别和出生日期等信息；选课表包含选课记录的id、学生id和课程id等信息。请根据以下要求，编写SQL语句查询对应的结果。

1. 查询每个学生选了多少门课程，并按照选课门数从多到少排序。

2. 查询每个学生所选的课程名称和任课教师，并按照学生id升序排序。

3. 查询选了“数学”课程的学生的姓名和选课门数，并按照选课门数从多到少排序。

4. 查询选了“数学”课程但未选“英语”课程的学生的姓名和选课门数，并按照选课门数从多到少排序。

5. 查询选课门数排名前三的学生的id、姓名和选课门数。

考试答案：

1.

SELECT cs.student_id, COUNT(*) AS course_count
FROM course_student AS cs
GROUP BY cs.student_id
ORDER BY course_count DESC;

2.

SELECT s.id, s.name, c.name AS course_name, c.teacher
FROM student AS s
JOIN course_student AS cs ON s.id = cs.student_id
JOIN course AS c ON cs.course_id = c.id
ORDER BY s.id ASC;

3.

SELECT s.name, COUNT(*) AS course_count
FROM student AS s
JOIN course_student AS cs ON s.id = cs.student_id
JOIN course AS c ON cs.course_id = c.id
WHERE c.name = '数学'
GROUP BY s.id
ORDER BY course_count DESC;

4.

SELECT s.name, COUNT(*) AS course_count
FROM student AS s
JOIN course_student AS cs1 ON s.id = cs1.student_id
JOIN course AS c1 ON cs1.course_id = c1.id
LEFT JOIN course_student AS cs2 ON s.id = cs2.student_id AND cs2.course_id = (SELECT id FROM course WHERE name = '英语')
WHERE c1.name = '数学' AND cs2.course_id IS NULL
GROUP BY s.id
ORDER BY course_count DESC;

5.

SELECT s.id, s.name, COUNT(*) AS course_count
FROM student AS s
JOIN course_student AS cs ON s.id = cs.student_id
GROUP BY s.id
ORDER BY course_count DESC
LIMIT 3;

希望以上MySQL数据库期末考试题和答案能够对您有所帮助。