school五十道练习题,高级查询

2024-05-01 00:38
文章标签 查询 练习题 高级 school 五十道

本文主要是介绍school五十道练习题,高级查询，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

简单解释就是假设两个表A、B。

以A表为左，B表为右。

如果是ON A.key = B.key,如果符合条件那么B表的内容也会列出来

如果不符合条件 那么A表的属性会显示，但是B表的内容就全部显示为NULL

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
select stu.s_id,stu.s_name,stu.s_birth,stu.s_sex,sc.s_score 
from student stu 
right join 
(select s1.s_id,s1.s_score from score s1,score s2 where s1.c_id='01' and s2.c_id='02' and s1.s_id=s2.s_id and s1.s_score>s2.s_score) sc 
on sc.s_id=stu.s_id;--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:
select stu.s_id,stu.s_name,stu.s_birth,stu.s_sex,sc.s_score from student stu 
right join 
(select s1.s_id,s1.s_score from score s1,score s2 where s1.c_id='01' and s2.c_id='02' and s1.s_id=s2.s_id and s1.s_score<s2.s_score) sc 
on sc.s_id=stu.s_id;--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:
select s_id,avg(s_score) from score group by s_id having avg(s_score)>=60select sc.s_id,sc.avgsc,stu.s_name
from
(select s_id,avg(s_score) avgsc from score group by s_id having avg(s_score)>=60) sc
left join student stu
on stu.s_id=sc.s_id;--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:(包括有成绩的和无成绩的)
select sc.s_id,stu.s_name,avg(sc.s_score) as avgsc
from student stu
right join score sc on sc.s_id=stu.s_id
group by sc.s_id,stu.s_name
having avgsc<60
union 
select stu.s_id,stu.s_name,NULL
from score sc
right join student stu on stu.s_id=sc.s_id
where sc.s_score is null;--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
select stu.s_id,stu.s_name,count(sc.c_id),sum(sc.s_score)
from student stu
left join score sc on sc.s_id=stu.s_id
group by stu.s_id,stu.s_name--6、查询"李"姓老师的数量:
select count(t_name) from teacher where t_name like '李%';--7、查询学过"张三"老师授课的同学的信息:
select stu.* from teacher t
left join course c on c.t_id=t.t_id
left join score sc on sc.c_id=c.c_id
left join student stu on stu.s_id=sc.s_id
where t.t_name='张三'--8、查询没学过"张三"老师授课的同学的信息:
select stu.* from student stu 
left join teacher t on t.t_name='张三'
left join course c on c.t_id=t.t_id
left join score sc on sc.s_id=stu.s_id and sc.c_id=c.c_id
where sc.s_score is null;select stu2.* from (select stu.* from teacher t
left join course c on c.t_id=t.t_id
left join score sc on sc.c_id=c.c_id
left join student stu on stu.s_id=sc.s_id
where t.t_name='张三') stu1
right join student stu2 on stu2.s_id=stu1.s_id
where stu1.s_id is null;--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:
select stu.* from score sc
left join score sc1 on sc1.s_id=sc.s_id and sc1.c_id='01'
left join student stu on stu.s_id=sc.s_id
where sc.c_id=02 and sc.s_score is not null and sc1.s_score is not null;--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:
select stu.* from score sc1
left join score sc2 on sc2.s_id=sc1.s_id and sc2.c_id=02
left join student stu on stu.s_id=sc1.s_id
where sc1.c_id=01 and sc1.s_score is not null and sc2.s_score is null;--11、查询没有学全所有课程的同学的信息:
--–先查询出课程的总数量–再查询所需结果
select stu.* from course cs
left join score sc on sc.c_id=cs.c_id
left join student stu on stu.s_id=sc.s_idselect count(c_id) from course;
select stu.* from student stu
left join score sc on sc.s_id=stu.s_id
left join (select count(*) as sumc from course) cs--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:
select distinct stu.* from student stu
left join score s1 on s1.s_id='01'
left join score s2 on s2.s_id=stu.s_id and s2.c_id=s1.c_id
where s2.s_score is not null--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:
select stu1.* from student stu1
left join (select distinct stu.s_id from student stu
left join course cs
left join score s1 on s1.s_id='01' and s1.c_id=cs.c_id
left join score sc on sc.c_id=s1.c_id and sc.s_id=stu.s_id
where sc.s_score is null) stu2 on stu1.s_id=stu2.s_id
where stu1.s_id<>'01' and stu2.s_id is null;--14、查询没学过"张三"老师讲授的任一门课程的学生姓名:
select stu.s_name from student stu 
left join teacher t on t.t_name='张三'
left join course cs on cs.t_id=t.t_id
left join score sc on sc.c_id=cs.c_id and sc.s_id=stu.s_id
where sc.s_score is null；--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩:
select stu.s_id,stu.s_name,avg(sc.s_score) from student stu
left join score sc on sc.s_id=stu.s_id
left join score s1 on s1.s_id=sc.s_id
where s1.c_id<>sc.c_id and sc.s_score<60 and s1.s_score<60
group by stu.s_id,stu.s_name--16、检索"01"课程分数小于60，按分数降序排列的学生信息:
select * from student stu
left join score sc on stu.s_id=sc.s_id and sc.c_id='01'
where sc.s_score<60
order by sc.s_score desc--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:
select sc.*,s1.avgsc from score sc
left join (select s_id,avg(s_score) as avgsc from score  group by s_id) s1 on sc.s_id=s1.s_id  
order by s1.avgsc desc;--18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率:
select cs.c_id,cs.c_course,sc.maxsc,sc.minsc,sc.avgsc,sc.jigelv from course cs
left join 
(select c_id,
max(s_score) maxsc,
min(s_score) minsc,
round(avg(s_score),2) avgsc,
concat(round(sum(if(s_score>60,1,0))/sum(if(s_score is not null,1,0))*100,2),'%') jigelv
from score group by c_id) sc on sc.c_id=cs.c_id;--19、按各科成绩进行排序，并显示排名:– row_number() over()分组排序功能
select cs.c_id,cs.c_course,stu.s_name,sc.s_id,sc.s_score,row_number()over(partition by sc.c_id order by sc.s_score desc) as rank
from course cs
left join score sc on sc.c_id=cs.c_id
left join student stu on stu.s_id=sc.s_id;--20、查询学生的总成绩并进行排名:
select sc.s_id,stu.s_name,sum(sc.s_score),row_number() over(order by sum(sc.s_score) desc)
from score sc
left join student stu on stu.s_id=sc.s_id
group by sc.s_id,stu.s_name;--21、查询不同老师所教不同课程平均分从高到低显示:
select c_id,avg(s_score) as avgsc from score group by c_id;select cs.*,t.t_name,sc.avgsc from course cs
left join 
teacher t on t.t_id=cs.t_id
left join 
(select c_id,avg(s_score) as avgsc from score group by c_id) sc
on sc.c_id=cs.c_id
order by avgsc desc;--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:
--因为row_number()over()的执行晚于 where 、group by、  order by 的执行
select 
s_id,
s_score,
c_id,
row_number() over(partition bt c_id order by s_score desc) as rk
from score;select stu.*,sc.s_score,cs.c_course,sc.rk from student stu
right join 
(select 
s_id,
s_score,
c_id,
row_number() over(partition by c_id order by s_score desc) as rk
from score) as sc on sc.s_id=stu.s_id
left join course cs on cs.c_id=sc.c_id
where rk between 2 and 3;
--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select 
c_id,
sum(if(s_score>=85 and s_score<100,1,0)) as `[100-85]`,
sum(if(s_score>=75 and s_score<85,1,0)) as `[84-70]`,
sum(if(s_score>=60 and s_score<75,1,0)) as `[69-69]`,
sum(case when s_score<60 then 1 else 0 end) as `[60-0]`
from score
group by c_id;select cs.c_id,cs.c_course,sc.*
from course cs
left join 
(select 
c_id,
sum(if(s_score>=85 and s_score<100,1,0)) as `[100-85]`,
sum(if(s_score>=75 and s_score<85,1,0)) as `[84-70]`,
sum(if(s_score>=60 and s_score<75,1,0)) as `[69-69]`,
sum(case when s_score<60 then 1 else 0 end) as `[60-0]`
from score
group by c_id) sc on sc.c_id=cs.c_id;--24、查询学生平均成绩及其名次:
select s_id,
round(avg(s_score),2),
row_number()over(order by avg(s_score) desc) as rk 
from score group by s_id;--25、查询各科成绩前三名的记录   三个语句
select s_id,
c_id,
row_number()over(partition by c_id order by s_score desc) as rk
from score;select sc.*,cs.c_course,stu.s_name
from (select s_id,
c_id,
row_number()over(partition by c_id order by s_score desc) as rk
from score) sc
left join course cs on cs.c_id=sc.c_id
left join student stu on stu.s_id=sc.s_id
where rk between 1 and 3;--26、查询每门课程被选修的学生数:
select sc.c_id,count(sc.s_id) from score sc group by sc.c_id;--27、查询出只有两门课程的全部学生的学号和姓名:
select stu.s_id,stu.s_name 
from (select s_id from score group by s_id having count(c_id)=2) sc
left join student stu on stu.s_id=sc.s_id;--28、查询男生、女生人数:
select stu.s_sex,count(s_sex) from student stu group by s_sex;--29、查询名字中含有"风"字的学生信息:
select stu.* from student stu where stu.s_name like '%风%';--30、查询同名同性学生名单，并统计同名人数:
select stu.s_name,count(1) from student stu group by stu.s_name;--31、查询1990年出生的学生名单:
select stu.* from student stu where year(s_birth)=1990;--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:
select c_id,avg(s_score) avgsc from score group by c_id order by avgsc desc,c_id asc;--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
select s_id,avg(s_score) avgsc from score group by s_id;select sc.s_id,stu.s_name,sc.avgsc from (select s_id,avg(s_score) avgsc from score group by s_id) sc
left join student stu on stu.s_id=sc.s_id
where avgsc>=85;--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:
select stu.s_name,sc.s_score from course cs
left join score sc on sc.c_id=cs.c_id
left join student stu on stu.s_id=sc.s_id
where cs.c_course='数学' and sc.s_score<60;--35、查询所有学生的课程及分数情况:
select stu.*,sc.*,cs.c_course from student stu
left join score sc on sc.s_id=stu.s_id
left join course cs on cs.c_id=sc.c_id;--36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:
select distinct stu.s_name,cs.c_course,sc.s_score from course cs
left join score sc on sc.c_id=cs.c_id
left join student stu on stu.s_id=sc.s_id
where s_score>70;--37、查询课程不及格的学生:
select distinct stu.s_name from score sc
left join student stu on stu.s_id=sc.s_id
where sc.s_score<60;--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
select sc.s_id,stu.s_name from score sc
left join student stu on stu.s_id=sc.s_id
where sc.c_id='01' and sc.s_score>80;--39、求每门课程的学生人数:
select c_id,count(1) from score sc group by c_id;--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:
select stu.*,sc.s_score
from score sc
left join course cs on cs.c_id=sc.c_id
left join teacher t on t.t_name='张三'
left join student stu on stu.s_id=sc.s_id
order by sc.s_score desc
limit 1;--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
select distinct s1.s_id,s1.c_id,s1.s_score
from score s1
left join score s2 on s1.s_score=s2.s_score
where s1.c_id<>s2.c_id;--42、查询每门课程成绩最好的前三名:
select *,
row_number()over(partition by c_id order by s_score) rk
from score;select sc.*,stu.s_name from (select *,
row_number()over(partition by c_id order by s_score desc) rk
from score) sc
left join student stu on stu.s_id=sc.s_id
where rk between 1 and 3;--43、统计每门课程的学生选修人数（超过5人的课程才统计）:
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id,count(1) as sumcs
from score 
group by c_id 
having count(1)>5 
order by sumcs desc,c_id asc;--44、检索至少选修两门课程的学生学号:
select sc.s_id
from 
(select s_id,count(*)over(partition by s_id) sumcs from score) sc
where sc.sumcs>=2;--45、查询选修了全部课程的学生信息:
select s_id,count(*)over(partition by s_id) sumcs from scoreselect distinct stu.*
from 
(select s_id,count(*)over(partition by s_id) sumcs from score) sc
left join (select count(*) sumcs from course) cs
left join student stu on stu.s_id=sc.s_id
where sc.sumcs=cs.sumcs;--46、查询各学生的年龄(周岁):
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s_id,s_name,
(case when  year(s_birth)<year(current_date()) and month(s_birth)>month(current_date())
then year(current_date())-year(s_birth)-1 
when  year(s_birth)<year(current_date()) and month(s_birth)=month(current_date()) and day(s_birth)>day(current_date()) 
then year(current_date())-year(s_birth)-1 
else  year(current_date())-year(s_birth) end) as age
from student--47、查询本周过生日的学生:
select * from student stu where weekofyear(s_birth)=weekofyear(current_date());--48、查询下周过生日的学生:
SELECT * FROM student WHERE weekofyear(s_birth)=weekofyear(current_date()) + 1;--49、查询本月过生日的学生:
select * from student where month(current_date())=month(s_birth);--50、查询12月份过生日的学生:
SELECT * FROM student WHERE 12=MONTH(s_birth);

