30道SQL经典笔试题及其答案解析

已于 2024-02-25 13:16:24 修改
阅读量7.4k 收藏 135
点赞数 14
文章标签： mysql sql 数据库 redis nosql
于 2021-01-18 14:06:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_41105242/article/details/112625072
版权

前言

搭配该文章食用更佳：MySQL常用操作指令大全

欢迎在评论区对该文章进行勘误。

一、建表

create table Student(sid varchar(10),sname varchar(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' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid 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);

二、题目

  1. 查询 “01” 课程比 “02” 课程成绩高的学生的学号、姓名
  2. 查询平均成绩大于60分的学生的学号、平均成绩
  3. 查询各个学生的学号、姓名、选课数量、总成绩
  4. 查询姓 “李” 的老师的个数
  5. 查询没学过 “张三” 老师的课程的学生的学号、姓名
  6. 查询学过 “01” 课程和 “02” 课程的学生的学号、姓名
  7. 查询各个课程成绩均小于60分的学生的学号、姓名
  8. 查询没有学全所有课程的学生的学号、姓名
  9. 查询至少有一门课与学号为 “01” 的学生所学相同的学生的学号、姓名
  10. 查询和学号为 “01” 的学生学习课程完全相同的其他学生的学号、姓名
  11. 查询 “张三” 老师所教的课程的平均成绩
  12. 查询没有学习过 “张三” 老师所教的任一门课程的学生姓名
  13. 查询两门及其以上课程不及格的学生的学号，姓名、平均成绩
  14. 查询各个课程的课程号、课程名、最高成绩、最低成绩、平均成绩、及格率
  15. 查询各个老师的职工号、所教课程的平均成绩，按所教课程的平均成绩降序排列
  16. 查询各个课程中，成绩排名为第2名或第3名的学生的学号、课程号、课程成绩、成绩排名
  17. 查询各个课程在不同成绩区间人数的所占百分比，成绩区间分别为 [85-100]、[70-85]、[60-70]、[0-60]
  18. 查询各个学生的学号、平均成绩、平均成绩的排名
  19. 查询各课程成绩前三名的课程号、学号、成绩排名、课程成绩
  20. 查询所有学生中男生数量、女生数量
  21. 查询姓名中含有 “风” 字的学生的学号、姓名
  22. 查询同名同性的学生的姓名、性别、数量
  23. 查询1990年出生的学生的学号、出生日期（注：Student 表中 sage 列的类型是 datetime）
  24. 查询所有选修 “张三” 老师所教课程的学生中，课程成绩最高的学生姓名、课程成绩
  25. 查询选修了全部课程的学生的学号
  26. 查询各个学生的学号、年龄
  27. 查询在本周过生日的学生的学号
  28. 查询在下周过生日的学生的学号
  29. 查询在本月过生日的学生的学号
  30. 查询在下月过生日的学生的学号

三、答案

  1. 查询 “01” 课程比 “02” 课程成绩高的学生的学号、姓名

    select t4.sid   as sid,
       t4.sname as sname
from (
         select distinct t1.sid as sid
         from (select * from sc where sc.cid = '01') as t1
                  left join (select * from sc where sc.cid = '02') as t2
                            on t1.sid = t2.sid
         where t1.score > t2.score
     ) as t3
         left join student as t4
                   on t3.sid = t4.sid;

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

    select t1.sid        as sid,
       avg(t1.score) as avg_score
from sc as t1
group by t1.sid
having avg_score > 60;

  3. 查询各个学生的学号、姓名、选课数量、总成绩

    select t1.sid                 as sid,
       t1.sname               as sname,
       count(distinct t2.cid) as count_course,
       sum(t2.score)          as sum_score
from student as t1
         left join sc as t2
                   on t1.sid = t2.sid
group by t1.sid, t1.sname;

  4. 查询姓 “李” 的老师的个数

    select count(distinct t1.tid) as tname_count
from teacher as t1
where t1.tname like '李%';

  5. 查询没学过 “张三” 老师的课程的学生的学号、姓名

    select t1.sid   as sid,
       t1.sname as sname
from student as t1
where t1.sid not in (
    select t4.sid as sid
    from teacher as t2
             left join course as t3
                       on t2.tid = t3.tid
             left join sc as t4
                       on t3.cid = t4.cid
    where t2.tname = '张三'
);

  6. 查询学过 “01” 课程和 “02” 课程的学生的学号、姓名

    select t2.sid   as sid,
       t3.sname as sname
from (
         select t1.sid as sid,
                count(if(t1.cid = '01', t1.score, null)) as count1,
                count(if(t1.cid = '02', t1.score, null)) as count2
         from sc as t1
         group by t1.sid
         having count1 > 0
            and count2 > 0
     ) as t2
         left join student as t3
                   on t2.sid = t3.sid;

  7. 查询各个课程成绩均小于60分的学生的学号、姓名

    select t3.sid   as sid,
       t3.sname as sname
from (
         select t1.sid        as sid,
                max(t1.score) as max_score
         from sc as t1
         group by t1.sid
         having max_score < 60
     ) as t2
         left join student as t3
                   on t2.sid = t3.sid;

  8. 查询没有学全所有课程的学生的学号、姓名

    select t4.sid   as sid,
       t4.sname as sname
from (
         select t1.sid        as sid,
                count(t1.cid) as count_cid
         from sc as t1
         group by t1.sid
         having count_cid < (
             select count(t2.cid) as count_cid
             from course as t2
         )
     ) as t3
         left join student as t4
                   on t3.sid = t4.sid;

  9. 查询至少有一门课与学号为 “01” 的学生所学相同的学生的学号、姓名

    select t5.sid   as sid,
       t5.sname as sname
from (
         select distinct t3.sid as sid
         from (
                  select t1.cid as cid
                  from sc as t1
                  where t1.sid = '01'
              ) as t2
                  left join sc as t3
                            on t2.cid = t3.cid
     ) as t4
         left join student as t5
                   on t4.sid = t5.sid;

  10. 查询和学号为 “01” 的学生学习课程完全相同的其他学生的学号、姓名

    select t5.sid   as sid,
       t5.sname as sname
from (
         select t3.sid                 as sid,
                count(distinct t3.cid) as count_cid
         from (
                  select t1.cid
                  from sc as t1
                  where t1.sid = '01'
              ) as t2
                  left join sc as t3
                            on t2.cid = t3.cid
         group by t3.sid
         having count_cid = (select count(distinct sc.cid) from sc where sc.sid = '01')
     ) as t4
         left join student as t5
                   on t4.sid = t5.sid
where t5.sid != '01';

  11. 查询 “张三” 老师所教的课程的平均成绩

    select avg(t4.score) as avg_score
from sc as t4
where t4.cid = (
    select distinct t1.cid as cid
    from sc as t1
             left join course as t2
                       on t1.cid = t2.cid
             left join teacher as t3
                       on t2.tid = t3.tid
    where t3.tname = '张三'
)
group by t4.cid;

  12. 查询没有学习过 “张三” 老师所教的任一门课程的学生姓名

    select t4.sname as sname
from student as t4
where t4.sid not in (
    select distinct t1.sid as sid
    from sc as t1
             left join course as t2
                       on t1.cid = t2.cid
             left join teacher as t3
                       on t2.tid = t3.tid
    where t3.tname = '张三'
);

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

    select t3.sid       as sid,
       t3.sname     as sname,
       t2.avg_score as avg_score
from (
         select t1.sid                           as sid,
                avg(score)                       as avg_score,
                count(if(score < 60, cid, null)) as count_score
         from sc as t1
         group by t1.sid
         having count_score >= 2
     ) as t2
         left join student as t3
                   on t2.sid = t3.sid;

  14. 查询各个课程的课程号、课程名、最高成绩、最低成绩、平均成绩、及格率

    select t2.cid       as cid,
       t3.cname     as cname,
       t2.max_score as max_score,
       t2.min_score as min_score,
       t2.avg_score as avg_score,
       t2.pass_rate as pass_rate
from (
         select t1.cid                                                  as cid,
                max(t1.score)                                           as max_score,
                min(t1.score)                                           as min_score,
                avg(t1.score)                                           as avg_score,
                count(if(t1.score >= 60, t1.sid, null)) / count(t1.sid) as pass_rate
         from sc as t1
         group by t1.cid
     ) as t2
         left join course as t3
                   on t2.cid = t3.cid;

  15. 查询各个老师的职工号、所教课程的平均成绩，按所教课程的平均成绩降序排列

    select t1.tid        as tid,
       avg(t2.score) as avg_score
from course as t1
         left join sc as t2
                   on t1.cid = t2.cid
group by t1.tid
order by avg_score desc;

  16. 查询各个课程中，成绩排名为第2名或第3名的学生的学号、课程号、课程成绩、成绩排名

    select t2.sid      as sid,
       t2.cid      as cid,
       t2.score    as score,
       t2.rank_num as rank_num
from (
         select rank() over (partition by t1.cid order by t1.score desc) as rank_num,
                t1.sid                                                   as sid,
                t1.score                                                 as score,
                t1.cid                                                   as cid
         from sc as t1
     ) as t2
where t2.rank_num in (2, 3);

  17. 查询各个课程在不同成绩区间人数的所占百分比，成绩区间分别为 [85-100]、[70-85]、[60-70]、[0-60]

    select t1.cid                                                               as cid,
       t2.cname                                                             as name,
       count(if(t1.score between 85 and 100, t1.sid, null)) / count(t1.sid) as '[85-100]_pct',
       count(if(t1.score between 70 and 85, t1.sid, null)) / count(t1.sid)  as '[70-85]_pct',
       count(if(t1.score between 60 and 70, t1.sid, null)) / count(t1.sid)  as '[60-70]_pct',
       count(if(t1.score between 0 and 60, t1.sid, null)) / count(t1.sid)   as '[0-60]_pct'
from sc as t1
         left join course as t2
                   on t1.cid = t2.cid
group by t1.cid, t2.cname;

  18. 查询各个学生的学号、平均成绩、平均成绩的排名

    select t2.sid                                   as sid,
       t2.avg_score                             as avg_score,
       rank() over (order by t2.avg_score desc) as rank_avg_score
from (
         select t1.sid        as sid,
                avg(t1.score) as avg_score
         from sc as t1
         group by t1.sid
     ) as t2;

  19. 查询各课程成绩前三名的课程号、学号、成绩排名、课程成绩

    select t2.cid        as cid,
       t2.sid        as sid,
       t2.rank_score as rank_score,
       t2.score      as score
from (
         select t1.cid                                                as cid,
                t1.sid                                                as sid,
                rank() over (partition by t1.cid order by score desc) as rank_score,
                t1.score                                              as score
         from sc as t1
     ) as t2
where rank_score between 1 and 3;

  20. 查询所有学生中男生数量、女生数量

    select t1.ssex                as ssex,
       count(distinct t1.sid) as count_sid
from student as t1
group by t1.ssex;

  21. 查询姓名中含有 “风” 字的学生的学号、姓名

    select t1.sid   as sid,
       t1.sname as sname
from student as t1
where t1.sname like '%风%';

  22. 查询同名同性的学生的姓名、性别、数量

    select t1.sname      as sname,
       t1.ssex       as ssex,
       count(t1.sid) as count_sid
from student as t1
group by t1.sname, t1.ssex
having count_sid >= 2;

  23. 查询1990年出生的学生的学号、出生日期（注：Student 表中 sage 列的类型是 datetime）

    select t1.sid as sid,
       t1.sage as sage
from student as t1
where year(t1.sage) = 1990;

  24. 查询所有选修 “张三” 老师所教课程的学生中，课程成绩最高的学生姓名、课程成绩

    select t1.sid   as sid,
       t1.score as score
from sc as t1
         left join course as t2
                   on t1.cid = t2.cid
         left join teacher t3
                   on t2.tid = t3.tid
where t3.tname = '张三'
order by t1.score desc
limit 1;

  25. 查询选修了全部课程的学生的学号

    select t2.sid as sid
from sc as t2
group by t2.sid
having count(t2.cid) = (
    select count(distinct t1.cid)
    from sc as t1
);

  26. 查询各个学生的学号、年龄

    select t1.sid                       as sid,
       year(curdate()) - year(sage) as sage
from student as t1;

  27. 查询在本周过生日的学生的学号

    select t1.sid as sid
from student as t1
where weekofyear(sage) = weekofyear(curdate());

  28. 查询在下周过生日的学生的学号

    select t1.sid as sid
from student as t1
where weekofyear(t1.sage) = weekofyear(date_add(curdate(), interval 1 week));

  29. 查询在本月过生日的学生的学号

    select t1.sid as sid
from student as t1
where month(t1.sage) = month(curdate());

  30. 查询在下月过生日的学生的学号

    select t1.sid as sid
from student as t1
where month(t1.sage) = month(date_add(curdate(), interval 1 month));

写在最后：

一个值得尝试的 AI 赚钱小项目

扫描下方二维码或关注微信公众号：CodeFish 回复关键字 “电子书” 即可获取本文章相关书籍资料！

在这里插入图片描述

电子书

更多优质电子书资源持续更新中…

码渔
关注
花了三天三夜才收集整理出来的经典 SQL 数据库笔试题及答案
ifling99的博客
08-31 1063
金九银十期间，有蛮多小伙伴在跳槽找工作，但对于年限稍短的软件测试工程师，难免会需要进行笔试，而在笔试中，基本都会碰到一道关于数据库的大题，今天这篇文章呢，就是收集了一些小伙伴在面试中遇到的数据库的笔试题。......
SQL笔试经典50题及答案解析（题目21-30）
weixin_43890183的博客
03-18 3721
前言 最近博主在准备数据分析岗位的笔试和面试，其中的考察重点就是SQL。听说练了这50道题目，什么现场手撕SQL统统都不怕，我就赶紧来练习了！壮士们，练完以后就什么都不怕啦~ 环境： Navicat Premium 12 说明： 本篇练习的是题目21-30，其余参考链接 文章目录创建表表说明表创建表显示题目题目21题目22题目23题目24题目25题目26题目27题目28题目29题目30 创建表 表...
SQL笔试经典40题
weixin_44458771的博客
03-23 7614
SQL是我接触的第二门编程语言，学得挺早，但到现在已经好几年没用过SQL了，因为搞机器学习用python（numpy/pandas），最近刷笔试题遇到SQL题都有点忘了，故再次拾起，回顾回顾。本文所使用的数据库为Oracle19c。SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统应充分利用SQL语言提高计算机应用系统的工作质量与效率。SQL 语句可以单行或多行书写，以分号结尾；
大厂笔试SQL题
最新发布
m0_68143976的博客
08-22 891
大厂面试sql手撕题目总结_手撕sql-CSDN博客常见大厂SQL笔试面试题总结_sql笔试题-CSDN博客Hive常用窗口函数实战-腾讯云开发者社区-腾讯云问题描述：sql实现：group by+sum(if())/case when解1：select year, sum(if(month = 1,amount,0)) as m1, sum(if(month = 2,amount,0)) as m2, sum(if(month = 3,amount,0)) as m3, sum(if(month
sql经典笔试题一（含答案）
01-04
sql经典笔试题（含答案） sql经典笔试题（含答案） sql经典笔试题（含答案）
经典 SQL 数据库笔试题及答案整理
百晓生说测试的博客
07-09 2146
最近有蛮多小伙伴在跳槽找工作，但对于年限稍短的软件测试工程师，难免会需要进行笔试，而在笔试中，基本都会碰到一道关于数据库的大题，今天这篇文章呢，就收录了下最近学员反馈上来的一些数据库笔试题，包含答案！
经典的SQL笔试题题目
08-30
经典sql练习，几道经典的SQL笔试题题目。sql就在于多多练习
SQL笔试题库（有答案）
11-02
SQL笔试题库。
常见的SQL笔试选择题含答案.docx
09-05
常见的SQL笔试选择题含答案.docx,常见的SQL笔试选择题含答案.docx 常见的SQL笔试选择题含答案.docx,常见的SQL笔试选择题含答案.docx
SQL笔试经典50题及答案解析（题目41-50）
weixin_43890183的博客
03-17 6051
前言 最近博主在准备数据分析岗位的笔试和面试，其中的考察重点就是SQL。听说练了这50道题目，什么现场手撕SQL统统都不怕，我就赶紧来练习了！壮士们，练完以后就什么都不怕啦~ 环境： Navicat Premium 12 说明： 本篇练习的是题目41-50，哪怕不简单，大家也不用害怕，take it easy~ SQL笔试经典50题及答案解析（题目11-20） 文章目录创建表表说明表创建表显示题目...
50道SQL练习题及答案与详细分析
12-14
通过这50道练习题，我们可以深入理解和掌握SQL的基础语法以及高级特性。以下是一些关键知识点的讲解： 1. **创建表**：在SQL中，我们使用`CREATE TABLE`语句来创建新的表格。例如，`CREATE TABLE Student`定义了...
SQL数据库经典面试题(笔试题)（有答案）
01-02
SQL数据库经典面试题(笔试题)（有答案）
sql常见笔试题
08-17
以上SQL面试题，经常会出现在笔试环节，特别是java开发工程师岗位，虽然并不是很难，但对于那些比较熟悉SSH开发，不经常写SQL的同学来说，有时候还真是不知道怎么写。
自己整理的sql笔试题
11-24
整理的十几道sql的题目，基本是常用的一些查询语句，包括对sql函数，多表关联查询等都有涉及，方便在笔试前复习巩固
sql经典笔试题三（含答案）
01-04
sql经典笔试题三（含答案） sql经典笔试题三（含答案） sql经典笔试题三（含答案）
SQL笔试题，及其答案
06-01
SQL笔试题适合初学者以及即将面临找工作的程序员，不错非常有用
SQL笔试经典50题及答案解析（21-50题）
joe_lee1的博客
05-12 1195
21、查询不同老师所教不同课程平均分从高到低显示 SELECT t2.tid, t1.cid, t2.cname, AVG(t1.score) as avg_score FROM sc t1 LEFT JOIN course t2 ON t1.cid = t2.cid GROUP BY t1.cid ORDER BY avg_score DESC; 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 # mysql 8.0以上版本：窗口函数 SELECT
SQL笔试 I 经典44题及答案解析_.html
05-08
SQL笔试 I 经典44题及答案解析_.html
SQL笔试题
sjmz30071360的专栏
04-24 1501
====== MySQL数据库 ======已知表:1，学生表student2，课程表course3，成绩表score创建表语句：create table student(sno int not null auto_increment,sname varchar(20),sage int(3),ssex char(1),primary key(sno));create table course(c...
sql笔试题及答案
05-25
以下是几个 SQL 笔试题及其答案，供您参考： 1. 查询表中所有记录 ```sql SELECT * FROM table_name; ``` 2. 查询表中的某些字段 ```sql SELECT column1, column2, ... FROM table_name; ``` 3. 查询表中的某些记录 ```sql SELECT * FROM table_name WHERE condition; ``` 4. 对查询结果进行排序 ```sql SELECT * FROM table_name ORDER BY column_name ASC/DESC; ``` 5. 对查询结果进行分组 ```sql SELECT column1, COUNT(column2) FROM table_name GROUP BY column1; ``` 6. 对查询结果进行聚合计算 ```sql SELECT SUM(column_name) FROM table_name; ``` 7. 对查询结果进行连接 ```sql SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.column3 = table2.column4; ``` 以上是一些常见的 SQL 笔试题及其答案，希望对您有所帮助。
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值