30道经典笔试SQL题

最新推荐文章于 2024-05-22 20:11:58 发布
置顶 BB-X
最新推荐文章于 2024-05-22 20:11:58 发布
阅读量204
点赞数
分类专栏： # 笔试题解文章标签： sql 数据库
本文链接：https://blog.csdn.net/weixin_51091560/article/details/132653094
版权
笔试题解专栏收录该内容
10 篇文章 1 订阅
订阅专栏
-- 创建数据库并指定
create database sql_30;
use `sql_30`;

-- 创建表和插入数据(windows不区分大小写，linux区分大小写)
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” 课程成绩高的学生的学号、姓名
-- 1.查出每个学生课程01的成绩
select * from sc where cid = '01';
-- 2.查出每个学生课程02的成绩
select * from sc where cid = '02';
-- 3.联合查询(使用内连接：没修满两科的不比较)
select t1.sid, t1.score, t2.score
from (select * from sc where cid = '01') t1
inner join (select * from sc where cid = '02') t2
on t1.sid = t2.sid
where t1.score > t2.score;
-- 4.附加关联学生表的学号、姓名
select student.sid, sname from student
inner join(
    select t1.sid, t1.score score01, t2.score score02
    from (select * from sc where cid = '01') t1
    inner join (select * from sc where cid = '02') t2
    on t1.sid = t2.sid
    where t1.score > t2.score) t
on student.sid = t.sid;

-- 1.直接通过if组合列
select sid, sum(if(cid = '01', score, null)) score01, sum(if(cid = '02', score, null)) score02
from sc
group by sid
having score01 > score02; # null列不会进行比较
-- 1.直接通过case when then [else] end组合列
select sid, sum(case cid when '01' then score end) score01, sum(case cid when '02' then score end) score02
from sc
group by sid
having score01 > score02;
-- 2.附加关联学生表的学号、姓名
select student.sid, sname from student
inner join(
    select sid, sum(if(cid = '01', score, null)) score01, sum(if(cid = '02', score, null)) score02
    from sc
    group by sid
    having score01 > score02) t
on student.sid = t.sid;

# 2、查询平均成绩大于60分的学生的学号、平均成绩
select sid, round(avg(score), 2) score_avg # 四舍五入保留两位小数
from sc
group by sid
having score_avg > 60;

# 3、查询各个学生的学号、姓名、选课数量、总成绩
-- 1.查出复合子列
select sid, count(1) class_name, sum(score) total
from sc
group by sid;
-- 2.附加关联学生表学号、姓名
select student.sid, sname, class_name, total
from student
inner join(
    select sid, count(1) class_name, sum(score) total
    from sc
    group by sid) t
on student.sid = t.sid;

# 4、查询姓 “李” 的老师的个数
select count(1) from teacher where tname like '李%';

# 5、查询没学过 “张三” 老师的课程的学生的学号、姓名
-- 1.先查询学过“张三”老师的课程的学号
select sid from teacher
inner join course on teacher.tid = course.tid
inner join sc on course.cid = sc.cid
where tname = '张三';
-- 2.再过滤掉学过的学生即可not in(其中，内层sid不会外传，外层不用指定)
select sid, sname from student where sid not in(
    select sid from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    where tname = '张三');
-- 2.另一种写法not exists(其中，内层sid不会外传，外层不用指定；内层要指定并加where/and过滤笛卡尔积)
select sid, sname from student where not exists(
    select 1 from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    where tname = '张三' and student.sid = sc.sid);

# 6、查询学过 “01” 课程和 “02” 课程的学生的学号、姓名
-- 1.先查询学过01课程的学号
select sid from sc where cid = '01';
-- 2.先查循学过02课程的学号
select sid from sc where cid = '02';
-- 3.后汇总查询学过01且02课程的学号
select t1.sid
from (select sid from sc where cid = '01') t1
inner join (select sid from sc where cid = '02') t2
on t1.sid = t2.sid;
-- 4.附加关联学生表学号、姓名(效率：inner join>left/right join>[not]exists>[not]in)
select student.sid, sname from student
inner join (
    select t1.sid
    from (select sid from sc where cid = '01') t1
    inner join (select sid from sc where cid = '02') t2
    on t1.sid = t2.sid) t
on student.sid = t.sid;

