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

学生表 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

reate 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 "课程成绩高的学生的信息及课程分数

查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
因为需要全部的学生信息，则需要在sc表中得到符合条件的SId后与student表进行join，可以left join 也可以 right join

select * from Student RIGHT JOIN (select t1.SId, class1, class2 from(select SId, score as class1 from sc where sc.CId = '01')as t1, (select SId, score as class2 from sc where sc.CId = '02')as t2where t1.SId = t2.SId AND t1.class1 > t2.class2
)r 
on Student.SId = r.SId;
select * from  (select t1.SId, class1, class2 from(SELECT SId, score as class1 FROM sc WHERE sc.CId = '01') AS t1, (SELECT SId, score as class2 FROM sc WHERE sc.CId = '02') AS t2where t1.SId = t2.SId and t1.class1 > t2.class2
) r 
LEFT JOIN Student
ON Student.SId = r.SId;

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

select * from (select * from sc where sc.CId = '01') as t1, (select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

1.2查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

这一道就是明显需要使用join的情况了，02可能不存在，即为left join的右侧或right join 的左侧即可

select * from 
(select * from sc where sc.CId = '01') as t1
left join 
(select * from sc where sc.CId = '02') as t2
on t1.SId = t2.SId;
select * from 
(select * from sc where sc.CId = '02') as t2
right join 
(select * from sc where sc.CId = '01') as t1
on t1.SId = t2.SId;

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

select * from sc
where sc.SId not in (select SId from sc where sc.CId = '01'
) 
AND sc.CId= '02';

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

这里只用根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可. 注意，这里必须要给计算得到的AVG结果一个alias.（AS ss）
得到学生信息的时候既可以用join也可以用一般的联合搜索

select student.SId,sname,ss from student,(select SId, AVG(score) as ss from sc  GROUP BY SId HAVING AVG(score)> 60)r
where student.sid = r.sid;
select Student.SId, Student.Sname, r.ss from Student right join(select SId, AVG(score) AS ss from scGROUP BY SIdHAVING AVG(score)> 60
)r on Student.SId = r.SId;
select s.SId,ss,Sname from(
select SId, AVG(score) as ss from sc  
GROUP BY SId 
HAVING AVG(score)> 60
)r left join 
(select Student.SId, Student.Sname from
Student)s on s.SId = r.SId;

3.查询在 SC 表存在成绩的学生信息

select DISTINCT student.*
from student,sc
where student.SId=sc.SId

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

select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
group by sc.sid)r
where student.sid = r.sid;

如要显示没选课的学生(显示为NULL)，需要使用join:

select s.sid, s.sname,r.coursenumber,r.scoresum
from ((select student.sid,student.sname from student)s left join (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumberfrom sc group by sc.sid)r on s.sid = r.sid
);

4.1 查有成绩的学生信息

这一题涉及到in和exists的用法，在这种小表中，两种方法的效率都差不多，但是请参考SQL查询中in和exists的区别分析
当表2的记录数量非常大的时候，选用exists比in要高效很多.
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False.
结论：IN()适合B表比A表数据小的情况
结论：EXISTS()适合B表比A表数据大的情况

select * from student 
where exists (select sc.sid from sc where student.sid = sc.sid);
select * from student
where student.sid in (select sc.sid from sc);

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

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

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

select student.* from student,teacher,course,sc
where student.sid = sc.sid and course.cid=sc.cid and course.tid = teacher.tid and tname = '张三';

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

因为有学生什么课都没有选，反向思考，先查询选了所有课的学生，再选择这些人之外的学生.

select * from student
where student.sid not in (select sc.sid from scgroup by sc.sidhaving count(sc.cid)= (select count(cid) from course)
);

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信

这个用联合查询也可以，但是逻辑不清楚，我觉得较为清楚的逻辑是这样的：从sc表查询01同学的所有选课cid--从sc表查询所有同学的sid如果其cid在前面的结果中--从student表查询所有学生信息如果sid在前面的结果中

select * from student 
where student.sid in (select sc.sid from sc where sc.cid in(select sc.cid from sc where sc.sid = '01')
);

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select * from student
wheresid in (select sid from sc t1group by sidhavinggroup_concat(cid ORDER BY cid) = (select group_concat(cid ORDER BY cid) as str2 from scwheresid = '01')and sid != '01');

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

仍然还是嵌套，三层嵌套，或者多表联合查询

select * from studentwhere student.sid not in(select sc.sid from sc where sc.cid in(select course.cid from course where course.tid in(select teacher.tid from teacher where tname = "张三")));
select * from student
where student.sid not in(select sc.sid from sc,course,teacher wheresc.cid = course.cidand course.tid = teacher.tidand teacher.tname= "张三"
);

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

从SC表中选取score小于60的，并group by sid，having count 大于1
(更新采用评论1中的解法)

select student.SId, student.Sname,b.avg
from student RIGHT JOIN
(select sid, AVG(score) as avg from scwhere sid in (select sid from sc where score<60 GROUP BY sid HAVING count(score)>1)GROUP BY sid) b on student.sid=b.sid;

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

