经典30道SQL题（含过生日问题的完美解答）

有些题目和前辈们写的一致，有些根据自己的理解进行了调整。

参考：

30sql_26~30_哔哩哔哩_bilibili

 30道SQL经典笔试题及其答案解析_sql笔试题及答案_码渔的博客-CSDN博客

创建数据表

与网上的版本稍有不同，我改了几个数据

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '赵雷' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-26' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-09-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(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),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));
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课程成绩高的学生的学号、姓名
select st.sid, st.sname
from
(select 
     sc.sid, 
     ifnull(sum(case sc.cid when '01' then sc.score end), 0) as c1_scorce, 
     ifnull(sum(case sc.cid when '02' then sc.score end), 0) as c2_scorce
from sc
group by sc.sid
having c1_scorce>c2_scorce and c1_scorce != 0 and c2_scorce != 0) sc
join student st 
on sc.sid = st.sid;

2.查询平均成绩大于60的学生的学号、平均成绩

# 查询平均成绩大于60的学生的学号、平均成绩

select sid  , avg(score)
from sc
group by sid
  having avg(score)>60

3.查询各个学生的学号、姓名、选课数量、总成绩

第一种解法：

# 查询各个学生的学号、姓名、选课数量、总成绩


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

第二种解法：

select t1.sid, t2.sname, t1.num_c, t1.sum_s
from
(
select sid, count(cid) num_c, sum(score) sum_s
from sc
group by sid
) t1
join
student t2
on t1.sid = t2.sid;

4.查询姓“李”的老师个数

# 查询姓“李”的老师个数
select count(1)
from teacher
where tname like '李%';

5.查询没学过“张三”老师的课程的学生的学号、姓名

完美答案：

select sid, sname
from student
where sid not in(
select sc.sid
from course c left join teacher t on c.cid = t.tid
  left join sc on c.cid = sc.cid
where t.tname = '张三');

老师给的写法。但是当一个老师教授多个课程时，则不适用
 参考：30sql_26~30_哔哩哔哩_bilibili

select sid, sname
from 
student 
where sid not in (
select sc.sid
from teacher t
left join course c on t.tid = c.tid
left join sc on sc.cid = c.cid
where t.tname = '张三'
)

6.查询学过01课程和02课程的学生

# 查询学过01课程和02课程的学生
select sid
from sc
where cid in ('01', '02')
group by sid having count(sid) = 2;

7.查询各个课程成绩小于60分的学生的学号、姓名

# 查询各个课程成绩小于60分的学生的学号、姓名

select sid, sname
from student
where sid in (
select sid
from sc
group by sid having max(score) < 60);

8.查询没有学全部课程的学生的学号、姓名

# 查询没有学全部课程的学生的学号、姓名

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

9.查询至少有一门课与学号为“01”的学生所学相同的学生的学号、姓名

# 查询至少有一门课与学号为“01”的学生所学相同的学生的学号、姓名
select distinct s.sid, s.sname
from sc join student s on s.sid = sc.sid
where sc.cid in (select cid from sc where sid = '01');

9拓展.查询至少有两门课与学号为“01”的学生所学相同的学生的学号、姓名

select s.sid, s.sname
from sc left join student s
on s.sid = sc.sid
group by s.sid having 
count(if(sc.cid in (select cid from sc where sid = '01'), 1, null)) > 1;

10. 查询和学号为‘01’的学生学习课程完全相同的其他学生的学号、姓名

第一种：

select s.sid, s.sname
from 
(select cid
from sc
where sid = '01') t1
left join sc on t1.cid = sc.cid
left join student s on sc.sid = s.sid
where s.sid != '01'
group by sc.sid 
having count(sc.cid) = 
(select count(cid)
from sc
where sid = '01');

第二种：使用group_concat实现

select s.sid, s.sname
from 
(select t1.sid
from
(
select sid, group_concat(cid order by cid) c
from sc
group by sid 
) t1
where t1.c = (select group_concat(sc.cid order by sc.cid) from sc where sc.sid = '01')
 )t2 left join student s on t2.sid = s.sid;

11.查询“张三”老师所教课程的平均成绩

select cid, avg(score)
from sc
where cid in
(select c.cid
from teacher t join course c
on t.tid = c.tid
where t.tname = '张三')
group by cid;

12. 查询没有学过“张三”老师所教的任一门课的学生姓名

