SQL经典50题答案

以下是SQL经典50题的解答，涵盖了基础查询、连接、聚合、子查询等常见SQL操作。

基础查询

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT s.*, a.score AS score_01, b.score AS score_02
FROM Student s
JOIN SC a ON s.SId = a.SId AND a.CId = '01'
JOIN SC b ON s.SId = b.SId AND b.CId = '02'
WHERE a.score > b.score;

2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.SId, s.Sname, AVG(sc.score) AS avg_score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname
HAVING AVG(sc.score) >= 60;

3. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.SId, s.Sname, COUNT(sc.CId) AS course_count, SUM(sc.score) AS total_score
FROM Student s
LEFT JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname;

4. 查询"李"姓老师的数量

SELECT COUNT(*) AS li_count
FROM Teacher
WHERE Tname LIKE '李%';

5. 查询学过"张三"老师授课的同学的信息

SELECT DISTINCT s.*
FROM Student s
JOIN SC sc ON s.SId = sc.SId
JOIN Course c ON sc.CId = c.CId
JOIN Teacher t ON c.TId = t.TId
WHERE t.Tname = '张三';

连接查询

6. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s.*
FROM Student s
JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01'
JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02';

7. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT s.*
FROM Student s
JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01'
LEFT JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02'
WHERE sc2.SId IS NULL;

8. 查询没有学全所有课程的同学的信息

SELECT s.*
FROM Student s
LEFT JOIN (
    SELECT SId, COUNT(DISTINCT CId) AS course_count
    FROM SC
    GROUP BY SId
) sc ON s.SId = sc.SId
WHERE sc.course_count < (SELECT COUNT(*) FROM Course)
   OR sc.course_count IS NULL;

9. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT DISTINCT s.*
FROM Student s
JOIN SC sc ON s.SId = sc.SId
WHERE sc.CId IN (
    SELECT CId 
    FROM SC 
    WHERE SId = '01'
) AND s.SId != '01';

10. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT s.*
FROM Student s
WHERE s.SId IN (
    SELECT SId
    FROM SC
    WHERE SId != '01'
    GROUP BY SId
    HAVING COUNT(CId) = (SELECT COUNT(CId) FROM SC WHERE SId = '01')
    AND SUM(CASE WHEN CId IN (SELECT CId FROM SC WHERE SId = '01') THEN 1 ELSE 0 END) = 
        (SELECT COUNT(CId) FROM SC WHERE SId = '01')
);

聚合查询

11. 查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT s.Sname
FROM Student s
WHERE s.SId NOT IN (
    SELECT DISTINCT sc.SId
    FROM SC sc
    JOIN Course c ON sc.CId = c.CId
    JOIN Teacher t ON c.TId = t.TId
    WHERE t.Tname = '张三'
);

12. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.SId, s.Sname, AVG(sc.score) AS avg_score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
WHERE sc.score < 60
GROUP BY s.SId, s.Sname
HAVING COUNT(sc.CId) >= 2;

13. 检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT s.*, sc.score
FROM Student s
JOIN SC sc ON s.SId = sc.SId AND sc.CId = '01'
WHERE sc.score < 60
ORDER BY sc.score DESC;

14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s.SId, s.Sname, 
       MAX(CASE WHEN sc.CId = '01' THEN sc.score ELSE NULL END) AS score_01,
       MAX(CASE WHEN sc.CId = '02' THEN sc.score ELSE NULL END) AS score_02,
       MAX(CASE WHEN sc.CId = '03' THEN sc.score ELSE NULL END) AS score_03,
       AVG(sc.score) AS avg_score
FROM Student s
LEFT JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname
ORDER BY avg_score DESC;

15. 查询各科成绩最高分、最低分和平均分

SELECT c.CId, c.Cname, 
       MAX(sc.score) AS max_score,
       MIN(sc.score) AS min_score,
       AVG(sc.score) AS avg_score,
       COUNT(sc.SId) AS student_count,
       SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) AS pass_count,
       SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100 AS pass_rate
FROM Course c
LEFT JOIN SC sc ON c.CId = sc.CId
GROUP BY c.CId, c.Cname
ORDER BY c.CId;

子查询

16. 按各科成绩进行排序，并显示排名

