50道经典SQL练习题题解

网上传的SQL练习题，自己写的不一定是最好的解法。自己是按顺序从头写道尾的，所以前面题的解法可能会复杂一点，但是可能是最好理解的。除了第九题不知道怎么去用比较简洁的方法去解，其他应该都是对的，就是有几题可能少了无关紧要的字段。

建表：

如何建表就不累述了，笔者用的是MySQL, Navicat Premium做的。
以下给出建表后的结果：
course：
课表
sc:
分数表
student:
学生信息表
teacher:
老师信息表

题目：

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT
    A.`s#`,
    A.sname,
    A.sage,
    A.ssex 
FROM
    (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
        `c#` = 1 
    ) A
    JOIN (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
        `c#` = 2 
    ) B ON A.`s#` = B.`s#` 
WHERE
    A.score > B.score
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT
    A.* 
FROM
    (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
        `c#` = 1 
    ) A
    LEFT JOIN (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
        `c#` = 2 
    ) B ON A.sname = B.sname 
WHERE
    A.sname IS NOT NULL 
    AND B.sname IS NOT NULL
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT
    B.* 
FROM
    (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
        `c#` = 1 
    ) A
    RIGHT JOIN (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
        `c#` = 2 
    ) B ON A.sname = B.sname 
WHERE
    A.sname IS NULL
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT
    A.* 
FROM
    (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
        `c#` = 1 
    ) A
    LEFT JOIN (
    SELECT
        student.*,
        sc.`c#`,
        sc.score 
    FROM
        sc
        LEFT JOIN student ON student.`s#` = sc.`s#` 
    WHERE
    `c#` = 2 
    ) B ON A.sname = B.sname
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT
    A.`s#`,
    B.sname,
    A.average 
FROM
    ( SELECT DISTINCT `s#`, AVG( score ) AS average FROM sc GROUP BY `s#` ) A
    JOIN student B ON A.`s#` = B.`s#` 
WHERE
    average >= 60
3. 查询在 SC 表存在成绩的学生信息
SELECT
    B.* 
FROM
    ( SELECT DISTINCT `s#` FROM sc ) A
    JOIN student B ON A.`s#` = B.`s#`
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT DISTINCT
    B.`s#`,
    B.sname,
    COUNT( A.`c#` ) AS course_num,
    SUM( A.score ) AS totle_score 
FROM
    sc A
    RIGHT JOIN student B ON A.`s#` = B.`s#` 
GROUP BY
    B.`s#`
4.1 查有成绩的学生信息
SELECT DISTINCT
    student.* 
FROM
    sc
    JOIN student ON sc.`s#` = student.`s#`
5. 查询「李」姓老师的数量
SELECT
    count( * ) AS teacher_num 
FROM
    teacher 
WHERE
    Tname LIKE '李%'
6. 查询学过「张三」老师授课的同学的信息
SELECT
    temp_2.*,
    teacher.Tname 
FROM
    (
    SELECT
        temp_1.*,
        course.cname,
        course.`t#` 
    FROM
        ( SELECT student.*, sc.`C#` FROM student JOIN sc ON student.`s#` = sc.`s#` ) temp_1
        JOIN course ON temp_1.`c#` = course.`c#` 
    ) temp_2
    JOIN teacher ON teacher.`T#` = temp_2.`T#` 
WHERE
    teacher.Tname LIKE '张三'
7. 查询没有学全所有课程的同学的信息
SELECT
    count( temp.`c#` ) AS course_count,
    temp.* 
FROM
    ( SELECT student.*, sc.`c#` FROM sc RIGHT JOIN student ON sc.`s#` = student.`s#` ) temp 
GROUP BY
    temp.sname 
HAVING
    course_count = 3
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT DISTINCT
    temp.`s#`,
    temp.sname,
    temp.sage,
    temp.ssex 
FROM
    ( SELECT student.*, sc.`C#` FROM student JOIN sc ON student.`s#` = sc.`S#` ) temp
    JOIN ( SELECT student.*, sc.`C#` FROM student JOIN sc ON student.`s#` = sc.`S#` WHERE student.`s#` = 1 ) temp_1 
