SQL 经典题型解答(6)
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 = '数学'
程序运行结果：