SELECT sc1.CId, sc1.SId, sc1.score, COUNT(sc2.score) + 1 AS rank
FROM SC sc1
LEFT JOIN SC sc2 ON sc1.CId = sc2.CId AND sc2.score > sc1.score
GROUP BY sc1.CId, sc1.SId, sc1.score
ORDER BY sc1.CId, rank;

17. 查询学生的总成绩并进行排名

SELECT s.SId, s.Sname, SUM(sc.score) AS total_score, 
       RANK() OVER (ORDER BY SUM(sc.score) DESC) AS rank
FROM Student s
LEFT JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname
ORDER BY total_score DESC;

18. 查询不同老师所教不同课程平均分从高到低显示

SELECT t.TId, t.Tname, c.CId, c.Cname, AVG(sc.score) AS avg_score
FROM Teacher t
JOIN Course c ON t.TId = c.TId
LEFT JOIN SC sc ON c.CId = sc.CId
GROUP BY t.TId, t.Tname, c.CId, c.Cname
ORDER BY avg_score DESC;

19. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

WITH RankedScores AS (
    SELECT sc.CId, sc.SId, sc.score, 
           RANK() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS rank
    FROM SC sc
)
SELECT s.*, rs.CId, rs.score
FROM RankedScores rs
JOIN Student s ON rs.SId = s.SId
WHERE rs.rank BETWEEN 2 AND 3
ORDER BY rs.CId, rs.rank;

20. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[<60]

SELECT c.CId, c.Cname,
       SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]',
       SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85-70]',
       SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '[70-60]',
       SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS '[<60]'
FROM Course c
LEFT JOIN SC sc ON c.CId = sc.CId
GROUP BY c.CId, c.Cname;

高级查询

21. 查询学生平均成绩及其名次

SELECT s.SId, s.Sname, AVG(sc.score) AS avg_score,
       RANK() OVER (ORDER BY AVG(sc.score) DESC) AS rank
FROM Student s
LEFT JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname;

22. 查询各科成绩前三名的记录

WITH RankedScores AS (
    SELECT sc.CId, sc.SId, sc.score, 
           RANK() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS rank
    FROM SC sc
)
SELECT rs.CId, s.SId, s.Sname, rs.score, rs.rank
FROM RankedScores rs
JOIN Student s ON rs.SId = s.SId
WHERE rs.rank <= 3
ORDER BY rs.CId, rs.rank;

23. 查询每门课程被选修的学生数

SELECT c.CId, c.Cname, COUNT(sc.SId) AS student_count
FROM Course c
LEFT JOIN SC sc ON c.CId = sc.CId
GROUP BY c.CId, c.Cname;

24. 查询出只选修两门课程的学生学号和姓名

SELECT s.SId, s.Sname
FROM Student s
JOIN (
    SELECT SId, COUNT(CId) AS course_count
    FROM SC
    GROUP BY SId
    HAVING COUNT(CId) = 2
) sc ON s.SId = sc.SId;

25. 查询男生、女生人数

SELECT Ssex, COUNT(*) AS count
FROM Student
GROUP BY Ssex;

复杂查询

26. 查询名字中含有"风"字的学生信息

SELECT *
FROM Student
WHERE Sname LIKE '%风%';

27. 查询同名同性学生名单，并统计同名人数

SELECT Sname, Ssex, COUNT(*) AS count
FROM Student
GROUP BY Sname, Ssex
HAVING COUNT(*) > 1;

28. 查询1990年出生的学生名单

SELECT *
FROM Student
WHERE YEAR(Sage) = 1990;

29. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT CId, AVG(score) AS avg_score
FROM SC
GROUP BY CId
ORDER BY avg_score DESC, CId ASC;

30. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT s.SId, s.Sname, AVG(sc.score) AS avg_score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname
HAVING AVG(sc.score) >= 85;

其他查询

31. 查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT s.Sname, sc.score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
JOIN Course c ON sc.CId = c.CId
WHERE c.Cname = '数学' AND sc.score < 60;

32. 查询所有学生的课程及分数情况

