SQL必练50题（附答案）

媒介：为资助大家学习SQL，在此提供SQL必练50题供大家训练

本篇延用第二章所建数据库，在此提供建表语句。

1. -- 创建学生表并插入数据
2. DROP TABLE student;
3. CREATE TABLE student (
4.   s_id INT NOT NULL PRIMARY KEY,
5.   s_name VARCHAR ( 10 ) NOT NULL,
6.   s_sex VARCHAR ( 5 ) NOT NULL,
7. s_age INT NOT NULL
8. );
9. DELETE FROM student;
10. INSERT INTO student VALUES(1,'胡图图','男',6);
11. INSERT INTO student VALUES(2,'牛爷爷','男',55);
12. INSERT INTO student VALUES(3,'壮壮妈','女',33);
13. INSERT INTO student VALUES(4,'胡英俊','男',32);
14. INSERT INTO student VALUES(5,'壮壮','男',7);
15. INSERT INTO student VALUES(6,'张小丽','女',31);
16. INSERT INTO student VALUES(7,'小怪','男',2);
17. -- 创建教师表并插入数据
18. DROP TABLE teacher;
19. CREATE TABLE teacher (
20. t_id INT,
21. t_name VARCHAR ( 10 ));
22. DELETE FROM teacher;
23. INSERT INTO teacher VALUES(1,'图图妈');
24. INSERT INTO teacher VALUES(2,'神厨小福贵');
25. INSERT INTO teacher VALUES(3,'健康哥哥');
26. -- 创建成绩表并插入数据
27. DROP TABLE score;
28. CREATE TABLE score ( s_id INT, c_id INT, s_score INT );
29. DELETE FROM score;
30. INSERT INTO score VALUES(1,2,90);
31. INSERT INTO score VALUES(1,3,50);
32. INSERT INTO score VALUES(2,1,60);
33. INSERT INTO score VALUES(2,3,80);
34. INSERT INTO score VALUES(3,1,80);
35. INSERT INTO score VALUES(3,2,50);
36. INSERT INTO score VALUES(3,3,80);
37. INSERT INTO score VALUES(4,1,70);
38. INSERT INTO score VALUES(4,2,60);
39. INSERT INTO score VALUES(4,3,70);
40. INSERT INTO score VALUES(5,2,85);
41. INSERT INTO score VALUES(5,3,55);
42. INSERT INTO score VALUES(6,1,95);
43. INSERT INTO score VALUES(6,2,65);
44. INSERT INTO score VALUES(6,3,100);
45. -- 创建课程表并插入数据
46. DROP TABLE course;
47. CREATE TABLE course (
48. c_id INT PRIMARY KEY,
49. c_name VARCHAR ( 10 ) NOT NULL,
50. t_id INT NOT NULL );
51. DELETE FROM course;
52. INSERT INTO course VALUES(1,'厨艺',2);
53. INSERT INTO course VALUES(2,'体育',3);
54. INSERT INTO course VALUES(3,'生活',1);

复制代码

知识拓展：

        UNION 操纵符：用于合并两个或多个 SELECT 语句的结果。利用 UNION 时，每个 SELECT 语句必须具有雷同数目标列，且对应列的数据类型必须相似。UNION 操纵符默认会去除重复的记录，如果需要保存所有重复记录，可以利用 UNION ALL 操纵符。
SQL UNION 语法
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
        在SQL中，ROW_NUMBER() 是一个窗口函数（Window Function），它用于为结果集中的每一行分配一个唯一的连续整数。这个整数是根据OVER()子句中指定的排序顺序来分配的。PARTITION BY（可选）：指定分区列。如果指定了分区列，ROW_NUMBER()将在每个分区内独立盘算。如果不指定分区列，则整个结果集被视为一个单一的分区。ORDER BY：指定用于分配行号的排序顺序。这是必须的，因为ROW_NUMBER()需要知道如何对行进行排序以便分配唯一的连续整数。
基本语法
ROW_NUMBER() OVER (  
    PARTITION BY column1, column2, ...  
    ORDER BY columnA, columnB, ...  )
题目：

• 查询课程编号为1的课程比2的课程成绩高的所有学生的学号（子查询+连接）
  
• 查询平均成绩大于60分的学生的学号和平均成绩
  
• 查询所有学生的学号、姓名、选课数、总成绩
  
• 查询姓“刘”的老师的个数
  
• 查询没学过“图图妈”老师课的学生的学号、姓名
  
