SQL练习题 | 夜雨小屋

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

数据SQL

-- 数据SQL
-- 学⽣表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex
varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙⻛' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');
insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥');
insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥');
insert into Student values('09' , '张三' , '2017-12-20' , '⼥');
insert into Student values('10' , '李四' , '2017-12-25' , '⼥');
insert into Student values('11' , '李四' , '2012-06-06' , '⼥');
insert into Student values('12' , '赵六' , '2013-06-13' , '⼥');
insert into Student values('13' , '孙七' , '2014-06-01' , '⼥');
-- 科⽬表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语⽂' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

练习题目

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

SELECT stu.*,s.score FROM
    student AS stu
    JOIN (
            SELECT
                s1.sid,
                s1.score 
            FROM
                ( SELECT sid, score FROM sc WHERE Cid = 01 ) AS s1
                JOIN 
                ( SELECT sid, score FROM sc WHERE Cid = 02 ) AS s2 ON s1.sid = s2.sid 
            WHERE
                s1.score > s2.score 
    ) AS s ON stu.sid = s.sid;

+------+--------+---------------------+------+-------+
| SId  | Sname  | Sage                | Ssex | score |
+------+--------+---------------------+------+-------+
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |  70.0 |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |  50.0 |
+------+--------+---------------------+------+-------+

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

SELECT s1.sid,s1.score as 01_score,s2.score as 02_score FROM
(SELECT sid,score from sc WHERE cid=01) as s1
JOIN
(SELECT sid,score from sc WHERE cid=02) as s2
on s1.sid = s2.sid;

+------+----------+----------+
| sid  | 01_score | 02_score |
+------+----------+----------+
| 01   |     80.0 |     90.0 |
| 02   |     70.0 |     60.0 |
| 03   |     80.0 |     80.0 |
| 04   |     50.0 |     30.0 |
| 05   |     76.0 |     87.0 |
+------+----------+----------+
5 rows in set (0.00 sec)

3.查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

1	`mysql SELECT s1.sid,s1.score as 01_score,s2.score as 02_score FROM (SELECT sid,score from sc WHERE cid=01) as s1 LEFT JOIN (SELECT sid,score from sc WHERE cid=02) as s2 on s1.sid = s2.sid;`

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

SELECT s2.sid,s1.score as 01_score,s2.score as 02_score FROM
(SELECT sid,score from sc WHERE cid=01) as s1
RIGHT JOIN
(SELECT sid,score from sc WHERE cid=02) as s2
on s1.sid = s2.sid;

5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩

SELECT stu.sid,stu.`sname`,s1.avg_score
from student as stu JOIN 
(SELECT sid,avg(score) as avg_score from sc GROUP BY sid) as s1
on stu.sid = s1.sid
WHERE s1.avg_score >60;

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

1	SELECT distinct stu.* from student as stu,sc WHERE stu.SId=sc.SId

7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

1 2	SELECT stu.SId,stu.Sname,count(sc.SId) as '选课总数',sum(sc.score) as '所有课程的总成绩' from student as stu LEFT join sc on stu.SId = sc.SId GROUP BY stu.SId,stu.sname;

8.查询「李」姓老师的数量

1	SELECT count(*) FROM teacher WHERE tname like '李%'

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

SELECT s1.* FROM 
(SELECT stu.*,sc.CId from student as stu join sc on stu.SId = sc.SId) as s1 
JOIN 
(SELECT teacher.Tname,course.cid FROM course join teacher on course.tid = teacher.Tid) as c1 
on s1.cid = c1.cid WHERE c1.tname = '张三'

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

1
2
3

SELECT stu.* FROM student as stu 
where sid not in 
(SELECT s1.sid FROM (SELECT sid,count(sid) as count_sid FROM sc GROUP BY sid) as s1 WHERE s1.count_sid=3)

11.查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息

SELECT DISTINCT stu.* from 
student as stu JOIN sc on stu.sid = sc.SId 
WHERE sc.CId 
in
(SELECT cid FROM sc where sid=01)

12.查询和” 01 “号的同学学习的课程完全相同的其他同学的信息

SELECT stu.* FROM student as stu JOIN
(SELECT s2.sid FROM sc as s1 JOIN sc as s2 on s1.cid = s2.cid and s1.sid=01 and s2.sid!=01
GROUP BY s2.sid HAVING count(s2.cid)=(SELECT count(*) from sc where sid=01)) as s
on stu.SId = s.sid

13.查询没学过”张三”老师讲授的任⼀门课程的学生姓名

SELECT * from student  
WHERE SId not in 
(SELECT s1.sid FROM 
(SELECT stu.*,sc.CId from student as stu join sc on stu.SId = sc.SId) as s1 
JOIN 
(SELECT teacher.Tname,course.cid FROM course join teacher on course.tid = teacher.Tid) as c1 
on s1.cid = c1.cid WHERE c1.tname = '张三')

