写在前面的话
本文参考原博《 》和《 》进行练习，部分题目在不变化其练习目的的情况下进行了题意改动，并删除部分重复和无表的题目。 在此之前已练习完两遍并分别放在WizNote和Github，其中第二遍在Github上针对个人的sql弱项题目进行了更详细的说明（ ），随着越来越熟练，本文作为第三次练习，部分重点题目的详细解析和想法在这里就没有具体描述，或许以后会补上，但是谁知道呢（摊手）。
涉及的表结构和测试数据
使用数据库软件直接导出的SQL（偷懒直接把Part1和Part2的导在一起的，因为表名各不相同所以这里并不影响练习使用），数据库是MySQL。
--> <--
Part 1 练习题和参考解
（1）查询“001”课程比“002”课程成绩低的所有学生的学号、001学科成绩、002学科成绩
SELECT s1.StudentNo, s1.score AS '001', s2.score AS '002'FROM score s1, ( SELECT * FROM score s WHERE s.CourseNo = 2 ) s2WHERE s1.CourseNo = 1 AND s1.StudentNo = s2.StudentNo AND s1.score < s2.score ORDER BY s1.StudentNo
21
1
SELECT
2
s1.StudentNo,
3
s1.score AS '001',
4
s2.score AS '002'
5
FROM
6
score s1,
7
(
8
SELECT
9
*
10
FROM
11
score s
12
WHERE
13
s.CourseNo = 2
14
) s2
15
WHERE
16
s1.CourseNo = 1
17
AND
18
s1.StudentNo = s2.StudentNo
19
AND
20
s1.score < s2.score
21
ORDER BY s1.StudentNo
SELECT s1.StudentNo, s1.score AS '001', s2.score AS '002'FROM score s1, score s2WHERE s1.CourseNo = 001 AND s2.CourseNo = 002 AND s1.StudentNo = s2.StudentNo AND s1.score < s2.score ORDER BY s1.StudentNo
16
1
SELECT
2
s1.StudentNo,
3
s1.score AS '001',
4
s2.score AS '002'
5
FROM
6
score s1,
7
score s2
8
WHERE
9
s1.CourseNo = 001
10
AND
11
s2.CourseNo = 002
12
AND
13
s1.StudentNo = s2.StudentNo
14
AND
15
s1.score < s2.score
16
ORDER BY s1.StudentNo
（2）查询平均成绩大于60分的同学的学号和平均成绩
SELECT s1.StudentNo, AVG(s1.score)FROM score s1GROUP BY s1.StudentNoHAVING AVG(s1.score)>60
7
1
SELECT
2
s1.StudentNo,
3
AVG(s1.score)
4
FROM
5
score s1
6
GROUP BY s1.StudentNo
7
HAVING AVG(s1.score)>60
（3）查询所有同学的学号、姓名、选课数、总成绩
SELECT s1.StudentNo, stu1.name, COUNT(*), SUM(s1.score)FROM score s1, student stu1WHERE s1.StudentNo = stu1.StudentNoGROUP BY s1.StudentNo
11
1
SELECT
2
s1.StudentNo,
3
stu1.name,
4
COUNT(*),
5
SUM(s1.score)
6
FROM
7
score s1,
8
student stu1
9
WHERE
10
s1.StudentNo = stu1.StudentNo
11
GROUP BY s1.StudentNo
（4）查询姓“李”的老师的个数
SELECT COUNT(*)FROM teacher t1WHERE t1.name like '李%'
6
1
SELECT
2
COUNT(*)
3
FROM
4
teacher t1
5
WHERE
6
t1.name like '李%'
（5）查询没学过“叶平”老师课的同学的学号、姓名
SELECT stu1.StudentNo, stu1.nameFROM student stu1WHERE stu1.StudentNo NOT IN ( SELECT DISTINCT s1.StudentNo FROM score s1, course c1, teacher t1 WHERE s1.courseNo = c1.CourseNo AND c1.teacherNo = t1.teacherNo AND t1.name = '叶平' )
21
1
SELECT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
student stu1
6
WHERE
7
stu1.StudentNo NOT IN
8
(
9
SELECT DISTINCT
10
s1.StudentNo
11
FROM
12
score s1,
13
course c1,
14
teacher t1
15
WHERE
16
s1.courseNo = c1.CourseNo
17
AND
18
c1.teacherNo = t1.teacherNo
19
AND
20
t1.name = '叶平'
21
)
（6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
-- 这个算法比普通的要有想法SELECT s1.StudentNo, stu1.nameFROM score s1, student stu1WHERE s1.StudentNo = stu1.StudentNo AND s1.CourseNo IN (1, 2)GROUP BY s1.StudentNoHAVING COUNT(*) = 2
13
1
-- 这个算法比普通的要有想法
2
SELECT
3
s1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1
8
WHERE
9
s1.StudentNo = stu1.StudentNo
10
AND
11
s1.CourseNo IN (1, 2)
12
GROUP BY s1.StudentNo
13
HAVING COUNT(*) = 2
SELECT s1.StudentNo, stu1.nameFROM score s1, student stu1WHERE s1.StudentNo = stu1.StudentNo AND s1.CourseNo = 1 AND s1.StudentNo IN ( SELECT s2.StudentNo FROM score s2 WHERE s2.CourseNo = 2 )
20
1
SELECT
2
s1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.CourseNo = 1
11
AND
12
s1.StudentNo IN
13
(
14
SELECT
15
s2.StudentNo
16
FROM
17
score s2
18
WHERE
19
s2.CourseNo = 2
20
)
（7）查询学过“叶平”老师所教的所有课的同学的学号、姓名
-- 利用主键值不相同，它们的和一定各不相同，叶平老师的课程的主键值和如果与学生所学的叶平老师的课程的主键值和相等，那么说明学了叶平老师所有课程SELECT stu1.StudentNo, stu1.nameFROM score s1, student stu1, course c1, teacher t1WHERE s1.StudentNo = stu1.StudentNo AND s1.CourseNo = c1.CourseNo AND c1.teacherNo = t1.teacherNo AND t1.name = '叶平'GROUP BY s1.StudentNoHAVING SUM(s1.CourseNo)=(SELECT SUM(c2.CourseNo)FROM course c2, teacher t2WHERE c2.teacherNo = t2.teacherNo AND t2.name = '叶平')
30
1
-- 利用主键值不相同，它们的和一定各不相同，叶平老师的课程的主键值和如果与学生所学的叶平老师的课程的主键值和相等，那么说明学了叶平老师所有课程
2
SELECT
3
stu1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1,
8
course c1,
9
teacher t1
10
WHERE
11
s1.StudentNo = stu1.StudentNo
12
AND
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.StudentNo
19
HAVING SUM(s1.CourseNo)=
20
(
21
SELECT
22
SUM(c2.CourseNo)
23
FROM
24
course c2,
25
teacher t2
26
WHERE
27
c2.teacherNo = t2.teacherNo
28
AND
29
t2.name = '叶平'
30
)
-- 如果学生学习叶平老师的课程数量，与叶平老师所教学课程的数量相同，那么说明该同学学了叶平老师的所有课程SELECT stu1.StudentNo, stu1.nameFROM score s1, student stu1, course c1, teacher t1WHERE s1.StudentNo = stu1.StudentNo AND s1.CourseNo = c1.CourseNo AND c1.teacherNo = t1.teacherNo AND t1.name = '叶平'GROUP BY s1.StudentNoHAVING COUNT(*) =(SELECT COUNT(*)FROM course c2, teacher t2WHERE c2.teacherNo = t2.teacherNo AND t2.name = '叶平' )
30
1
-- 如果学生学习叶平老师的课程数量，与叶平老师所教学课程的数量相同，那么说明该同学学了叶平老师的所有课程
2
SELECT
3
stu1.StudentNo,
4
stu1.name
5
FROM
6
score s1,
7
student stu1,
8
course c1,
9
teacher t1
10
WHERE
11
s1.StudentNo = stu1.StudentNo
12
AND
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.StudentNo
19
HAVING COUNT(*) =
20
(
21
SELECT
22
COUNT(*)
23
FROM
24
course c2,
25
teacher t2
26
WHERE
27
c2.teacherNo = t2.teacherNo
28
AND
29
t2.name = '叶平'
30
)
（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
SELECT stu1.studentNo, stu1.nameFROM score s1, ( SELECT s2.StudentNo, s2.score FROM score s2 WHERE s2.CourseNo = 1 ) t2, student stu1WHERE s1.CourseNo = 2 AND s1.StudentNo = t2.StudentNo AND s1.score < t2.score AND s1.StudentNo = stu1.studentNo
23
1
SELECT
2
stu1.studentNo,
3
stu1.name
4
FROM
5
score s1,
6
(
7
SELECT
8
s2.StudentNo,
9
s2.score
10
FROM
11
score s2
12
WHERE
13
s2.CourseNo = 1
14
) t2,
15
student stu1
16
WHERE
17
s1.CourseNo = 2
18
AND
19
s1.StudentNo = t2.StudentNo
20
AND
21
s1.score < t2.score
22
AND
23
s1.StudentNo = stu1.studentNo
（9）查询有课程成绩小于60分的同学的学号、姓名
SELECT DISTINCT s1.StudentNo, stu1.nameFROM score s1, student stu1WHERE s1.StudentNo = stu1.studentNo AND s1.score < 60
10
1
SELECT DISTINCT
2
s1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.studentNo
9
AND
10
s1.score < 60
（10）查询没有学全所有课的同学的学号、姓名
SELECT stu1.StudentNo, stu1.nameFROM score s1, student stu1WHERE s1.StudentNo = stu1.StudentNoGROUP BY s1.StudentNoHAVING COUNT(*) < (SELECT COUNT(*)FROM course c1)
16
1
SELECT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
GROUP BY s1.StudentNo
10
HAVING COUNT(*) <
11
(
12
SELECT
13
COUNT(*)
14
FROM
15
course c1
16
)
（11）查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
SELECT DISTINCT stu1.StudentNo, stu1.nameFROM score s1, student stu1WHERE s1.StudentNo = stu1.StudentNo AND s1.CourseNo IN ( SELECT s2.CourseNo FROM score s2 WHERE s2.StudentNo = 1 )
18
1
SELECT DISTINCT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.CourseNo IN
11
(
12
SELECT
13
s2.CourseNo
14
FROM
15
score s2
16
WHERE
17
s2.StudentNo = 1
18
)
（12）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名（和11题撞脸，排除1号同学就可以了）
SELECT DISTINCT stu1.StudentNo, stu1.nameFROM score s1, student stu1WHERE s1.StudentNo = stu1.StudentNo AND s1.StudentNo != 1 AND s1.CourseNo IN ( SELECT s2.CourseNo FROM score s2 WHERE s2.StudentNo = 1 )
20
1
SELECT DISTINCT
2
stu1.StudentNo,
3
stu1.name
4
FROM
5
score s1,
6
student stu1
7
WHERE
8
s1.StudentNo = stu1.StudentNo
9
AND
10
s1.StudentNo != 1
11
AND
12
s1.CourseNo IN
13
(
14
SELECT
15
s2.CourseNo
16
FROM
17
score s2
18
WHERE
19
s2.StudentNo = 1
20
)
（13）把“score”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
-- 涉及将两表联合，将本表某字段的值按条件设置为另个表的某个字段的值 （参考链接：MySQL:把一个表中的数据按键值更新(update)到另一个表）UPDATE score s, ( SELECT s1.CourseNo as courseNo, AVG(s1.score) as avgScore FROM score s1, course c1, teacher t1 WHERE s1.CourseNo = c1.CourseNo AND c1.teacherNo = t1.teacherNo AND t1.name = '叶平' GROUP BY s1.CourseNo ) as tSET s.score = t.avgScoreWHERE s.CourseNo = t.courseNo
23
1
-- 涉及将两表联合，将本表某字段的值按条件设置为另个表的某个字段的值 （参考链接：MySQL:把一个表中的数据按键值更新(update)到另一个表）
2
UPDATE
3
score s,
4
(
5
SELECT
6
s1.CourseNo as courseNo,
7
AVG(s1.score) as avgScore
8
FROM
9
score s1,
10
course c1,
11
teacher t1
12
WHERE
13
s1.CourseNo = c1.CourseNo
14
AND
15
c1.teacherNo = t1.teacherNo
16
AND
17
t1.name = '叶平'
18
GROUP BY s1.CourseNo
19
) as t
20
SET
21
s.score = t.avgScore
22
WHERE
23
s.CourseNo = t.courseNo
（14）查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
SELECT stu.studentNo, stu.nameFROM score s, student stuWHERE s.StudentNo != 2 AND s.StudentNo = stu.studentNoGROUP BY s.StudentNoHAVING SUM(s.CourseNo)=(SELECT SUM(s1.CourseNo)FROM score s1WHERE s1.StudentNo = 2)
20
1
SELECT
2
stu.studentNo,
3
stu.name
4
FROM
5
score s,
6
student stu
7
WHERE
8
s.StudentNo != 2
9
AND
10
s.StudentNo = stu.studentNo
11
GROUP BY s.StudentNo
12
HAVING SUM(s.CourseNo)=
13
(
14
SELECT
15
SUM(s1.CourseNo)
16
FROM
17
score s1
18
WHERE
19
s1.StudentNo = 2
20
)
（15）删除学习“叶平”老师课的SC表记录
DELETE FROM score sWHERE s.CourseNo IN ( SELECT c.CourseNo FROM course c, teacher t WHERE c.teacherNo = t.teacherNo AND t.name = '叶平' )
15
1
DELETE FROM
2
score s
3
WHERE
4
s.CourseNo IN
5
(
6
SELECT
7
c.CourseNo
8
FROM
9
course c,
10
teacher t
11
WHERE
12
c.teacherNo = t.teacherNo
13
AND
14
t.name = '叶平'
15
)
（16）向SC表中插入一些记录，这些记录要求符合以下条件：1、没有上过编号“002”课程的同学学号；2、插入“002”号课程的平均成绩
-- 本题采用插入子查询的方式，三个字段中后两个字段为常量（基本格式：INSERT INTO R(A1, A2 ... ,An) 子查询）INSERT INTO score(StudentNo, CourseNo, score)(SELECT stu.studentNo, 2, (SELECT AVG(s3.score) FROM score s3 WHERE s3.CourseNo = 2)FROM student stu WHERE stu.studentNo NOT IN (SELECT s2.StudentNo FROM score s2 WHERE s2.CourseNo = 2))
22
1
-- 本题采用插入子查询的方式，三个字段中后两个字段为常量（基本格式：INSERT INTO R(A1, A2 ... ,An) 子查询）
2
INSERT INTO
3
score(StudentNo, CourseNo, score)
4
(
5
SELECT
6
stu.studentNo,
7
2,
8
(SELECT AVG(s3.score) FROM score s3 WHERE s3.CourseNo = 2)
9
FROM
10
student stu
11
WHERE
12
stu.studentNo
13
NOT IN
14
(
15
SELECT
16
s2.StudentNo
17
FROM
18
score s2
19
WHERE
20
s2.CourseNo = 2
21
)
22
)
（17）按学号由低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分
-- 用了个极蠢的办法，虽然很瓜很绕但是也算是温习了下相关子查询、CASE WHEN、EXISTS了，另外对自己也有所启发，就留下了-- 然后做到这里的时候感慨，随着练习总是越来越熟练的，尽管自己写得很绕，但以往是根本想不到用什么CASE WHEN、EXISTS之类的，也算是成长吧SELECT stu.studentNo, CASE WHEN EXISTS (SELECT * FROM score s1 WHERE s1.CourseNo = 1 AND s1.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 1 AND s.studentNo = stu.studentNo) ELSE NULL END AS "语文", CASE WHEN EXISTS (SELECT * FROM score s2 WHERE s2.CourseNo = 2 AND s2.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 2 AND s.studentNo = stu.studentNo) ELSE NULL END AS "数学", CASE WHEN EXISTS (SELECT * FROM score s3 WHERE s3.CourseNo = 3 AND s3.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 3 AND s.studentNo = stu.studentNo) ELSE NULL END AS "英语", t1.validateCount AS '有效科目数', t2.validateAVG AS '有效平均分'FROM student stu, (SELECT s.studentNo, COUNT(*) AS validateCount FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t1, (SELECT s.studentNo, AVG(s.score) AS validateAVG FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t2WHERE stu.studentNo = t1.studentNo AND stu.studentNo = t2.studentNoORDER BY stu.StudentNo
18
1
-- 用了个极蠢的办法，虽然很瓜很绕但是也算是温习了下相关子查询、CASE WHEN、EXISTS了，另外对自己也有所启发，就留下了
2
-- 然后做到这里的时候感慨，随着练习总是越来越熟练的，尽管自己写得很绕，但以往是根本想不到用什么CASE WHEN、EXISTS之类的，也算是成长吧
3
SELECT
4
stu.studentNo,
5
CASE WHEN EXISTS (SELECT * FROM score s1 WHERE s1.CourseNo = 1 AND s1.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 1 AND s.studentNo = stu.studentNo) ELSE NULL END AS "语文",
6
CASE WHEN EXISTS (SELECT * FROM score s2 WHERE s2.CourseNo = 2 AND s2.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 2 AND s.studentNo = stu.studentNo) ELSE NULL END AS "数学",
7
CASE WHEN EXISTS (SELECT * FROM score s3 WHERE s3.CourseNo = 3 AND s3.studentNo = stu.studentNo) THEN (SELECT s.score FROM score s WHERE s.CourseNo = 3 AND s.studentNo = stu.studentNo) ELSE NULL END AS "英语",
8
t1.validateCount AS '有效科目数',
9
t2.validateAVG AS '有效平均分'
10
FROM
11
student stu,
12
(SELECT s.studentNo, COUNT(*) AS validateCount FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t1,
13
(SELECT s.studentNo, AVG(s.score) AS validateAVG FROM score s WHERE s.CourseNo IN (1, 2, 3) GROUP BY s.StudentNo) as t2
14
WHERE
15
stu.studentNo = t1.studentNo
16
AND
17
stu.studentNo = t2.studentNo
18
ORDER BY stu.StudentNo
-- 另外，参考博客中博主给出的答案如下，比我的就简洁多了，-- 其次，他在这里的有效课程数和有效平均分是针对学生所有的成绩，而并非此处的仅仅三科-- 因为题意也不是很清楚，也就作罢，正好算是两种形式吧SELECT s.StudentNo, (SELECT s1.score FROM score s1 WHERE s1.CourseNo=1 AND s1.StudentNo = s.StudentNo) AS "语文", (SELECT s2.score FROM score s2 WHERE s2.CourseNo=2 AND s2.StudentNo = s.StudentNo) AS "数学", (SELECT s3.score FROM score s3 WHERE s3.CourseNo=3 AND s3.StudentNo = s.StudentNo) AS "英语", COUNT(s.CourseNo) AS "有效课程数", AVG(s.score) AS "有效平均分"FROM score sGROUP BY s.StudentNoORDER BY s.StudentNo
14
1
-- 另外，参考博客中博主给出的答案如下，比我的就简洁多了，
2
-- 其次，他在这里的有效课程数和有效平均分是针对学生所有的成绩，而并非此处的仅仅三科
3
-- 因为题意也不是很清楚，也就作罢，正好算是两种形式吧
4
SELECT
5
s.StudentNo,
6
(SELECT s1.score FROM score s1 WHERE s1.CourseNo=1 AND s1.StudentNo = s.StudentNo) AS "语文",
7
(SELECT s2.score FROM score s2 WHERE s2.CourseNo=2 AND s2.StudentNo = s.StudentNo) AS "数学",
8
(SELECT s3.score FROM score s3 WHERE s3.CourseNo=3 AND s3.StudentNo = s.StudentNo) AS "英语",
9
COUNT(s.CourseNo) AS "有效课程数",
10
AVG(s.score) AS "有效平均分"
11
FROM
12
score s
13
GROUP BY s.StudentNo
14
ORDER BY s.StudentNo
（18）查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT s.CourseNo, MAX(s.score), MIN(s.score)FROM score sGROUP BY s.CourseNo
8
1
SELECT
2
s.CourseNo,
3
MAX(s.score),
4
MIN(s.score)
5
FROM
6
score s
7
GROUP BY
8
s.CourseNo
（19）按各科平均成绩从低到高和及格率的百分数从高到低顺序；
-- 看了下上次在Github上写的，不得不说，practice makes perfectSELECT s.CourseNo, c.name, AVG(s.score) AS '平均分', SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END) AS '及格数', COUNT(*) AS '总数', SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '及格率'FROM score s, course cWHERE s.CourseNo = c.courseNoGROUP BY s.CourseNoORDER BY AVG(s.score), SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*) DESC
15
1
-- 看了下上次在Github上写的，不得不说，practice makes perfect
2
SELECT
3
s.CourseNo,
4
c.name,
5
AVG(s.score) AS '平均分',
6
SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END) AS '及格数',
7
COUNT(*) AS '总数',
8
SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS '及格率'
9
FROM
10
score s,
11
course c
12
WHERE
13
s.CourseNo = c.courseNo
14
GROUP BY s.CourseNo
15
ORDER BY AVG(s.score), SUM(CASE WHEN s.score > 60 THEN 1 ELSE 0 END)/COUNT(*) DESC
还不完全，参考原博主，加isnull
（20）查询不同老师所教不同课程平均分从高到低显示
SELECT c1.name, t1.name, AVG(s1.score)FROM score s1, course c1, teacher t1WHERE s1.CourseNo = c1.courseNo AND c1.teacherNo = t1.teacherNoGROUP BY s1.CourseNoORDER BY AVG(s1.score) DESC
14
1
SELECT
2
c1.name,
3
t1.name,
4
AVG(s1.score)
5
FROM
6
score s1,
7
course c1,
8
teacher t1
9
WHERE
10
s1.CourseNo = c1.courseNo
11
AND
12
c1.teacherNo = t1.teacherNo
13
GROUP BY s1.CourseNo
14
ORDER BY AVG(s1.score) DESC
（21）统计列印各科成绩，各分数段人数：课程ID，课程名称，(100-85)，(85-70，(70-60)，( 低于60)
SELECT c.courseNo AS '课程ID', c.name AS '课程名称', SUM(CASE WHEN s.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '(100-85)', SUM(CASE WHEN s.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '(85-70)', SUM(CASE WHEN s.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '(70-60)', SUM(CASE WHEN s.score < 60 THEN 1 ELSE 0 END) AS '(低于60)'FROM course c, score sWHERE c.courseNo = s.CourseNoGROUP BY c.courseNo
13
1
SELECT
2
c.courseNo AS '课程ID',
3
c.name AS '课程名称',
4
SUM(CASE WHEN s.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '(100-85)',
5
SUM(CASE WHEN s.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '(85-70)',
6
SUM(CASE WHEN s.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '(70-60)',
7
SUM(CASE WHEN s.score < 60 THEN 1 ELSE 0 END) AS '(低于60)'
8
FROM
9
course c,
10
score s
11
WHERE
12
c.courseNo = s.CourseNo
13
GROUP BY c.courseNo
（22）查询各科成绩前三名的记录（不考虑成绩并列情况）
SELECT *FROM score sWHERE ( SELECT COUNT(*) FROM score s1 WHERE s1.CourseNo = s.CourseNo AND s1.score > s.score ) < 3ORDER BY s.CourseNo
16
1
SELECT
2
*
3
FROM
4
score s
5
WHERE
6
(
7
SELECT
8
COUNT(*)
9
FROM
10
score s1
11
WHERE
12
s1.CourseNo = s.CourseNo
13
AND
14
s1.score > s.score
15
) < 3
16
ORDER BY s.CourseNo
（23）查询每门课程被选修的学生数
SELECT c.name AS '课程', COUNT(s.StudentNo) AS '选修学生数'FROM course c LEFT JOIN score s ON c.courseNo = s.CourseNoGROUP BY c.courseNo
6
1
SELECT
2
c.name AS '课程',
3
COUNT(s.StudentNo) AS '选修学生数'
4
FROM
5
course c LEFT JOIN score s ON c.courseNo = s.CourseNo
6
GROUP BY c.courseNo
（24）查询出只选修了一门课程的全部学生的学号和姓名
SELECT stu1.StudentNo AS '学号', stu1.name AS '姓名'FROM (SELECT StudentNo, COUNT(CourseNo) AS amount FROM score GROUP BY StudentNo) t1, student stu1WHERE t1.StudentNo = stu1.studentNo AND t1.amount = 1
10
1
SELECT
2
stu1.StudentNo AS '学号',
3
stu1.name AS '姓名'
4
FROM
5
(SELECT StudentNo, COUNT(CourseNo) AS amount FROM score GROUP BY StudentNo) t1,
6
student stu1
7
WHERE
8
t1.StudentNo = stu1.studentNo
9
AND
10
t1.amount = 1
SELECT stu1.studentNo AS '学号', stu1.name AS '姓名'FROM score s, student stu1WHERE s.StudentNo = stu1.studentNoGROUP BY s.StudentNoHAVING COUNT(s.CourseNo) = 1
10
1
SELECT
2
stu1.studentNo AS '学号',
3
stu1.name AS '姓名'
4
FROM
5
score s,
6
student stu1
7
WHERE
8
s.StudentNo = stu1.studentNo
9
GROUP BY s.StudentNo
10
HAVING COUNT(s.CourseNo) = 1
（25）查询男生、女生的人数
SELECT s.sex AS '性别', COUNT(*) AS '人数'FROM student sGROUP BY s.sex
6
1
SELECT
2
s.sex AS '性别',
3
COUNT(*) AS '人数'
4
FROM
5
student s
6
GROUP BY s.sex
（26）查询同名同姓学生名单，并统计同名人数
SELECT s.name AS '姓名', COUNT(*) AS '学生数'FROM student sGROUP BY s.name
6
1
SELECT
2
s.name AS '姓名',
3
COUNT(*) AS '学生数'
4
FROM
5
student s
6
GROUP BY s.name
（27）查询1991年出生的学生名单
SELECT s.name AS '姓名'FROM student sWHERE YEAR(CURDATE()) - s.age = 1991
6
1
SELECT
2
s.name AS '姓名'
3
FROM
4
student s
5
WHERE
6
YEAR(CURDATE()) - s.age = 1991
（28）查询每门课程的平均成绩，结果按平均成绩升序排列
#未考虑到课程无人选修的情况SELECT c.name AS '课程名称', AVG(s.score) AS '平均成绩'FROM score s, course cWHERE s.CourseNo = c.courseNoGROUP BY c.courseNoORDER BY AVG(s.score)#如果某课程无人选修，其平均成绩显示为nullSELECT c.name AS '课程名称', AVG(s.score) AS '平均成绩'FROM course c LEFT JOIN score s ON c.courseNo = s.CourseNoGROUP BY c.courseNo ORDER BY AVG(s.score)
22
1
#未考虑到课程无人选修的情况
2
SELECT
3
c.name AS '课程名称',
4
AVG(s.score) AS '平均成绩'
5
FROM
6
score s,
7
course c
8
WHERE
9
s.CourseNo = c.courseNo
10
GROUP BY c.courseNo
11
ORDER BY AVG(s.score)
12
13
14
15
#如果某课程无人选修，其平均成绩显示为null
16
SELECT
17
c.name AS '课程名称',
18
AVG(s.score) AS '平均成绩'
19
FROM
20
course c LEFT JOIN score s ON c.courseNo = s.CourseNo
21
GROUP BY c.courseNo
22
ORDER BY AVG(s.score)
（29）查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT stu.studentNo AS '学号', stu.name AS '姓名', AVG(s.score) AS '平均成绩'FROM score s, student stuWHERE s.StudentNo = stu.studentNoGROUP BY s.StudentNoHAVING AVG(s.score) > 85
11
1
SELECT
2
stu.studentNo AS '学号',
3
stu.name AS '姓名',
4
AVG(s.score) AS '平均成绩'
5
FROM
6
score s,
7
student stu
8
WHERE
9
s.StudentNo = stu.studentNo
10
GROUP BY s.StudentNo
11
HAVING AVG(s.score) > 85
（30）查询课程名称为“数学”，且分数低于60的学生姓名和分数
SELECT stu.name AS '姓名', s.score AS '数学成绩'FROM score s, course c, student stuWHERE s.CourseNo = c.courseNo AND s.StudentNo = stu.studentNo AND c.name = '数学' AND s.score < 60
15
1
SELECT
2
stu.name AS '姓名',
3
s.score AS '数学成绩'
4
FROM
5
score s,
6
course c,
7
student stu
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.StudentNo = stu.studentNo
12
AND
13
c.name = '数学'
14
AND
15
s.score < 60
（31）查询所有学生的选课情况
SELECT stu.name AS '姓名', c.name AS '选课'FROM score s, course c, student stuWHERE s.CourseNo = c.courseNo AND s.StudentNo = stu.studentNoORDER BY stu.name
12
1
SELECT
2
stu.name AS '姓名',
3
c.name AS '选课'
4
FROM
5
score s,
6
course c,
7
student stu
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.StudentNo = stu.studentNo
12
ORDER BY stu.name
（32）查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT stu.name AS '姓名', c.name AS '课程名称', s.score AS '分数'FROM score s, student stu, course cWHERE s.StudentNo = stu.studentNo AND s.CourseNo = c.courseNo AND s.score > 70
14
1
SELECT
2
stu.name AS '姓名',
3
c.name AS '课程名称',
4
s.score AS '分数'
5
FROM
6
score s,
7
student stu,
8
course c
9
WHERE
10
s.StudentNo = stu.studentNo
11
AND
12
s.CourseNo = c.courseNo
13
AND
14
s.score > 70
（33）查询不及格的课程，并按课程号从大到小排列
#包含不及格记录的课程SELECT DISTINCT c.courseNo AS '课程号', c.name AS '课程名称' FROM score s, course cWHERE s.CourseNo = c.courseNo AND s.score < 60#不及格的课程的选修记录SELECT stu.name AS '姓名', c.name AS '课程名称', s.score AS '分数'FROM score s, student stu, course cWHERE s.StudentNo = stu.studentNo AND s.CourseNo = c.courseNo AND s.score < 60ORDER BY c.courseNo DESC
28
1
#包含不及格记录的课程
2
SELECT DISTINCT
3
c.courseNo AS '课程号',
4
c.name AS '课程名称'
5
FROM
6
score s,
7
course c
8
WHERE
9
s.CourseNo = c.courseNo
10
AND
11
s.score < 60
12
13
#不及格的课程的选修记录
14
SELECT
15
stu.name AS '姓名',
16
c.name AS '课程名称',
17
s.score AS '分数'
18
FROM
19
score s,
20
student stu,
21
course c
22
WHERE
23
s.StudentNo = stu.studentNo
24
AND
25
s.CourseNo = c.courseNo
26
AND
27
s.score < 60
28
ORDER BY c.courseNo DESC
（34） 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
SELECT stu.studentNo AS '学号', stu.name AS '姓名'FROM score s, student stuWHERE s.StudentNo = stu.studentNo AND s.CourseNo = 3 AND s.score > 80
12
1
SELECT
2
stu.studentNo AS '学号',
3
stu.name AS '姓名'
4
FROM
5
score s,
6
student stu
7
WHERE
8
s.StudentNo = stu.studentNo
9
AND
10
s.CourseNo = 3
11
AND
12
s.score > 80
（35）求选了课程的学生人数
#method-1SELECT COUNT(DISTINCT s.StudentNo) AS '选了课程的学生人数'FROM score s#method-2SELECT COUNT(*) AS '选了课程的学生人数'FROM (SELECT * FROM score s GROUP BY s.StudentNo) t
11
1
#method-1
2
SELECT
3
COUNT(DISTINCT s.StudentNo) AS '选了课程的学生人数'
4
FROM
5
score s
6
7
#method-2
8
SELECT
9
COUNT(*) AS '选了课程的学生人数'
10
FROM
11
(SELECT * FROM score s GROUP BY s.StudentNo) t
（36）查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT stu.name AS '学生姓名', s.score AS '成绩'FROM score s, student stu, course c, teacher tWHERE s.StudentNo = stu.studentNo AND s.CourseNo = c.courseNo AND c.teacherNo = t.teacherNo AND t.name = '杨艳'ORDER BY s.score DESCLIMIT 0, 1
18
1
SELECT
2
stu.name AS '学生姓名',
3
s.score AS '成绩'
4
FROM
5
score s,
6
student stu,
7
course c,
8
teacher t
9
WHERE
10
s.StudentNo = stu.studentNo
11
AND
12
s.CourseNo = c.courseNo
13
AND
14
c.teacherNo = t.teacherNo
15
AND
16
t.name = '杨艳'
17
ORDER BY s.score DESC
18
LIMIT 0, 1
（37） 查询各个课程及相应的选修人数
SELECT c.name AS '课程名称', COUNT(*) AS '选修人数'FROM score s, course cWHERE s.CourseNo = c.courseNoGROUP BY s.CourseNo
9
1
SELECT
2
c.name AS '课程名称',
3
COUNT(*) AS '选修人数'
4
FROM
5
score s,
6
course c
7
WHERE
8
s.CourseNo = c.courseNo
9
GROUP BY s.CourseNo
（38）查询不同课程但成绩相同的学生的学号、课程号、学生成绩
#method-1SELECT s.StudentNo AS '学号', s.CourseNo AS '课程号', s.score AS '成绩'FROM score sWHERE (SELECT COUNT(*) FROM score s1 WHERE s1.score = s.score AND s1.CourseNo <> s.COurseNo) > 0ORDER BY s.score DESC, s.StudentNo, s.CourseNo#method-2SELECT DISTINCT s1.StudentNo AS '学号', s1.CourseNo AS '课程号', s1.score AS '成绩'FROM score s1, score s2WHERE s1.score = s2.score AND s1.CourseNo <> s2.CourseNo ORDER BY s1.score DESC, s1.StudentNo, s1.CourseNo
x
1
#method-1
2
SELECT
3
s.StudentNo AS '学号',
4
s.CourseNo AS '课程号',
5
s.score AS '成绩'
6
FROM
7
score s
8
WHERE
9
(SELECT COUNT(*) FROM score s1 WHERE s1.score = s.score AND s1.CourseNo <> s.COurseNo) > 0
10
ORDER BY s.score DESC, s.StudentNo, s.CourseNo
11
12
#method-2
13
SELECT DISTINCT
14
s1.StudentNo AS '学号',
15
s1.CourseNo AS '课程号',
16
s1.score AS '成绩'
17
FROM
18
score s1,
19
score s2
20
WHERE
21
s1.score = s2.score
22
AND
23
s1.CourseNo <> s2.CourseNo
24
ORDER BY s1.score DESC, s1.StudentNo, s1.CourseNo
（39）查询每门课程成绩最好的前两名
SELECT s.CourseNo AS '课程号', s.StudentNo AS '学号', s.score AS '分数'FROM score sWHERE (SELECT COUNT(*) FROM score s1 WHERE s1.CourseNo = s.CourseNo AND s1.score > s.score) < 2ORDER BY s.CourseNo
1
SELECT
2
s.CourseNo AS '课程号',
3
s.StudentNo AS '学号',
4
s.score AS '分数'
5
FROM
6
score s
7
WHERE
8
(SELECT COUNT(*) FROM score s1 WHERE s1.CourseNo = s.CourseNo AND s1.score > s.score) < 2
9
ORDER BY s.CourseNo
持续更新：
2017-06-28
附件列表