双表联合查询，在查询最后可以设置排序方式，语法为ORDER BY ***** DESC\ASC;

select student.*, sc.score from student, sc
where student.sid = sc.sid
and sc.score < 60
and cid = "01"
ORDER BY sc.score DESC;

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

select *  from sc 
left join (select sid,avg(score) as avscore from sc group by sid)r 
on sc.sid = r.sid
order by avscore desc;

14.查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

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

15按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

这一道题有点tricky，可以用变量，但也有更为简单的方法，即自交（左交）
用sc中的score和自己进行对比，来计算“比当前分数高的分数有几个”

select a.cid, a.sid, a.score, count(b.score)+1 as rank
from sc as a 
left join sc as b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;

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

这里主要学习一下使用变量。在SQL里面变量用@来标识。

SELECT 
*,
dense_rank() over (ORDER BY sum_score DESC) AS 排名
FROM (SELECT sid,SUM(score) AS sum_scoreFROM scGROUP BY SId) AS a;

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

分析题目关键词:“课程编号”、“课程名称”、“分数段”、“人数”

第一步:锁定表，成绩表、课程表

第二步:使用CASE WHEN,按照课程编号统计各分数段人数

SELECT
CId,
SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) AS '[0-60]',
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) AS '[60-70]',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) AS '[70-85]',
SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) AS '[85-100]'
FROM sc
GROUP BY CId;

第三步:转化成所占百分比

SELECT
CId,
SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) / COUNT(*) AS '[0-60]',
SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(*) AS '[60-70]',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(*) AS '[70-85]',
SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) / COUNT(*) AS '[85-100]'
FROM sc
GROUP BY CId;

第四步:使用CONCAT拼接，输出百分比符号

