开放原子开发者工作坊

写在前面的话

本文参考原博《 走向面试之数据库基础：一、你必知必会的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

附件列表