• 查询学过“康健哥哥”老师所教的所有课的同砚的学号、姓名
  
• 查询学过编号为1的课程并且也学过编号为2的课程的学生的学号、姓名
  
• 查询课程编号为2的课程总成绩
  
• 查询所有课程成绩小于60分的学生的学号、姓名（）未选课程则成绩视为0，最大值小于60则均小于60，别的思量无成绩学生。
  
• 查询至少有一门课与学号为1的学生所学课程雷同的学生的学号和姓名 （难）
  
• 查询和1号同砚所学课程完全雷同的其他同砚的学号（难）（NOT IN与COUNT）
  
• 查询没学过"神厨小福贵"老师讲授的任一门课程的学生姓名
  
• 查询两门及其以上不合格课程的同砚的学号，姓名及其平均成绩（连接区块化完成任务，COUNT内不可加查询）
  
• 检索课程1分数小于60，按分数降序排列的学生信息
  
• 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)
  
• 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，合格率，中等率，优良率，优秀率（按课程分组，逻辑）
  
• 按各科成绩进行排序，并显示排名(难)（1）按照每个科目进行一次排名 （2）对所有分数进行一个总的排名
  
• 查询学生的总成绩并进行排名
  
• 查询不同老师所教不同课程平均分从高到低显示
  
• 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
  
• 利用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
  
• 查询学生平均成绩及其名次。观察点：row_number 的用法,记得里面可以指定是升序照旧降序排列
  
• 查询各科成绩前三名的记录（不思量成绩并列环境）
  
• 查询每门课程被选修的学生数
  
• 查询出只有两门课程的全部学生的学号和姓名
  
• 查询男生、女生人数
  
• 查询名字中含有"状"字的学生信息
  
• 查询1990年出生的学生名单
  -- 观察点：Year()、Month()、Day()
  -- 变形题：查询2月出生的学生名单，查询2号出生的学生名单
  
• 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
  
• 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩雷同时，按课程号降序排列
  
• 查询课程名称为"生活"，且分数低于60的学生姓名和分数
  
• 查询所有学生的课程及分数环境
  
• 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
  
• 查询不合格的课程并按课程号从大到小排列
  
• 查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
  
• 求每门课程的学生人数
  
• 查询选修“图图妈”老师所授课程的学生中成绩最高的学生姓名及其成绩
  
• 查询不同课程成绩雷同的学生的学生编号、课程编号、学生成绩 （难）
  这个学生至少修了两门课以上，且这些课程成绩都雷同
  
• 查询每门功成绩最好的前两名
  
• 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数雷同，按课程号升序排列
  
• 检索至少选修两门课程的学生学号
  
• 查询选修了全部课程的学生信息
  
• 查询各学生的年龄
  
• 查询两门以上不合格课程的同砚的学号及其平均成绩
  
• 查询本月过生日的学生
  
• 查询下一个月过生日的学生
  

答案：

1.查询课程编号为1的课程比2的课程成绩高的所有学生的学号（子查询+连接）
方法一：分离题干限定条件课程号得两小表与结果对应表联立（之前我们已经讲过AS命名表别名的用法，现实上AS还可以命名列别名，列名称要加英文双引号，上述两种方法均可省略AS）

SELECT st.*,a.s_score AS "课程1成绩",b.s_score AS "课程2成绩"

FROM student as st

INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 1 ) AS a ON st.s_id = a.s_id

INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 2 ) AS b ON st.s_id = b.s_id

WHERE a.s_score > b.s_score;

方法二：分离score表得单课程成绩表联立学生号比较成绩。

SELECT a.s_id

FROM ( SELECT s_id, s_score FROM score WHERE c_id = 1 ) AS a

INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = 2 ) AS b ON a.s_id = b.s_id

WHERE a.s_score > b.s_score;

SELECT *

FROM ( SELECT s_id, s_score FROM score WHERE c_id = 1 ) AS a

INNER JOIN ( SELECT s_id, s_score FROM score WHERE c_id = 2 ) AS b ON a.s_id = b.s_id

WHERE a.s_score > b.s_score;

2.查询平均成绩大于60分的学生的学号和平均成绩

SELECT s_id, AVG(s_score) AS avg_score FROM score GROUP BY s_id HAVING AVG(s_score) > 60；

3.查询所有学生的学号、姓名、选课数、总成绩

SELECT

