50道SQL练习题及参考答案（原创）

最新推荐文章于 2024-04-11 20:33:28 发布

Hill_GM

最新推荐文章于 2024-04-11 20:33:28 发布

阅读量4k

点赞数 3

分类专栏： Mysql 文章标签： Mysql

本文链接：https://blog.csdn.net/weixin_42323337/article/details/86519688

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、基本信息

1.学生表

Student(S,Sname,Sage,Ssex)

–S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

2.课程表

Course(C,Cname,T)

C 课程编号,Cname 课程名称,T 教师编号

3.教师表

Teacher(T,Tname)

T 教师编号,Tname 教师姓名

4.成绩表

SC(S,C,score)

S 学生编号,C 课程编号,score 分数

二、创建数据库

1.学生表 Student

create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));

insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);

insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);

insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);

insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);

insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);

insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);

insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);

insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);

2.科目表 Course

create table Course(C varchar(10),Cname nvarchar(10),T varchar(10));

insert into Course values(‘01’ , ‘语文’ , ‘02’);

insert into Course values(‘02’ , ‘数学’ , ‘01’);

insert into Course values(‘03’ , ‘英语’ , ‘03’);

3.教师表 Teacher

create table Teacher(T varchar(10),Tname nvarchar(10));

insert into Teacher values(‘01’ , ‘张三’);

insert into Teacher values(‘02’ , ‘李四’);

insert into Teacher values(‘03’ , ‘王五’);

4.成绩表 SC

create table SC(S varchar(10),C varchar(10),score decimal(18,1));

insert into SC values(‘01’ , ‘01’ , 80);

insert into SC values(‘01’ , ‘02’ , 90);

insert into SC values(‘01’ , ‘03’ , 99);

insert into SC values(‘02’ , ‘01’ , 70);

insert into SC values(‘02’ , ‘02’ , 60);

insert into SC values(‘02’ , ‘03’ , 80);

insert into SC values(‘03’ , ‘01’ , 80);

insert into SC values(‘03’ , ‘02’ , 80);

insert into SC values(‘03’ , ‘03’ , 80);

insert into SC values(‘04’ , ‘01’ , 50);

insert into SC values(‘04’ , ‘02’ , 30);

insert into SC values(‘04’ , ‘03’ , 20);

insert into SC values(‘05’ , ‘01’ , 76);

insert into SC values(‘05’ , ‘02’ , 87);

insert into SC values(‘06’ , ‘01’ , 31);

insert into SC values(‘06’ , ‘03’ , 34);

insert into SC values(‘07’ , ‘02’ , 89);

insert into SC values(‘07’ , ‘03’ , 98);

50道联系题目及参考答案（若有错，请指出，谢谢！）

1.查询"01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT c.*,d.c,d.score

FROM

  (

    SELECT student.s,sname,c,score

    FROM student,sc

    WHERE student.s=sc.s AND c='01'

  )

  c,

  (

    SELECT student.s,c,score

    FROM student,sc

    WHERE student.s=sc.s AND c='02'

  )d

    WHERE c.s=d.s AND c.score >d.score;

+------+--------+------+-------+------+-------+

| s    | sname | c    | score | c    | score |

+------+--------+------+-------+------+-------+

| 02   |钱电   | 01  |  70.0 | 02   | 60.0 |

| 04   |李云   | 01  |  50.0 | 02   | 30.0 |

+------+--------+------+-------+------+-------+

1.1查询同时存在"01 “课程和” 02 "课程的情况

SELECT A.S,A.C,B.C

FROM

(

  SELECT *

  FROM sc

  WHERE sc.c='01'

)A

  INNER JOIN

(

    SELECT *

    FROM sc

    WHERE sc.c='02'

)B

  ON A.s=B.s;

+------+------+------+

| S    | C   | C    |

+------+------+------+

| 01   | 01  | 02   |

| 02   | 01  | 02   |

| 03   | 01  | 02   |

| 04   | 01  | 02   |

| 05   | 01  | 02   |

+------+------+------+

1.2查询存在"01 “课程但可能不存在” 02 "课程的情况(不存在时显示为NULL )

SELECT A.c,A.c,A.score,B.c,B.score

FROM

(

  SELECT *

  FROM sc

  WHERE sc.c='01'

)A

LEFT JOIN

(

  SELECT *

  FROM sc

  WHERE sc.c='02'

)B

ON A.s=B.s

WHERE B.c is NULL;

+------+------+-------+------+-------+

| C    | C   | score | C    | score |

+------+------+-------+------+-------+

| 01   | 01  |  31.0 | NULL |  NULL |

+------+------+-------+------+-------+