14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT stu.sname,stu.sid,s1.avg_score from 
student as stu 
JOIN 
(SELECT sid,AVG(score) as avg_score from sc where score<60 GROUP BY sid HAVING count(*)>=2) as s1 
on stu.sid = s1.sid

15.检索” 01 “课程分数⼩于 60，按分数降序排列的学生信息16.按平均成绩从⾼到低显示所有学生的所有课程的成绩以及平均成绩

1	SELECT * FROM student WHERE sid in (SELECT sid from sc WHERE cid=01 and score<60 ORDER BY score DESC)

16.按平均成绩从⾼到低显示所有学生的所有课程的成绩以及平均成绩

SELECT sc.*,s2.avg_score 
FROM sc  
join (SELECT sid,AVG(score) as avg_score from sc GROUP BY sid) as s2
on sc.sid = s2.sid
ORDER BY s2.avg_score DESC

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

SELECT sc.cid,course.Cname,max(sc.score) as '最高分',min(sc.score) as '最低分',
AVG(sc.score) as '平均分',count(sc.CId) as '选修人数',
SUM(case when sc.score>=60 then 1 else 0 end)/count(sc.CId) as '及格率',
SUM(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(sc.CId) as '中等率',
SUM(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(sc.CId) as '优良率',
SUM(case when sc.score>=90 then 1 else 0 end)/count(sc.CId) as '优秀率'
from sc,course WHERE sc.CId=course.CId 
GROUP BY cid,course.Cname
ORDER BY '选修人数' DESC,sc.cid;

18.按各科平均成绩进⾏排序，并显示排名， Score 重复时保留名次空缺

select s2.cid,s2.avg_sc,count(s1.avg_sc) as rank
from 
(SELECT cid,ROUND(AVG(score),2) as avg_sc from sc GROUP BY cid ) as s1
join
(SELECT cid,ROUND(AVG(score),2) as avg_sc from sc GROUP BY cid ) as s2
on s1.avg_sc>=s2.avg_sc and s1.cid = s1.cid
group by s2.cid, s2.avg_sc
order by rank;

19.按各科平均成绩进⾏排序，并显示排名， Score 重复时不保留名次空缺

1
2
3

SELECT b.cid,b.avg_sc,@i:=@i+1 as rank 
from (SELECT @i :=0) as a,
(SELECT cid,round(avg(score),2) as avg_sc from sc GROUP BY cid ORDER BY avg_sc desc) as b

20.查询学生的总成绩，并进⾏排名，总分重复时保留名次空缺

SELECT s2.sid,s2.sum_sc,COUNT(s2.sum_sc) as rank from
(SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sid) as s1
JOIN 
(SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sid) as s2
on s1.sum_sc>=s2.sum_sc
group by s2.sid,s2.sum_sc
order by rank;

21.查询学生的总成绩，并进⾏排名，总分重复时不保留名次空缺

1
2
3

SELECT b.sid,b.sum_sc,@i:=@i+1 as rank 
from (SELECT @i :=0) as a,
(SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sum_sc desc) as b

22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比

SELECT sc.CId,c.cname,
SUM(case when sc.score>85 and sc.score<=100 then 1 else 0 end) as '[100-85]',
SUM(case when sc.score>85 and sc.score<=100 then 1 else 0 end)/count(sc.CId) as '百分比',
SUM(case when sc.score>70 and sc.score<=85 then 1 else 0 end) as '[85-70]',
SUM(case when sc.score>70 and sc.score<=85 then 1 else 0 end)/count(sc.CId) as '百分比',
SUM(case when sc.score>60 and sc.score<=70 then 1 else 0 end) as '[70-60]',
SUM(case when sc.score>60 and sc.score<=70 then 1 else 0 end)/count(sc.CId) as '百分比',
SUM(case when sc.score>0 and sc.score<=60 then 1 else 0 end) as '[60-0]',
SUM(case when sc.score>0 and sc.score<=60 then 1 else 0 end)/count(sc.CId) as '百分比'
FROM sc join course as c on sc.CId=c.cid
GROUP BY sc.CId,c.cname

23.查询各科成绩前三名的记录

(select CId,score from SC where CId = '01' order by score desc limit 3)
union all
(select CId,score from SC where CId = '02' order by score desc limit 3)
union all
(select CId,score from SC where CId = '03' order by score desc limit 3)

24.查询每门课程被选修的学生数

1	SELECT cid,count(cid) as 选课人数 FROM sc GROUP BY CId;

25.查询出只选修两门课程的学生学号和姓名

SELECT s2.sid,s2.sname FROM
(SELECT sid,count(sid) as '选修课程数'  FROM sc GROUP BY SId) as s1
JOIN student as s2 on s1.SId = s2.SId
WHERE s1.选修课程数=2

26.查询男生、⼥生⼈数

1	SELECT ssex,count(SId) FROM student GROUP BY ssex

27.查询名字中含有「风」字的学生信息

1	SELECT * FROM student WHERE Sname like '%风%'

28.查询同名同姓学生名单，并统计同名⼈数

select sname,count(*) as 人数
from student
group by sname
having count(*)>=2;

29.查询 1990 年出生的学生名单

1	SELECT * FROM student WHERE YEAR(sage)=1990

30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

1	SELECT cid,AVG(score) FROM sc GROUP BY CId ORDER BY AVG(score),CId

31.查询平均成绩⼤于等于 85 的所有学生的学号、姓名和平均成绩

SELECT s1.sid,s1.sname,s2.平均分
FROM student as s1 
join
(SELECT sid,AVG(score) as '平均分' FROM sc GROUP BY sid) as s2
on s1.SId = s2.SId
WHERE s2.平均分>=85

32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT s2.sname,s1.score
FROM
course as c1 JOIN sc as s1 on c1.cid=s1.CId
JOIN student as s2 on s1.sid =s2.SId
WHERE c1.cname='数学' and s1.score<60

33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT s2.sname,s2.sid,c1.cname,s1.score
FROM
 sc as s1
right JOIN student as s2 on s1.sid =s2.SId
left JOIN course as c1 on s1.cid=c1.cid
GROUP BY s2.sid,s2.sname,c1.cname,s1.score

34.查询任何⼀门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT s2.sname,c1.cname,s1.score
FROM
 sc as s1
JOIN student as s2 on s1.sid =s2.SId
JOIN course as c1 on s1.cid=c1.cid
WHERE s1.score>70

35.查询不及格的课程

SELECT s2.sname,c1.cname,s1.score
FROM
 sc as s1
JOIN student as s2 on s1.sid =s2.SId
JOIN course as c1 on s1.cid=c1.cid
WHERE s1.score<60

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

SELECT s1.sid,s2.Sname
FROM
 (SELECT sid,score FROM sc WHERE cid='01') as s1
JOIN student as s2 on s1.sid =s2.SId
WHERE score>=80


select SC.SId,Student.Sname
from SC join Student on SC.SId = Student.SId
where SC.Score > 80 and SC.CId = '01';

37.求每门课程的学生⼈数

1	SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid

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

SELECT s2.sname,s2.SId,s1.score
FROM
course as c1 JOIN sc as s1 on c1.cid=s1.CId
JOIN student as s2 on s1.sid =s2.SId
JOIN teacher as t1 on c1.tid=t1.tid
WHERE t1.tname='张三'
ORDER BY s1.score DESC  LIMIT 1

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

SELECT s2.sname,s2.sid,s1.score FROM student as s2 JOIN sc as s1 on s1.SId =s2.SId
WHERE score=(
SELECT max(score) FROM sc as s1 WHERE cid=(SELECT c1.cid FROM course as c1 JOIN teacher as t1 on c1.tid = t1.tid WHERE t1.tname = '张三'))

-- 其它⽅案
select a.sname,b.score
from student a join sc b on a.sid=b.sid
and b.cid in (select cid from course where tid in (select tid from teacher
where tname='张三'))
join (select cid,max(score) m from sc group by cid) c on b.cid=c.cid and
b.score=c.m;

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

SELECT sid,cid,score FROM sc WHERE sid=(select sid from (select sid,score from sc group by sid,score) as s1
group by sid having count(sid)=1)

-- 其他方案
select distinct s1.SId,s1.CId,s1.Score
from SC s1 join SC s2
on s1.CId != s2.CId and s1.score = s2.score
group by s1.SId,s1.CId,s1.Score

41.查询每门课程成绩最好的前两名

SELECT s1.* FROM sc s1 WHERE
(
SELECT COUNT(1) FROM sc s2 WHERE
s1.cid=s2.cid AND s2.score>=s1.score
)<=2
ORDER BY s1.cid,s1.score DESC;

-- 其他方案
(select CId,score from SC where CId = '01' order by score desc limit 2)
union all
(select CId,score from SC where CId = '02' order by score desc limit 2)
union all
(select CId,score from SC where CId = '03' order by score desc limit 2)

42.统计每门课程的学生选修⼈数（超过 5 ⼈的课程才统计）。

1	SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid HAVING count(cid)>5

43.检索⾄少选修两门课程的学生学号

1	SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid HAVING count(cid)>=5

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

1	SELECT sid FROM sc GROUP BY SId HAVING count(sid)=3

45.查询各学生的年龄，只按年份来算

1	SELECT sname,year(now())-YEAR(sage) as '年龄' FROM student

46.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减⼀

TIMESTAMPDIFF() 从日期时间表达式中减去间隔 https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

select student.sid, student.sname,student.ssex, sage,
timestampdiff(year,sage,now()) as '按月日计算',  
year(now())-year(sage) as '按年份计算'  
from student;

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

返回日期从范围内的数字日历星期1到53

1
2
3

select sid, sname,ssex, sage
from student
WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())

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

1
2
3

select sid, sname,ssex, sage
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1

49.查询本月过生日的学生

1
2
3

select *
from student
where month(student.sage)=month(now());

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

1
2
3

select *
from student
where month(student.sage)=month(now())+1;

本文转自 https://zhuanlan.zhihu.com/p/197840403