0
0

SQL必练50题（附答案）

前言：为帮助大家学习SQL，在此提供SQL必练50题供大家练习

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

-- 创建学生表并插入数据
DROP TABLE student;
CREATE TABLE student (
  s_id INT NOT NULL PRIMARY KEY,
  s_name VARCHAR ( 10 ) NOT NULL,
  s_sex VARCHAR ( 5 ) NOT NULL,
s_age INT NOT NULL 
);
DELETE FROM student;
INSERT INTO student VALUES(1,'胡图图','男',6);
INSERT INTO student VALUES(2,'牛爷爷','男',55);
INSERT INTO student VALUES(3,'壮壮妈','女',33);
INSERT INTO student VALUES(4,'胡英俊','男',32);
INSERT INTO student VALUES(5,'壮壮','男',7);
INSERT INTO student VALUES(6,'张小丽','女',31);
INSERT INTO student VALUES(7,'小怪','男',2);
-- 创建教师表并插入数据
DROP TABLE teacher;
CREATE TABLE teacher (
t_id INT,
t_name VARCHAR ( 10 ));
DELETE FROM teacher;
INSERT INTO teacher VALUES(1,'图图妈');
INSERT INTO teacher VALUES(2,'神厨小福贵');
INSERT INTO teacher VALUES(3,'健康哥哥');
-- 创建成绩表并插入数据
DROP TABLE score;
CREATE TABLE score ( s_id INT, c_id INT, s_score INT );
DELETE FROM score;
INSERT INTO score VALUES(1,2,90);
INSERT INTO score VALUES(1,3,50);
INSERT INTO score VALUES(2,1,60);
INSERT INTO score VALUES(2,3,80);
INSERT INTO score VALUES(3,1,80);
INSERT INTO score VALUES(3,2,50);
INSERT INTO score VALUES(3,3,80);
INSERT INTO score VALUES(4,1,70);
INSERT INTO score VALUES(4,2,60);
INSERT INTO score VALUES(4,3,70);
INSERT INTO score VALUES(5,2,85);
INSERT INTO score VALUES(5,3,55);
INSERT INTO score VALUES(6,1,95);
INSERT INTO score VALUES(6,2,65);
INSERT INTO score VALUES(6,3,100);
-- 创建课程表并插入数据
DROP TABLE course;
CREATE TABLE course ( 
c_id INT PRIMARY KEY, 
c_name VARCHAR ( 10 ) NOT NULL, 
t_id INT NOT NULL );
DELETE FROM course;
INSERT INTO course VALUES(1,'厨艺',2);
INSERT INTO course VALUES(2,'体育',3);
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. 查询课程编号为1的课程比2的课程成绩高的所有学生的学号（子查询+连接）
  2. 查询平均成绩大于60分的学生的学号和平均成绩
  3. 查询所有学生的学号、姓名、选课数、总成绩
  4. 查询姓“刘”的老师的个数
  5. 查询没学过“图图妈”老师课的学生的学号、姓名
  6. 查询学过“健康哥哥”老师所教的所有课的同学的学号、姓名
  7. 查询学过编号为1的课程并且也学过编号为2的课程的学生的学号、姓名
  8. 查询课程编号为2的课程总成绩
  9. 查询所有课程成绩小于60分的学生的学号、姓名（）未选课程则成绩视为0，****最大值小于60则均小于60，此外考虑无成绩学生。
  10. 查询至少有一门课与学号为1的学生所学课程相同的学生的学号和姓名 （难）
  11. 查询和1号同学所学课程完全相同的其他同学的学号（难）（NOT IN与COUNT）
  12. 查询没学过"神厨小福贵"老师讲授的任一门课程的学生姓名
  13. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（连接区块化完成任务，COUNT内不可加查询）
  14. 检索课程1分数小于60，按分数降序排列的学生信息
  15. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(难)
  16. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（按课程分组，逻辑）
  17. 按各科成绩进行排序，并显示排名(难)（1）按照每个科目进行一次排名 （2）对所有分数进行一个总的排名
  18. 查询学生的总成绩并进行排名
  19. 查询不同老师所教不同课程平均分从高到低显示
  20. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
  21. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
  22. 查询学生平均成绩及其名次。考察点：row_number 的用法,记得里面可以指定是升序还是降序排列
  23. 查询各科成绩前三名的记录（不考虑成绩并列情况）
  24. 查询每门课程被选修的学生数
  25. 查询出只有两门课程的全部学生的学号和姓名
  26. 查询男生、女生人数
  27. 查询名字中含有"状"字的学生信息
  28. 查询1990年出生的学生名单-- 考察点：Year()、Month()、Day()-- 变形题：查询2月出生的学生名单，查询2号出生的学生名单
  29. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
  30. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
  31. 查询课程名称为"生活"，且分数低于60的学生姓名和分数
  32. 查询所有学生的课程及分数情况
  33. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
  34. 查询不及格的课程并按课程号从大到小排列
  35. 查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
  36. 求每门课程的学生人数
  37. 查询选修“图图妈”老师所授课程的学生中成绩最高的学生姓名及其成绩
  38. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （难）这个学生至少修了两门课以上，且这些课程成绩都相同
  39. 查询每门功成绩最好的前两名
  40. 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
  41. 检索至少选修两门课程的学生学号
  42. 查询选修了全部课程的学生信息
  43. 查询各学生的年龄
  44. 查询两门以上不及格课程的同学的学号及其平均成绩
  45. 查询本月过生日的学生
  46. 查询下一个月过生日的学生

答案：

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;

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

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);

  1. 使用分段[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;

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

考察点：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 ;

  1. 查询各科成绩前三名的记录（不考虑成绩并列情况）

为达到“各科”，故使用“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 ;

  1. 查询课程名称为"生活"，且分数低于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);

标签： sql 数据库 mysql
本文转载自: https://blog.csdn.net/zzhqjnm/article/details/140763640
版权归原作者 zzhqjnm 所有， 如有侵权，请联系我们删除。
登录后发布评论

“SQL必练50题（附答案）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

将 GitHub 关联到 Git 的详细教程

大数据新视界 -- 大数据大厂之 Impala 性能优化：融合人工智能预测的资源预分配秘籍（上）（29 / 30）

NAT的基本介绍

（udp）网络编程套接字Linux（整理）

java+vue计算机毕设茶叶产品质量安全可追溯系统【源码+程序+论文+开题】

Postman：Postman API测试：Postman数据驱动测试实践

解决：IntelliJ IDEA 项目中代码文件不能运行的问题(即：J 标文件的问题)

文章导航