1.3查询不存在"01 “课程但存在” 02 "课程的情况

SELECT *

FROM

(

  SELECT *

  FROM sc

  WHERE sc.c='01'

)A

RIGHT JOIN

(

  SELECT *

  FROM sc

  WHERE sc.c='02'

)B

ON A.s=B.s

WHERE A.c is NULL;

+------+------+-------+------+------+-------+

| S    | C   | score | S    | C    | score |

+------+------+-------+------+------+-------+

| NULL | NULL |  NULL | 07  | 02   |  89.0 |

+------+------+-------+------+------+-------+

2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT st.s,st.sname,avg(score)

FROM student AS st,sc

WHERE st.s=sc.s

GROUP BY st.s

HAVING avg(score)>=60;

+------+--------+------------+

| s    | sname | avg(score) |

+------+--------+------------+

| 01   |赵雷   |  89.66667 |

| 02   |钱电   |  70.00000 |

| 03   |孙风   |  80.00000 |

| 05   |周梅   |  81.50000 |

| 07   |郑竹   |  93.50000 |

+------+--------+------------+

3.查询在SC表存在成绩的学生信息

SELECT st.s,st.ssex,st.sage,sc.c,sc.score

FROM student AS st,sc

WHERE st.s=sc.s

GROUP BY st.s;

+------+------+---------------------+------+-------+

| s    | ssex | sage                | c    | score |

+------+------+---------------------+------+-------+

| 01   |男   | 1990-01-01 00:00:00 | 01   | 80.0 |

| 02   |男   | 1990-12-21 00:00:00 | 01   | 70.0 |

| 03   |男   | 1990-05-20 00:00:00 | 01   | 80.0 |

| 04   |男   | 1990-08-06 00:00:00 | 01   |  50.0|

| 05   |女   | 1991-12-01 00:00:00 | 01   | 76.0 |

| 06   |女   | 1992-03-01 00:00:00 | 01   | 31.0 |

| 07   |女   | 1989-07-01 00:00:00 | 02   | 89.0 |

+------+------+---------------------+------+-------+

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为NULL )

SELECT A.s,sname,COUNT(c)选课总数,score  总成绩

FROM student A

LEFT JOIN sc B

ON A.s=B.s

GROUP BY A.s;

+------+--------+--------------+-----------+

| s    | sname |选课总数    |总成绩    |

+------+--------+--------------+-----------+

| 01   |赵雷   |           3 |      80.0 |

| 02   |钱电   |           3 |      70.0 |

| 03   |孙风   |           3 |      80.0 |

| 04   |李云   |           3 |      50.0 |

| 05   |周梅   |           2 |      76.0 |

| 06   |吴兰   |           2 |      31.0 |

| 07   |郑竹   |           2 |      89.0 |

| 08   |王菊   |           0 |      NULL |

+------+--------+--------------+-----------+

4.1查有成绩的学生信息

SELECT A.s,sname,COUNT(c)选课总数,score  总成绩

FROM student A

INNER JOIN sc B

ON A.s=B.s

GROUP BY A.s ;

+------+--------+--------------+-----------+

| s    | sname |选课总数    |总成绩    |

+------+--------+--------------+-----------+

| 01   |赵雷   |           3 |      80.0 |

| 02   |钱电   |           3 |      70.0 |

| 03   |孙风   |           3 |      80.0 |

| 04   |李云   |           3 |      50.0 |

| 05   |周梅   |           2 |      76.0 |

| 06   |吴兰   |           2 |      31.0 |

| 07   |郑竹   |           2 |      89.0 |

+------+--------+--------------+-----------+

5.查询「李」姓老师的数量

SELEC Tteacher.tname,COUNT(Tname)

FROM teacher 

WHERE tname

LIKE '李%'

GROUP BY tname;

+--------+--------------+

| tname  | COUNT(Tname) |

+--------+--------------+

|李四   |           1 |

+--------+--------------+

6.查询学过「张三」老师授课的同学的信息

SELECT student.*

FROM student

WHERE s

IN

(

  SELECT s

  FROM sc

  WHERE c

  IN

  (

    SELECT c

    FROM course

    WHERE t

    IN

    (

      SELECT t

      FROM teacher

      WHERE tname ='张三'

    )

  )

);

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |

| 02   |钱电   | 1990-12-21 00:00:00 |男   |

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

| 04   |李云   | 1990-08-06 00:00:00 |男   |

| 05   |周梅   | 1991-12-01 00:00:00 |女   |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   |

+------+--------+---------------------+------+

7.查询没有学全所有课程的同学的信息

SELECT st.*,sc.c,sc.score

FROM student AS st,sc

WHERE st.s=sc.s

GROUP BY st.s

