SQL必练50题（附答案）

已于 2024-07-31 17:30:26 修改
阅读量1.4k 收藏 16
点赞数 19
文章标签： sql 数据库 mysql
于 2024-07-31 16:38:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zzhqjnm/article/details/140763640
版权

前言：为帮助大家学习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);

zzhqjnm
关注
  • 19
    点赞
  • 踩
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
要想学好SQL语句必会的50题，也是考试常考的点
zhangyulin54321的专栏
09-07 1万+
-- 一、创建教学系统的数据库，表，以及数据 --student(sno,sname,sage,ssex) 学生表 --course(cno,cname,tno) 课程表 --sc(sno,cno,score) 成绩表 --teacher(tno,tname) 教师表 --1.创建数据库test1 use master GO IF EXISTS (SELECT name F
SQL 50 题（MySQL 版，包括建库建表、插入数据等完整过程，适合复习 SQL 知识点）
热门推荐
代码星辰的博客
11-16 2万+
本文整理了经典的 50 道 SQL 题目，文本分为建库建表、插入数据以及 SQL 50 题这三个部分。
sql50题带建表语句带详细答案（sql高阶函数详解）
啊策策大数据社区
03-23 4589
1. sql基础 ) 建表语句 --建表 --学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL...
经典SQL语句50题
12-02
一个项目涉及到50个SQL语句。（经典）
MySQL练习50题
dieheng7501的博客
03-29 163
这是网上的练习，花了点时间用MySQL做了一下。 初始化数据库： create database db50issues; use db50issues; create table Student(sid varchar(10),sname nvarchar(10),sage datetime,ssex nvarchar(10)); insert into Stude...
SQL经典50题
雷神乐乐的博客
12-08 1万+
SQL经典50题——mysql8.0版本
50道SQL练习题
最新发布
皮卡丘的情绪
06-14 1万+
50道SQL练习题
经典sql50题,附答案,适合初学者练手!
12-14
/* create database test50; use test50; create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10)); insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);...
Sql经典练习题库（附答案）
02-17
以上练习题覆盖了SQL中的各种核心概念和技术，包括子查询、连接（JOIN）、聚合函数（如COUNT、SUM）、条件语句（如IN、ANY、ALL）等。这些练习有助于加深对SQL的理解，并提高解决实际问题的能力。
SQL练习题共50道附答案（MySQL）
Code__rookie的博客
08-31 9753
SQL练习题共50道附答案（MySQL） 数据库表名称和字段 1.学生表 student(s_id,s_name,s_birth,s_sex) -- 学生编号,学生姓名, 出生年月,学生性别 2.课程表 course(c_id,c_name,t_id) -- 课程编号, 课程名称, 教师编号 3.教师表 teacher(t_id,t_name) -– 教师编号,教师姓名 4.成绩表 score(s_id,c_id,s_score) --学生编号,课程编号,分数 建立
SQl server练习50题
10-28
SQl server练习50题，基础练习，查询、多表查询等
sql语句练习题及答案
03-28
sql数据库练习题
2017年江西省信息技术高考真题(附答案)
06-08
答案解析部分将提供每道题的正确答案和解题思路，有助于考生理解和掌握知识。 3. **核心知识点** - **计算机基础知识**：包括计算机系统组成、操作系统原理、输入输出设备、文件管理等。 - **网络技术**：涵盖TCP...
MySQL数据库详细教程（附sql语句练习题及答案）
qq_45821255的博客
05-04 1673
本文详细讲解了MYSQL数据库的SQL语句的快速入门使用及使用细节和注意事项，一看就会！每个知识点都有SQL语句的示例，绝对是小白学习的最佳教程！！
sql语句练习50题(含解析)
m0_45210394的博客
09-21 1万+
sql练习笔记
MySQL全方位练习（学生表 教师表 课程表 分数表）
weixin_30298497的博客
08-01 2757
一、创建表并初始化数据 1、表说明 student(学生表)、teacher(教师表)、course(课程表)、sc(分数表) 2、创建表 //学生表create table student( sno varchar2(10) primary key, sname varchar2(20), sage number(2), ...
经典sql面试及答案（50）
chuyinquan2438的博客
10-10 1156
经典sql面试及答案（50） （新链接）SQL 经典五十道题 联想笔记本数据库csdn1010 复习 重点知识/关键词 like、rlike MySQL EXTRACT() 函数 行列转换&多行合并为一行（第40）【数据库表行列转置的实现_My...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值