WHERE
    temp_1.`C#` = temp.`C#`
9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT
    * 
FROM
    student 
WHERE
    `s#` NOT IN (
    SELECT
        sc.`s#` 
    FROM
        sc,
        course,
        teacher 
    WHERE
        sc.`c#` = course.`c#` 
        AND course.`t#` = teacher.`t#` 
    AND teacher.tname = "张三" 
    )
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
    temp_2.*,
    temp_1.avg_score 
FROM
    ( SELECT `s#`, AVG( score ) AS avg_score FROM sc GROUP BY `s#` ) temp_1,
    (
    SELECT
        student.`s#`,
        student.sname,
        SUM( CASE WHEN sc.score < 60 THEN 1 ELSE 0 END ) AS fail_num_course 
    FROM
        student,
        sc 
    WHERE
        student.`s#` = sc.`s#` 
    GROUP BY
        `s#` 
    ) temp_2 
WHERE
    temp_1.`s#` = temp_2.`s#` 
    AND temp_2.fail_num_course >= 2
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT
    * 
FROM
    student 
WHERE
    `s#` IN ( SELECT `s#` FROM sc WHERE score < 60 AND `c#` = 1 )
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
    sc.*,
    a_score.`avg_score` 
FROM
    sc
    LEFT JOIN ( SELECT *, AVG( `score` ) AS avg_score FROM `sc` GROUP BY `s#` ) a_score ON sc.`s#` = a_score.`s#` 
ORDER BY
    a_score.`avg_score` DESC
14. 查询各科成绩最高分、最低分和平均分:

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
    `c#`,
    MAX( score ) AS 最高分,
    MIN( score ) AS 最低分,
    AVG( score ) AS 平均分,
    SUM( CASE WHEN score >= 60 THEN 1 ELSE 0 END ) / COUNT( * ) AS 及格率,
    SUM( CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END ) / COUNT( * ) AS 中等率,
    SUM( CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END ) / COUNT( * ) AS 优良率,
    SUM( CASE WHEN score >= 90 THEN 1 ELSE 0 END ) / COUNT( * ) AS 优秀率,
    COUNT( * ) AS 选修人数 
FROM
    sc 
GROUP BY
    `c#`
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT
    A.`C#`,
    A.`S#`,
    score,排名 
FROM
    ( SELECT *, DENSE_rank ( ) over ( PARTITION BY `C#` ORDER BY score DESC ) 排名 FROM SC ) A
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT
    A.`s#`,
    A.`总分`,
    DENSE_RANK ( ) OVER ( ORDER BY A.总分 ) AS 排名 
FROM
    ( SELECT `s#`, SUM( score ) AS 总分 FROM sc GROUP BY `s#` ) A
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT
    A.`s#`,
    A.`总分`,
    RANK ( ) OVER ( ORDER BY A.总分 ) AS 排名 
FROM
    ( SELECT `s#`, SUM( score ) AS 总分 FROM sc GROUP BY `s#` ) A
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT
    `c#`,
    SUM( CASE WHEN score > 85 AND score <= 100 THEN 1 ELSE 0 END ) / COUNT( * ) AS `[100-80]`,
    SUM( CASE WHEN score > 70 AND score <= 80 THEN 1 ELSE 0 END ) / COUNT( * ) AS `[85-70]`,
    SUM( CASE WHEN score > 60 AND score <= 70 THEN 1 ELSE 0 END ) / COUNT( * ) AS `[70-60]`,
    SUM( CASE WHEN score >= 0 AND score <= 60 THEN 1 ELSE 0 END ) / COUNT( * ) AS `[60-0]` 
FROM
    sc 
GROUP BY
    `c#`
18. 查询各科成绩前三名的记录
SELECT
    * 
FROM
    ( SELECT *, DENSE_RANK ( ) OVER ( PARTITION BY `c#` ORDER BY score DESC ) AS ranking FROM sc ) AS temp 