HAVING COUNT(st.sname)< 3;

+------+--------+---------------------+------+------+-------+

| S    | Sname | Sage                | Ssex |c    | score |

+------+--------+---------------------+------+------+-------+

| 05   |周梅   | 1991-12-01 00:00:00 |女   | 01  |  76.0 |

| 06   |吴兰   | 1992-03-01 00:00:00 |女   | 01  |  31.0 |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   | 02  |  89.0 |

+------+--------+---------------------+------+------+-------+

8.查询至少有一门课与学号为"01 "的同学所学相同的同学的信息

SELECT *

FROM

(

  SELECT st.*

  FROM student

  AS st,sc

  WHERE st.s=sc.s

  AND sc.c= any

  (

    SELECT c

    FROM sc

    WHERE s='01'

  )

) A

GROUP BY s;

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |

| 02   |钱电   | 1990-12-21 00:00:00 |男   |

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

| 04   |李云   | 1990-08-06 00:00:00 |男   |

| 05   |周梅   | 1991-12-01 00:00:00 |女   |

| 06   |吴兰   | 1992-03-01 00:00:00 |女   |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   |

+------+--------+---------------------+------+

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT st.*

FROM student AS st,sc

WHERE st.s=sc.s

GROUP BY st.s

HAVING st.s<>'01'

AND sum(c)=

(

  SELECT sum(c)

  FROM sc

  WHERE s='01'

  GROUP BY s

);

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 02   |钱电   | 1990-12-21 00:00:00 |男   |

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

| 04   |李云   | 1990-08-06 00:00:00 |男   |

+------+--------+---------------------+------+

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT st.*

FROM student AS st,sc

WHERE st.s=sc.s

AND sc.c in

(

  SELECT c

  FROM course

  WHERE t = any

  (

    SELECT t

    FROM teacher

    WHERE tname='张三'

  )

);

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |

| 02   |钱电   | 1990-12-21 00:00:00 |男   |

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

| 04   |李云   | 1990-08-06 00:00:00 |男   |

| 05   |周梅   | 1991-12-01 00:00:00 |女   |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   |

+------+--------+---------------------+------+

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT A.*

FROM student A ,

(

  SELECT *

  FROM sc

  WHERE score <60

  GROUP BY s

  HAVING COUNT(s)>=2

)B

WHERE A.s = B.s;



#或者



SELECT A.*

FROM student A

INNER JOIN

(

  SELECT *

  FROM sc

  WHERE score <60

  GROUP BY s

  HAVING COUNT(s)>=2

)B

ON A.s = B.s;

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 04   |李云   | 1990-08-06 00:00:00 |男   |

| 06   |吴兰   | 1992-03-01 00:00:00 |女   |

+------+--------+---------------------+------+

12.检索"01 "课程分数小于60，按分数降序排列的学生信息

SELECT st.*,A.score

FROM student AS st,

(

  SELECT *

  FROM sc

  WHERE c='01'

  AND score<60

)A

WHERE st.s=A.s

ORDER BY score DESC;

+------+--------+---------------------+------+-------+

| S    | Sname | Sage                | Ssex |score |

+------+--------+---------------------+------+-------+

| 04   |李云   | 1990-08-06 00:00:00 |男   | 50.0 |

| 06   |吴兰   | 1992-03-01 00:00:00 |女   | 31.0 |

+------+--------+---------------------+------+-------+

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT sc.*,平均值

FROM sc LEFT JOIN

(

  SELECT *,avg(score) as平均值

  FROM sc GROUP BY s

)A

ON sc.s=A.s

ORDER BY平均值DESC;

+------+------+-------+-----------+

| S    | C   | score |平均值   |

+------+------+-------+-----------+

| 07   | 02  |  89.0 |  93.50000 |

| 07   | 03  |  98.0 |  93.50000 |

| 01   | 03  |  99.0 |  89.66667 |

| 01   | 01  |  80.0 |  89.66667 |

| 01   | 02  |  90.0 |  89.66667 |

| 05   | 01  |  76.0 |  81.50000 |

| 05   | 02  |  87.0 |  81.50000 |

| 03   | 01  |  80.0 |  80.00000 |

| 03   | 02  |  80.0 |  80.00000 |

| 03   | 03  |  80.0 |  80.00000 |

| 02   | 01  |  70.0 |  70.00000 |

| 02   | 02  |  60.0 |  70.00000 |

| 02   | 03  |  80.0 |  70.00000 |

| 04   | 02  |  30.0 |  33.33333 |

| 04   | 03  |  20.0 |  33.33333 |

| 04   | 01  |  50.0 |  33.33333 |

| 06   | 01  |  31.0 |  32.50000 |