这篇关于school五十道练习题,高级查询的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/950320。

相关文章

Mybatis 传参与排序模糊查询功能实现

Mybatis 传参与排序模糊查询功能实现

《Mybatis传参与排序模糊查询功能实现》：本文主要介绍Mybatis传参与排序模糊查询功能实现,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友跟随小编一起看看吧... 目录一、#{ }和${ }传参的区别二、排序三、like查询四、数据库连接池五、mysql 开发企业规范一、#{ }和${ }传参的
阅读更多...
前端高级CSS用法示例详解

前端高级CSS用法示例详解

《前端高级CSS用法示例详解》在前端开发中，CSS（层叠样式表）不仅是用来控制网页的外观和布局，更是实现复杂交互和动态效果的关键技术之一,随着前端技术的不断发展，CSS的用法也日益丰富和高级,本文将深... 前端高级css用法在前端开发中，CSS（层叠样式表）不仅是用来控制网页的外观和布局，更是实现复杂交
阅读更多...
浅谈mysql的sql_mode可能会限制你的查询

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景：问题描述原因分析：解决方案：第一种：修改后，只有当前生效，若是mysql服务重启，就会失效；
阅读更多...
MySQL多列IN查询的实现

MySQL多列IN查询的实现

《MySQL多列IN查询的实现》多列IN查询是一种强大的筛选工具,它允许通过多字段组合快速过滤数据,本文主要介绍了MySQL多列IN查询的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录一、基础语法：多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析与优化1.
阅读更多...
mybatis-plus 实现查询表名动态修改的示例代码