st.s_id,st.s_name,COUNT(s.c_id) AS "选课数",SUM(case WHEN s.s_score is NULL THEN 0 ELSE s.s_score END) AS "总成绩"

FROM

student AS st

LEFT JOIN score AS s ON st.s_id = s.s_id

GROUP BY st.s_id;

GROUP BY与函数结合；case WHEN用法。
4.查询姓“刘”的老师的个数

SELECT COUNT(1) FROM teacher WHERE t_name LIKE '刘%';

5.查询没学过“图图妈”老师课的学生的学号、姓名

SELECT s_id,s_name FROM student

WHERE s_id NOT IN ( SELECT st.s_id FROM student AS st

INNER JOIN score AS sc ON st.s_id = sc.s_id

INNER JOIN course AS c ON c.c_id = sc.c_id

INNER JOIN teacher AS t ON t.t_id = c.t_id WHERE t.t_name = '图图妈');

6.查询学过“康健哥哥”老师所教的所有课的同砚的学号、姓名

SELECT s_id,s_name FROM student WHERE s_id IN (

SELECT st.s_id FROM student AS st

INNER JOIN score AS sc ON st.s_id = sc.s_id

INNER JOIN course AS c ON c.c_id = sc.c_id

INNER JOIN teacher AS t ON t.t_id = c.t_id WHERE t_name = '康健哥哥'

);

7.查询学过编号为1的课程并且也学过编号为2的课程的学生的学号、姓名
内连接作并且用。

SELECT st.s_id,st.s_name

FROM student as st

INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 1 ) AS a ON st.s_id = a.s_id

INNER JOIN ( SELECT s_id,s_score FROM score WHERE c_id = 2 ) AS b ON st.s_id = b.s_id;

8、查询课程编号为2的课程总成绩

SELECT c_id,SUM(s_score) FROM score WHERE c_id = 2；

9、查询所有课程成绩小于60分的学生的学号、姓名（）未选课程则成绩视为0
最大值小于60则均小于60，别的思量无成绩学生。
方法一：INNER JOIN 后OR用法

SELECT

DISTINCT st.s_id,

st.s_name

FROM

student AS st

INNER JOIN ( SELECT s_id FROM score GROUP BY s_id HAVING MAX( s_score ) < 60 ) AS t ON st.s_id = t.s_id

OR st.s_id not in (SELECT DISTINCT s_id FROM score);

方法二：子查询（未出现重复以及score表缺失项错补，结果更优？）

SELECT

a.s_id,

a.s_name

FROM

student AS a

WHERE

( SELECT max( s_score ) FROM score GROUP BY s_id HAVING s_id = a.s_id ) < 60

OR a.s_id NOT IN ( SELECT DISTINCT s_id FROM score );

10、查询至少有一门课与学号为1的学生所学课程雷同的学生的学号和姓名 （难）

SELECT DISTINCT st.s_id,st.s_name FROM student AS st

INNER JOIN score as sc ON st.s_id = sc.s_id

WHERE sc.c_id IN(

SELECT c_id FROM score WHERE s_id = 1

) AND st.s_id != 1;

11、查询和1号同砚所学课程完全雷同的其他同砚的学号（难）（NOT IN与COUNT）

SELECT

s_id,

s_name

FROM

student

WHERE

s_id IN (

SELECT s_id FROM score WHERE s_id != 1 GROUP BY s_id

HAVING COUNT( * ) = ( SELECT COUNT( c_id ) FROM score WHERE s_id = 1 )

)

AND s_id NOT IN ( SELECT DISTINCT s_id FROM score WHERE c_id NOT IN ( SELECT c_id FROM score WHERE s_id = 1 ) );

12、查询没学过"神厨小福贵"老师讲授的任一门课程的学生姓名

SELECT

s_id,

s_name

FROM

student

WHERE

s_id NOT IN (

SELECT

s_id

FROM

score AS sc

INNER JOIN course AS c ON sc.c_id = c.c_id

INNER JOIN teacher AS t ON t.t_id = c.t_id

WHERE

t.t_name = '神厨小福贵'

);

13、查询两门及其以上不合格课程的同砚的学号，姓名及其平均成绩（连接区块化完成任务，COUNT内不可加查询）

SELECT

  t3.s_id,

  avg( t4.s_score )