# 7、查询各个课程成绩均小于60分的学生的学号、姓名
-- 1.查询出学生最大成绩小于60分的学号
select sid from sc
group by sid
having max(score) < 60;
-- 2.附加关联学生表查询姓名
select student.sid, sname
from student
inner join(
    select sid from sc
    group by sid
    having max(score) < 60) s
on student.sid = s.sid;

# 8、查询没有学全所有课程的学生的学号、姓名
-- 1.查询课程数量少于所有课程的学号
select sid from sc
group by sid
having count(1) < 3;
-- 2.附加关联学生表查询姓名
select student.sid, sname
from student
inner join(
    select sid from sc
    group by sid
    having count(1) < 3) s
on student.sid = s.sid;

# 9、查询至少有一门课与学号为 “01” 的学生所学相同的学生的学号、姓名
-- 1.查询出01学生所学的课程
select cid from sc where sid = '01';
-- 2.查询出学过01学生所学课程的学号
select distinct sid from sc # distinct和group by本质差不多，均用于去重，效率也差不多，distinct更简洁优雅
where cid in(select cid from sc where sid = '01');
-- 3.附加关联学生表查询姓名
select student.sid, sname
from student
inner join(
    select distinct sid from sc
    where cid in(select cid from sc where sid = '01')) s
on student.sid = s.sid;

# 10、查询和学号为 “01” 的学生学习课程完全相同的其他学生的学号、姓名
-- 1.查询出01学生所学的课程号
select cid from sc where sid = '01';
-- 2.查询出01学生所学的课程数量
select count(1) from sc where sid = '01';
-- 3.查询出其它同学学过01所学课程的课程
select *
from (select cid from sc where sid = '01') s
inner join sc on sc.cid = s.cid
where sid != '01'; # 一般单判断直接用!=
-- 3.查询出其它同学学过01所学课程的课程
select *
from (select cid from sc where sid = '01') s
inner join sc on sc.cid = s.cid
where not sid = '01'; # 一般not用于多判断或比较</<=/>/>=
-- 4.查询出其它同学学过01所学课程的数量
select sid, count(1)
from (select cid from sc where sid = '01') s
inner join sc on sc.cid = s.cid
where sid != '01'
group by sid;
-- 5.查询出课程量和01所学课程数量相等的学号
select sid
from (select cid from sc where sid = '01') s
inner join sc on sc.cid = s.cid
where sid != '01'
group by sid having count(1) = (select count(1) from sc where sid = '01');
-- 6.附加关联学生表查询姓名
select student.sid, sname
from student
inner join(
    select sid
    from (select cid from sc where sid = '01') s
    inner join sc on sc.cid = s.cid
    where sid != '01'
    group by sid having count(1) = (select count(1) from sc where sid = '01')) s1
on student.sid = s1.sid;

# 11、查询 “张三” 老师所教的课程的平均成绩
-- 1.查询张三老师的教师号
select tid from teacher where tname = '张三';
-- 2.查询张三老师所教的课程号
select cid
from course
inner join (select tid from teacher where tname = '张三') t on course.tid = t.tid;
-- 2.查询张三老师所教的课程号
select cid
from (select tid from teacher where tname = '张三') t # 写法符合优化SQL之小表驱动大表(inner join中可略)
inner join course on t.tid = course.tid;
-- 3.查询张三老师所教课程的平均成绩
select round(avg(score), 2) socre_avg
from sc where cid in (
    select cid
    from (select tid from teacher where tname = '张三') t
    inner join course on t.tid = course.tid)
group by cid;

# 12、查询没有学习过 “张三” 老师所教的任一门课程的学生姓名
-- 1.查询学过张三老师的课程的学生号
select sid
from teacher
inner join course on teacher.tid = course.tid
inner join sc on course.cid = sc.cid
where teacher.tname = '张三';
-- 2.查询没学过张三老师的学生信息(not in写法)
select sname
from student
where sid not in (
    select sid
    from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    where teacher.tname = '张三');
-- 2.查询没学过张三老师的学生信息(not exists写法)
select sname
from student
where not exists (
    select 1
    from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    where teacher.tname = '张三' and student.sid = sc.sid);