WHERE
    temp.ranking <= 3
19. 查询每门课程被选修的学生数
SELECT
    `c#`,
    COUNT( * ) AS student_num 
FROM
    sc 
GROUP BY
    `c#`
20. 查询出只选修两门课程的学生学号和姓名
SELECT
    student.`s#`,
    student.sname,
    COUNT( `c#` ) AS num_of_course 
FROM
    student
    LEFT JOIN sc ON student.`s#` = sc.`s#` 
GROUP BY
    `s#`
21. 查询男生、女生人数
SELECT
    ssex,
    COUNT( ssex ) AS total_num 
FROM
    student 
GROUP BY
    ssex
22. 查询名字中含有「风」字的学生信息
SELECT
    * 
FROM
    student 
WHERE
    sname LIKE '%风%'
23. 查询同名同性学生名单，并统计同名人数
SELECT
    sname,
    COUNT( * ) AS 人数 
FROM
    student 
GROUP BY
    sname 
HAVING
    人数 >1
24. 查询 1990 年出生的学生名单
SELECT
    * 
FROM
    student 
WHERE
    DATE( sage ) BETWEEN '1990-01-01' 
    AND '1990-12-31'
25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
    `c#`,
    AVG( score ) AS avg_score 
FROM
    sc 
GROUP BY
    `c#` 
ORDER BY
    avg_score DESC,
    `c#` ASC
26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT
    student.*,
    AVG( sc.score ) AS avg_score 
FROM
    student,
    sc 
WHERE
    student.`s#` = sc.`s#` 
GROUP BY
    sc.`s#` 
HAVING
    avg_score >= 85
27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT
    student.sname,
    sc.score 
FROM
    student,
    sc,
    course 
WHERE
    student.`s#` = sc.`s#` 
    AND sc.`c#` = course.`c#` 
    AND sc.`c#` = 1 
    AND sc.score < 60
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT
    student.*,
    sc.`c#`,
    sc.score 
FROM
    student
    LEFT JOIN sc ON student.`s#` = sc.`s#`
29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT
    student.sname,
    sc.`c#`,
    sc.score 
FROM
    student,
    sc 
WHERE
    student.`s#` = sc.`s#` 
    AND sc.score >= 70
30. 查询不及格的课程
SELECT
    student.sname,
    sc.`c#`,
    sc.score 
FROM
    student,
    sc 
WHERE
    student.`s#` = sc.`s#` 
    AND sc.score <= 60
31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT
    student.`s#`,
    student.`sname` 
FROM
    student,
    sc 
WHERE
    student.`s#` = sc.`s#` 
    AND sc.`c#` = 1 
    AND sc.score >= 80
32. 求每门课程的学生人数
SELECT
    `c#`,
    COUNT( * ) AS student_num 
FROM
    sc 
GROUP BY
    `c#`
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT
    student.*,
    sc.score 
FROM
    student,
    sc,
    course,
    teacher 
WHERE
    student.`s#` = sc.`s#` 
    AND sc.`c#` = course.`c#` 
    AND course.`t#` = teacher.`t#` 
    AND teacher.`t#` = 1 
ORDER BY
    sc.score DESC 
    LIMIT 1 OFFSET 0
34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT
    student.*,
    sc.score 
FROM
    student,
    sc,
    course,
    teacher,
    (
    SELECT
        student.*,
        sc.score 
    FROM
        student,
        sc,
        course,
        teacher 
    WHERE
        student.`s#` = sc.`s#` 
        AND sc.`c#` = course.`c#` 
        AND course.`t#` = teacher.`t#` 
        AND teacher.`t#` = 1 
    ORDER BY
        sc.score DESC 
        LIMIT 1 OFFSET 0 
    ) temp 
WHERE
    student.`s#` = sc.`s#` 
    AND sc.`c#` = course.`c#` 
    AND course.`t#` = teacher.`t#` 
    AND teacher.`t#` = 1 
    AND temp.score = sc.score
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT
    student.`s#`,
    sc1.`c#`,
    sc1.score 
