答案（自用，可能有误）

原创已于 2025-11-28 20:49:41 修改 · 370 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #java

于 2025-11-28 20:48:42 首次发布

题（无答案）

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

SELECT s.*, sc1.s_score AS '01课程分数', sc2.s_score AS '02课程分数'
FROM tb_student s
JOIN tb_score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN tb_score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE sc1.s_score > sc2.s_score;

2、查询"01"课程成绩低于"02"课程的学生信息及分数

SELECT s.*, sc1.s_score AS '01课程分数', sc2.s_score AS '02课程分数'
FROM tb_student s
JOIN tb_score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN tb_score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE sc1.s_score < sc2.s_score;

3、查询平均成绩≥60分的学生信息

SELECT s.s_id, s.s_name, AVG(sc.s_score) AS '平均成绩'
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score) >= 60;

4、查询平均成绩<60分的学生信息（含无成绩学生）

SELECT s.s_id, s.s_name, IFNULL(AVG(sc.s_score), 0) AS '平均成绩'
FROM tb_student s
LEFT JOIN tb_score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
HAVING IFNULL(AVG(sc.s_score), 0) < 60;

5、查询所有学生的选课总数和总成绩

SELECT s.s_id, s.s_name, COUNT(sc.c_id) AS 选课总数, IFNULL(SUM(sc.s_score), 0) AS 总成绩
FROM tb_student s
LEFT JOIN tb_score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name;

6、查询李姓老师数量

SELECT COUNT(t_id) AS 李姓老师数量
FROM tb_teacher
WHERE t_name LIKE '李%';

7、查询学过张三老师课程的学生信息

SELECT DISTINCT s.*
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
JOIN tb_course c ON sc.c_id = c.c_id
JOIN tb_teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三';

8、查询未学过张三老师课程的学生

SELECT s.*
FROM tb_student s
WHERE s.s_id NOT IN (
    SELECT DISTINCT sc.s_id
    FROM tb_score sc
    JOIN tb_course c ON sc.c_id = c.c_id
    JOIN tb_teacher t ON c.t_id = t.t_id
    WHERE t.t_name = '张三'
);

9、查询同时学习01和02课程的学生

SELECT s.*
FROM tb_student s
JOIN tb_score sc1 ON s.s_id = sc1.s_id AND sc1.c_id = '01'
JOIN tb_score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02';

10、查询学习01但未学习02课程的学生

SELECT s.*
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id AND sc.c_id = '01'
WHERE s.s_id NOT IN (
    SELECT sc2.s_id 
    FROM tb_score sc2 
    WHERE sc2.c_id = '02'
);

11、查询未学完全部课程的学生

SELECT s.*
FROM tb_student s
LEFT JOIN tb_score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING COUNT(DISTINCT sc.c_id) < (SELECT COUNT(*) FROM tb_course);

12、查询与01同学有相同课程的学生

SELECT DISTINCT s.*
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
JOIN tb_score sc01 ON sc.c_id = sc01.c_id AND sc01.s_id = '01'
WHERE s.s_id != '01';

13、查询与01同学课程完全相同的同学

SELECT s.*
FROM student s
JOIN score sc ON s.s_id=sc.s_id AND sc.s_id<>'01'
GROUP BY s.s_id
HAVING GROUP_CONCAT(sc.c_id ORDER BY sc.c_id)=
(SELECT GROUP_CONCAT(c_id ORDER BY c_id) FROM score WHERE s_id='01' GROUP BY s_id);

14、查询未修过张三老师课程的学生

SELECT s_name
FROM tb_student
WHERE s_id NOT IN (
    SELECT sc.s_id
    FROM tb_score sc
    JOIN tb_course c ON sc.c_id = c.c_id
    JOIN tb_teacher t ON c.t_id = t.t_id
    WHERE t.t_name = '张三'
);

15、查询两门及以上不及格课程的学生

SELECT s.s_id, s.s_name, AVG(sc.s_score) AS 平均成绩
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
WHERE sc.s_score < 60
GROUP BY s.s_id
HAVING COUNT(*) >= 2;

16、查询01课程不及格学生（按分数降序）