FROM

  (

  SELECT

    t1.s_id

  FROM

    student t1

    INNER JOIN ( SELECT * FROM score WHERE s_score < 60 ) t2 ON t1.s_id = t2.s_id

  GROUP BY

    t1.s_id

  HAVING

    count( 1 )>= 2

  ) t3

  INNER JOIN score t4 ON t3.s_id = t4.s_id

GROUP BY

  t3.s_id;

14、检索课程1分数小于60，按分数降序排列的学生信息

SELECT

st.*

FROM

student AS st,

score AS sc

WHERE

st.s_id = sc.s_id

AND sc.c_id = 1

AND sc.s_score < 60

ORDER BY

sc.s_score DESC;

15、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)

SELECT s_id,s_name,

(select s_score from score where s_id = st.s_id and c_id = 1) as '1_score',

(select s_score from score where s_id = st.s_id and c_id = 2) as '2_score',

(select s_score from score where s_id = st.s_id and c_id = 3) as '3_score',

(select AVG(s_score) from score where s_id = st.s_id) as 'avg_score'

FROM student AS st

ORDER BY avg_score DESC;

16、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，合格率，中等率，优良率，优秀率（按课程分组，逻辑）

SELECT

sc.c_id '课程ID',

co.c_name '课程name',

MAX(sc.s_score) '最高分',

MIN(sc.s_score) '最低分',

AVG(sc.s_score) '平均分',

(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score >= 60)/COUNT(*) '合格率',

(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score BETWEEN 70 AND 80)/COUNT(*) '中等率',

(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score BETWEEN 80 AND 90)/COUNT(*) '优良率',

(SELECT COUNT(*) FROM score WHERE c_id = sc.c_id AND s_score >= 90 )/COUNT(*) '优秀率'

FROM score as sc,course AS co

WHERE sc.c_id = co.c_id

GROUP BY sc.c_id;

17、按各科成绩进行排序，并显示排名(难)
（1）按照每个科目进行一次排名

SELECT

  s_id,

  c_id,

  s_score,

  row_number() over ( PARTITION BY c_id ORDER BY s_score DESC ) AS 'm_rank'

FROM

  score

ORDER BY

  c_id;

• 对所有分数进行一个总的排名
  

select s_id,c_id,s_score,row_number() over (order by s_score desc) as 'm_rank'

from score

order by m_rank;

18、查询学生的总成绩并进行排名

SELECT st.s_id,st.s_name,

 (case when sum(s_score) is Null then 0 else sum(s_score) end) as "总分",

 rank() over (ORDER BY sum(s_score) DESC) AS 'rank'

FROM student AS st LEFT JOIN score as sc

ON st.s_id = sc.s_id

GROUP BY st.s_id;

19、查询不同老师所教不同课程平均分从高到低显示
三表联立GROUP BY利用中间表字段

方法一：

SELECT t.t_id,t.t_name,co.c_id,co.c_name,AVG(sc.s_score) AS 's_avg'

FROM teacher AS t

INNER JOIN course AS co ON t.t_id = co.t_id

INNER JOIN score AS sc ON co.c_id = sc.c_id

GROUP BY sc.c_id

ORDER BY s_avg DESC;

方法二：

SELECT t.t_id,t.t_name,co.c_id,co.c_name,AVG(sc.s_score) AS 's_avg'

FROM teacher AS t,course AS co,score AS sc

WHERE t.t_id = co.t_id AND co.c_id = sc.c_id

GROUP BY sc.c_id

ORDER BY s_avg DESC;

20、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（紧张 25类似）
方法一：

(select c_id,a.s_id,s_name,s_score,row_number() over (order by s_score desc) as 'rank'

from score as a right join student as b

on b.s_id = a.s_id

where c_id = 1

order by c_id,s_score desc

limit 1,2)

union all

(select c_id,a.s_id,s_name,s_score,row_number() over (order by s_score desc) as 'rank'

from score as a right join student as b

on b.s_id = a.s_id

where c_id = 2

order by c_id,s_score desc

limit 1,2)

union all

(select c_id,a.s_id,s_name,s_score,row_number() over (order by s_score desc) as 'rank'

from score as a right join student as b

on b.s_id = a.s_id

where c_id = 3

order by c_id,s_score desc

limit 1,2);

方法二：

SELECT * FROM

(SELECT sc.c_id,st.s_name,sc.s_score,row_number() over (PARTITION BY c_id ORDER BY s_score DESC) AS 'm_rank'

FROM score AS sc INNER JOIN student AS st

ON sc.s_id = st.s_id) AS c

WHERE m_rank IN (2,3);

