sql经典45题详解亲测可用版带结果----31至45题

有很多同学是只看了前面的几道题,所以我倒序整理,先写后面15个题目。

后续篇章链接:https://blog.csdn.net/s_xing/article/details/106589779

练习数据

--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 分数

 

练习题目

31.  查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

思路:在SC里查询出01课程在80分以上的sid,关联学生表得到姓名

答案:

select
sc.sid, s.sname, sc.score
from
sc inner join student s
on sc.sid = s.sid
where sc.cid='01' and score>=80;

32.  求每门课程的学生人数

思路:在SC里对cid做聚合计算

答案:

select
cid, count(1)
from sc
group by cid;

33.  成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

思路:在SC作为基本表cid关联到course里面的tid,再关联到teacher表里面得到tname,就可以筛选得到张三老师的学生们sid的集合,再关联到student表就可以得到学生的信息。然后依据score做降序排列第一个人就是最高成绩者。

答案:

select *
from 
sc inner join course c on sc.cid = c.cid
inner join teacher t on c.tid = t.tid
inner join student s on s.sid = sc.sid
where t.tname = '张三' 
order by sc.score desc limit 1;

34.  成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

思路:在SC作为基本表cid关联到course里面的tid,再关联到teacher表里面得到tname,就可以筛选得到张三老师的学生们sid的集合,再关联到student表就可以得到学生的信息。然后依据score做降序排列我们得到张老师学生的排序表T了,现在要处理成绩重复的情况。

在T表的基础上,我们增加两个变量 rank记录排名,从0开始;score记录上一个同学的成绩,从-1开始;就可以比较当前和上一个的成绩了,出现变化则rank+1.这样就得到每个同学的排名X表。从X表容易筛选得到排名为1的同学集。

答案:

select * from 
(
        select T.*,
            @rank:=(case when @sco=score then @rank else @rank+1 end) as rn,
            @sco:=score as scoreRecord

            from 
                        (select t.tname, sc.cid, sc.score, s.*
                                from 
                                sc inner join course c on sc.cid = c.cid
                                inner join teacher t on c.tid = t.tid
                                inner join student s on s.sid = sc.sid
                                where t.tname = '张三' 
                                order by sc.score desc limit 5) T 
                        inner join (select @rank:=0, @sco:=-1) ext -- 千万别忘了起别名
        ) X
where rn < 2;

35.  查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

思路:在SC里做自关联,要求是同一个学生,不同科目,相同成绩的才关联成功。这时候一共会有C23,6个条目其中有重复的,我们通过group by 去重

答案:

select a.sid, a.cid, a.score
from sc a inner join sc b
on a.sid = b.sid and a.cid != b.cid and a.score = b.score
group by a.sid, a.cid, a.score;

36.  查询每门功成绩最好的前两名

思路:在SC里排序,cid首个排序关键字,score次排序关键字(降序)。在这个基础上给每个同学附加一个排名信息。需要用到变量rank记录排名,cidR记录上一条的课程名字。如果cidR没变说明是同一门课程rank增长,如果cidR变了说明是新的课程了,rank赋值1.有了每个同学的排名信息再此基础之上筛选前二名即可。

答案:

select sid, cid, score, rn
from 
        (select 
        sc.*, 
        @rank:=if(@cidR=cid, @rank+1, 1) as rn,
        @cidR:=cid as cidR 

        from sc, (select @rank:=0, @cidR:=null) ext
        order by cid, score desc) X
where rn < 3;

37.  统计每门课程的学生选修人数(超过 5 人的课程才统计)

思路:在SC里对cid分类聚合并且只筛出其中数目大于5的情况。

答案:

select 
cid, count(1)
from sc
group by cid
having count(1)>5;

38.  检索至少选修两门课程的学生学号

思路:在SC里对sid分类聚合并且只筛出其中数目大于2的情况。

答案:

select sid
from sc
group by sid
having count(1) >= 2;

39.  查询选修了全部课程的学生信息

思路:在SC里对sid进行聚合并计数,筛选出其中计数为3的同学。其中的3又需要通过对cid计数得到。

答案:

select 
stu.*
from sc inner join student stu
on sc.sid=stu.sid
group by sc.sid
having count(1)>=(select count(1)
from (select distinct cid from sc) b
);

40.  查询各学生的年龄,只按年份来算

思路:year()函数抽取年份信息,now()函数获得当前时间信息

答案:

select 
sid, sname, year(now())-year(sage) as nianling, sage
from student;

41.  按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一

思路:timestampdiff()函数里面传递year,得到年差距,这里的计算包含了去尾

答案:

select 
sid, sname, sage, timestampdiff(year, sage, curdate()) as accurateAge
from student;

42.  查询本周过生日的学生

思路:week函数得到当前date的week数,字符串拼接可以得到date数据。年末的情况没考虑

答案:

select 
sid, 
sname,
sage,
curdate(),
concat(year(curdate()), '-', month(sage), '-', day(sage)) as birthdayThisYear
from student
where week("2020-07-03") = week(concat(year(curdate()), '-', month(sage), '-', day(sage)));

43.  查询下周过生日的学生

思路:week函数得到当前date的week数,字符串拼接可以得到date数据。

答案:

select 
sid, 
sname,
sage,
curdate(),
concat(year(curdate()), '-', month(sage), '-', day(sage)) as birthdayThisYear
from student
where week("2020-06-25") = week(concat(year(curdate()), '-', month(sage), '-', day(sage)))-1;

45.  查询下月过生日的学生

思路:本年内的好算1月的下个月是2月,跨年的情况需要综合考虑year和month信息。

答案:

select *
from student
where month('2020-12-23')+1=month(sage) or month('2020-12-23')+1=month(sage)+12  -- 跨年

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值