创建大学数据库多表查询

假设我们有以下几个表：

1. 学生表 (students)
- student_id: 学生ID (主键)
- name: 学生姓名
- gender: 学生性别
- major_id: 所学专业ID (外键)

2. 专业表 (majors)
- major_id: 专业ID (主键)
- major_name: 专业名称

3. 课程表 (courses)
- course_id: 课程ID (主键)
- course_name: 课程名称
- credit: 学分

4. 学生选课表 (student_courses)
- student_id: 学生ID (外键)
- course_id: 课程ID (外键)
- score: 成绩

5. 教师表 (teachers)
- teacher_id: 教师ID (主键)
- name: 教师姓名

6. 课程授课表 (course_teachers)
- course_id: 课程ID (外键)
- teacher_id: 教师ID (外键)

以下是几个常见的多表查询：

1. 查询某个学生所选的课程及成绩

SELECT courses.course_name, student_courses.score
FROM student_courses
INNER JOIN courses ON student_courses.course_id = courses.course_id
WHERE student_courses.student_id = [学生ID];

2. 查询某个专业的所有学生及其所选的课程

SELECT students.name, courses.course_name
FROM students
INNER JOIN student_courses ON students.student_id = student_courses.student_id
INNER JOIN courses ON student_courses.course_id = courses.course_id
WHERE students.major_id = [专业ID];

3. 查询某门课程的所有学生及其成绩

SELECT students.name, student_courses.score
FROM students
INNER JOIN student_courses ON students.student_id = student_courses.student_id
WHERE student_courses.course_id = [课程ID];

4. 查询某门课程的授课教师及其所在的专业

SELECT teachers.name, majors.major_name
FROM course_teachers
INNER JOIN teachers ON course_teachers.teacher_id = teachers.teacher_id
INNER JOIN courses ON course_teachers.course_id = courses.course_id
INNER JOIN majors ON courses.major_id = majors.major_id
WHERE courses.course_id = [课程ID];

5. 查询某个学生的平均成绩

SELECT AVG(score) AS avg_score
FROM student_courses
WHERE student_id = [学生ID];