经典SQL50题

原题链接：
https://blog.csdn.net/weixin_39337018/article/details/83177627
https://zhuanlan.zhihu.com/p/38354000

自己做了一下。
(14-18,36有难度）

#创建表
create table Student(SId varchar(10) PRIMARY KEY,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' , '女');
create table Course(CId varchar(10) PRIMARY KEY,Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TId varchar(10) PRIMARY KEY,Tname varchar(10));
insert into Teacher values('01' , '张老师');
insert into Teacher values('02' , '李老师');
insert into Teacher values('03' , '王老师');
create table SC(SId varchar(10),CId varchar(10), score decimal(18,1),PRIMARY KEY(SId,CId));
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);

简单查询

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

SELECT count(*) FROM teacher
WHERE Tname like '李%';

22.查询名字中含有「风」字的学生信息
22和5本质一样

SELECT * FROM student
WHERE Sname LIKE '%风%';

21.查询男生、女生人数

SELECT Ssex, count(SId) 人数 FROM student
GROUP BY Ssex;

复杂查询

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT score12.SId, Sname, Sage, Ssex, score1, score2 FROM (
  SELECT a.SId,a.score1, b.score2 FROM (
    select SId, score as score1 from sc WHERE CId='01'
    ) a
    INNER JOIN (
    select SId, score as score2 FROM sc WHERE CId='02'
    ) b
    on a.SId=b.SId
    WHERE a.score1 > b.score2
) score12
LEFT JOIN student
on score12.SId = student.SId;

select * from (
select s1.SId,s1.score class1,s2.score class2 from sc s1
left join sc s2
on s1.SId = s2.SId
where s1.CId = "01" and s2.CId = "02" and s1.score > s2.score
)r
left join student
on r.SId = student.SId;

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

#5条
SELECT a.SId,a.score1, b.score2 FROM (
select SId, score as score1 from sc WHERE CId='01') a
INNER JOIN (                                              # inner join，并集，两者都有
select SId, score as score2 FROM sc WHERE CId='02') b
on a.SId=b.SId;

SELECT * FROM sc t1
LEFT JOIN sc t2
ON t1.SId=t2.SId
WHERE t1.CId='01' and t2.CId='02';

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

# 6条
SELECT a.SId,a.score1, b.score2 FROM (
select SId, score as score1 from sc WHERE CId='01') a
LEFT JOIN (                                                  # left join，【可能】不在b表里
select SId, score as score2 FROM sc WHERE CId='02') b
on a.SId=b.SId;

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

# 1条
SELECT SId, score as score2 FROM sc WHERE CId='02'
and SId not in (select SId from sc WHERE CId='01');

select b.SId,a.score1, b.score2 FROM (
select SId, score as score2 from sc WHERE CId='02') b
LEFT JOIN (                                                  # left join+is null,【必定】不在第二个表里
select SId, score as score1 FROM sc WHERE CId='01') a
on a.SId=b.SId
where a.score1 is null;

以下四题2，13，25，26都是关于平均成绩
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

# 5条
SELECT avg.*, student.Sname FROM (
SELECT SId, AVG(score) as avg_score FROM sc
GROUP BY SId
HAVING avg_score >= 60    # having而不是where,对于groupby之后的结果
) avg
LEFT JOIN student
ON avg.SId=student.SId;

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

SELECT sc.*, avg.avg_score FROM sc LEFT JOIN (
SELECT SId, AVG(score) avg_score FROM sc
GROUP BY SId
) avg
on sc.SId=avg.SId
ORDER BY avg_score DESC;

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

SELECT CId, avg(score) 平均成绩 FROM sc
GROUP BY CId
ORDER BY 平均成绩 DESC, CId;

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

SELECT avgs.SId, student.Sname, 平均成绩 FROM student
RIGHT JOIN (
SELECT SId, avg(score) 平均成绩 FROM sc
GROUP BY SId
HAVING avg(score)>=85
) avgs
on student.SId=avgs.SId;

SELECT sc.SId, student.Sname, avg(score) 平均成绩 FROM sc
LEFT JOIN student
ON sc.SId=student.SId
GROUP BY sc.SId
HAVING avg(score)>=85;

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

SELECT DISTINCT student.* FROM sc
LEFT JOIN student
ON sc.SId=student.SId;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

SELECT student.*, c.`选课总数`, c.`成绩总和` FROM student
LEFT JOIN (
select SId, COUNT(*) 选课总数, sum(score) 成绩总和 FROM sc
GROUP BY SId
) c
on student.SId=c.SId;

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

# 6条
SELECT student.* FROM sc
LEFT JOIN student
on sc.SId=student.SId
WHERE sc.CId in (
SELECT CId FROM course
WHERE TId = (SELECT TId FROM teacher WHERE Tname='张老师')
);

SELECT student.* FROM student
INNER JOIN sc on sc.SId=student.SId
INNER JOIN course on course.CId=sc.CId
INNER JOIN teacher on teacher.TId=course.TId
WHERE teacher.Tname='张老师';

10.查询没学过张老师讲授的任一门课程的学生姓名
6和10完全相反，可以对比

# 6条
SELECT Sname FROM student
WHERE SId not in (
SELECT DISTINCT SId FROM sc
WHERE CId IN (
SELECT CId FROM course
WHERE TId=(SELECT TId FROM teacher WHERE Tname='张老师')
)
);

以下7，39，20，38都非常相似
7.查询没有学全所有课程的同学的信息

# 3条
SELECT student.* FROM (
SELECT SId, count(*) as countc FROM sc
GROUP BY SId
HAVING countc < (SELECT count(CId) FROM course)
) c
LEFT JOIN student
on c.SId=student.SId;

SELECT * FROM student
WHERE SId in (
SELECT SId FROM sc
GROUP BY SId
HAVING count(CId) < (SELECT count(CId) FROM course)
);

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

SELECT student.* FROM student
WHERE SId in (
SELECT SId FROM sc
GROUP BY SId
HAVING count(CId)= (
SELECT count(CId) FROM course
)
);

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

SELECT SId, Sname FROM student
WHERE SId IN (
SELECT SId FROM sc
GROUP BY SId
HAVING COUNT(DISTINCT CId)=2
);

38.检索至少选修两门课程的学生学号
20和38本质一样

SELECT SId, count(CId) FROM sc
GROUP BY SId
HAVING count(CId)>=2;

以下19，32，37都很相似，和以上四题本质一样，只是“课程”和“学生”互换。
19.查询每门课程被选修的学生数

SELECT CId, COUNT(DISTINCT SId) FROM sc
GROUP BY CId;

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

SELECT CId, COUNT(DISTINCT SId) FROM sc
GROUP BY CId;

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

SELECT CId, COUNT(DISTINCT SId) FROM sc
GROUP BY CId
HAVING count(DISTINCT SId)>=5;

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

# 6条
SELECT * FROM student
WHERE SId in (
SELECT DISTINCT SId FROM sc
WHERE CId in (SELECT CId FROM sc WHERE SId ='01') and SId != '01'
);

SELECT DISTINCT student.* FROM student
RIGHT JOIN sc
on student.SId=sc.SId
WHERE sc.CId in (SELECT CId FROM sc WHERE SId ='01') and sc.SId != '01';

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

# 3条
SELECT * FROM student WHERE SId IN (
SELECT SId FROM sc
WHERE CId in (SELECT CId FROM sc WHERE SId ='01') and SId !='01'
GROUP BY SId
HAVING count(CId) = (SELECT count(CId) FROM sc WHERE SId='01')
);

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

SELECT sc.SId, student.Sname, AVG(score) FROM sc
RIGHT JOIN student
ON sc.SId=student.SId
WHERE sc.SId IN (
SELECT SId FROM sc
WHERE score<60
GROUP BY SId
HAVING count(CId)>=2
)
GROUP BY sc.SId;

30.查询存在不及格的课程

SELECT DISTINCT sc.CId, course.Cname FROM sc
INNER JOIN course
ON sc.CId=course.CId
WHERE score<60;

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

SELECT sc.SId, score,student.Sname, student.Sage, student.Ssex FROM sc
LEFT JOIN student
ON sc.SId=student.SId
WHERE sc.CId='01' and sc.score<60
ORDER BY sc.score DESC;

以下两题14和17是分段统计问题。使用函数：CASE WHEN 条件 THEN 值 ELSE 值 END。
14.查询各科成绩最高分、最低分和平均分,以如下形式显示：
课程ID 课程name 课程人数 最高分 最低分 平均分 及格率 中等率 优良率 优秀率
CId Cname number max min mean >=60 70-80 80-90 90-100

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

SELECT
    sc.CId,
    c.Cname,
    COUNT(DISTINCT sc.SId) 课程人数,
    AVG(sc.score) 平均分,
    MAX(sc.score) 最高分,
    MIN(sc.score) 最低分,
    COUNT(CASE WHEN sc.score >= 60 THEN 1 ELSE NULL END) / COUNT(DISTINCT sc.SId) 及格率,
    COUNT(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE NULL END) / COUNT(DISTINCT sc.SId) 中等率,
    COUNT(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE NULL END) / COUNT(DISTINCT sc.SId) 优良率,
    COUNT(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE NULL END) / COUNT(DISTINCT sc.SId) 优秀率
FROM
    sc,
    course c
WHERE
    c.CId = sc.CId
GROUP BY CId
ORDER BY 课程人数 DESC , sc.CId;
————————————————
版权声明：本文为CSDN博主「木公鼠跪鱼」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_39337018/java/article/details/83177627

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

SELECT sc.CId, course.Cname, 
COUNT(CASE WHEN sc.score BETWEEN 85 and 100 THEN 1 ELSE NULL END) count_85_100,
COUNT(CASE WHEN sc.score BETWEEN 85 and 100 THEN 1 ELSE NULL END)/count(sc.SId) per_85_100,
COUNT(CASE WHEN sc.score BETWEEN 70 and 85 THEN 1 ELSE NULL END) count_70_85,
COUNT(CASE WHEN sc.score BETWEEN 70 and 85 THEN 1 ELSE NULL END)/count(sc.SId) per_70_85,
COUNT(CASE WHEN sc.score BETWEEN 60 and 70 THEN 1 ELSE NULL END) count_60_70,
COUNT(CASE WHEN sc.score BETWEEN 60 and 70 THEN 1 ELSE NULL END)/count(sc.SId) per_60_70,
COUNT(CASE WHEN sc.score BETWEEN 0 and 60 THEN 1 ELSE NULL END) count_0_60,
COUNT(CASE WHEN sc.score BETWEEN 0 and 60 THEN 1 ELSE NULL END)/count(sc.SId) per_0_60
FROM sc
LEFT JOIN course
ON sc.CId=course.CId
GROUP BY sc.CId;

为了解决重复计数的问题，可以改条件。

SELECT sc.CId, course.Cname, 
COUNT(CASE WHEN sc.score >=85 THEN 1 ELSE NULL END) count_85_100,
COUNT(CASE WHEN sc.score >=85 THEN 1 ELSE NULL END)/count(sc.SId) per_85_100,
COUNT(CASE WHEN sc.score <85 and sc.score>=70 THEN 1 ELSE NULL END) count_70_85,
COUNT(CASE WHEN sc.score <85 and sc.score>=70 THEN 1 ELSE NULL END)/count(sc.SId) per_70_85,
COUNT(CASE WHEN sc.score <70 and sc.score>=60 THEN 1 ELSE NULL END) count_60_70,
COUNT(CASE WHEN sc.score <70 and sc.score>=60 THEN 1 ELSE NULL END)/count(sc.SId) per_60_70,
COUNT(CASE WHEN sc.score <60 THEN 1 ELSE NULL END) count_0_60,
COUNT(CASE WHEN sc.score <60 THEN 1 ELSE NULL END)/count(sc.SId) per_0_60
FROM sc
LEFT JOIN course
ON sc.CId=course.CId
GROUP BY sc.CId;

以下15，16，18，36是排序问题
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT
    sc.SId, sc.CId, sc.score, tp.ranks
FROM sc
LEFT JOIN
        (SELECT SId,CId,
(SELECT COUNT(DISTINCT sc2.score) + 1 FROM sc sc2
WHERE sc1.CId = sc2.CId
AND sc2.score > sc1.score) ranks
        FROM sc sc1) tp
    ON sc.SId = tp.SId AND sc.CId = tp.CId
ORDER BY sc.CId , ranks
————————————————
版权声明：本文为CSDN博主「木公鼠跪鱼」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_39337018/java/article/details/83177627

或者用开窗函数，但mysql目前不支持。
排名重复时保留名次空缺，对应 RANK() 函数。

SELECT CId, 
      SId, 
      score,
      RANK() OVER(PARTITION BY CId ORDER BY score DESC) 排名 
FROM sc;

16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
用开窗函数。
排名重复时不保留名次空缺，对应 DENSE_RANK() 函数。

SELECT SId, 
       sum(score) 总分, 
        DENSE_RANK() OVER(ORDER BY 总分 DESC) 排名
FROM sc
GROUP BY SId;

18.查询各科成绩前三名的记录
重复分数按顺序排名，用ROW_NUMBER()函数。

SELECT CId, 
        SId, 
       ROW_NUMBER() OVER(PARTITION BY CId ORDER BY score DESC) rank
FROM sc
HAVING rank<=3;

36.查询每门功成绩最好的前两名
18和36本质一样

SELECT CId, 
        SId, 
       ROW_NUMBER() OVER(PARTITION BY CId ORDER BY score DESC) rank
FROM sc
HAVING rank<=2;

原博主的方法：

SELECT CId,SId,score FROM sc
ORDER BY CId,score DESC
SELECT
    a.CId, a.SId, a.score
FROM
    sc a
LEFT JOIN
    sc b
ON 
    a.CId = b.CId
    AND a.score < b.score
GROUP BY a.CId , a.SId
HAVING COUNT(a.CId) < 2
ORDER BY CId , score DESC
————————————————
版权声明：本文为CSDN博主「木公鼠跪鱼」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_39337018/java/article/details/83177627

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

SELECT s1.*, 人数 FROM student s1
RIGHT JOIN (
SELECT Sname, COUNT(SId) 人数 FROM student
GROUP BY Sname
HAVING COUNT(SId) >1
) s2
on s1.Sname=s2.Sname;

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

SELECT student.Sname, s.score FROM student
RIGHT JOIN (
SELECT SId, score FROM sc
WHERE CId = (SELECT CId FROM course WHERE Cname='数学') AND score <60
) s
ON student.SId=s.SId;

SELECT student.Sname, sc.score FROM student
INNER JOIN sc
ON student.SId=sc.SId
INNER JOIN course
ON sc.CId = course.CId
WHERE course.Cname='数学' AND sc.score<60;

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

SELECT student.SId,student.Sname, sc.CId,sc.score FROM student
LEFT JOIN sc
ON student.SId=sc.SId;

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

SELECT sc.SId, student.Sname, course.Cname, sc.score FROM sc
INNER JOIN student ON sc.SId=student.SId
INNER JOIN course ON sc.CId=course.CId
WHERE sc.score>70;

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

SELECT s.SId, student.Sname FROM student
RIGHT JOIN (
SELECT SId FROM sc
WHERE CId='01' AND score>=80
) s
ON student.SId=s.SId;

SELECT sc.SId, student.Sname FROM sc
LEFT JOIN student
ON sc.SId=student.SId
WHERE sc.CId='01' AND sc.score>=80

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

SELECT student.*, sc.score FROM sc
INNER JOIN student
ON sc.SId=student.SId
WHERE sc.CId = (SELECT CId FROM course WHERE TId = (
SELECT TId FROM teacher WHERE Tname='张老师'
)
)
ORDER BY score DESC
LIMIT 1 ;

SELECT student.*, sc.score FROM sc
INNER JOIN student ON student.SId=sc.SId
INNER JOIN course ON sc.CId=course.CId
INNER JOIN teacher ON teacher.TId=course.TId
WHERE teacher.Tname='张老师'
ORDER BY score DESC
LIMIT 1;

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

SELECT student.*, sc.score FROM sc
INNER JOIN student ON student.SId=sc.SId
INNER JOIN course ON sc.CId=course.CId
INNER JOIN teacher ON teacher.TId=course.TId
WHERE teacher.Tname='张老师' AND sc.score = (
SELECT max(score) FROM sc
INNER JOIN course ON sc.CId=course.CId
INNER JOIN teacher ON teacher.TId=course.TId
WHERE teacher.Tname='张老师'
);

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

SELECT s1.score, s1.CId, s1.SId FROM sc s1
LEFT JOIN sc s2
on s1.score=s2.score
WHERE s1.CId!=s2.CId
GROUP BY s1.CId, s1.SId;

和时间相关的查询

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

SELECT * FROM student
WHERE YEAR(Sage)=1990;

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

SELECT SId, Sname, 2020-YEAR(sage) 年龄 FROM student;

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

SELECT SId, Sname, TIMESTAMPDIFF(YEAR,Sage,CURDATE()) 年龄 FROM student;

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

SELECT SId, Sname, Sage FROM student
WHERE WEEKOFYEAR(Sage)=WEEKOFYEAR(CURDATE());

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

SELECT SId,Sname,Sage FROM student
WHERE WEEKOFYEAR(Sage)=WEEKOFYEAR(CURDATE())+1;

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

SELECT SId, Sname, Sage FROM student
WHERE MONTH(Sage)=MONTH(CURDATE());

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

SELECT SId, Sname, Sage FROM student
WHERE MONTH(Sage)=MONTH(CURDATE())+1;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • 经典SQL50题
    二刷经典SQL面试50题，当当，决定将解法重新梳理一遍。从一刷的磕磕绊绊，自己就是常见错误解法，到二刷思维...
    jinghenggl阅读 772评论 0赞 2
  • SQL经典50题
    SQL练习50题 测试表格： 1) Student +------+--------+--------------...
    开嘻嘻阅读 458评论 0赞 0
  • sql 经典50题
    50道SQL练习题及答案与详细分析 https://www.jianshu.com/p/cc23c19ef639[...
    将代码写成诗阅读 2,302评论 0赞 3
  • sql经典50题
    一、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）一刷：excel思路：观察原始表格数...
    西西西瓜君阅读 3,002评论 9赞 2
  • SQL面试经典50题
    SQL语句的执行顺序： 创建school数据库 创建四张表 往表里插值 看下建好的四张表 创建一张总总表 ***1...
    猴小白阅读 36,842评论 21赞 21