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。

相关文章

活用c4d官方开发文档查询代码

活用c4d官方开发文档查询代码

当你问AI助手比如豆包，如何用python禁止掉xpresso标签时候，它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来
阅读更多...
系统架构师考试学习笔记第三篇——架构设计高级知识（20）通信系统架构设计理论与实践

系统架构师考试学习笔记第三篇——架构设计高级知识（20）通信系统架构设计理论与实践

本章知识考点：         第20课时主要学习通信系统架构设计的理论和工作中的实践。根据新版考试大纲,本课时知识点会涉及案例分析题(25分),而在历年考试中,案例题对该部分内容的考查并不多,虽在综合知识选择题目中经常考查,但分值也不高。本课时内容侧重于对知识点的记忆和理解,按照以往的出题规律,通信系统架构设计基础知识点多来源于教材内的基础网络设备、网络架构和教材外最新时事热点技术。本课时知识
阅读更多...
dp算法练习题【8】

dp算法练习题【8】

不同二叉搜索树 96. 不同的二叉搜索树 给你一个整数 n ，求恰由 n 个节点组成且节点值从 1 到 n 互不相同的 二叉搜索树 有多少种？返回满足题意的二叉搜索树的种数。 示例 1： 输入：n = 3输出：5 示例 2： 输入：n = 1输出：1 class Solution {public int numTrees(int n) {int[] dp = new int
阅读更多...
ural 1026. Questions and Answers 查询

ural 1026. Questions and Answers 查询

1026. Questions and Answers Time limit: 2.0 second Memory limit: 64 MB Background The database of the Pentagon contains a top-secret information. We don’t know what the information is — you
阅读更多...
Mybatis中的like查询

Mybatis中的like查询

<if test="templateName != null and templateName != ''">AND template_name LIKE CONCAT('%',#{templateName,jdbcType=VARCHAR},'%')</if>
阅读更多...
Java基础回顾系列-第七天-高级编程之IO

Java基础回顾系列-第七天-高级编程之IO

Java基础回顾系列-第七天-高级编程之IO 文件操作字节流与字符流OutputStream字节输出流FileOutputStream InputStream字节输入流FileInputStream Writer字符输出流FileWriter Reader字符输入流字节流与字符流的区别转换流InputStreamReaderOutputStreamWriter 文件复制 字符编码内存操作流(
阅读更多...
Java基础回顾系列-第五天-高级编程之API类库

Java基础回顾系列-第五天-高级编程之API类库

Java基础回顾系列-第五天-高级编程之API类库 Java基础类库StringBufferStringBuilderStringCharSequence接口AutoCloseable接口RuntimeSystemCleaner对象克隆 数字操作类Math数学计算类Random随机数生成类BigInteger/BigDecimal大数字操作类 日期操作类DateSimpleDateForma
阅读更多...
京东物流查询｜开发者调用API接口实现

京东物流查询｜开发者调用API接口实现

快递聚合查询的优势 1、高效整合多种快递信息。2、实时动态更新。3、自动化管理流程。 聚合国内外1500家快递公司的物流信息查询服务，使用API接口查询京东物流的便捷步骤，首先选择专业的数据平台的快递API接口：物流快递查询API接口-单号查询API - 探数数据 以下示例是参考的示例代码： import requestsurl = "http://api.tanshuapi.com/a
阅读更多...
DAY16：什么是慢查询，导致的原因，优化方法 | undo log、redo log、binlog的用处 | MySQL有哪些锁

DAY16：什么是慢查询，导致的原因，优化方法 | undo log、redo log、binlog的用处 | MySQL有哪些锁

目录 什么是慢查询，导致的原因，优化方法 undo log、redo log、binlog的用处  MySQL有哪些锁   什么是慢查询，导致的原因，优化方法 数据库查询的执行时间超过指定的超时时间时，就被称为慢查询。 导致的原因： 查询语句比较复杂：查询涉及多个表，包含复杂的连接和子查询，可能导致执行时间较长。查询数据量大：当查询的数据量庞大时，即使查询本身并不复杂，也可能导致
阅读更多...
Mysql高级篇（中）——索引介绍

Mysql高级篇（中）——索引介绍

Mysql高级篇（中）——索引介绍 一、索引本质二、索引优缺点三、索引分类（1）按数据结构分类（2）按功能分类（3） 按存储引擎分类（4） 按存储方式分类（5） 按使用方式分类 四、 索引基本语法（1）创建索引（2）查看索引（3）删除索引（4）ALTER 关键字创建/删除索引 五、适合创建索引的情况思考题 六、不适合创建索引的情况 一、索引本质 索引本质 是 一种数据结构，它用
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2