二.答案

1. 查询所有学生的信息。

SELECT * FROM students;

2. 查询所有老师的信息。

SELECT * FROM teachers;

3. 查询所有课程的信息。

SELECT * FROM courses;

4. 查询所有学生的姓名和年龄。

SELECT name, age FROM students;

5. 查询所有选修“数据结构”课程的学生姓名。

SELECT s.name 
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '数据结构';

6. 查询选修了“高等数学”课程的学生姓名和成绩。

SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '高等数学';

7. 查询所有课程的课程名称和对应的老师姓名。

SELECT c.course_name, t.name
FROM courses c
JOIN teachers t ON c.teacher_id = t.teacher_id;

8. 查询选修了“量子力学”课程的学生姓名和成绩，并按照成绩从高到低排序。

SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '量子力学'
ORDER BY sc.grade DESC;

9. 查询每位学生的课程名称和成绩。

SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

10. 查询所有学生的平均成绩（按学生分组）。

SELECT s.name, AVG(sc.grade) AS avg_grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id;

11. 查询每门课程的平均成绩（按课程分组）。

SELECT c.course_name, AVG(sc.grade) AS avg_grade
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;

12. 查询成绩大于80的学生姓名和成绩。

SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.grade > 80;

13. 查询每位老师所教授的课程数量。

SELECT t.name, COUNT(c.course_id) AS course_count
FROM teachers t
LEFT JOIN courses c ON t.teacher_id = c.teacher_id
GROUP BY t.teacher_id;

14. 查询所有课程以及没有学生选修的课程（即没有出现在学生选课表中的课程）。

SELECT c.course_name
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
WHERE sc.student_id IS NULL;

15. 查询所有选修了“有机化学”课程的学生数量。

SELECT COUNT(DISTINCT sc.student_id) AS student_count
FROM student_courses sc
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '有机化学';

16. 查询所有学生的姓名及其所选的课程名称，要求每个学生至少选一门课程。

SELECT s.name, c.course_name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

17. 查询每门课程的最高分和最低分。

SELECT c.course_name, MAX(sc.grade) AS max_grade, MIN(sc.grade) AS min_grade
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;

18. 查询选修了最多课程的学生姓名。

SELECT s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
ORDER BY COUNT(sc.course_id) DESC
LIMIT 1;

19. 查询每位老师教授的课程及其学生的平均成绩（按老师分组）。

SELECT t.name AS teacher_name, c.course_name, AVG(sc.grade) AS avg_grade
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id;

20. 查询年龄大于20岁的学生所选的课程名称及其成绩。

SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE s.age > 20;

21. 查询所有学生的课程名称和成绩，要求每位学生每门课程的成绩显示在同一行。

SELECT s.name,
       MAX(CASE WHEN c.course_name = '数据结构' THEN sc.grade END) AS 数据结构,
       MAX(CASE WHEN c.course_name = '高等数学' THEN sc.grade END) AS 高等数学,
       MAX(CASE WHEN c.course_name = '量子力学' THEN sc.grade END) AS 量子力学,
       MAX(CASE WHEN c.course_name = '有机化学' THEN sc.grade END) AS 有机化学
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
GROUP BY s.student_id;

22. 查询每个学生的课程数量以及是否选修了“量子力学”课程。

SELECT s.name,
       COUNT(sc.course_id) AS course_count,
       MAX(CASE WHEN c.course_name = '量子力学' THEN '是' ELSE '否' END) AS 是否选修量子力学
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
GROUP BY s.student_id;

23. 查询成绩在85以上的学生，按课程名称排序，显示学生姓名和课程名称。

SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade >= 85
ORDER BY c.course_name;

24. 查询每门课程的平均成绩，并显示成绩低于平均成绩的学生。

SELECT s.name, c.course_name, sc.grade, avg_grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
JOIN (
    SELECT course_id, AVG(grade) AS avg_grade
    FROM student_courses
    GROUP BY course_id
) AS avg_scores ON c.course_id = avg_scores.course_id
WHERE sc.grade < avg_scores.avg_grade;

25. 查询每个学生在不同课程中的排名（按成绩从高到低排名），只显示前3名的学生。

SELECT s.name, c.course_name, sc.grade,
       RANK() OVER (PARTITION BY c.course_name ORDER BY sc.grade DESC) AS rank
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE RANK() OVER (PARTITION BY c.course_name ORDER BY sc.grade DESC) <= 3;

26. 查询每位老师教授的所有课程及其学生的平均成绩，并且按照教师的平均成绩排序。

SELECT t.name AS teacher_name, c.course_name, AVG(sc.grade) AS avg_grade
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id
ORDER BY AVG(sc.grade) DESC;

27. 查询选修最多课程的学生和最少课程的学生，并分别显示其所选的课程数量。

SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
ORDER BY course_count DESC
LIMIT 1;

SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
ORDER BY course_count ASC
LIMIT 1;

28. 查询每位学生及其选修课程的名称和成绩，只显示有成绩大于80的课程。

SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade > 80;

29. 查询每位学生的课程数量，只有选修课程数量大于等于2门的学生才显示。

SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(sc.course_id) >= 2;

30. 查询每门课程的学生数量，并显示学生人数超过2人的课程。

SELECT c.course_name, COUNT(sc.student_id) AS student_count
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id
HAVING COUNT(sc.student_id) > 2;

31. 查询所有没有选修任何课程的学生姓名。

SELECT s.name
FROM students s
LEFT JOIN student_courses sc ON s.student_id = sc.student_id
WHERE sc.course_id IS NULL;

32. 查询每个学生的排名（按成绩总和降序排列），并显示学生的总成绩。

