经典MySQL50题-经管之家官网！

经济学管理学金融学统计学

您当前的位置> 考研考博>>

经典MySQL50题

发布：hylnwoau | 分类：考研

关于本站

人大经济论坛-经管之家：分享大学、考研、论文、会计、留学、数据、经济学、金融学、管理学、统计学、博弈论、统计年鉴、行业分析包括等相关资源。
经管之家是国内活跃的在线教育咨询平台!

提供"微信号、微博、抖音、快手、头条、小红书、百家号、企鹅号、UC号、一点资讯"等虚拟账号交易，真正实现买卖双方的共赢。【请点击这里访问】

TOP热门关键词

专题页面精选

最近花了三四天把入门经典《SQL必知必会》看完了，然后开始做网上的50道经典练习题，部分习题比较难，大部分比较简单。下面，我po出自己的解答思路与结果：（如有错误或改进的地方还望留言指正~）#1.1查询01课程比02 ...

扫码加入统计交流群

最近花了三四天把入门经典《SQL必知必会》看完了，然后开始做网上的50道经典练习题，部分习题比较难，大部分比较简单。下面，我po出自己的解答思路与结果：（如有错误或改进的地方还望留言指正~）
#1.1查询01课程比02课程成绩高的学生信息及课程分数 01 02都存在
select student.*,
max(case when sc.cid='01' then score end) as C1,
max(case when sc.cid='02' then scoreend)as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is not null
and c2 is not null
and c1>c2;
select * from student right join(
select t1.sid, C1,C2 from
(select sid,score as C1 from sc where sc.cid='01') as t1,
(select sid,score as C2 from sc where sc.cid='02') as t2
where t1.sid=t2.sid and t1.c1>t2.c2
)r
on student.sid=r.sid;
#1.2查询01课程比02课程成绩高的学生信息及课程分数 01存在 02可能不存在
select student.*,
max(case when cid='01' then score end) as C1,
max(case when cid='02' then score end) as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is not null
and c1>c2
or c2 is null;
#1.3查询01课程分数不存在但02存在
select student.*,
max(case when cid='01' then score end) as C1,
max(case when cid='02' then score end) as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is null
and c2 is not null;
##2.查询平均成绩大于等于60的同学的学生编号和学生姓名和平均成绩
select Sname,SId,(select avg(score)
from sc
where sc.SId=student.SId
group by SId
having AVG(score)>=60)as avg_score
from student;
##3.查询SC表存在成绩的学生信息
select Sname,Sage,Ssex
from student
where SId IN(select SId from sc where score IS NOT NULL);
#4 查询所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score
from student left join sc
on sc.SId=student.SId
group by SId;
#4.1 查询所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩(有成绩)
select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score
from student inner join sc
on sc.SId=student.SId
group by SId;
#5.查询李姓老师的数量
select count(*) as num_li
from teacher
where Tname like '李%';
#6.查询学过张三老师课程的学生的信息
select *
from student
where SId in(
select SId from sc where CId in(select CId
from course
where TId=(select TId
from teacher
where Tname='张三'))); #子查询
select student.SId,Sname,Sage,Ssex
from student,sc,course,teacher
where student.SId=sc.SId
and sc.CId=course.CId
and teacher.TId=course.TId
and teacher.Tname='张三'; #多表联结
#7.查询没有学全所有课程的同学的信息
select student.*,count(sc.SId) as num_course
from student left join sc
on student.SId=sc.SId
group by student.SId
having num_course<(select count(CId) from course); #分组后筛选用having
#8.查询至少一门课与学号为‘01’的同学所学相同的同学的信息
select *
from student
where SId in (
select distinct SId from sc
where CId in (select CId from sc where SId='01'));#课程总数
#9.查询和01同学课程完全相同的学生的信息
select SId,Count(SId) as num_course
from sc
where CId in('01','02','03')
group by SId
having num_course=3;
select *
from student
where SId in(select SId
from sc
where CId in(select CId from sc where SId='01')
group by SId
having Count(SId)=(select count(CId)from sc where SId='01'));
#10.查询没学过‘张三’老师教授的任一门课程的学生姓名
select CId,teacher.TId,Tname
from course inner join teacher
on course.Tid=teacher.tid
where Tname='张三'; #教授的课程编号
select student.*
from student
where SId not in (select distinct student.SId
from student left join sc
on student.sid=sc.sid
where cidin (select CId
from course inner join teacher
on course.Tid=teacher.tid
where Tname='张三')); #学过张三老师任一门课的同学)
select student.*
from student
where SId not in (select distinct student.SId
from student,sc,course,teacher
where student.sid=sc.sid
and course.cid=sc.cid
and course.Tid=teacher.tid
andTname='张三');#学过张三老师任一门课的同学
#11.查询两门及以上不及格课程的同学的学号，姓名和平均成绩
select student.SId,Sname,avg(score) as avg_score
from sc inner join student
on sc.SId=student.SId
group by sc.SId
having sc.SId in (select sid
from sc
where score<60
group by SId
having count(CId)>=2); #2门及以上课程不及格同学的ID
#12.检索01课程分数小于60，按分数降序排列的学生信息
select student.*
from student,sc
where student.sid=sc.sid
and cid='01'
and score<60
order by score desc;
#13.查询平均成绩由高到低显示所有学生的所有课程的成绩及平均成绩
select * from sc
left join (select sid,avg(score) as avg_score from sc
group by sid)r
on r.sid=sc.sid
order by avg_score desc;
#14.查询各科成绩的最高分、最低分和平均分
/*create view course_score as
select course.CId,course.Cname,sc.score
from course left join sc
on course.cid=sc.cid;*/
select cid,cname,max(score) as max_score,
min(score) as min_score,
avg(score) as avg_score,
sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when 80>score&score>=70 then 1 else 0 end)/count(*) as 中等率,
sum(case when 90>score&score>=80 then 1 else 0 end)/count(*) as 优良率,
sum(case when score>=90 then 1 else 0 end)/count(*) as 优秀率,
count(*) as num
from course_score
group by cid
order by num desc,cid asc;
#15.按各科成绩进行排序，并显示排名，成绩重复时保留名次空缺
select A.CID,A.sid,A.score,count(B.score)+1 as rank
from sc as A
left join sc as B
on B.score>A.score and B.cid=A.cid
group by A.cid,A.sid
order by A.cid,rank asc;
#15.1 按各科成绩进行排序，并显示排名，成绩重复时合并名次
select a.*,count(distinct b.score)+1 as rank
from sc as a
left join sc as b
on b.score>a.score and b.cid=a.cid
group by a.cid,a.sid
order by a.cid,rank;
#16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.sid,a.total,count(b.total)+1 as rank
from (select sid,sum(score) as total
from sc
group by sid )a
left join (select sid,sum(score) as total
from sc
group by sid )b
on b.total>a.total
group by a.sid;
#16.1查询学生的总成绩，并进行排名，总分重复时合并名次
select a.sid,a.total,count(distinct b.total)+1 as rank
from (select sid,sum(score) as total
from sc
group by sid )a
left join (select sid,sum(score) as total
from sc
group by sid )b
on b.total>a.total
group by a.sid;
#17.统计各科成绩各分数段的人数：课程编号，课程名称，[100-85],[85-70],[70-60],[60-0]
select sc.cid,cname,
sum(case when score<=100 and score>=85 then 1 else 0 end)/count(*) as s1,
sum(case when score<85 and score>=70 then 1 else 0 end)/count(*) as s2,
sum(case when score<70 and score>=60 then 1 else 0 end)/count(*) as s3,
sum(case when score<60 then 1 else 0 end)/count(*) as s4
from sc,course
where sc.cid=course.cid
group by sc.cid;
#18.查询各科成绩前三名的记录
select a.*,count(b.sid)+1 as rank
from sc as a
left join sc as b
on a.cid=b.cid and b.score>a.score
group by a.cid,a.sid
having rank<=3
order by a.cid,a.score desc;
#19.查询每门课选修的学生人数
select cid,count(sid)
from sc
group by cid;
#20.查询出只修两门课程的学生学号和姓名
select student.sid,student.Sname
from student inner join sc
on sc.sid=student.sid
group by sc.sid
having count(cid)=2;
#21.查询男生、女生人数
select Ssex,count(*) as num
from student
group by Ssex;
#22.查询名字中带有风字的学生信息
select *
from student
where Sname like '%风%';
#24.查询1990年出生的学生名单
select *
from student
where year(sage)=1990;
#25.查询每门课的平均成绩，结果按平均成绩降序排列，若平均成绩相同时，则按课程号升序排列
select cid,avg(score) as avg_score
from sc
group by cid
order by avg_score desc,cid asc;
#26.查询平均成绩≥85的所有学生的学号、姓名和平均成绩
select student.sid,student.sname,avg(score) as avg_score
from student,sc
where student.sid=sc.sid
group by student.sid
having avg_score>=85;
#27.查询课程名称为数学，且分数低于60的学生姓名和分数
select student.sname,sc.score
from student,sc,course
where student.sid=sc.sid
and course.cid=sc.cid
and course.Cname='数学'
and sc.score<60;
#28.查询所有学生的课程及分数情况
select student.sid,student.sname,cid,score
from student left join sc
on student.sid=sc.sid;
#29.查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
select student.sname,course.cname,sc.score
from student,sc,course
where student.sid=sc.sid
and course.cid=sc.cid
and student.sid not in (select sid from sc group by sid having max(score)<=70);
#30.查询存在不及格的课程
select distinct course.cname,sc.cid
from course,sc
where course.cid=sc.cid
having sc.cid not in (select sid from sc group by cid having min(score)>=60);
#31.查询课程编号为01且课程成绩在80分以上的学生学号和姓名
select student.sid,sname
from student,sc
where sc.sid=student.sid
and sc.score>80
and sc.cid='01';
#32.求每门课程的学生人数
select cid,count(sid) as num
from sc
group by cid;
#33.成绩不重复，查询选张三老师授课的学生中，成绩最高的学生信息及其成绩
select student.*,score
from sc,student,course,teacher
where sc.sid=student.sid
and course.cid=sc.cid
and course.tid=teacher.tid
and tname='张三'
order by score desc
limit 0,1; #降序排列取第一名
#34.成绩重复，查询选李四老师授课的学生中，成绩最高的学生信息及其成绩
select student.*,score
from sc,student,course,teacher
where sc.sid=student.sid
and course.cid=sc.cid
and course.tid=teacher.tid
and tname='李四'
and sc.score=(select max(score)from sc,student,course,teacher
where sc.sid=student.sid
and course.cid=sc.cid
and course.tid=teacher.tid
and tname='李四') #找出最高分;
#35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from sc as a
left join sc as b
on a.sid=b.sid and b.cid<>a.cid
where a.score=b.score
group by a.cid;
#36.查询每门课程成绩最好的前两名
select a.*,count(b.sid)+1 as rank
from sc as a
left join sc as b
on b.score>a.score and a.cid=b.cid #左连结b表
group by a.cid,a.sid
having rank<=2
order by cid;
#37.统计每门课程的选修人数，超过5人
select cid,count(sid) as num
from sc
group by cid
having num>5;
#38.查询至少选修了两门课程的学生学号
select sid
from sc
group by sid
having count(sid)>=2;
#39.查询选修了全部课程的学生信息
select student.*
from student,sc
where student.sid=sc.sid
and sc.cid in (select cid from course)
group by sc.sid
having count(sc.cid)=(select count(distinct cid) from course);
#40.查询各学生的年龄，只按年份计算
select sname,year(now())-year(sage) as Age
from student;
#41.按出生日期来算，当前月日<出生月日，则年龄减1
select sid,case when month(now())<month(sage) or
( month(now())=month(sage) and day(now())<day(sage))
then year(now())-year(sage)-1elseyear(now())-year(sage) end as Age
from student;
#42.查询本周过生日的同学
select date_format(now(),'%w'); #查询今天星期几
set @mon=(select subdate(curdate(),date_format(curdate(),'%w')-1)); #本周一的日期
set @sun=(select adddate(curdate(),7-date_format(now(),'%w'))); #本周日的日期
select sid
from student
where
date_format(sage,'%m-%d')<=date_format(@sun,'%m-%d')
and date_format(sage,'%m-%d')>=date_format(@mon,'%m-%d');
#43.查询下周过生日的同学
set @Nextmon=(select adddate(curdate(),7-date_format(curdate(),'%w')+1)); #下周一的日期
set @Nextsun=(select adddate(curdate(),7-date_format(now(),'%w')+7)); #下周日的日期
select sid
from student
where
date_format(sage,'%m-%d')<=date_format(@Nextsun,'%m-%d')
and date_format(sage,'%m-%d')>=date_format(@Nextmon,'%m-%d');
#44.查询本月过生日的同学
select sid
from student
where month(sage)=month(now());
#45.查询下月过生日的同学
select sid
from student
where month(sage)=month(now())+1;

「经管之家」APP：经管人学习、答疑、交友，就上经管之家！
免流量费下载资料----在经管之家app可以下载论坛上的所有资源，并且不额外收取下载高峰期的论坛币。
涵盖所有经管领域的优秀内容----覆盖经济、管理、金融投资、计量统计、数据分析、国贸、财会等专业的学习宝库，各类资料应有尽有。
来自五湖四海的经管达人----已经有上千万的经管人来到这里，你可以找到任何学科方向、有共同话题的朋友。
经管之家（原人大经济论坛），跨越高校的围墙，带你走进经管知识的新世界。
扫描下方二维码下载并注册APP

本文关键词：

本文论坛网址：https://bbs.pinggu.org/thread-7020324-1-1.html

上一篇 | 两个调节变量相关系数太高怎么解决

下一篇 | 在读硕士实习招聘中科院战略咨询院

考研精彩帖子推荐更多

您可能感兴趣的文章

本站推荐的文章

人气文章

本文标题：经典MySQL50题

本文链接网址：https://bbs.pinggu.org/jg/kaoyankaobo_kaoyan_7020324_1.html

1.凡人大经济论坛-经管之家转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。

经典MySQL50题-经管之家官网！

考研