select s.sname
from student s 
where s.sid not in
(select sc.sid
from teacher t join course c on t.tid = c.cid
join sc on c.cid = sc.cid
where t.tname = '张三');

13. 查询两门及以上课程不及格的学生的学号，姓名、平均成绩

select s.sid, avg(sc.score)
from student s inner join sc on s.sid = sc.sid
group by sid having count(if(score<60,1,null)) > 1;

14. 查询各个课程的课程号、课程名、最高成绩、最低成绩、平均成绩、及格率

select c.cid, c.cname, max(sc.score), min(sc.score), avg(sc.score), count(if(sc.score >60, 1, null))/count(score) pass_rate
from course c join sc on c.cid = sc.cid
group by c.cid ;

15. 查询各个老师的职工号、所教课程的平均成绩、按所教课程的平均成绩降序排列

select t.tid, avg(score) avg_score
from teacher t inner join course c on t.tid = c.tid
inner join sc on c.cid = sc.cid
group by c.cid
order by avg_score desc;

16. 查询各个课程中，成绩排名为第2名、第3名的学生的学号、课程号、课程成绩、排名

注意：由于自己的mysql版本问题，以下使用了rank()函数的语句都没有运行验证，大家可以自行验证

select t1.sid, t1.cid, t1.score , t1.rank_num
from
(select 
sc.sid, sc.cid, sc.score,
rank() over(partition by sc.cid order by sc.score desc) as rank_num
from sc) t1
where t1.rank_num in (2, 3);

17. 查询各个课程在不同成绩区间人数的所占百分比，成绩区间分别为[85-100]、[70-85]、[60-70]、[0-60]

select count(if(score between 85 and 100, 1, null))/count(*) as '85-100',
count(if(score between 70 and 85, 1, null))/count(*) as '70-85',
count(if(score between 60 and 70, 1, null))/count(*) as '60-70',
count(if(score between 0 and 60, 1, null))/count(*) as '0-60'
from sc
group by cid;

18. 查询各个学生的学号、平均成绩、平均成绩的排名

select t1.*, rank() over(order by t1.avg_score desc) as ranking
from
(
select sc.sid, avg(sc.score) as avg_score
from sc
group by sid
) t1;

19.查询各个课程成绩前三名的课程号、学号、成绩排名、课程成绩

select t1.cid, t1.sid, t1.ranking, t1.score
from (
select *, rank() over(partition by cid order by score desc) as ranking
from sc
) t1
where t1.ranking between 1 and 3;

20.查询所有学生中男生数量、女生数量

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

21. 查询姓名中含有“风”字的学生的学号、姓名

select sid, sname
from student
where sname like '%风%';

22. 查询同名同性的学生的姓名、性别、数量

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

23.查询1990年出生的学生的学号、出生日期

第一种方法：

select sid, sage
from student 
where year(sage) = '1990';

第二种方法：

select sid, sage
from student 
where extract(year from sage)= '1990';

24. 查询所有选修“张三”老师所教课程的学生中，课程成绩最高的学生姓名、课程成绩

第一种，不用窗口函数，比较简单。

select s.sname, t1.score
from(
select sc.sid, sc.score
from teacher t
left join course c on t.tid = c.tid
left join sc on c.cid = sc.cid
where t.tname = '张三'
order by score desc
limit 1) t1
left join student s on t1.sid = s.sid;

第二种，用了窗口函数

select s.sname, t1.score
from (
select sc.sid, sc.score, 
rank() over(order by sc.score) as ranking
from teacher t
left join course c on t.tid = c.tid
left join sc on c.cid = sc.cid
where t.tname = '张三')t1 join student s
where t1.ranking = 1;

25. 查询选修了所有课程的学生的学号

select sid
from sc
group by sid 
having count(sc.cid) = (select count(cid) from course);

26. 查询各个学生的学号、年龄

select sid , year(curdate()) - year(sage) age
from student;

27. 查询在本周过生日的学生的学号

select sid
from student
where weekofyear(date_add(sage, interval (year(curdate()) - year(sage)) year))
= weekofyear(curdate());

28.查询在下周过生日的学生的学号

select sid
from student
where weekofyear(date_add(sage, interval (year(curdate()) - year(sage)) year))
= weekofyear(date_add(curdate(), interval 1 week));

29. 查询在本月过生日的学生的学号

select sid
from student
where month(sage) = month(curdate());

30.查询在下月过生日的学生的学号

select sid
from student
where month(sage) = month(date_add(curdate(), interval 1 month));