最近这段时间有浏览到最经典的50道SQL题,看完瞬间有感,于是自己动手写了一下,大家也可以一起来刷一下。
自己写完再看其他解答,效果更佳!
初始化
create table student
(
Sid varchar(10) null comment '学生编号',
Sname varchar(10) charset utf8 null comment '学生姓名',
Sage datetime null comment '出生年月',
Ssex varchar(10) charset utf8 null comment '学生性别'
)
comment '学生表';
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');
INSERT INTO student (Sid, Sname, Sage, Ssex) VALUES ('09', '王菊', '1990-01-20 00:00:00', '女');
create table teacher
(
Tid varchar(10) null comment '教师编号',
Tname varchar(10) charset utf8 null comment '教师姓名'
)
comment '教师表';
INSERT INTO teacher (Tid, Tname) VALUES ('01', '张三');
INSERT INTO teacher (Tid, Tname) VALUES ('02', '李四');
INSERT INTO teacher (Tid, Tname) VALUES ('03', '王五');
create table course
(
Cid varchar(10) null comment '课程编号',
Cname varchar(10) charset utf8 null comment '课程名称',
Tid varchar(10) null comment '教师编号'
)
comment '课程表';
INSERT INTO course (Cid, Cname, Tid) VALUES ('01', '语文', '02');
INSERT INTO course (Cid, Cname, Tid) VALUES ('02', '数学', '01');
INSERT INTO course (Cid, Cname, Tid) VALUES ('03', '英语', '03');
create table sc
(
Sid varchar(10) null comment '学生编号',
Cid varchar(10) null comment '课程编号',
score decimal(18, 1) null comment '分数'
)
comment '成绩表';
INSERT INTO sc (Sid, Cid, score) VALUES ('01', '01', 80.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('01', '02', 90.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('01', '03', 99.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('02', '01', 70.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('02', '02', 60.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('02', '03', 80.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('03', '01', 80.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('03', '02', 80.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('03', '03', 80.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('04', '01', 50.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('04', '02', 30.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('04', '03', 20.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('05', '01', 76.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('05', '02', 87.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('06', '01', 31.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('06', '03', 34.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('07', '02', 89.0);
INSERT INTO sc (Sid, Cid, score) VALUES ('07', '03', 98.0);
题目
-
查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
-
查询同时存在" 01 “课程和” 02 "课程的情况
-
查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null)
-
查询不存在" 01 “课程但存在” 02 "课程的情况
-
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-
查询在 SC 表存在成绩的学生信息
-
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)
-
查有成绩的学生信息
-
查询「李」姓老师的数量
-
查询学过「张三」老师授课的同学的信息
-
查询没有学全所有课程的同学的信息
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-
查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
-
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
-
检索" 01 "课程分数小于 60,按分数降序排列的学生信息
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-
查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 -
按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
-
按各科成绩进行排序,并显示排名, Score 重复时合并名次
-
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
-
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-
查询各科成绩前三名的记录
-
查询每门课程被选修的学生数
-
查询出只选修两门课程的学生学号和姓名
-
查询男生、女生人数
-
查询名字中含有「风」字的学生信息
-
查询同名同性学生名单,并统计同名人数
-
查询 1990 年出生的学生名单
-
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
-
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-
查询不及格的课程
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
-
求每门课程的学生人数
-
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-
查询每门功成绩最好的前两名
-
统计每门课程的学生选修人数(超过 5 人的课程才统计)。
-
检索至少选修两门课程的学生学号
-
查询选修了全部课程的学生信息
-
查询各学生的年龄,只按年份来算
-
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
-
查询本周过生日的学生
-
查询下周过生日的学生
-
查询本月过生日的学生
-
查询下月过生日的学生
我的写法
写的不对的地方还请多多指教
-- 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select a.*, b.Cid, b.score from student a join
(select * from sc where Sid in
(select a.Sid from
(select * from sc where cid = 01) a join
(select * from sc where cid = 02) b on a.Sid = b.Sid
where a.score > b.score
)
)b where a.Sid = b.Sid;
-- 查询同时存在" 01 "课程和" 02 "课程的情况
select * from
(select * from sc where Cid = 01) a join
(select * from sc where Cid = 02) b on a.Sid = b.Sid;
-- 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from
(select * from sc where Cid in (01) ) a join
(select * from sc where Sid not in (
select Sid from sc where Cid = 02
)
) b on a.Sid = b.Sid;
-- 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from
(select * from sc where Cid in (02) ) a join
(select * from sc where Sid not in (
select Sid from sc where Cid = 01
)
) b on a.Sid = b.Sid;
-- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.Sid, a.Sname, avg from student a join
(select Sid, avg(score) as avg from sc group by Sid) b on a.Sid = b.Sid
where avg >= 60;
-- 查询在 SC 表存在成绩的学生信息
select * from student where Sid in
(select Sid from sc);
-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select a.Sid, a.Sname, count, sum from student a left join
(select Sid, count(Cid) count, sum(score) sum from sc group by Sid) b on a.Sid = b.Sid;
-- 查有成绩的学生信息
select * from student where Sid in
(select Sid from sc);
-- 查询「李」姓老师的数量
select count(*) from teacher where Tname like '李%';
-- 查询学过「张三」老师授课的同学的信息
select * from student a left join sc b on a.Sid = b.Sid where b.Cid in (
select b.Cid from teacher a left join course b on a.Tid = b.Tid where a.Tname = '张三'
);
-- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select a.Sid, a.Sname, b.avg from student a join
(select Sid, avg(score) avg from sc group by Sid) b on a.Sid = b.Sid
where a.Sid in (
select Sid from sc where Sid in (
select Sid from (
select Sid, count(cid) count from sc where score < 60 group by Sid
) a where count >= 2
)
);
-- 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select * from student a left join (select Sid, score from sc where Cid = 01) b on a.Sid = b.Sid
where a.Sid in (
select b.Sid from course a left join sc b on a.Cid = b.Cid
where a.Cid = 01 and b.score < 60
) order by score desc;
-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.sid, a.Sname, a.Cname, a.score, b.avg from
(select a.sid, a.Sname, c.Cname, b.score from student a
left join sc b on a.Sid = b.Sid
left join course c on b.Cid = c.Cid) a
join
(select Sid, avg(score) avg from sc group by Sid) b on a.Sid = b.Sid
order by avg desc;
-- 查询各科成绩最高分、最低分和平均分:
# 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
# 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
# 要求输出课程号和选修人数,查询各科成绩最高分询结果按人数降序排列,若人数相同,按课程号升序排列
select a.Cid, a.Cname, max(b.score) max, min(b.score) min, avg(b.score) avg,
concat(round((sum(if(b.score >= 60, 1, 0))/count(*)) * 100, 2), '%') passing_rate,
concat(round((sum(if(b.score >= 70 and b.score <= 80, 1, 0))/count(*)) * 100, 2), '%') moderate_rate,
concat(round((sum(if(b.score >= 80 and b.score <= 90, 1, 0))/count(*)) * 100, 2), '%') good_rate,
concat(round((sum(if(b.score >= 90, 1, 0))/count(*)) * 100, 2), '%') excellent_rate
from course a join sc b on a.Cid = b.Cid group by a.Cid, a.Cname;
-- 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
set @rank=0;
select a.*, @rank:= @rank+1 as rownum from (
select a.Cid, a.Cname, b.score from course a join sc b on a.Cid = b.Cid
order by b.score desc
) a;
SELECT
sid,cid,score,@rank:=@rank+1 as rk
FROM SC,(SELECT @rank:=0) as t
ORDER BY score desc;
select *, RANK()over(order by score desc) rownum from sc;
-- 按各科成绩进行排序,并显示排名, Score 重复时合并名次
set @rank=0;
set @sco=null;
select a.*, if((@sco = score), @rank, @rank := @rank + 1) as rownum, @sco:= score from (
select a.Cid, a.Cname, b.score from course a join sc b on a.Cid = b.Cid
order by b.score desc
) a;
select *, if((@sco = score), @rank, @rank := @rank + 1) as rn,
@sco:= score -- 保存上一次的分数
from SC ,(select @rank:=0,@sco:=null) as t order by score desc;
-- 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
select Sid, sum(score) sum, @rank:= @rank+1 as rownum from sc, (SELECT @rank:=0) as r
group by Sid order by sum desc;
-- 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
select *, rank() over (partition by Sid order by sum(score) desc) from sc;
select Sid, sum(score) sum, rank() over (order by sum(score) desc) rownum from sc group by Sid;
-- 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
select b.Cid, b.Cname,
concat(round(sum(if(a.score <= 100 and a.score >= 85, 1, 0)) / count(*) * 100, 2), '%') as '[100-85]',
concat(round(sum(if(a.score <= 85 and a.score >= 70, 1, 0)) / count(*) * 100, 2), '%') as '[85-70]',
concat(round(sum(if(a.score <= 70 and a.score >= 60, 1, 0)) / count(*) * 100, 2), '%') as '[70-60]',
concat(round(sum(if(a.score <= 60 and a.score >= 0, 1, 0)) / count(*) * 100, 2), '%') as '[60-0]'
from sc a join course b on a.Cid = b.Cid group by b.Cid, b.Cname;
-- 查询各科成绩前三名的记录
select * from (
select *, row_number() over (partition by Cid order by score desc) rownum from sc
) a where rownum <=3;
-- 查询每门课程被选修的学生数
select Cid, count(*) from sc group by Cid;
-- 查询出只选修两门课程的学生学号和姓名
select a.Sid, a.Sname, count(a.Sid) count from student a join sc b on a.Sid = b.Sid
group by a.Sid, a.Sname having count = 2;
-- 查询男生、女生人数
select Ssex, count(*) from student group by Ssex;
-- 查询名字中含有「风」字的学生信息
select * from student where Sname like '%风%';
-- 查询同名同性学生名单,并统计同名人数
select count(*) from (
select Sname, Ssex from student group by Sname, Ssex having count(*) >= 2
) a;
-- 查询 1990 年出生的学生名单
select * from student where year(sage) = '1990';
-- 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select Cid, avg(score) avg
from sc group by Cid order by avg desc, Cid;
-- 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select a.Sid, a.Sname, avg(b.score) avg
from student a join sc b on a.Sid = b.Sid
group by a.Sid, a.Sname having avg > 85;
-- 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select a.Sname, c.Cname, b.score from student a
join sc b on a.Sid = b.Sid
join course c on b.Cid = c.Cid
where c.Cname = '数学' and b.score < 60;
-- 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select a.Sid, a.Sname, c.Cname, b.score from student a
left join sc b on a.Sid = b.Sid
left join course c on b.Cid = c.Cid;
-- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select a.Sname, c.Cname, b.score from student a
join sc b on a.Sid = b.Sid
join course c on b.Cid = c.Cid
where b.score > 70;
-- 查询不及格的课程
select Cid, avg(score) avg from sc group by Cid having avg < 60;
-- 查询课程编号为 01 且课程成绩在 70 分以上的学生的学号和姓名
select b.Sid, b.Sname from sc a join student b on a.Sid = b.Sid where Cid = 01 and score > 70;
-- 求每门课程的学生人数
select Cid, count(*) from sc group by Cid;
-- 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select a.*, b.score from student a
join sc b on a.Sid = b.Sid
join course c on b.Cid = c.Cid
join teacher d on c.Tid = d.Tid
where d.Tname = '张三' order by b.score desc limit 1;
-- 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select a.*, b.score from student a
join sc b on a.Sid = b.Sid
join course c on b.Cid = c.Cid
join teacher d on c.Tid = d.Tid
where d.Tname = '张三' order by b.score desc limit 1;
-- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.cid, a.sid from sc as a
inner join
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
group by cid, sid;
select a.* from sc a join
(select Sid from sc group by Sid, score having count(score) >= 2) b
on a.Sid = b.Sid;
-- 查询每门功成绩最好的前两名
select * from (
select *, ROW_NUMBER()over(partition by Cid order by score desc) rownum from sc
) a where rownum <= 2;
-- 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
select Cid, count(Sid) count from sc group by Cid having count(Sid) > 5;
-- 检索至少选修两门课程的学生学号
select * from student where Sid in (
select Sid from sc group by Sid having count(Cid) >= 2
);
-- 查询选修了全部课程的学生信息
select * from student where Sid in (
select Sid from sc group by Sid having count(Cid) = 3
);
-- 查询各学生的年龄,只按年份来算
select *, year(now()) - year(Sage) from student;
-- 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select * from student where DATE_FORMAT(now(),'%m-%d') > DATE_FORMAT(Sage,'%m-%d');
-- 查询本周过生日的学生
select * from student where week(Sage) = week(now());
-- 查询下周过生日的学生
select * from student where week(Sage) = week(now()) + 1;
-- 查询本月过生日的学生
select * from student where month(Sage) = month(now());
-- 查询下月过生日的学生
select * from student where month(Sage) = month(now()) + 1;