50道经典SQL练习题解答

最新推荐文章于 2024-08-22 01:19:55 发布

华生watson

最新推荐文章于 2024-08-22 01:19:55 发布

阅读量305

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44679921/article/details/116140157

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

50道经典SQL练习题

数据表介绍
–1.学⽣表
Student(SId,Sname,Sage,Ssex)
–SId 学⽣编号,Sname 学⽣姓名,Sage 出⽣年⽉,Ssex 学⽣性别
–2.课程表
Course(CId,Cname,TId)
–CId 课程编号,Cname 课程名称,TId 教师编号
–3.教师表
Teacher(TId,Tname)
–TId 教师编号,Tname 教师姓名
–4.成绩表
SC(SId,CId,score)
–SId 学⽣编号,CId 课程编号,score 分数

查询" 01 “课程⽐” 02 "课程成绩⾼的学⽣的信息及课程分数

select student.SId,Sname,Sage,Ssex,CId,score
from student left join sc
on student.SId=sc.SId
where student.SId in(select sc1.SId
from sc sc1 join sc sc2
on sc1.SId=sc2.SId
where sc1.CId='01' and sc2.CId='02' and sc1.score>sc2.score);

在这里插入图片描述

查询同时存在" 01 “课程和” 02 "课程的情况

select sc1.SId
from sc sc1 join sc sc2
on sc1.SId=sc2.SId
where sc1.CId='01' and sc2.CId='02';

在这里插入图片描述
3. 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

select *
from (select *
from sc
where CId='01') T1 left join(select *
from sc
where CId='02') T2
on T1.SId=T2.SId;

在这里插入图片描述

查询不存在" 01 “课程但存在” 02 "课程的情况

select *
from (select *
from sc
where CId='02') T1 left join(select *
from sc
where CId='01') T2
on T1.SId=T2.SId where T2.CId is null;

在这里插入图片描述
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩

select T1.SId,Sname,avg
from (select SId,avg(score) avg
from sc 
group by SId
having avg(score)>=60) T1 join student
on T1.SId=student.SId;

在这里插入图片描述

6.查询在 SC 表存在成绩的学⽣信息

select distinct student.*
from sc join student
on sc.SId=student.SId;

在这里插入图片描述
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select student.SId,Sname,count(CId),sum(score)
from student left join sc
on student.SId=sc.SId
group by student.SId,Sname;

在这里插入图片描述
8.查询「李」姓⽼师的数量

select count(*)
from teacher
where Tname like '李%';

在这里插入图片描述

9.查询学过「张三」⽼师授课的同学的信息

select *
from student
where SId in(select SId
from sc
where CId=(select CId
from course join teacher
on course.TId=teacher.TId
where Tname='张三'));

在这里插入图片描述

10.查询没有学全所有课程的同学的信息

select *
from student
where SId not in (select SId
from sc
group by SId
having count(CId)=(select count(*)
from course));

在这里插入图片描述
11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息

select *
from student
where SId in(select distinct SId
FROM sc 
where CId in(select CId
from sc
where SId='01'));

在这里插入图片描述
12.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select *
from student
where SId in (select sc2.SId
from sc sc1 join sc sc2
on sc1.CId=sc2.CId
where sc1.SId='01' and sc2.SId!='01'
group by sc2.SId
having count(sc2.CId)=(select count(*)
from sc
where SId='01') and count(sc2.CId)=(select count(*)
from sc
where SId='02'));

在这里插入图片描述

13.查询没学过"张三"⽼师讲授的任⼀⻔课程的学⽣姓名

select *
from student
where SId not in (select SId
from sc
where CId in (select course.CId
from course join teacher
on course.TId=teacher.TId
where Tname='张三'));

在这里插入图片描述
14.查询两⻔及其以上不及格课程的同学的学号，姓名及其平均成绩

select sc.SId,Sname,avg(score)
from sc join student
on sc.SId=student.SId
where score<60
group by sc.SId,Sname
having count(*)>=2;

在这里插入图片描述

15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息

select *
from sc join student
on sc.SId=student.SId
where score<60 and sc.CId='01'
order by score desc;

在这里插入图片描述
16.按平均成绩从⾼到低显示所有学⽣的所有课程的成绩以及平均成绩

解法1：

select *
from sc join (select SId,avg(score) avg_score
from sc
group by SId) T1
on sc.SId=T1.SId
order by avg_score desc;

在这里插入图片描述
解法2：

select Sname,sc1.score 语文,sc2.score 数学,sc3.score 英语,avg(sc4.score) 平均成绩
from student left join sc sc1 on student.SId=sc1.SId and sc1.CId='01'
left join sc sc2 on student.SId=sc2.SId and sc2.CId='02'
left join sc sc3 on student.SId=sc3.SId and sc3.CId='03'
left join sc  sc4 on student.SId=sc4.SId
group by Sname,语文,数学,英语
order by 平均成绩 desc;

在这里插入图片描述
17.查询各科成绩最⾼分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最⾼分，最低
分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀
为：>=90 要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列

select sc.CId,Cname,max(score) 最高分,min(score) 最低分,round(avg(score),2) 平均分,count(sc.SId) 选修人数,
sum(case when score>=60 then 1 else 0 end)/count(sc.SId) 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(sc.SId) 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(sc.SId) 优良率,
sum(case when score>=90 then 1 else 0 end)/count(sc.SId) 优秀率
from sc join course on sc.CId=course.CId
group by sc.CId,Cname
order by 选修人数 desc,sc.CId;

在这里插入图片描述
18.按各科平均成绩进⾏排序，并显示排名， Score 重复时保留名次空缺

select s2.CId,s2.avg_score,count(distinct s1.avg_score) rank
from (select CId,avg(score) avg_score
from sc
group by CId)  s1 join (select CId,avg(score) avg_score
from sc
group by CId) s2 on s1.avg_score>=s2.avg_score
group by s2.CId,s2.avg_score
order by rank;

在这里插入图片描述
19.按各科平均成绩进⾏排序，并显示排名， Score 重复时不保留名次空缺

select b.CId,b.avg_score,@i:=@i+1 as rank
from (select @i:=0) as a,
(select CId,avg(score) avg_score
from sc
group by CId
order by avg_score desc) as b;