FROM
    student,
    sc AS sc1,
    sc AS sc2,
    sc AS sc3 
WHERE
    student.`s#` = sc1.`s#` 
    AND sc1.`s#` = sc2.`s#` 
    AND sc2.`s#` = sc3.`s#` 
    AND sc1.`c#` != sc2.`c#` 
    AND sc2.`c#` != sc3.`c#` 
    AND sc1.score = sc2.score 
    AND sc2.score = sc3.score

SELECT DISTINCT
    a.* 
FROM
    sc AS a
    JOIN sc AS b ON a.`s#` = b.`s#` 
    AND a.`c#` != b.`c#` 
    AND a.score = b.score
36. 查询每门功成绩最好的前两名
SELECT
    * 
FROM
    ( SELECT *, DENSE_RANK ( ) OVER ( PARTITION BY `c#` ORDER BY score ) AS ranking FROM sc ) temp 
WHERE
    temp.ranking <= 2
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT
    temp.* 
FROM
    ( SELECT `c#`, COUNT( * ) AS num_student FROM sc GROUP BY `c#` ) temp 
WHERE
    temp.num_student >= 5
38. 检索至少选修两门课程的学生学号
SELECT
    `s#` 
FROM
    ( SELECT `s#`, COUNT( * ) AS course_num FROM sc GROUP BY `s#` ) temp 
WHERE
    temp.course_num >= 2
39. 查询选修了全部课程的学生信息
SELECT
    temp.* 
FROM
    (
    SELECT
        student.*,
        COUNT( course.`c#` ) AS num_course 
    FROM
        student,
        sc,
        course 
    WHERE
        student.`s#` = sc.`s#` 
        AND sc.`c#` = course.`c#` 
    GROUP BY
        student.`s#` 
    ) temp 
WHERE
    temp.num_course = 3
40. 查询各学生的年龄，只按年份来算
SELECT
    *,
    YEAR ( CURRENT_DATE ) - YEAR ( sage ) AS real_age 
FROM
    student
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT
    *,
    (
    CASE
            
            WHEN DATEDIFF( DATE( CURRENT_DATE ), DATE( sage ) ) > 0 THEN
            YEAR ( CURRENT_DATE ) - YEAR ( sage ) ELSE YEAR ( CURRENT_DATE ) - YEAR ( sage ) - 1 
        END 
        ) AS real_age 
FROM
    student
42. 查询本周过生日的学生
SELECT
    *,
    ( CASE WHEN DATEDIFF( DATE( CURRENT_DATE ), DATE( sage ) ) <= 7 THEN 'YES' ELSE 'NO' END ) AS BIRTHDAY_THIS_WEEK 
FROM
    student
43. 查询下周过生日的学生
SELECT
    *,
    (
    CASE
            
            WHEN DATEDIFF( DATE( CURRENT_DATE ), DATE( sage ) ) <= 7 AND DATEDIFF( DATE( CURRENT_DATE ), DATE( sage ) ) >= 14 THEN
            'YES' ELSE 'NO' 
        END 
        ) AS BIRTHDAY_THIS_WEEK 
FROM
    student
44. 查询本月过生日的学生
SELECT
    *,
    ( CASE WHEN DATEDIFF( MONTH ( CURRENT_DATE ), MONTH ( sage ) ) = 0 THEN 'YES' ELSE 'NO' END ) AS BIRTHDAY_THIS_MONTH 
FROM
    student
45. 查询下月过生日的学生
SELECT
    *,
    ( CASE WHEN DATEDIFF( DATE( CURRENT_DATE ), DATE( sage ) ) = 1 THEN 'YES' ELSE 'NO' END ) AS BIRTHDAY_NEXT_WEEK 
FROM
    student
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,293评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,604评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,958评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,729评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,719评论 5赞 366
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,630评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,000评论 3赞 397
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,665评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,909评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,646评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,726评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,400评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,986评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,959评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,197评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,996评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,481评论 2赞 342

推荐阅读更多精彩内容