# 13、查询两门及其以上课程80分以下的学生的学号，姓名、平均成绩
-- 1.查询两门及以上80分以下的学号(count+if)
select sid, round(avg(score), 2)
from sc
group by sid
having count(if(score < 80, 0, null)) > 1; # count不统计null列
-- 1.查询两门及以上80分以下的学号(sum+if)
select sid, round(avg(score), 2)
from sc
group by sid
having sum(if(score < 80, 1, null)) > 1; # if适用于单项判断if/else
-- 1.查询两门及以上80分以下的学号(count+case when then [else] end)
select sid, round(avg(score), 2)
from sc
group by sid
having count(case when score < 80 then 0 end) > 1; # else null可略，不满足条件默认为null
-- 1.查询两门及以上80分以下的学号(sum+case when then [else] end)
select sid, round(avg(score), 2)
from sc
group by sid
having sum(case when score < 80 then 1 end) > 1; # case when then [else] end适用于多项判断if/else if/else
-- 2.附加关联学生表查询姓名
select student.sid, sname, score_avg
from student
inner join (
    select sid, round(avg(score), 2) score_avg
    from sc
    group by sid
    having count(if(score < 80, 0, null)) > 1) s
on student.sid = s.sid;

# 14、查询各个课程的课程号、课程名、最高成绩、最低成绩、平均成绩、及格率
-- 1.查询各个课程的已有和组合信息
select cid, max(score) score_max, min(score) score_min, round(avg(score), 2) score_avg, round(count(if(score >= 60, 0, null)) / count(1), 2) pass_rate
from sc
group by cid;
-- 2.附加关联课程表查询课程名
select course.cid, course.cname, score_max, score_min, score_avg, pass_rate
from course
inner join (
    select cid, max(score) score_max, min(score) score_min, round(avg(score), 2) score_avg, round(count(if(score >= 60, 0, null)) / count(1), 2) pass_rate
    from sc
    group by cid) s
on course.cid = s.cid;

# 15、查询各个老师的职工号、所教课程的平均成绩，按所教课程的平均成绩降序排列
-- 1.查询所有老师下的所有课程的平均成绩
select tid, round(avg(score), 2) score_avg
from course
inner join sc on course.cid = sc.cid
group by tid
order by score_avg desc;
-- 1.查询所有老师下的各个课程的平均成绩
select tid, round(avg(score), 2) score_avg
from course
inner join sc on course.cid = sc.cid
group by tid, course.cid
order by score_avg desc;

# 16、查询各个课程中，成绩排名为第2名或第3名的学生的学号、课程号、课程成绩、成绩排名
-- 1.查询各课程排行等信息
select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
from sc;
-- (分组前的统计列也是用where过滤[where分组前过滤，having分组后过滤]，统计列在当前层sql仅能对having可视，外层sql则where/having均可视)
-- 2.过滤第二和第三名(where+between and写法)
select sid, cid, score, score_rank
from (
    select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
    from sc) s
where score_rank between 2 and 3;
-- 2.过滤第二和第三名(where+in写法)
select sid, cid, score, score_rank
from (
    select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
    from sc) s
where score_rank in (2, 3); # between and效率过于in(前者为有序过滤，后者为无序匹配)
-- 2.过滤第二和第三名(having+between and写法)
select sid, cid, score, score_rank
from (
    select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
    from sc) s
having score_rank between 2 and 3; # where效率高于having(where是过滤数据集后做操作，having是获取全部数据后做过滤)
-- 2.过滤第二和第三名(having+in写法)
select sid, cid, score, score_rank
from (
    select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
    from sc) s
having score_rank in (2, 3);

# 17、查询各个课程在不同成绩区间人数的所占百分比，成绩区间分别为 [85-100]、[70-84]、[60-69]、[0-59]
-- 1.统计课程的分布情况
select cid,
       round(count(if(score between 85 and 100, 0, null)) / count(1), 2) 85_100,
       round(count(if(score between 70 and 84, 0, null)) / count(1), 2)  70_84,
       round(count(if(score between 60 and 69, 0, null)) / count(1), 2)  60_69,
       round(count(if(score between 0 and 59, 0, null)) / count(1), 2)   0_59
from sc
group by cid;
-- 2.附加关联课程表查询课程名
select course.cid, cname, 85_100, 70_84, 60_69, 0_59
from course
inner join (
    select cid,
           round(count(if(score between 85 and 100, 0, null)) / count(1), 2) 85_100,
           round(count(if(score between 70 and 84, 0, null)) / count(1), 2)  70_84,
           round(count(if(score between 60 and 69, 0, null)) / count(1), 2)  60_69,
           round(count(if(score between 0 and 59, 0, null)) / count(1), 2)   0_59
    from sc
    group by cid) s
