你必知必会的SQL面试题

时间：2021-12-25 00:53:27

写在前面的话

本文参考原博《走向面试之数据库基础：一、你必知必会的SQL语句练习-Part 1》和《走向面试之数据库基础：一、你必知必会的SQL语句练习-Part 2》进行练习，部分题目在不变化其练习目的的情况下进行了题意改动，并删除部分重复和无表的题目。


在此之前已练习完两遍并分别放在WizNote和Github，其中第二遍在Github上针对个人的sql弱项题目进行了更详细的说明（Github：MyTraining/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

  ) s2

WHERE

  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 s2

WHERE

  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 s1

GROUP BY s1.StudentNo

HAVING 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 stu1

WHERE

  s1.StudentNo = stu1.StudentNo

GROUP 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 t1

WHERE

  t1.name like '李%'
6
1
SELECT
2
  COUNT(*)
3
FROM
4
  teacher t1
5
WHERE
6
  t1.name like '李%'


（5）查询没学过“叶平”老师课的同学的学号、姓名

SELECT

  stu1.StudentNo,

  stu1.name

FROM

  student stu1

WHERE

  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.name

FROM

  score s1,

  student stu1

WHERE

  s1.StudentNo = stu1.StudentNo

  AND

  s1.CourseNo IN (1, 2)

GROUP BY s1.StudentNo

HAVING 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.name

FROM

  score s1,

  student stu1

WHERE

  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.name

FROM

  score s1,

  student stu1,

  course c1,

  teacher t1

WHERE

  s1.StudentNo = stu1.StudentNo

  AND

  s1.CourseNo = c1.CourseNo

  AND

  c1.teacherNo = t1.teacherNo

  AND

  t1.name = '叶平'

GROUP BY s1.StudentNo

HAVING SUM(s1.CourseNo)=

(

SELECT

  SUM(c2.CourseNo)

FROM

  course c2,

  teacher t2

WHERE

  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.name

FROM

  score s1,

  student stu1,

  course c1,

  teacher t1

WHERE

  s1.StudentNo = stu1.StudentNo

  AND

  s1.CourseNo = c1.CourseNo

  AND

  c1.teacherNo = t1.teacherNo

  AND

  t1.name = '叶平'

GROUP BY s1.StudentNo

HAVING COUNT(*) =

(

SELECT

  COUNT(*)

FROM

  course c2,

  teacher t2

WHERE

  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.name

FROM

  score s1,

  (

  SELECT

    s2.StudentNo,

    s2.score

  FROM

    score s2

  WHERE

    s2.CourseNo = 1

  ) t2,

  student stu1

WHERE

  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.name

FROM

  score s1,

  student stu1

WHERE

  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.name

FROM

  score s1,

  student stu1

WHERE

  s1.StudentNo = stu1.StudentNo

GROUP BY s1.StudentNo

HAVING 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.name

FROM

  score s1,

  student stu1

WHERE

  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.name

FROM

  score s1,

  student stu1

WHERE

  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 t

SET

  s.score = t.avgScore

WHERE

  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.name

FROM

  score s,

  student stu

WHERE

  s.StudentNo != 2

  AND

  s.StudentNo = stu.studentNo

GROUP BY s.StudentNo

HAVING SUM(s.CourseNo)=

(

SELECT

  SUM(s1.CourseNo)

FROM

  score s1

WHERE

  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 s

WHERE

  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 t2

WHERE

  stu.studentNo = t1.studentNo

  AND

  stu.studentNo = t2.studentNo

ORDER 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 s

GROUP BY s.StudentNo

ORDER 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 s

GROUP 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 perfect

SELECT

  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 c

WHERE

  s.CourseNo = c.courseNo

GROUP BY s.CourseNo

ORDER 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 t1

WHERE

  s1.CourseNo = c1.courseNo

  AND

  c1.teacherNo = t1.teacherNo

GROUP BY s1.CourseNo

ORDER 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 s

WHERE

  c.courseNo = s.CourseNo

GROUP 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 s

WHERE

  (

	SELECT

		COUNT(*)

	FROM

		score s1

	WHERE

		s1.CourseNo = s.CourseNo

		AND

		s1.score > s.score

	) < 3

ORDER 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.CourseNo

GROUP 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 stu1

WHERE

  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 stu1

WHERE

  s.StudentNo = stu1.studentNo

GROUP BY s.StudentNo

HAVING 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 s

GROUP 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 s

GROUP 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 s

WHERE

  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 c

WHERE

  s.CourseNo = c.courseNo

GROUP BY c.courseNo

ORDER BY AVG(s.score)

#如果某课程无人选修，其平均成绩显示为null

SELECT

  c.name AS '课程名称',

  AVG(s.score) AS '平均成绩'

FROM

  course c LEFT JOIN score s ON c.courseNo = s.CourseNo

GROUP 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 stu

WHERE

  s.StudentNo = stu.studentNo

GROUP BY s.StudentNo

HAVING 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 stu

WHERE

  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 stu

WHERE

  s.CourseNo = c.courseNo

  AND

  s.StudentNo = stu.studentNo

ORDER 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 c

WHERE

  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 c

WHERE

  s.CourseNo = c.courseNo

  AND

  s.score < 60

#不及格的课程的选修记录

SELECT

  stu.name AS '姓名',

  c.name AS '课程名称',

  s.score AS '分数'

FROM

  score s,

  student stu,

  course c

WHERE

  s.StudentNo = stu.studentNo

  AND

  s.CourseNo = c.courseNo

  AND

  s.score < 60

ORDER 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 stu

WHERE

  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-1

SELECT

  COUNT(DISTINCT s.StudentNo) AS '选了课程的学生人数'

FROM

  score s

#method-2

SELECT

  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 t

WHERE

  s.StudentNo = stu.studentNo

  AND

  s.CourseNo = c.courseNo

  AND

  c.teacherNo = t.teacherNo

  AND

  t.name = '杨艳'

ORDER BY s.score DESC

LIMIT 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 c

WHERE

  s.CourseNo = c.courseNo

GROUP 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-1

SELECT

  s.StudentNo AS '学号',

  s.CourseNo AS '课程号',

  s.score AS '成绩'

FROM

  score s

WHERE

  (SELECT COUNT(*) FROM score s1 WHERE s1.score = s.score AND s1.CourseNo <> s.COurseNo) > 0

ORDER BY s.score DESC, s.StudentNo, s.CourseNo

#method-2

SELECT DISTINCT

  s1.StudentNo AS '学号',

  s1.CourseNo AS '课程号',

  s1.score AS '成绩'

FROM

  score s1,

  score s2

WHERE

  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 s

WHERE

  (SELECT COUNT(*) FROM score s1 WHERE s1.CourseNo = s.CourseNo AND s1.score > s.score) < 2

ORDER 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



























附件列表


相关文章