SELECT s.SId, s.Sname,
       MAX(CASE WHEN c.Cname = '语文' THEN sc.score ELSE NULL END) AS '语文',
       MAX(CASE WHEN c.Cname = '数学' THEN sc.score ELSE NULL END) AS '数学',
       MAX(CASE WHEN c.Cname = '英语' THEN sc.score ELSE NULL END) AS '英语'
FROM Student s
LEFT JOIN SC sc ON s.SId = sc.SId
LEFT JOIN Course c ON sc.CId = c.CId
GROUP BY s.SId, s.Sname;

33. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT s.Sname, c.Cname, sc.score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
JOIN Course c ON sc.CId = c.CId
WHERE sc.score > 70;

34. 查询不及格的课程

SELECT s.SId, s.Sname, c.Cname, sc.score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
JOIN Course c ON sc.CId = c.CId
WHERE sc.score < 60;

35. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

SELECT s.SId, s.Sname
FROM Student s
JOIN SC sc ON s.SId = sc.SId
WHERE sc.CId = '01' AND sc.score > 80;

综合查询

36. 求每门课程的学生人数

SELECT CId, COUNT(SId) AS student_count
FROM SC
GROUP BY CId;

37. 成绩不重复，查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT s.*, sc.score
FROM Student s
JOIN SC sc ON s.SId = sc.SId
JOIN Course c ON sc.CId = c.CId
JOIN Teacher t ON c.TId = t.TId
WHERE t.Tname = '张三'
ORDER BY sc.score DESC
LIMIT 1;

38. 成绩有重复的情况下，查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

WITH TopScores AS (
    SELECT sc.SId, sc.score,
           RANK() OVER (ORDER BY sc.score DESC) AS rank
    FROM SC sc
    JOIN Course c ON sc.CId = c.CId
    JOIN Teacher t ON c.TId = t.TId
    WHERE t.Tname = '张三'
)
SELECT s.*, ts.score
FROM TopScores ts
JOIN Student s ON ts.SId = s.SId
WHERE ts.rank = 1;

39. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT a.SId, a.CId, a.score
FROM SC a
JOIN SC b ON a.SId = b.SId AND a.CId != b.CId AND a.score = b.score;

40. 查询每门课程成绩最好的前两名

WITH RankedScores AS (
    SELECT sc.CId, sc.SId, sc.score,
           ROW_NUMBER() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS rank
    FROM SC sc
)
SELECT rs.CId, s.Sname, rs.score, rs.rank
FROM RankedScores rs
JOIN Student s ON rs.SId = s.SId
WHERE rs.rank <= 2
ORDER BY rs.CId, rs.rank;

统计查询

41. 统计每门课程的学生选修人数（超过5人的课程才统计）

SELECT CId, COUNT(SId) AS student_count
FROM SC
GROUP BY CId
HAVING COUNT(SId) > 5;

42. 检索至少选修两门课程的学生学号

SELECT SId
FROM SC
GROUP BY SId
HAVING COUNT(CId) >= 2;

43. 查询选修了全部课程的学生信息

SELECT s.*
FROM Student s
JOIN (
    SELECT SId, COUNT(CId) AS course_count
    FROM SC
    GROUP BY SId
    HAVING COUNT(CId) = (SELECT COUNT(*) FROM Course)
) sc ON s.SId = sc.SId;

44. 查询各学生的年龄

SELECT SId, Sname, 
       TIMESTAMPDIFF(YEAR, Sage, CURDATE()) AS age
FROM Student;

45. 查询本周过生日的学生

SELECT *
FROM Student
WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(CURDATE());

复杂统计

46. 查询下周过生日的学生

SELECT *
FROM Student
WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(CURDATE()) + 1;

47. 查询本月过生日的学生

SELECT *
FROM Student
WHERE MONTH(Sage) = MONTH(CURDATE());

48. 查询下月过生日的学生

SELECT *
FROM Student
WHERE MONTH(Sage) = MONTH(CURDATE()) + 1;

49. 查询所有学生的学号、姓名、选课数、总成绩

SELECT s.SId, s.Sname, 
       COUNT(sc.CId) AS course_count,
       SUM(sc.score) AS total_score
FROM Student s
LEFT JOIN SC sc ON s.SId = sc.SId
GROUP BY s.SId, s.Sname;

50. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

```sql SELECT s.SId, s.Sname, AVG(sc.score