• 利用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
  

select

distinct sc.c_id,

co.c_name AS "课程名称",

(select count(*) from score where c_id = sc.c_id) "总数",

(select count(*) from score where c_id = sc.c_id and s_score between 85 and 100) AS "100-85",

(select count(*) from score where c_id = sc.c_id and s_score between 70 and 85) AS "85-70",

(select count(*) from score where c_id = sc.c_id and s_score between 60 and 70) AS "70-60",

(select count(*) from score where c_id = sc.c_id and s_score < 60) AS "<60"

from score as sc, course as co

where sc.c_id = co.c_id;

• 查询学生平均成绩及其名次
  

观察点：row_number 的用法,记得里面可以指定是升序照旧降序排列

SELECT sc.s_id,st.s_name,AVG(sc.s_score),row_number() over (ORDER BY AVG(sc.s_score) DESC) AS 'm_rank'

FROM score AS sc,student AS st

WHERE sc.s_id = st.s_id

GROUP BY sc.s_id

ORDER BY m_rank ;

• 查询各科成绩前三名的记录（不思量成绩并列环境）
  

为达到“各科”，故利用“IN”并外附select查询。（对各组分组后无法利用limit）

SELECT * FROM(

SELECT c_id,s_id,s_score,row_number() over (PARTITION by c_id ORDER BY s_score DESC) AS 'm_rank'

FROM score

) AS t

WHERE m_rank IN (1,2,3);

24、查询每门课程被选修的学生数

SELECT c_id,COUNT(1) AS '学生数'

FROM score GROUP BY c_id;

25、查询出只有两门课程的全部学生的学号和姓名

方法一：

SELECT sc.s_id,st.s_name FROM score AS sc

INNER JOIN student AS st ON sc.s_id = st.s_id

GROUP BY sc.s_id HAVING COUNT(*) =2;

方法二：

select s_id,s_name

from student where s_id in(

select s_id

from score

group by s_id

having count(*) = 2);

26、查询男生、女生人数

SELECT s_sex,COUNT(*) FROM student

GROUP BY s_sex;

27、查询名字中含有"状"字的学生信息

SELECT * FROM student

WHERE s_name LIKE '%壮%';

28、查询1990年出生的学生名单
-- 观察点：Year()、Month()、Day()
-- 变形题：查询2月出生的学生名单，查询2号出生的学生名单

SELECT * FROM student

WHERE YEAR(s_birth)  = 1990

SELECT * FROM student

WHERE MONTH(s_birth)  = 1

SELECT * FROM student

WHERE DAY(s_birth)  = 1

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

SELECT st.s_id,st.s_name,AVG(sc.s_score) AS '平均成绩'

FROM student AS st

INNER JOIN score AS sc

ON st.s_id = sc.s_id

GROUP BY sc.s_id HAVING AVG(sc.s_score) >= 85;

30、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩雷同时，按课程号降序排列

SELECT c_id,AVG(s_score) AS avg_score FROM score

GROUP BY c_id

ORDER BY avg_score ASC,c_id DESC ;

• 查询课程名称为"生活"，且分数低于60的学生姓名和分数
  

SELECT st.s_id,st.s_name,sc.s_score FROM student AS st,score AS sc,course AS co

WHERE st.s_id = sc.s_id AND sc.c_id = co.c_id

AND co.c_name = '生活' AND sc.s_score < 60

32、查询所有学生的课程及分数环境
思绪同17题
方法一：

select

distinct c.s_id,

c.s_name,

(select (case when s_score is NULL then 0 else s_score end) from score as a,course as b where a.c_id = b.c_id and a.s_id = c.s_id and c_name = '厨艺') "厨艺",

(select (case when s_score is NULL then 0 else s_score end) from score as a,course as b where a.c_id = b.c_id and a.s_id = c.s_id and c_name = '体育') "体育",

(select (case when s_score is NULL then 0 else s_score end) from score as a,course as b where a.c_id = b.c_id and a.s_id = c.s_id and c_name = '生活') "生活"

from student as c,score as d

where c.s_id = d.s_id;

子查询设计course表与score表用于得到课程分数列，这里用不到student列因此无需三表联查，后续条件为三表建立接洽制止冗余（本质为两表连接时重名字段系统主动编号作为不同字段），而后为得到学生id与name，再团结。
方法二：

select sc.s_id "id",

st.s_name "姓名",