SELECT
CId,
CONCAT(SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[0-60]',
CONCAT(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[60-70]',
CONCAT(SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[70-85]',
CONCAT(SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[85-100]'
FROM sc
GROUP BY CId;

第五步:关联课程表，输出课程名称

SELECT a.*,b.Cname
FROM (SELECTCId,CONCAT(SUM(CASE WHEN score >= 0  AND score < 60 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[0-60]',CONCAT(SUM(CASE WHEN score >= 60 AND score < 70 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[60-70]',CONCAT(SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[70-85]',CONCAT(SUM(CASE WHEN score >= 85 AND score <= 100 THEN 1 ELSE 0 END) / COUNT(*)*100,'%') AS '[85-100]'FROM scGROUP BY CId) AS a
LEFT JOIN course AS b
ON a.CId = b.CId;

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

分析题目关键词，“各科成绩”、“前三名”

第一步:各科成绩排名

SELECT 
*,
dense_rank() over (PARTITION BY CId ORDER BY score DESC) AS score_rank
FROM sc;

第二步:筛选前三名

SELECT *
FROM(SELECT *,dense_rank() over (PARTITION BY CId ORDER BY score DESC) AS score_rankFROM sc) AS a
WHERE score_rank <= 3;

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

分析题目的关键词，“每门课程”、“学生数”

第一步:锁定表，成绩表

第二步:按照课程编号进行分组统计

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

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

嵌套查询

select student.sid, student.sname from student
where student.sid in
(select sc.sid from sc
group by sc.sid
having count(sc.cid)=2
);

联合查询

SELECT a.SId,b.Sname
FROM(SELECT SId,COUNT(*) AS CTFROM scGROUP BY SIdHAVING CT = 2) AS a
LEFT JOIN student AS b
ON a.SId = b.SId;

21.查询男生、女生人数

select ssex, count(*) from student
group by ssex;

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

select *
from student 
where student.Sname like '%风%';

23.查询同名同性学生名单，并统计同名人数

找到同名的名字并统计个数

select sname, count(*) from student
group by sname
having count(*)>1;

嵌套查询列出同名的全部学生的信息

select * from student
where sname in (
select sname from student
group by sname
having count(*)>1
);

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

select *
from student
where YEAR(student.Sage)=1990;

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

分析题目关键词:“平均成绩”、“排序”

第一步:锁定表，成绩表

第二步:计算每门课程的平均成绩

SELECT CId,AVG(score) AS avg_score
FROM sc
GROUP BY CId;

第三步:排序(按照平均成绩降序、课程编号升序)

SELECT CId,AVG(score) AS avg_score
FROM sc
GROUP BY CId
ORDER BY avg_score DESC,CId ASC;

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

分析题目关键词:“平均成绩”、“学号”、“姓名”

第一步:锁定表，成绩表、学生表

第二步:分组聚合求出每个学生平均成绩，并筛选出平均成绩大于等于85分的学生

SELECT SId,AVG(score) AS avg_score
FROM sc
GROUP BY SId
HAVING avg_score >= 85;

第三步:关联学生表，获取学生姓名

SELECT a.sid,b.Sname,avg_score
FROM (SELECT SId,AVG(score) AS avg_scoreFROM scGROUP BY SIdHAVING avg_score >= 85) AS a
LEFT JOIN student AS b
ON a.SId = b.SId;

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.sname, cid, score from student
left join sc
on student.sid = sc.sid;

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

select student.sname, course.cname,sc.score from student,course,sc
where sc.score>70
and student.sid = sc.sid
and sc.cid = course.cid;

30.查询不及格的课程


SELECT DISTINCT a.Cid, a.Cname, a.Tid 
FROM course AS a 
INNER JOIN sc AS b 
ON a.Cid = b.Cid 
WHERE b.score < 60;

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

select student.sid,student.sname 
from student,sc
where cid="01"
and score>=80
and student.sid = sc.sid;

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

select sc.CId,count(*) as 学生人数
from sc
GROUP BY sc.CId;

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

分析题目的关键词:“张三老师”、“成绩不重复”、“成绩最高”、“学生信息”、“成绩”

第一步:锁定表，教师表、课程表、成绩表、学生表

第二步:关联4张表

SELECT *
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId;

第三步:增加筛选条件,[张三]老师

SELECT *
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三';

第四步:按照分数排序，只显示第一条记录(limit)

SELECT b.*,score,Tname
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三'
ORDER BY score DESC
LIMIT 1;

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

分析题目的关键词:“张三老师”、“成绩重复”、“成绩最高”、“学生信息”、“成绩”

第一步:锁定表，教师表、课程表、成绩表、学生表

第二步:关联4张表，并筛选出选修[张三]老师所授课程的记录

SELECT *
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三';

第三步:使用dense_rank()进行排名

SELECT 
b.*，a.score,d.Tname,
dense_rank() over (ORDER BY score DESC) AS score_rank
FROM sc AS a
LEFT JOIN student AS b
ON a.SId = b.SId
LEFT JOIN course AS c
ON a.CId = c.CId
LEFT JOIN teacher AS d
ON c.TId = d.TId
WHERE d.Tname = '张三';

第四步:筛选出第1名

SELECT *
FROM(SELECT b.*，a.score,d.Tname,dense_rank() over (ORDER BY score DESC) AS score_rankFROM sc AS aLEFT JOIN student AS bON a.SId = b.SIdLEFT JOIN course AS cON a.CId = c.CIdLEFT JOIN teacher AS dON c.TId = d.TIdWHERE d.Tname = '张三') 
WHERE score_rank = 1;

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

分析题目的关键词:“不同课程”、“成绩相同”

第一步:锁定表，成绩表

第二步:自连接成绩表，连接条件是学生编号相同、课程编号不同、成绩相同

SELECT a.*
FROM sc AS a
INNER JOIN sc AS b
ON a.SId = b.SId AND a.CId != b.CId AND a.score = b.score;

第三步:去重

SELECT DISTINCT a.*
FROM sc AS a
INNER JOIN sc AS b
ON a.SId = b.SId AND a.CId != b.CId AND a.score = b.score;

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

分析题目的关键词:“每门科目”、“成绩最好的前两名”

第一步:锁定表，成绩表

第二步:使用dense_rank()排名

SELECT
*,
dense_rank() over (PARTITION BY CId ORDER BY score DESC) AS score_rank
FROM sc;

第三步:筛选出前两名

SELECT *
FROM (SELECT*,dense_rank() over (PARTITION BY CId ORDER BY score DESC) AS score_rankFROM sc) AS a
WHERE score_rank <= 2;

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

select sc.cid, count(sid) as cc from sc
group by cid
having cc >5;

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

select sid, count(cid) as cc from sc
group by sid
having cc>=2;

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

分析题目的关键词:“选修全部课程”、“学生信息”

第一步:锁定表，学生表、成绩表、课程表

第二步:求出课程表的总课程数

SELECT COUNT(*) FROM course;

第三步:求出每名学生的选修课程数

SELECT SId,COUNT(*)
FROM sc
GROUP BY SId;

第四步:筛选出学习全部课程的学生学生SId

SELECT SId,COUNT(*)
FROM sc
GROUP BY SId
HAVING COUNT(*) = (SELECT COUNT(*) FROM course);

第五步:使用子查询，获取学生信息

SELECT *
FROM student
WHERE SId IN(SELECT SIdFROM scGROUP BY SIdHAVING COUNT(*) = (SELECT COUNT(*) FROM course));

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

SELECT Sid,Sname,YEAR(NOW())-YEAR(sage) 年龄 FROM Student

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

分析题目的关键词:“年龄”、“当前月日<出生年月的月日，则年龄减一”

第一步:锁定表，学生表

第二步:使用NOW()、TIMESTAMPDIFF()函数计算年龄

SELECT *,TIMESTAMPDIFF(YEAR,Sage,NOW()) AS age
FROM student;

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

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE());

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

select *
from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1;

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

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE());

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

select *
from student 
where MONTH(student.Sage)=MONTH(CURDATE())+1;