mybatis-plus 实现查询表名动态修改的示例代码

《mybatis-plus实现查询表名动态修改的示例代码》通过MyBatis-Plus实现表名的动态替换,根据配置或入参选择不同的表,本文主要介绍了mybatis-plus实现查询表名动态修改的示... 目录实现数据库初始化依赖包配置读取类设置 myBATis-plus 插件测试通过 mybatis-plu
阅读更多...
MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固 通俗易懂版)

MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固 通俗易懂版)

《MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固通俗易懂版)》本文主要讲解了MySQL中的多表查询,包括子查询、笛卡尔积、自连接、多表查询的实现方法以及多列子查询等,通过实际例子和操... 目录复合查询1. 回顾查询基本操作group by 分组having1. 显示部门号为10的部门名，员
阅读更多...
kotlin中的行为组件及高级用法

kotlin中的行为组件及高级用法

《kotlin中的行为组件及高级用法》Jetpack中的四大行为组件：WorkManager、DataBinding、Coroutines和Lifecycle,分别解决了后台任务调度、数据驱动UI、异... 目录WorkManager工作原理最佳实践Data Binding工作原理进阶技巧Coroutine
阅读更多...
mysql关联查询速度慢的问题及解决

mysql关联查询速度慢的问题及解决

《mysql关联查询速度慢的问题及解决》：本文主要介绍mysql关联查询速度慢的问题及解决方案,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql关联查询速度慢1. 记录原因1.1 在一次线上的服务中1.2 最终发现2. 解决方案3. 具体操作总结mysql
阅读更多...
mysql线上查询之前要性能调优的技巧及示例

mysql线上查询之前要性能调优的技巧及示例

《mysql线上查询之前要性能调优的技巧及示例》文章介绍了查询优化的几种方法,包括使用索引、避免不必要的列和行、有效的JOIN策略、子查询和派生表的优化、查询提示和优化器提示等,这些方法可以帮助提高数... 目录避免不必要的列和行使用有效的JOIN策略使用子查询和派生表时要小心使用查询提示和优化器提示其他常
阅读更多...
SQL 中多表查询的常见连接方式详解

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接（INNERJOIN）、左连接（LEFTJOIN）、右连接（RIGHTJOIN）、全外连接（FULLOUTER... 目录一、连接类型图表（ASCII 形式）二、前置代码（创建示例表）三、连接方式代码示例1. 内连接（I
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2