知乎50道SQL题 分享

这篇具有很好参考价值的文章主要介绍了知乎50道SQL题 分享。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

知乎50道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 DEFAULT '',
PRIMARY KEY (`s_id`)
);
-- 课程表
CREATE TABLE `Course`
(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY (`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`
(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`)
);
-- 成绩表
CREATE TABLE `Score`
(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY (`s_id`, `c_id`)
);
-- 插入学生表测试数据
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', '女');
-- 课程表测试数据
insert into Course
values ('01', '语文', '02');
insert into Course
values ('02', '数学', '01');
insert into Course
values ('03', '英语', '03');

-- 教师表测试数据
insert into Teacher
values ('01', '张三');
insert into Teacher
values ('02', '李四');
insert into Teacher
values ('03', '王五');

-- 成绩表测试数据
insert into Score
values ('01', '01', 80);
insert into Score
values ('02', '02', 60);
insert into Score
values ('03', '03', 80);
insert into Score
values ('04', '01', 50);
insert into Score
values ('04', '02', 30);
insert into Score
values ('04', '03', 20);
insert into Score
values ('05', '01', 76);
insert into Score
values ('05', '02', 87);
insert into Score
values ('06', '01', 31);
insert into Score
values ('06', '03', 34);
insert into Score
values ('07', '02', 89);
insert into Score
values ('07', '03', 98);

-- 1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
select stsc1.s_id s_id
from (select st.s_id s_id, sc.c_id c_id, sc.s_score s_score
from student st
left join Score sc on st.s_id = sc.s_id) stsc1
join (select st.s_id s_id, sc.c_id c_id, sc.s_score s_score
from student st
left join Score sc on st.s_id = sc.s_id) stsc2
on stsc1.s_id = stsc2.s_id and stsc1.c_id = '01' and stsc2.c_id = '02'
and stsc1.s_score > stsc2.s_score;

-- 2. 查询平均成绩大于60分的学生的学号和平均成绩
select st.s_id s_id,
avg(sc.s_score) avg_score
from student st
left join Score sc on st.s_id = sc.s_id
group by st.s_id
having avg(sc.s_score) > 60;

-- 3. 查询所有学生的学号、姓名、选课数、总成绩
select st.s_id,
st.s_name,
count(sc.s_id) count_sid,
ifnull(sum(sc.s_score), 0) sum_score
from student st
left join Score sc on st.s_id = sc.s_id
left join course c on sc.c_id = c.c_id
group by st.s_id;

-- 4. 查询姓“张”的老师的个数
select count(t.t_id) count_tid
from teacher t
where t.t_name like '张%';

-- 5. 查询没学过“张三”老师课的学生的学号、姓名
select distinct st.s_id s_id, st.s_name s_name
from student st
where st.s_id not in
(select st.s_id
from student st
left join score sc on st.s_id = sc.s_id
left join course c on c.c_id = sc.c_id
left join teacher t on t.t_id = c.t_id
where t_name = '张三');

-- 6. 查询学过“张三”老师所教的所有课的同学的学号、姓名
select distinct st.s_id s_id, st.s_name s_name
from student st
join score sc on st.s_id = sc.s_id
join
(select c.c_id c_id
from course c
left join teacher t on t.t_id = c.t_id
where t_name = '张三') ct on sc.c_id = ct.c_id;

-- 7. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select st01.s_id s_id, st01.s_name s_name
from (select st.s_id s_id, st.s_name s_name
from student st
join score sc on st.s_id = sc.s_id
where sc.c_id = '01') st01
join
(select st.s_id s_id
from student st
join score sc on st.s_id = sc.s_id
where sc.c_id = '02') st02 on st01.s_id = st02.s_id;

-- 8. 查询课程编号为“02”的总成绩
select sum(sc.s_score) sum_score
from score sc
where sc.c_id = '02'
group by sc.c_id;

-- 9. 查询所有课程成绩小于60分的学生的学号、姓名
select st.s_id s_id, st.s_name s_name
from student st
join score sc on st.s_id = sc.s_id
group by st.s_id, st.s_name
having max(sc.s_score) < 60;

-- 10. 查询没有学全所有课的学生的学号、姓名
select sc.s_id s_id, st.s_name s_name
from score sc
join
student st on st.s_id = sc.s_id
group by s_id
having count(sc.c_id) <> (select count(c_id) from course);

-- 11. 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
select distinct st.s_id s_id, st.s_name s_name
from student st
join score sc on st.s_id = sc.s_id
right join
(select sc.c_id c_id
from student st
join score sc on st.s_id = sc.s_id
where st.s_id = '01') stsc on sc.c_id = stsc.c_id;

-- 12. 查询和“01”号同学所学课程完全相同的其他同学的学号
select distinct in01.s_id s_id, in01.s_name s_name
from (select st.s_id s_id, st.s_name s_name
from student st
join score sc on st.s_id = sc.s_id
join
(select sc.c_id c_id
from student st
join score sc on st.s_id = sc.s_id
where st.s_id = '01') stsc2 on sc.c_id = stsc2.c_id) in01
join
(select st.s_id s_id
from student st
join score sc on st.s_id = sc.s_id
group by st.s_id
having count(sc.c_id) = (select count(c_id)
from score sc
where sc.s_id = '01')) c01
on in01.s_id = c01.s_id
where s_id <> '01';

-- 15. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select st.s_id s_id, st.s_name s_name, avg(sc.s_score) avg_score
from student st
join score sc on st.s_id = sc.s_id
where sc.s_score < 60
group by st.s_id, st.s_name
having count(sc.c_id) >= 2;

-- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率。
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
select c.c_id '课程ID',
c.c_name '课程name',
max(sc.s_score) '最高分',
min(sc.s_score) '最低分',
avg(sc.s_score) '平均分',
concat(round(100 * sum(case when sc.s_score >= 60 then 1 else 0 end) / count(sc.s_id), 2), '%') '及格率',
concat(round(100 * sum(case when sc.s_score >= 70 and sc.s_score < 80 then 1 else 0 end) / count(sc.s_id), 2),
'%') '中等率',
concat(round(100 * sum(case when sc.s_score >= 80 and sc.s_score < 90 then 1 else 0 end) / count(sc.s_id), 2),
'%') '优良率',
concat(round(100 * sum(case when sc.s_score >= 90 then 1 else 0 end) / count(sc.s_id), 2), '%') '优秀率'
from course c
join score sc on c.c_id = sc.c_id
group by c.c_id, c.c_name;

-- 19. 按各科成绩进行排序,并显示排名
select st.s_id, sc.c_id, sc.s_score, rank() over (order by sc.s_score desc) rank_score
from student st
join score sc on st.s_id = sc.s_id
order by sc.s_score desc;

-- 20. 查询学生的总成绩并进行排名
select st.s_id, sum(sc.s_score) sum_score, rank() over (order by sum(sc.s_score) desc) rank_score
from student st
join score sc on st.s_id = sc.s_id
group by st.s_id;

-- 21. 查询不同老师所教不同课程平均分从高到低显示
select c.t_id t_id, c.c_id c_id, avg(sc.s_score) avg_score
from score sc
join course c on sc.c_id = c.c_id
group by c.c_id, c.t_id
order by avg(sc.s_score) desc;

-- 22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select *
from (select st.*, sc.c_id, rank() over (partition by sc.c_id order by sc.s_score desc) score_rank
from student st
join score sc on st.s_id = sc.s_id) stsr
where score_rank between 2 and 3;

-- 23. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数、课程ID和课程名称
select sum(case when sc.s_score > 85 and sc.s_score <= 100 then 1 else 0 end) '[100-85]',
sum(case when sc.s_score > 70 and sc.s_score <= 85 then 1 else 0 end) '[85-70]',
sum(case when sc.s_score > 60 and sc.s_score <= 70 then 1 else 0 end) '[70-60]',
sum(case when sc.s_score <= 60 then 1 else 0 end) '[<=60]',
c.c_id,
c.c_name
from score sc
join course c on sc.c_id = c.c_id
group by c.c_id, c.c_name;

-- 24. 查询学生平均成绩及其名次
select st.s_id s_id, avg(sc.s_score) avg_score, rank() over (order by avg(sc.s_score) desc) rank_score
from student st
join score sc on st.s_id = sc.s_id
group by st.s_id;

-- 25. 查询各科成绩前三名的记录(不考虑成绩并列情况)
select rs.c_id c_id,
max(case when rs.number_score = 1 then rs.s_score end) 01_score,
max(case when rs.number_score = 2 then rs.s_score end) 02_score,
max(case when rs.number_score = 3 then rs.s_score end) 03_score
from (select sc.c_id c_id,
sc.s_score s_score,
row_number() over (partition by sc.c_id order by sc.s_score desc) number_score
from score sc) rs
where rs.number_score <= 3
group by rs.c_id;

-- 26. 查询每门课程被选修的学生数
select count(sc.s_id) count_s
from student st
join score sc on st.s_id = sc.s_id
group by c_id;

-- 27. 查询出只有两门课程的全部学生的学号和姓名
select st.s_id, st.s_name
from student st
join score sc on st.s_id = sc.s_id
group by st.s_id
having count(sc.c_id) = 2;

-- 28. 查询男生、女生人数
select st.s_sex sex, count(s_id) count_id
from student st
group by st.s_sex;

-- 29. 查询名字中含有"风"字的学生信息
select *
from student st
where st.s_name like '%风%';

-- 31. 查询1990年出生的学生名单
select *
from student st
where year(st.s_birth) = 1990;

-- 32. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select st.s_id s_id, st.s_name s_name, avg(sc.s_score) avg_score
from student st
join score sc on st.s_id = sc.s_id
group by st.s_id, st.s_name
having avg(sc.s_score) >= 85;

-- 33. 查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
select c_id, avg(s_score) avg_score
from score sc
group by c_id
order by avg_score, c_id desc;

-- 34. 检索"01"课程分数小于60,按分数降序排列的学生信息
select st.*
from student st
join score sc on st.s_id = sc.s_id
where sc.c_id = '01'
and sc.s_score < 60
order by sc.s_score desc;

-- 35.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select st.s_id s_id,
max(case when sc.c_id = '02' then sc.s_score end) '语文',
max(case when sc.c_id = '01' then sc.s_score end) '数学',
max(case when sc.c_id = '03' then sc.s_score end) '英语',
avg(sc.s_score) avg_score
from student st
join score sc on st.s_id = sc.s_id
group by st.s_id
order by avg_score desc;

-- 36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select st.s_name, c.c_name, sc.s_score
from student st
join score sc on st.s_id = sc.s_id
join course c on sc.c_id = c.c_id
group by st.s_name, c.c_name, sc.s_score
having min(sc.s_score) > 70;

-- 37. 查询不及格的课程并按课程号从大到小排列
select sc.s_id, c.c_name
from course c
join score sc on c.c_id = sc.c_id
where sc.s_score < 60
order by c.c_id desc;

-- 40. 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩
select st.s_name, sc.s_score
from student st
join score sc on st.s_id = sc.s_id
join
(select c.c_id
from course c
join teacher t on c.t_id = t.t_id
where t.t_name = '张三') ct on sc.c_id = ct.c_id
order by s_score desc
limit 1;

-- 41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select sct1.c_id c_id, sct1.s_score s_score, sct1.s_id s_id
from (select c.c_id c_id, sc.s_score s_score, st.s_id s_id
from student st
join score sc on st.s_id = sc.s_id
join course c on sc.c_id = c.c_id) sct1
join (select c.c_id c_id, sc.s_score s_score, st.s_id s_id
from student st
join score sc on st.s_id = sc.s_id
join course c on sc.c_id = c.c_id) sct2
on sct1.s_id = sct2.s_id AND sct1.c_id <> sct2.c_id and sct1.s_score = sct2.s_score;

-- 44. 查询没学过"张三"老师讲授的任一门课程的学生姓名
select st.s_name s_name
from student st
where st.s_id not in
(select st.s_id
from student st
join score sc on st.s_id = sc.s_id
join course c on sc.c_id = c.c_id
join teacher t on c.t_id = t.t_id
where t.t_name = '张三');

-- 45. 查询选修了全部课程的学生信息
select st.*
from student st
join score sc on st.s_id = sc.s_id
join course c on sc.c_id = c.c_id
group by st.s_id
having count(sc.c_id) = (select count(c_id) from course);

-- 46. 查询各学生的年龄(精确到月份)
select floor(datediff(current_date(), s_birth) / 30) month
from student;

-- 47. 查询本月过生日的学生
select st.s_id
from student st
where month(s_birth) = month(current_date);

-- 48. 查询下周过生日的学生
select st.s_id
from student st
where week(s_birth) = week(date_add(current_date(), INTERVAL 7 DAY));文章来源地址https://www.toymoban.com/news/detail-490383.html

到了这里,关于知乎50道SQL题 分享的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 2023年全国最新高校辅导员精选真题及答案50

    百分百题库提供 高校辅导员 考试试题、 辅导员 考试预测题、 高校辅导员 考试真题、 辅导员 证考试题库等 , 提供在线做题刷题,在线模拟考试,助你考试轻松过关。 94.一般认为,在具有了道德认知和道德情感的情况下,道德行为的产生取决于道德意志。 答案: 正确 9

    2023年04月17日
    浏览(54)
  • 找工作吗?50道Python面试题集锦【附答案】

    嗨害大家好鸭!我是爱摸鱼的芝士~ 希望能够帮助你在今年的求职面试中脱颖而出, 找到一份高薪工作~ 这些面试题涉及Python基础知识、Python编程、数据分析以及Python函数库等多个方面。 提前预祝给这篇文章点赞收藏的友友们~ 拿到心中最满意的那一份OFFER~ Q1、Python中的列表

    2023年04月18日
    浏览(39)
  • 自己编写小程序背日语50音图

    简而言之呢就是最近心血来潮打算自学日语,学日语自然就要先从50音图开始,就是下面这个: 可以看出来50音图横向是按照a i u e o这5个元音顺序,纵向按照a ka sa ta na ha ma ya ra wa 的顺序排布的。按顺序编排自然是为了方便记忆,但是也容易按照顺序能背出来,打乱顺序是否

    2024年02月06日
    浏览(38)
  • C++ Primer第五版_第十四章习题答案(41~50)

    练习14.41 你认为 C++ 11 标准为什么要增加 lambda?对于你自己来说,什么情况下会使用 lambda,什么情况下会使用类? 使用 lambda 是非常方便的,当需要使用一个函数,而这

    2024年02月01日
    浏览(43)
  • C++ Primer第五版_第十六章习题答案(41~50)

    练习16.41 编写一个新的 sum 版本,它返回类型保证足够大,足以容纳加法结果。 练习16.42 对下面每个调用,确定 T 和 val

    2024年02月04日
    浏览(42)
  • 50个kafka常见面试题及答案,撸完阿里P7见

    LEO:LogEndOffset 当前日志文件中下一条待写信息的offset HW/LEO这两个都是指最后一条的下一条的位置而不是指最后一条的位置。 LSO:Last Stable Offset 对未完成的事务而言,LSO 的值等于事务中第一条消息的位置(firstUnstableOffset),对已完成的事务而言,它的值同 HW 相同 LW:Low Watermark 低

    2024年04月14日
    浏览(71)
  • 50道微服务经典面试题,想进大厂你必须弄懂(内附答案)(1)

    图2:微服务的  优点 – 微服务访谈问题 独立开发  – 所有微服务都可以根据各自的功能轻松开发 独立部署  – 基于其服务,可以在任何应用程序中单独部署它们 故障隔离  – 即使应用程序的一项服务不起作用,系统仍可继续运行 混合技术堆栈  – 可以使用不同的语言

    2024年04月27日
    浏览(33)
  • 50道web前端工程师面试题及答案解析,你学会了吗

    简介:本文包含了50个实用的前端面试题及答案解析,涵盖了HTML、CSS、JavaScript、DOM、Ajax、MVC、模块化、ES6、SPA、Webpack、Babel、Virtual DOM、响应式设计、移动优先设计、响应式图片、CSS 预处理器、后处理器、模块化、布局、盒模型、浮动、定位、动画、选择器、HTML5、WebGL、

    2024年02月02日
    浏览(83)
  • 大学生创新创业万学答案(三)

    1 、 (单选) 经过第一轮的创业行业选择之后,需要进行重新分组,将第一轮筛选出的行业进行统合,进行第二轮的筛选。 A 、 是 B 、 否 【您的答案】 A 【正确答案】 A 2 、 (单选) 第二轮的创业行业选择,不能再通过简单的渠道进行,必须是全面的实地调研。 A 、 是

    2024年02月06日
    浏览(34)
  • 50道SQL面试题

    有需要互关的小伙伴,关注一下,有关必回关,争取今年认证早日拿到博客专家 1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号 理解:同一学生的01比02高,例如:张三的01比张三的02高 6、查询学过“数学老师-杰斯”老师所教的 所有 课的同学的学号 10、查询

    2024年03月16日
    浏览(41)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包