| 06   | 03  |  34.0 |  32.50000 |

+------+------+-------+-----------+

14.查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT A.c,cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率

FROM course A

LEFT JOIN

(

  SELECT c,max(score)最高分,min(score)最低分,avg(score)平均分

  FROM sc

  GROUP BY c) B

  ON A.c=B.c

  LEFT JOIN

  (

    SELECT c,

    (

      CONVERT

      (

        (

          (

            sum(CASE WHEN score >=60 then 1

                ELSE 0 END

                )

            *1.00

          )/COUNT(*)

        )*100,

        DECIMAL(5,2)))及格率

        FROM sc

        GROUP BY c

      )C

      ON A.c=C.c

      LEFT JOIN

      (

        SELECT c,

        (

          CONVERT

          (

            (

              (

                sum(CASE WHEN score >=70 AND score <80 then 1

                    ELSE 0 END

                   )*1.00

              )/COUNT(*))*100,

              DECIMAL(5,2)

            )

          )中等率

          FROM sc

          GROUP BY c

        )D

        ON A.c=D.c

        LEFT JOIN

        (

          SELECT c,

          (

            CONVERT

            (

              (

                (

                  sum(CASE WHEN score >=80 AND score <90 then 1

                     ELSE 0 END

                     )*1.00

                )/COUNT(*)

              )*100,

              DECIMAL(5,2)

            )

          )优良率

          FROM sc

          GROUP BY c

        )E

        ON A.c=E.c

        LEFT JOIN

        (

          SELECT c,

          (

            CONVERT

            (

              (

                (

                  sum(CASE WHEN score >=90 then 1

                      ELSE 0 END

                     )*1.00

                )/COUNT(*)

              )*100,

              DECIMAL(5,2)

            )

          )优秀率

          FROM sc

          GROUP BY c

        )F

        ON A.c=F.c;


+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

|c    | cname  |最高分    |最低分    |平均分    |及格率    |中等率    |优良率    |优秀率    |

+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

|01   |语文   |     80.0 |      31.0 |  64.50000 |    66.67 |     33.33 |     33.33 |      0.00 |

|02   |数学   |     90.0 |      30.0 |  72.66667 |    83.33 |      0.00 |     50.00 |    16.67 |

|03   |英语   |     99.0 |      20.0 |  68.50000 |    66.67 |      0.00 |     33.33 |    33.33 |

+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

15.按各科成绩进行排序，并显示排名，Score重复时合并保留名次空缺

SELECT *,RANK() OVER(PARTITION BY c ORDER BY score DESC)排名

FROM sc;

+------+------+-------+--------+

| S    | C   | score |排名  |

+------+------+-------+--------+

| 01   | 01  |  80.0 |      1 |

| 03   | 01  |  80.0 |      1 |

| 05   | 01  |  76.0 |      3 |

| 02   | 01  |  70.0 |      4 |

| 04   | 01  |  50.0 |      5 |

| 06   | 01  |  31.0 |      6 |

| 01   | 02  |  90.0 |      1 |

| 07   | 02  |  89.0 |      2 |

| 05   | 02  |  87.0 |      3 |

| 03   | 02  |  80.0 |      4 |

| 02   | 02  |  60.0 |      5 |

| 04   | 02  |  30.0 |      6 |

| 01   | 03  |  99.0 |      1 |

| 07   | 03  |  98.0 |      2 |

| 02   | 03  |  80.0 |      3 |

| 03   | 03  |  80.0 |      3 |

| 06   | 03  |  34.0 |      5 |

| 04   | 03  |  20.0 |      6 |

+------+------+-------+--------+

15.1按各科成绩进行排序，并显示排名，Score重复时合并名次

SELECT *,ROW_NUMBER() OVER(PARTITION BY c ORDER BY score DESC)排名

FROM sc;

+------+------+-------+--------+

| S    | C   | score |排名  |

+------+------+-------+--------+

| 01   | 01  |  80.0 |      1 |

| 03   | 01  |  80.0 |      2 |

| 05   | 01  |  76.0 |      3 |

| 02   | 01  |  70.0 |      4 |

| 04   | 01  |  50.0 |      5 |

| 06   | 01  |  31.0 |      6 |

| 01   | 02  |  90.0 |      1 |

| 07   | 02  |  89.0 |      2 |

| 05   | 02  |  87.0 |      3 |

| 03   | 02  |  80.0 |      4 |

| 02   | 02  |  60.0 |      5 |

| 04   | 02  |  30.0 |      6 |

| 01   | 03  |  99.0 |      1 |

| 07   | 03  |  98.0 |      2 |

| 02   | 03  |  80.0 |      3 |