on course.cid = s.cid;

# 18、查询各个学生的学号、平均成绩、平均成绩的排名
select sid, round(avg(score), 2) score_avg, rank() over (order by avg(score) desc) score_rank
from sc
group by sid;
-- (排名rank函数是mysql8.0后引入的，企业环境Linux部署的mysql通常是5.7)
select sid, score_avg, rank() over (order by score_avg desc) score_rank
from (
    select sid, round(avg(score), 2) score_avg
    from sc
    group by sid) s;

# 19、查询各课程成绩前三名的课程号、学号、成绩排名、课程成绩
-- 1.查询各课程排行等信息
select cid, sid, rank() over (partition by cid order by score desc) score_rank, score
from sc;
-- (分组前的统计列也是用where过滤[where分组前过滤，having分组后过滤]，统计列在当前层sql仅能对having可视，外层sql则where/having均可视)
-- 2.过滤前三名(between and写法)
select cid, sid, score_rank, score
from (
    select cid, sid, rank() over (partition by cid order by score desc) score_rank, score
    from sc) s
where score_rank between 1 and 3;
-- 2.过滤前三名(in写法)
select cid, sid, score_rank, score
from (
    select cid, sid, rank() over (partition by cid order by score desc) score_rank, score
    from sc) s
where score_rank in (1, 2, 3);

# 20、查询所有学生中男生数量、女生数量
select ssex, count(1) from student group by ssex;

# 21、查询姓名中含有 “风” 字的学生的学号、姓名
select sid, sname from student where sname like '%风%';

# 22、查询同名同性的学生的姓名、性别、数量
select sname, ssex, count(1) num
from student
group by sname, ssex
having num > 1;

# 23、查询1990年出生的学生的学号、出生日期（注：student 表中 sage 列的类型是 datetime）
-- year年份函数
select sid, sage,
       year(sage), # 年份
       quarter(sage), # 季度
       month(sage), # 月份
       week(sage), # 周
       day(sage), # 日
       hour(sage), # 小时
       minute(sage), # 分钟
       second(sage), # 秒
       microsecond(sage), # 微秒(1秒=1*10^6微秒)
       date(sage), # 日期
       time(sage), # 时间
       timestamp(sage) # 时间戳(展示日期时间)
from student
where year(sage) = '1990';
-- extract提取函数
select sid, sage,
       extract(year from sage), # 年份
       extract(quarter from sage), # 季度
       extract(month from sage), # 月份
       extract(week from sage), # 周
       extract(day from sage), # 日
       extract(hour from sage), # 小时
       extract(minute from sage), # 分钟
       extract(second from sage), # 秒
       extract(microsecond from sage) # 微秒(1秒=1*10^6微秒)
from student
where extract(year from sage) = '1990';
-- date_format格式化函数
select sid, sage,
       date_format(sage, '%Y'), # 年份
       date_format(sage, '%m'), # 月份
       date_format(sage, '%d'), # 日
       date_format(sage, '%H'), # 小时
       date_format(sage, '%i'), # 分钟
       date_format(sage, '%s'), # 秒
       date_format(sage, '%p') # AM上午/PM下午
from student
where date_format(sage, '%Y') = '1990';

# 24、查询所有选修 “张三” 老师所教课程的学生中，课程成绩最高的学生姓名、课程成绩
select sname, score
from teacher
inner join course on teacher.tid = course.tid
inner join sc on course.cid = sc.cid
inner join student s on sc.sid = s.sid
where tname = '张三'
order by score desc
limit 1;
-- inner join写里面和外面都行，limit 1等价于limit 0,1
select sname, score
from student
inner join (
    select sid, score
    from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    where tname = '张三'
    order by score desc
    limit 0, 1) t
on student.sid = t.sid;

# 25、查询选修了全部课程的学生的学号
-- 1.查询全部课程的数量
select count(1) from course;
-- 2.查询选修课程数量等于全部课程数量的学号
select sid
from sc
group by sid
having count(sid) = (select count(1) from course);