SELECT s.*, sc.s_score
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id AND sc.c_id = '01'
WHERE sc.s_score < 60
ORDER BY sc.s_score DESC;

17、按平均成绩降序显示学生成绩

SELECT s.s_id, s.s_name,
(SELECT sc1.s_score FROM tb_score sc1 WHERE sc1.s_id = s.s_id AND sc1.c_id = '01') AS 语文,
(SELECT sc2.s_score FROM tb_score sc2 WHERE sc2.s_id = s.s_id AND sc2.c_id = '02') AS 数学,
(SELECT sc3.s_score FROM tb_score sc3 WHERE sc3.s_id = s.s_id AND sc3.c_id = '03') AS 英语,
IFNULL(AVG(sc.s_score), 0) AS 平均成绩
FROM tb_student s
LEFT JOIN tb_score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
ORDER BY 平均成绩 DESC;

18、查询各科成绩统计信息

SELECT c.c_id, c.c_name, MAX(sc.s_score) AS 最高分, MIN(sc.s_score) AS 最低分,
AVG(sc.s_score) AS 平均分,
SUM(CASE WHEN sc.s_score>=60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS 及格率,
SUM(CASE WHEN sc.s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END)/COUNT(*)*100 AS 中等率,
SUM(CASE WHEN sc.s_score BETWEEN 80 AND 90 THEN 1 ELSE 0 END)/COUNT(*)*100 AS 优良率,
SUM(CASE WHEN sc.s_score>=90 THEN 1 ELSE 0 END)/COUNT(*)*100 AS 优秀率
FROM tb_course c
JOIN tb_score sc ON c.c_id = sc.c_id
GROUP BY c.c_id;

#19、按照各科成绩排序并显示排名

SELECT 
    c.c_name AS 课程名称,
    s.s_id AS 学生编号,
    s.s_name AS 学生姓名,
    sc.s_score AS 成绩,
    (SELECT COUNT(sc2.s_score) 
     FROM tb_score sc2 
     WHERE sc2.c_id = sc.c_id AND sc2.s_score > sc.s_score) + 1 AS 排名
FROM tb_score sc
JOIN tb_student s ON sc.s_id = s.s_id
JOIN tb_course c ON sc.c_id = c.c_id
ORDER BY c.c_name, 排名 ASC;

#20、查询学生总成绩及排名

SELECT 
    s.s_name,
    SUM(sc.s_score) AS '总成绩',
    ROW_NUMBER() OVER(ORDER BY SUM(sc.s_score) DESC) AS '排名'
FROM Student s 
LEFT JOIN Score sc ON s.s_id=sc.s_id
GROUP BY s.s_name;

#21、查询不同老师所教课程的平均分(从高到低)

SELECT 
    t.t_name AS 老师姓名,
    c.c_name AS 课程名称,
    AVG(sc.s_score) AS 平均成绩
FROM tb_teacher t
JOIN tb_course c ON t.t_id = c.t_id
JOIN tb_score sc ON c.c_id = sc.c_id
GROUP BY t.t_name, c.c_name
ORDER BY 平均成绩 DESC;

#22、查询课程成绩第2-3名的学生信息

SELECT 
    c.c_name AS 课程名称,
    s.s_id AS 学生编号,
    s.s_name AS 学生姓名,
    sc.s_score AS 成绩,
    (SELECT COUNT(sc2.s_score) 
     FROM tb_score sc2 
     WHERE sc2.c_id = sc.c_id AND sc2.s_score > sc.s_score) + 1 AS 排名
FROM tb_score sc
JOIN tb_student s ON sc.s_id = s.s_id
JOIN tb_course c ON sc.c_id = c.c_id
WHERE (SELECT COUNT(sc2.s_score) 
       FROM tb_score sc2 
       WHERE sc2.c_id = sc.c_id AND sc2.s_score > sc.s_score) + 1 BETWEEN 2 AND 3
ORDER BY c.c_name, 排名;

#23、统计各科成绩分数段人数及百分比

SELECT 
    c.c_id AS 课程编号,
    c.c_name AS 课程名称,
    SUM(CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]人数',
    SUM(CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '[100-85]百分比',
    SUM(CASE WHEN sc.s_score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS '[85-70]人数',
    SUM(CASE WHEN sc.s_score BETWEEN 70 AND 84 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '[85-70]百分比',
    SUM(CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS '[70-60]人数',
    SUM(CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '[70-60]百分比',
    SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) AS '[0-60]人数',
    SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '[0-60]百分比'
FROM tb_course c
JOIN tb_score sc ON c.c_id = sc.c_id
GROUP BY c.c_id, c.c_name;

#24、查询学生平均成绩及名次

SELECT 
    s.s_name,
    ROUND(AVG(sc.s_score),2) AS '总成绩',
    ROW_NUMBER() OVER(ORDER BY AVG(sc.s_score) DESC) AS '排名'
FROM Student s 
LEFT JOIN Score sc ON s.s_id=sc.s_id
GROUP BY s.s_name;

#25、查询各科成绩前三名的记录

SELECT 
    r.c_name,
    r.rank_num,
    s.s_name,
    r.s_score
FROM (
    SELECT 
        c.c_name,
        sc.s_id,
        sc.s_score,
        ROW_NUMBER() OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num
    FROM Course c
    LEFT JOIN Score sc ON c.c_id=sc.c_id
) r
JOIN Student s ON r.s_id=s.s_id AND r.rank_num<=3;

#26、查询每门课选修学生数

SELECT 
    c.c_id AS 课程编号,
    c.c_name AS 课程名称,
    COUNT(sc.s_id) AS 选修人数
FROM tb_course c
LEFT JOIN tb_score sc ON c.c_id = sc.c_id 
GROUP BY c.c_id, c.c_name;

#27、查询选修两门课程的学生

SELECT 
    s.s_id AS 学生编号,
    s.s_name AS 学生姓名
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id 
GROUP BY s.s_id, s.s_name
HAVING COUNT(sc.c_id) = 2;

#28、查询男女生人数

SELECT 
    s_sex AS 性别,
    COUNT(s_id) AS 人数
FROM tb_student
GROUP BY s_sex;

#29、查询名字含"风"的学生

SELECT *
FROM tb_student
WHERE s_name LIKE '%风%';

#30、查询同名同性的学生

SELECT 
    s_name AS 姓名,
    s_sex AS 性别,
    COUNT(s_id) AS 同名人数
FROM tb_student
GROUP BY s_name, s_sex;

#31、查询1990年出生的学生

SELECT * 
FROM tb_student 
WHERE YEAR(s_birth) = 1990;

#32、查询课程平均成绩(降序排列)

SELECT 
    c.c_id AS 课程编号,
    c.c_name AS 课程名称,
    AVG(sc.s_score) AS 平均成绩
FROM tb_course c
JOIN tb_score sc ON c.c_id = sc.c_id
GROUP BY c.c_id
ORDER BY 平均成绩 DESC, c.c_id ASC;

#33、查询平均成绩≥85的学生

SELECT 
    s.s_id AS 学生编号,
    s.s_name AS 学生姓名,
    AVG(sc.s_score) AS 平均成绩
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING AVG(sc.s_score) >= 85;

#34、查询数学成绩<60的学生

SELECT 
    s.s_name AS 学生姓名,
    sc.s_score AS 分数
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
JOIN tb_course c ON sc.c_id = c.c_id
WHERE c.c_name = '数学'  
AND sc.s_score < 60;

#35、查询所有学生的课程及分数

SELECT 
    s.s_id AS 学生编号,
    s.s_name AS 学生姓名,
    c.c_name AS 课程名称,
    IFNULL(sc.s_score, '0') AS 分数 
FROM tb_student s
JOIN tb_course c  
LEFT JOIN tb_score sc ON s.s_id = sc.s_id AND c.c_id = sc.c_id  
ORDER BY s.s_id, c.c_id;

#36、查询课程成绩>70的学生

SELECT 
    s.s_name AS 学生姓名,
    c.c_name AS 课程名称,
    sc.s_score AS 分数
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
JOIN tb_course c ON sc.c_id = c.c_id
WHERE sc.s_score > 70;

37、查询不及格课程

SELECT 
    s.s_id AS 学生编号,
    s.s_name AS 学生姓名,
    c.c_name AS 课程名称,
    sc.s_score AS 分数
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
JOIN tb_course c ON sc.c_id = c.c_id
WHERE sc.s_score < 60;

38、查询课程01成绩≥80的学生

SELECT 
    s.s_id AS 学生编号,
    s.s_name AS 学生姓名
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
WHERE sc.c_id = '01' 
AND sc.s_score >= 80;

39、每门课程的学生人数

SELECT 
    c.c_id AS 课程编号,
    c.c_name AS 课程名称,
    COUNT(sc.s_id) AS 学生人数
FROM tb_course c
LEFT JOIN tb_score sc ON c.c_id = sc.c_id
GROUP BY c.c_id, c.c_name;

40、查询张三老师课程最高分学生

SELECT 
    s.*,
    c.c_name AS 课程名称,
    sc.s_score AS 分数
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
JOIN tb_course c ON sc.c_id = c.c_id
JOIN tb_teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三'  
AND sc.s_score = (
    SELECT MAX(sc2.s_score)
    FROM tb_score sc2
    JOIN tb_course c2 ON sc2.c_id = c2.c_id
    JOIN tb_teacher t2 ON c2.t_id = t2.t_id
    WHERE t2.t_name = '张三'
);

41、查询不同课程同分学生

SELECT DISTINCT 
    sc1.s_id AS 学生编号,
    sc1.c_id AS 课程编号1,
    sc2.c_id AS 课程编号2,
    sc1.s_score AS 成绩
FROM tb_score sc1
JOIN tb_score sc2 ON sc1.s_id = sc2.s_id 
AND sc1.c_id != sc2.c_id 
AND sc1.s_score = sc2.s_score;

42、查询每门课程前两名

SELECT r.*
FROM(
    SELECT 
        c_name,
        s_id,
        s_score,
        ROW_NUMBER() OVER(PARTITION BY c_name ORDER BY s_score DESC) AS rank_num
    FROM tb_score sc 
    JOIN tb_course c ON sc.c_id=c.c_id
) r
WHERE r.rank_num <= 2;

43、统计热门课程(选修人数>5)

SELECT 
    c.c_id AS 课程编号,
    COUNT(sc.s_id) AS 选修人数
FROM tb_course c
JOIN tb_score sc ON c.c_id = sc.c_id  
GROUP BY c.c_id
HAVING COUNT(sc.s_id) > 5
ORDER BY 选修人数 DESC, c.c_id ASC;

44、查询选修≥2门课程的学生

SELECT 
    s_id AS 学生编号
FROM tb_score
GROUP BY s_id
HAVING COUNT(c_id) >= 2;

45、查询选修全部课程的学生

SELECT s.*
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING COUNT(DISTINCT sc.c_id) = (SELECT COUNT(*) FROM tb_course);

46、计算学生年龄

SELECT 
    s_name AS 姓名,
    s_birth AS 出生日期,
    YEAR(CURDATE()) - YEAR(s_birth) - 
    CASE WHEN DATE_FORMAT(CURDATE(), '%m-%d') < DATE_FORMAT(s_birth, '%m-%d') 
    THEN 1 ELSE 0 END AS 年龄
FROM tb_student;

47、查询本周生日学生

SELECT *
FROM tb_student
WHERE DATE_FORMAT(s_birth, '%m-%d') 
BETWEEN DATE_FORMAT(CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY, '%m-%d')  
AND DATE_FORMAT(CURDATE() + INTERVAL (6 - WEEKDAY(CURDATE())) DAY, '%m-%d');

48、查询下周生日学生

SELECT *
FROM tb_student
WHERE DATE_FORMAT(s_birth, '%m-%d') 
BETWEEN DATE_FORMAT(CURDATE() + INTERVAL (7 - WEEKDAY(CURDATE())) DAY, '%m-%d')
AND DATE_FORMAT(CURDATE() + INTERVAL (13 - WEEKDAY(CURDATE())) DAY, '%m-%d');

49、查询本月生日学生

SELECT * FROM tb_student
WHERE MONTH(s_birth) = MONTH(CURDATE());

50、查询下月生日学生

SELECT * FROM tb_student
WHERE MONTH(s_birth) = MONTH(CURDATE()) + 1;