| 03   | 03  |  80.0 |      4 |

| 06   | 03  |  34.0 |      5 |

| 04   | 03  |  20.0 |      6 |

+------+------+-------+--------+

16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

SELECT s,RANK() OVER(ORDER BY sum(score) DESC)排名,sum(score)

FROM sc

GROUP BY s

ORDER BY sum(score) DESC;

+------+--------+------------+

| s    |排名   | sum(score) |

+------+--------+------------+

| 01   |     1 |      269.0 |

| 03   |     2 |      240.0 |

| 02   |     3 |      210.0 |

| 07   |     4 |      187.0 |

| 05   |     5 |      163.0 |

| 04   |     6 |      100.0 |

| 06   |     7 |       65.0 |

+------+--------+------------+

16.1查询学生的总成绩，并进行排名，总分重复时不保留名次空缺


SELECT s,DENSE_RANK() OVER(ORDER BY sum(score) DESC)排名,sum(score)

FROM sc

GROUP BY s

ORDER BY sum(score) DESC;

+------+--------+------------+

| s    |排名   | sum(score) |

+------+--------+------------+

| 01   |     1 |      269.0 |

| 03   |     2 |      240.0 |

| 02   |     3 |      210.0 |

| 07   |     4 |      187.0 |

| 05   |     5 |      163.0 |

| 04   |     6 |      100.0 |

| 06   |     7 |       65.0 |

+------+--------+------------+

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

SELECT A.c,100到85人数,100到85占比, 85到70人数,85到70占比, 70到60人数,70到60占比,60到0人数,60到0占比  

FROM sc A

LEFT JOIN

(

  SELECT s,c,sum(CASE WHEN score >85 then 1 ELSE 0 END)

  AS 100到85人数,

  CONVERT

  (

    (

      sum(CASE WHEN score >85 then 1 ELSE 0 END)*100

    )/COUNT(score),decimal(5,1)

  ) AS 100到85占比

  FROM sc

  GROUP BY c)B

  ON A.S=B.S

  LEFT JOIN

  (

    SELECT s,c,

    sum(CASE WHEN score >70 AND score<=85 then 1 ELSE 0 END)

    AS 85到70人数,

    CONVERT

    (

      (

        sum(CASE WHEN score >70 AND score<=85 then 1 ELSE 0 END)*100

      )/COUNT(score),decimal(5,1)

    ) AS 85到70占比

    FROM sc

    GROUP BY c)C

    ON A.S=C.S

    LEFT JOIN

    (

      SELECT s,c,sum(CASE WHEN score >60 AND score <=70 then 1 ELSE 0 END)

      AS 70到60人数,

      CONVERT

      (

        (

          sum(CASE WHEN score >60 AND score<=70 then 1 ELSE 0 END)*100

        )/COUNT(score),decimal(5,1)

      ) AS 70到60占比

      FROM sc

      GROUP BY c)D

      ON A.S=D.S

      LEFT JOIN

      (

        SELECT s,c,sum(CASE WHEN score >=0 AND score<=60 then 1 ELSE 0 END)

        AS 60到0人数,

        CONVERT

        (

          (

            sum(CASE WHEN score >=0 AND score <=60 then 1 ELSE 0 END)*100

          )/COUNT(score),decimal(5,1)

        ) AS 60到0占比 FROM sc GROUP BY c)E

        ON A.S=E.S

        WHERE A.C=B.C AND A.C=C.C AND A.C=D.C AND A.C=E.C;

+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
| c    | 100到85人数    | 100到85占比    | 85到70人数    | 85到70占比    | 70到60人数    | 70到60占比    | 60到0人数    | 60到0占比    |
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+
| 01   |              0 |            0.0 |             3 |          50.0 |             1 |          16.7 |            2 |         33.3 |
| 02   |              3 |           50.0 |             1 |          16.7 |             0 |           0.0 |            2 |         33.3 |
| 03   |              2 |           33.3 |             2 |          33.3 |             0 |           0.0 |            2 |         33.3 |
+------+----------------+----------------+---------------+---------------+---------------+---------------+--------------+--------------+

18.查询各科成绩前三名的记录

SELECT *

FROM

(

  SELECT *,row_number() over(PARTITION BY c ORDER BY score DESC)排名

  FROM sc

  ORDER BY c,排名

)A

WHERE 排名<=3;

+------+------+-------+--------+

| S    | C   | score |排名  |

+------+------+-------+--------+

| 01   | 01  |  80.0 |      1 |

| 03   | 01  |  80.0 |      2 |

| 05   | 01  |  76.0 |      3 |

| 01   | 02  |  90.0 |      1 |

| 07   | 02  |  89.0 |      2 |