SELECT s.name, SUM(sc.grade) AS total_score,
       RANK() OVER (ORDER BY SUM(sc.grade) DESC) AS rank
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id;

33. 查询每位学生选修的课程名称和成绩，并按照学生的总成绩从高到低排序。

SELECT s.name, c.course_name, sc.grade, SUM(sc.grade) OVER (PARTITION BY s.student_id) AS total_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
ORDER BY total_score DESC;

34. 查询每位老师教授的所有课程的学生人数，按学生人数降序排列。

SELECT t.name AS teacher_name, c.course_name, COUNT(sc.student_id) AS student_count
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id
ORDER BY student_count DESC;

35. 查询课程“数据结构”中，成绩大于90的学生的姓名和成绩。

SELECT s.name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE c.course_name = '数据结构' AND sc.grade > 90;

36. 查询选修了“量子力学”课程的学生，且他们的总成绩超过300的学生姓名。

SELECT s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
GROUP BY s.student_id
HAVING SUM(sc.grade) > 300 AND MAX(c.course_name) = '量子力学';

37. 查询每个学生的排名（按总成绩降序排列），显示排名、学生姓名和总成绩。

SELECT RANK() OVER (ORDER BY SUM(sc.grade) DESC) AS rank,
       s.name, SUM(sc.grade) AS total_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id;

38. 查询所有课程的名称以及每门课程成绩的标准差。

SELECT c.course_name, STDDEV(sc.grade) AS grade_stddev
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;

39. 查询所有教师教授的课程，以及这些课程的学生的最高成绩。

SELECT t.name AS teacher_name, c.course_name, MAX(sc.grade) AS max_grade
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY t.teacher_id, c.course_id;

40. 查询成绩在各自课程前3名的学生姓名和成绩。

SELECT s.name, c.course_name, sc.grade
FROM (
    SELECT student_id, course_id, grade,
           RANK() OVER (PARTITION BY course_id ORDER BY grade DESC) AS rank
    FROM student_courses
) AS ranked_sc
JOIN students s ON ranked_sc.student_id = s.student_id
JOIN courses c ON ranked_sc.course_id = c.course_id
WHERE ranked_sc.rank <= 3;

41. 查询选修了至少三门课程并且总成绩超过250分的学生姓名。

SELECT s.name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(sc.course_id) >= 3 AND SUM(sc.grade) > 250;

42. 查询每个学生的课程平均成绩，并显示成绩大于该学生平均成绩的课程名称及其成绩。

SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade > (
    SELECT AVG(grade)
    FROM student_courses
    WHERE student_id = s.student_id
)
ORDER BY s.name, c.course_name;

43. 查询每门课程中，选修该课程的学生中最高成绩和最低成绩之间的差异。

SELECT c.course_name, 
       (MAX(sc.grade) - MIN(sc.grade)) AS grade_difference
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;

44. 查询每个学生选修的课程数，并且筛选出那些选修课程数最多的学生。

SELECT s.name, COUNT(sc.course_id) AS course_count
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING COUNT(sc.course_id) = (
    SELECT MAX(course_count)
    FROM (
        SELECT COUNT(sc.course_id) AS course_count
        FROM students s
        JOIN student_courses sc ON s.student_id = sc.student_id
        GROUP BY s.student_id
    ) AS course_counts
);

45. 查询每位学生选修的课程和其成绩，显示每个学生每门课程的成绩排名（按成绩降序）。

SELECT s.name, c.course_name, sc.grade,
       RANK() OVER (PARTITION BY sc.course_id ORDER BY sc.grade DESC) AS rank
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

46. 查询成绩在班级中前10%的学生姓名和成绩（按课程分组）。

SELECT s.name, c.course_name, sc.grade
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.grade >= (
    SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY grade DESC)
    FROM student_courses
    WHERE course_id = c.course_id
)
ORDER BY c.course_name, sc.grade DESC;

47. 查询每位老师教授的课程及其所有学生的成绩，并显示每位学生的成绩在课程中的排名。

SELECT t.name AS teacher_name, c.course_name, s.name AS student_name, sc.grade,
       RANK() OVER (PARTITION BY c.course_id ORDER BY sc.grade DESC) AS grade_rank
FROM teachers t
JOIN courses c ON t.teacher_id = c.teacher_id
JOIN student_courses sc ON c.course_id = sc.course_id
JOIN students s ON sc.student_id = s.student_id;

48. 查询所有学生的总成绩，并显示那些成绩大于所有其他学生总成绩平均值的学生。

SELECT s.name, SUM(sc.grade) AS total_score
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
GROUP BY s.student_id
HAVING SUM(sc.grade) > (
    SELECT AVG(total_score)
    FROM (
        SELECT SUM(sc.grade) AS total_score
        FROM students s
        JOIN student_courses sc ON s.student_id = sc.student_id
        GROUP BY s.student_id
    ) AS total_scores
);

49. 查询每个课程的学生人数，成绩高于该课程平均成绩的学生的数量。

SELECT c.course_name, COUNT(sc.student_id) AS total_students,
       COUNT(CASE WHEN sc.grade > avg_grade THEN 1 END) AS above_average_students
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
JOIN (
    SELECT course_id, AVG(grade) AS avg_grade
    FROM student_courses
    GROUP BY course_id
) AS avg_scores ON c.course_id = avg_scores.course_id
GROUP BY c.course_id;

50. 查询每个学生的课程成绩，如果某个学生的某门课程成绩低于60分，显示其成绩“差”，否则显示其成绩为“合格”。

SELECT s.name, c.course_name, 
       CASE 
           WHEN sc.grade < 60 THEN '差'
           ELSE '合格'
       END AS grade_status
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;