SQL 经典习题解答（6）

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT
    t1.*,
    t2.all_num,
    CONCAT( ROUND( t1.num / t2.all_num * 100, 2 ), '%' ) '百分比'
FROM
    (
    SELECT
        m.C,
        m.Cname,
        (
        CASE
                
                WHEN n.score >= 85 THEN
                '85-100' 
                WHEN n.score >= 70 
                AND n.score < 85 THEN '70-85' WHEN n.score >= 60 
                    AND n.score < 70 THEN
                        '60-70' ELSE '0-60' 
                    END 
                    ) AS px,
                    count( 1 ) num 
                FROM
                    Course m,
                    sc n 
                WHERE
                    m.C = n.C 
                GROUP BY
                    m.C,
                    m.Cname,
                    px 
                ORDER BY
                    m.C 
                ) t1,
                (
                SELECT
                    m.C,
                    m.Cname,
                    count( 1 ) all_num 
                FROM
                    Course m,
                    sc n 
                WHERE
                    m.C = n.C 
                GROUP BY
                    m.C,
                    m.Cname 
                ORDER BY
                    m.C 
                ) t2 
        WHERE
    t1.c = t2.c

详解：

首先统计各科成绩各分数段人数：课程编号,课程名称，选择表 sc 和表 course ,通过 CASE ... WHEN ... THEN ... ELSE ... END 语句分出分数段，再查出每一个课程学习的总人数，最后相除即可得到百分比。 CASE ... WHEN ... THEN ... ELSE ... END 用法参考 SQL 字符串拼接

程序运行结果:

---

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

SELECT
    a.*,
    b.avgscore,
    b.mc 
FROM
    student a,
    (
    SELECT
        s,
        avg( score ) AS avgscore,
        rank ( ) over ( ORDER BY avg( score ) DESC ) AS mc 
    FROM
        sc 
    GROUP BY
        S 
    ) b 
WHERE
    a.s = b.s 
ORDER BY
    mc

详解：

首先从表 sc 中查出每个学生的平均成绩和根据平均成绩进行的排名，再与表 student 连接得到结果

程序运行结果：

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

SELECT
    a.*,
    b.c,
    b.score,
    b.mc 
FROM
    student a,
    ( SELECT *, row_number ( ) over ( PARTITION BY c ORDER BY score DESC ) AS mc FROM sc ) b 
WHERE
    a.s = b.s 
    AND mc BETWEEN 1 
    AND 3 
ORDER BY
    c,
    mc

详解：

首先在表 sc 根据课程成绩生成每一门课程的排名记为表 b ，然后与表 student 连接得到结果

程序运行结果：

---

26、查询每门课程被选修的学生数

SELECT
    c,
    count( s ) AS num 
FROM
    sc 
GROUP BY
    c

程序运行结果：

---

27、查询出只有两门课程的全部学生的学号和姓名

SELECT 
    a.s,
    a.sname 
FROM
    student a,
    ( SELECT s FROM sc GROUP BY s HAVING count( s ) = 2 ) b 
WHERE
    a.s = b.s

详解：

在表 sc 中，学号出现的次数即为学生课程数，通过 GROUP BY 和 HAVING 函数得出选课数为 2 的学生学号，连接表 student 得出结果

程序运行结果：

---

28、查询男生、女生人数

SELECT    Ssex,count(s) FROM student WHERE Ssex = '男'
UNION ALL
SELECT    Ssex,count(s) FROM student WHERE Ssex = '女'

程序运行结果：

---

29、查询名字中含有"风"字的学生信息

SELECT
    * 
FROM
    student 
WHERE
    Sname LIKE '%风%'

程序运行结果：

---

30、查询同名同性学生名单，并统计同名人数

SELECT
    Sname,
    Ssex,
    COUNT( 1 ) num 
FROM
    student 
GROUP BY
    Sname,
    Ssex 
HAVING
    count( 1 ) > 1

详解：

通过 GROUP BY 划分出同名同性的学生，在通过 HAVING 判断人数是否大于 1 程序运行结果：

---

31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT
    * 
FROM
    student 
WHERE
    Sage LIKE '1990%'

程序运行结果：

---

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

SELECT
    c,
    avg( score ) AS avgscore 
FROM
    sc 
GROUP BY
    c 
ORDER BY
    avg( score ) DESC,
    c 

详解：

ORDER BY，先根据 avg( score )排序，如果平均成绩相同，再根据课程编号升序排列

程序运行结果：

---

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

SELECT
    a.s,
    a.sname,
    b.avgscore 
FROM
    student a,
    ( SELECT s, avg( score ) AS avgscore FROM sc GROUP BY s HAVING avg( score ) >= 85 ) b
WHERE a.s = b.s

程序运行结果：

---

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

SELECT
    a.sname,
    b.score 
FROM
    student a,
    sc b,
    course c 
WHERE
    a.s = b.s 
    AND b.c = c.C 
    AND b.score < 60 
    AND c.Cname = '数学'

程序运行结果：