| 05   | 02  |  87.0 |      3 |

| 01   | 03  |  99.0 |      1 |

| 07   | 03  |  98.0 |      2 |

| 02   | 03  |  80.0 |      3 |

+------+------+-------+--------+

19.查询每门课程被选修的学生数

SELECT c,COUNT(s)

FROM sc

GROUP BY c;

+------+----------+

| c    | COUNT(s) |

+------+----------+

| 01   |       6 |

| 02   |       6 |

| 03   |       6 |

+------+----------+

20.查询出只选修两门课程的学生学号和姓名

SELECT s,sname,COUNT(s)课程数

FROM

(

  SELECT st.s,sname,c

  FROM student AS st,sc

  WHERE st.s=sc.s

)A

GROUP BY s

HAVING COUNT(s)=2;

+------+--------+-----------+

| s   | sname |课程数    |

+------+--------+-----------+

| 05   |周梅   |        2 |

| 06   |吴兰   |        2 |

| 07   |郑竹   |        2 |

+------+--------+-----------+

21.查询男生、女生人数

SELECT ssex,COUNT(ssex)

FROM student st

GROUP BY ssex;

+------+-------------+

| ssex | COUNT(ssex) |

+------+-------------+

|男   |          4 |

|女   |          4 |

+------+-------------+

22.查询名字中含有「风」字的学生信息

SELECT *

FROM student st

WHERE sname LIKE '%风%';

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

+------+--------+---------------------+------+

23.查询同名同性学生名单，并统计同名人数

SELECT s,sname,COUNT(sname)

FROM student

GROUP BY sname

HAVING COUNT(sname) >=2;



Empty set (0.00 sec)

24.查询1990年出生的学生名单

SELECT *

FROM student st

WHERE sage LIKE '1990-%';

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |

| 02   |钱电   | 1990-12-21 00:00:00 |男   |

| 03   |孙风   | 1990-05-20 00:00:00 |男   |

| 04   |李云   | 1990-08-06 00:00:00 |男   |

| 08   |王菊   | 1990-01-20 00:00:00 |女   |

+------+--------+---------------------+------+

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

SELECT *,avg(score)

FROM sc

GROUP BY s

ORDER BY avg(score) DESC,c ASC;

+------+------+-------+------------+

| S    | C   | score | avg(score) |

+------+------+-------+------------+

| 07   | 02  |  89.0 |   93.50000 |

| 01   | 01  |  80.0 |   89.66667 |

| 05   | 01  |  76.0 |   81.50000 |

| 03   | 01  |  80.0 |   80.00000 |

| 02   | 01  |  70.0 |   70.00000 |

| 04   | 01  |  50.0 |   33.33333 |

| 06   | 01  |  31.0 |   32.50000 |

+------+------+-------+------------+

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

SELECT s,sname,avg(score)

FROM

(

  SELECT st.s,sname,score

  FROM student st,sc

  WHERE st.s=sc.s

)A

GROUP BY s

HAVING avg(score)>=85;

+------+--------+------------+

| s    | sname | avg(score) |

+------+--------+------------+

| 01   |赵雷   |  89.66667 |

| 07   |郑竹   |  93.50000 |

+------+--------+------------+

27.查询课程名称为「数学」，且分数低于60的学生姓名和分数

SELECT st.s,score

FROM student AS st,sc

WHERE st.s=sc.s AND score<=60 AND c in

(

  SELECT cname

  FROM course

  WHERE cname = '数学'

);

Empty set (0.00 sec)

28.查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)

SELECT st.*,sc.c,sc.score

FROM student AS st

LEFT JOIN sc

ON st.s = sc.s

ORDER BY st.s,sc.c;

+------+--------+---------------------+------+------+-------+

| S    | Sname | Sage                | Ssex |c    | score |

+------+--------+---------------------+------+------+-------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   | 01  |  80.0 |

| 01   |赵雷   | 1990-01-01 00:00:00 |男   | 02  |  90.0 |

| 01   |赵雷   | 1990-01-01 00:00:00 |男   | 03  |  99.0 |

| 02   |钱电   | 1990-12-21 00:00:00 |男   | 01  |  70.0 |

| 02   |钱电   | 1990-12-21 00:00:00 |男   | 02  |  60.0 |

| 02   |钱电   | 1990-12-21 00:00:00 |男   | 03  |  80.0 |

| 03   |孙风   | 1990-05-20 00:00:00 |男   | 01  |  80.0 |

| 03   |孙风   | 1990-05-20 00:00:00 |男   | 02  |  80.0 |

| 03   |孙风   | 1990-05-20 00:00:00 |男   | 03  |  80.0 |