MAX(case when sc.c_id='01' then s_score else null end) "01_score",

MAX(case when sc.c_id='02' then s_score else null end) "02_score",

MAX(case when sc.c_id='03' then s_score else null end) "03_score",

avg(sc.s_score) "平均成绩"

from score as sc,student as st

where sc.s_id = st.s_id

group by sc.s_id

order by "平均成绩";

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

SELECT st.s_id,st.s_name,co.c_name,sc.s_score FROM student AS st,score AS sc,course AS co

WHERE st.s_id = sc.s_id AND sc.c_id = co.c_id AND sc.s_score > 70;

34、查询不合格的课程并按课程号从大到小排列

SELECT s_id,c_id

FROM score

WHERE s_score<60

ORDER BY c_id DESC;

35、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名

SELECT st.s_id,st.s_name,sc.c_id,sc.s_score FROM student AS st,score AS sc

WHERE st.s_id = sc.s_id AND sc.s_score > 80 AND sc.c_id = '03';

36、求每门课程的学生人数

SELECT c_id,COUNT(*) AS total FROM score

GROUP BY c_id;

37、查询选修“图图妈”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT

st.s_id,

st.s_name,

sc.s_score

FROM

student AS st,

score AS sc,

course AS co,

teacher AS t

WHERE

st.s_id = sc.s_id

AND sc.c_id = co.c_id

AND co.t_id = t.t_id

AND t.t_name = '图图妈'

ORDER BY

sc.s_score DESC

LIMIT 1;

38、查询不同课程成绩雷同的学生的学生编号、课程编号、学生成绩 （难）
这个学生至少修了两门课以上，且这些课程成绩都雷同

select a.s_id,st.s_name,a.s_score

from(

SELECT s_id,s_score FROM score

WHERE s_id in (SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*) >= 2)

GROUP BY c_id,s_score

) AS a

INNER JOIN student AS st on st.s_id = a.s_id

GROUP BY s_id,s_score

HAVING COUNT(*) = 1;

SELECT

  st.s_id,

  st.s_name,

  a.s_score

FROM

  ( SELECT s_id, s_score FROM score WHERE s_id IN ( SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*) >= 2 ) ) AS a

  INNER JOIN student AS st ON st.s_id = a.s_id

GROUP BY

  s_id,

  s_score

HAVING

  COUNT(*) >= 3;

39、查询每门功成绩最好的前两名

SELECT c.* FROM (

SELECT so.c_id,st.s_name,so.s_score,row_number() over (PARTITION by so.c_id ORDER BY so.s_score DESC) AS 'm_rank'

FROM score AS so INNER JOIN student as st ON st.s_id = so.s_id

) AS c

WHERE m_rank in(1,2);

40、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数雷同，按课程号升序排列

SELECT c_id,COUNT(*) FROM score

GROUP BY c_id HAVING COUNT(*) > 5

ORDER BY COUNT(*) DESC,c_id ASC;

41、检索至少选修两门课程的学生学号

SELECT s_id FROM score

GROUP BY s_id HAVING COUNT(*) >= 2;

42、查询选修了全部课程的学生信息

SELECT *

FROM student

WHERE s_id IN(SELECT

s_id

FROM

  score

GROUP BY

  s_id

HAVING

  count(*)=(

  SELECT

    COUNT(*)

FROM

  course));

43、查询各学生的年龄
方法一：仅年份相减，不精准，忽略未到生日，出现偏大，改进：比较月份与日期

SELECT

  s_name,(

  SELECT YEAR

    (

    NOW()) - YEAR ( s_birth )) AS '年龄'

FROM

  student;

方法二：取整大概会偏大，改进：round（*，1）保存一位小数，但不美观

select st.*, round(datediff(now(),st.s_birth)/365) as "年龄"

from student as st;

方法三：向下取整，无偏差且美观，可以根据需要选择方法

select st.*, floor(datediff(now(),st.s_birth)/365) as "年龄"

from student as st;

44、查询两门以上不合格课程的同砚的学号及其平均成绩

SELECT s_id AS '学号',AVG(s_score) AS "平均成绩"

FROM score

WHERE s_score < 60

GROUP BY s_id HAVING COUNT(*) > 2;

45、查询本月过生日的学生

SELECT * FROM student

WHERE  MONTH(NOW()) = MONTH(s_birth);

46、查询下一个月过生日的学生

select * from student

where  month(now()) + 1 ) % 12 = month(s_birth);

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。