# 26、查询各个学生的学号、年龄
select sid, sage,
       now(), # 当前日期时间
       curdate(), # 当前日期
       curtime(), # 当前时间
       datediff('2024-10-09 13:39:40', '2023-09-08 12:38:39'), # 日期差(前参大于后参)
       timediff('2024-10-09 13:39:40', '2023-09-08 12:38:39'), # 时间差(前参大于后参)
       timestampdiff(year, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 年份差(后参大于前参)
       timestampdiff(quarter, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 季度差(后参大于前参)
       timestampdiff(month, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 月份差(后参大于前参)
       timestampdiff(week, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 周差(后参大于前参)
       timestampdiff(day, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 日差(后参大于前参)
       timestampdiff(hour, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 小时差(后参大于前参)
       timestampdiff(minute, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 分钟差(后参大于前参)
       timestampdiff(second, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 秒差(后参大于前参)
       timestampdiff(microsecond, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 微秒差[1秒=1*10^6微秒](后参大于前参)
       year(now()) - year(sage) age1, # 精确度低
       floor(datediff(now(), sage) / 365) age2, # 精确度中
       timestampdiff(year, sage, now()) age3 # 精确度高
from student;

-- 向上取整(ceil是ceiling函数的别名)
select ceil(3.1);
select ceiling(3.1);
-- 向下取整(floor函数)
select floor(3.9);
-- 四舍五入取整(round函数，第二个参数是保留小数位)
select round(3.4);
select round(3.5);
select round(3.004, 2);
select round(3.005, 2);

# 27、查询在本周过生日的学生的 学号
select sid
from student
where week(sage) = week(now());

select week('2023-09-08'), # 日期中第几周
       weekofyear('2023-09-08'), # 日期中第几周
       weekday('2023-09-08'), # 周中第几天(0周一~6周日)，4即为周五
       dayofweek('2023-09-08'), # 周中第几天(1周日~7周六)，6即为周五
       yearweek('2023-09-08'); # 年份和日期中第几周组合

select day('2023-09-08'), # 日期中几号
       dayname('2023-09-08'), # 周几英文名称
       dayofmonth('2023-09-08'), # 月份中第几天
       dayofyear('2023-09-08'); # 年份中第几天

select month('2023-09-08'), # 日期中几月
       monthname('2023-09-08'); # 月份英文名称

select year('2023-09-08'); # 日期中几年

# 28、查询在下12周过生日的学生的学号
select sid
from student
where week(sage) = week(now()) + 12;

select sid
from student
where week(sage) = week(date_add(now(), interval 12 week));

select date_add('2023-09-08 12:38:39.123456789', interval 1 microsecond), # 添加1微秒(1秒=1*10^6微秒)
       date_add('2023-09-08 12:38:39.123456789', interval 1 second), # 添加1秒
       date_add('2023-09-08 12:38:39.123456789', interval 1 minute), # 添加1分钟
       date_add('2023-09-08 12:38:39.123456789', interval 1 hour), # 添加1小时
       date_add('2023-09-08 12:38:39.123456789', interval 1 day), # 添加1日
       date_add('2023-09-08 12:38:39.123456789', interval 1 week), # 添加1周
       date_add('2023-09-08 12:38:39.123456789', interval 1 month), # 添加1月
       date_add('2023-09-08 12:38:39.123456789', interval 1 quarter), # 添加1季度
       date_add('2023-09-08 12:38:39.123456789', interval 1 year); # 添加1年

select date_sub('2023-09-08 12:38:39.123456789', interval 1 microsecond), # 减少1微秒(1秒=1*10^6微秒)
       date_sub('2023-09-08 12:38:39.123456789', interval 1 second), # 减少1秒
       date_sub('2023-09-08 12:38:39.123456789', interval 1 minute), # 减少1分钟
       date_sub('2023-09-08 12:38:39.123456789', interval 1 hour), # 减少1小时
       date_sub('2023-09-08 12:38:39.123456789', interval 1 day), # 减少1日
       date_sub('2023-09-08 12:38:39.123456789', interval 1 week), # 减少1周
       date_sub('2023-09-08 12:38:39.123456789', interval 1 month), # 减少1月
       date_sub('2023-09-08 12:38:39.123456789', interval 1 quarter), # 减少1季度
       date_sub('2023-09-08 12:38:39.123456789', interval 1 year); # 减少1年

# 29、查询在本月过生日的学生的学号
select sid
from student
where month(sage) = month(now());

# 30、查询在下3个月过生日的学生的学号
select sid
from student
where month(sage) = month(now()) + 3;

select sid
from student
where month(sage) = month(date_add(now(), interval 3 month));

### 总结：先拆分查询小SQL组件，后拼接小SQL和附加条件，最后通过外键关联表信息 ###