| 04   |李云   | 1990-08-06 00:00:00 |男   | 01  |  50.0 |

| 04   |李云   | 1990-08-06 00:00:00 |男   | 02  |  30.0 |

| 04   |李云   | 1990-08-06 00:00:00 |男   | 03  |  20.0 |

| 05   |周梅   | 1991-12-01 00:00:00 |女   | 01  |  76.0 |

| 05   |周梅   | 1991-12-01 00:00:00 |女   | 02  |  87.0 |

| 06   |吴兰   | 1992-03-01 00:00:00 |女   | 01  |  31.0 |

| 06   |吴兰   | 1992-03-01 00:00:00 |女   | 03  |  34.0 |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   | 02  |  89.0 |

| 07   |郑竹   | 1989-07-01 00:00:00 |女   | 03  |  98.0 |

| 08   |王菊   | 1990-01-20 00:00:00 |女   | NULL | NULL |

+------+--------+---------------------+------+------+-------+

29.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT st.s,st.sname,c,score

FROM student AS st

INNER JOIN sc

ON st.s=sc.s

WHERE score >=70

ORDER BY st.s,c;

+------+--------+------+-------+

| s    | sname | c    | score |

+------+--------+------+-------+

| 01   |赵雷   | 01  |  80.0 |

| 01   |赵雷   | 02  |  90.0 |

| 01   |赵雷   | 03  |  99.0 |

| 02   |钱电   | 01  |  70.0 |

| 02   |钱电   | 03  |  80.0 |

| 03   |孙风   | 01  |  80.0 |

| 03   |孙风   | 02  |  80.0 |

| 03   |孙风   | 03  |  80.0 |

| 05   |周梅   | 01  |  76.0 |

| 05   |周梅   | 02  |  87.0 |

| 07   |郑竹   | 02  |  89.0 |

| 07   |郑竹   | 03  |  98.0 |

+------+--------+------+-------+

30.查询不及格的课程

SELECT c,score

FROM sc

WHERE score <60

ORDER BY c;

+------+-------+

| c    | score |

+------+-------+

| 01   | 50.0 |

| 01   | 31.0 |

| 02   | 30.0 |

| 03   | 20.0 |

| 03   | 34.0 |

+------+-------+

31.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

SELECT st.s,st.sname,c,score

FROM student AS st,sc

WHERE st.s=sc.s AND c='01' AND score >=80;

+------+--------+------+-------+

| s    | sname | c    | score |

+------+--------+------+-------+

| 01   |赵雷   | 01  |  80.0 |

| 03   |孙风   | 01  |  80.0 |

+------+--------+------+-------+

32.求每门课程的学生人数

SELECT c,COUNT(c)

FROM sc

GROUP BY c;

+------+----------+

| c    | COUNT(c) |

+------+----------+

| 01   |       6 |

| 02   |       6 |

| 03   |       6 |

+------+----------+

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT st.s,st.sname,score

FROM student AS st,sc

WHERE st.s=sc.s AND c in

(

  SELECT c

  FROM course,teacher

  WHERE course.t = teacher.t AND teacher.tname= '张三'

) 

ORDER BY score DESC limit 1 ;

+------+--------+-------+

| s    | sname | score |

+------+--------+-------+

| 01   |赵雷   | 90.0 |

+------+--------+-------+

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩


SELECT * FROM

(

  SELECT st.s,st.sname,score,dense_rank() over

  (

    ORDER BY score DESC

  )排名

    FROM student AS st,sc

  WHERE st.s=sc.s AND c in

  (

    SELECT c

    FROM course,teacher

    WHERE course.t = teacher.t AND teacher.tname = '张三'

  )

)A

WHERE 排名='1';

+------+--------+-------+--------+

| s    | sname | score |排名  |

+------+--------+-------+--------+

| 01   |赵雷   | 90.0 |      1 |

+------+--------+-------+--------+

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT s,c,score

FROM

(

  SELECT max(score),avg(score),COUNT(score),student.s,c,score

  FROM student

  LEFT JOIN sc

  ON student.s=sc.s

  GROUP BY student.s

  HAVING max(score)=avg(score) ANDCOUNT(score)>=2

)A;

+------+------+-------+

| s    | c   | score |

+------+------+-------+

| 03   | 01  |  80.0 |

+------+------+-------+

36.查询每门功成绩最好的前两名

SELECT s,c,score,排名

FROM

(

  SELECT s,c,score,row_number() over(partitionby c ORDER BY score DESC)排名

  FROM sc

  ORDER BY score DESC

)A WHERE 排名<=2

ORDER BY c,排名;

+------+------+-------+--------+

| s    | c   | score |排名  |

+------+------+-------+--------+

| 01   | 01  |  80.0 |      1 |

| 03   | 01  |  80.0 |      2 |

| 01   | 02  |  90.0 |      1 |

| 07   | 02  |  89.0 |      2 |

| 01   | 03  |  99.0 |      1 |

| 07   | 03  |  98.0 |      2 |

+------+------+-------+--------+

37.统计每门课程的学生选修人数(超过5人的课程才统计)。

SELECT c,COUNT(c)

FROM sc

GROUP BY c

HAVING COUNT(c)>5 ;

+------+----------+

| c    | COUNT(c) |

+------+----------+

| 01   |       6 |

| 02   |       6 |

| 03   |       6 |

+------+----------+

38.检索至少选修两门课程的学生学号

SELECT s,COUNT(c)

FROM sc

GROUP BY s

HAVING COUNT(c)>=2;

+------+----------+

| s    | COUNT(c) |

+------+----------+

| 01   |       3 |

| 02   |       3 |

| 03   |       3 |

| 04   |       3 |

| 05   |       2 |

| 06   |       2 |

| 07   |       2 |

+------+----------+

39.查询选修了全部课程的学生信息

SELECT sc.s,st.sname,COUNT(c)

FROM student AS st,sc

WHERE st.s=sc.s

GROUP BY s

HAVING COUNT(c)>=3;

+------+--------+----------+

| s    | sname | COUNT(c) |

+------+--------+----------+

| 01   |赵雷   |       3 |

| 02   |钱电   |       3 |

| 03   |孙风   |       3 |

| 04   |李云   |       3 |

+------+--------+----------+

40.查询各学生的年龄，只按年份来算

SELECT s,sname,(year(curdate())-year(sage))年龄

FROM student

ORDER BY 年龄;

+------+--------+--------+

| s    | sname |年龄   |

+------+--------+--------+

| 06   |吴兰   |    27 |

| 05   |周梅   |    28 |

| 01   |赵雷   |    29 |

| 02   |钱电   |    29 |

| 03   |孙风   |    29 |

| 04   |李云   |    29 |

| 08   |王菊   |    29 |

| 07   |郑竹   |    30 |

+------+--------+--------+

41.按照出生日期来算，当前月日<出生年月的月日则，年龄减一

SELECT s,sname,timestampdiff(year,sage,curdate())年龄

FROM student

ORDER BY 年龄;

+------+--------+--------+

| s    | sname |年龄   |

+------+--------+--------+

| 06   |吴兰   |    26 |

| 05   |周梅   |    27 |

| 02   |钱电   |    28 |

| 03   |孙风   |    28 |

| 04   |李云   |    28 |

| 08   |王菊   |    28 |

| 01   |赵雷   |    29 |

| 07   |郑竹   |    29 |

+------+--------+--------+

42.查询本周过生日的学生

--今天是2019.1.16

SET @day =8-dayofweek(curdate());



SELECT *

FROM student

WHERE date_format(sage, '%m%d') 

BETWEEN date_format(curdate(),'%m%d') 

AND

date_format (date_add(curdate(),interval @day day), '%m%d');

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 08   |王菊   | 1990-01-20 00:00:00 |女   |

+------+--------+---------------------+------+

43.查询下周过生日的学生

set @day =9-dayofweek(curdate());



SELECT *

FROM student

WHERE date_format(sage, '%m%d') 

BETWEEN date_format (date_add(curdate(),interval @day day), '%m%d')

AND

date_format (date_add(curdate(),interval @day+6 day), '%m%d');



Empty set (0.00 sec)

44.查询本月过生日的学生

SELECT *

FROM student

WHERE date_format(sage,'%m')=  date_format(curdate(),'%m');

+------+--------+---------------------+------+

| S    | Sname | Sage                | Ssex |

+------+--------+---------------------+------+

| 01   |赵雷   | 1990-01-01 00:00:00 |男   |

| 08   |王菊   | 1990-01-20 00:00:00 |女   |

+------+--------+---------------------+------+

45.查询下月过生日的学生

SELECT *

FROM student

WHERE date_format(sage,'%m')= date_format(date_add(curdate(),interval 1 month),'%m');



Empty set (0.00 sec)

Hill_GM

关注

3
点赞
踩
28

收藏

觉得还不错? 一键收藏
1
评论
50道SQL练习题及参考答案（原创）

一、基本信息1.学生表Student(S,Sname,Sage,Ssex)–S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别2.课程表Course(C,Cname,T)C 课程编号,Cname 课程名称,T 教师编号3.教师表Teacher(T,Tname)T 教师编号,Tname 教师姓名4.成绩表SC(S,C,score)S 学生编号,...
复制链接

扫一扫